Area from Vectors

The previous post looked at User Defined Functions (UDFs) for working with vectors, including examples of functions to find the area of polygons defined by coordinates.  In this post the functions will be extended to find other section properties (such as first and second moment of area), and compared with the functions presented previously in Section Properties07.xlsb.  The new functions (including full open source code) can be downloaded from: SecPropV.xlsb.  The new function returns up to 14 values as an array function; refer to Using Array Formulas if you are not familiar with their use.

The code used in the new functions is shown below:


For i = 1 To Num1 - 1
j = i + 1
Vect1(i, 1) = Coords(i, 1)
Vect1(i, 2) = Coords(i, 2)
Vect2(i, 1) = Coords(j, 1)
Vect2(i, 2) = Coords(j, 2)
CrosspA(i) = Vect1(i, 1) * Vect2(i, 2) - Vect1(i, 2) * Vect2(i, 1)
Next i

    k = 1
For i = 1 To Num1 - 1
Area = Area + CrosspA(i)
Next i
Area = -Area / 2

First Moment of Area (about X axis):

For i = 1 To Num1 - 1
AX = AX + CrosspA(i) * (Vect1(i, 2) + Vect2(i, 2))
Next i
AX = -AX / 6

Second Moment of Area (about X axis):

For i = 1 To Num1 - 1
IX = IX + (Vect1(i, 2) ^ 2 + Vect2(i, 2) ^ 2 + Vect1(i, 2) * Vect2(i, 2)) * CrosspA(i)
Next i
IX = -IX / 12

The full list of properties returned by the function is:

Secprop Function Output

The performance of the new function is compared below with the previous version in computing the section properties of a circle defined with 1 million points on the circumference:

Times for section properties for 1 million segment polygon

It can be seen that the new function (red line) has very little advantage for the area function, but the calculation of first and second moment of area is substantially faster. For both functions the calculation of section properties relative to different axes is almost instantaneous.

Details of input are shown below:

Input for SecProp Function

Input XY coordinates must be listed in a 2 column range, with the last point being the same as the first.  Points should be listed in a clockwise direction to return a positive area.  As shown in the example, a hollow shape my be analysed by entering the outer boundary in the clockwise direction, then the inner boundary in the anti-clockwise direction, finally returning to the starting point on the outer boundary along the same line.

If not all the output values are required the number to be calculated may be specified in the optional NumOut argument.  Alternatively, enter -1 list the symbols for each output value in sequence, or -2 for a description of each value.

Finally, if only the area is required, the algorithm taken from the Softsurfer site is considerably faster, at about 0.25 seconds for the 1 million segment circle. An example of the Area2D and Area3D functions is shown below:

Area2D and Area3D Functions

Area = 0
For i = 2 To n - 1
j = i + 1
k = i - 1

Area = Area + PointVals(i, 1) * (PointVals(j, 2) - PointVals(k, 2))
Next i
Area = Area + PointVals(i, 1) * (PointVals(1, 2) - PointVals(i - 1, 2))

Area = -Area / 2
This entry was posted in Arrays, Coordinate Geometry, Excel, Maths, UDFs, VBA and tagged , , , , , , . Bookmark the permalink.

5 Responses to Area from Vectors

  1. have to say this is one of the more interesting Excel blogs i’ve seen yet. Keep up the good work…


  2. Pingback: Daily Download 20: Section Properties | Newton Excel Bach, not (just) an Excel Blog

  3. metroxx says:

    Is it possible to find Xc and Yc for other side?
    I want to find Section modulus: For example Wy=Ixc/Ybar, but, if section not symmetric than I need to find also other Wy and for that I need Ybar for other side.
    Thank you


    • dougaj4 says:

      metroxx – if I understand what you want correctly, the section modulus for the top face is just: Ixc/(H-Ybar), where H is the overall height of the section, perpendicular to the axis of bending. This assumes that the axis of bending is parallel to the X axis.


Leave a Reply

Fill in your details below or click an icon to log in: Logo

You are commenting using your account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s