Section Properties UDF and UDF charting

The technique for calculating section properties from coordinates is conveniently coded into a UDF:

Function Area(xy_range As Variant) As Double
Dim XYcells As Variant
Dim N As Long, NumX As Long
Dim XD As Double, YSum As Double
XYcells = GetArray(xy_range)
NumX = UBound(XYcells, 1) - LBound(XYcells, 1) + 1
If NumX < 3 Then
 XYcells = Transpose1(XYcells)
NumX = UBound(XYcells, 1) - LBound(XYcells, 1) + 1
 End If
'Iterate index from 1 to 1 less than number of members
  For N = 1 To NumX - 1
  XD = XYcells((N + 1), 1) - XYcells(N, 1)
  YSum = XYcells(N, 2) + XYcells((N + 1), 2)
  Area = Area + XD * YSum / 2
  Next N
End Function

This function, and another (SecProp()) calculating first and second moments of area, and centroid positions, about the X and Y axis may be downloaded from here:
Both functions allow the coordinates to be listed in a vertical or horizontal range, or as an array (surrounded by {}) entered directly in the Function.

SecProp() returns a 14×1 array, which should either be entered as an array formula, or an optional output index may be entered to return a specific section property.

Also included is a UDF (XYChart()) to plot shapes defined by coordinates directly in the cell where the function is entered. To enlarge the chart either increase the cell width or height, or select a range of cells and enter as an array formula.

Note that XYChart makes use of undocumented features, and cannot be guranteed to work in future versions. Also note that it clears the undo stack, so undo will not work if it is entered anywhere in the workbook.

 XYChart is adapted from code by Rob van Gelder, posted at:

Output from Area(), SecProp() and XYChart() are illustrated below:

This entry was posted in Arrays, Charts, Excel, UDFs and tagged . Bookmark the permalink.

6 Responses to Section Properties UDF and UDF charting

  1. Pingback: Two Years Old Today « Newton Excel Bach, not (just) an Excel Blog

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

  3. Frank says:

    I tried to follow the link to Rob van Gelder’s post, but it is broken.

    Do you know a way to scale the in-cell chart so that x and y axes are scaled the same?


  4. dougaj4 says:

    Frank – on the scaling question, have a look at

    I haven’t read it, but it looks like it might be helpful!

    That post also has a link to the Rob van Gelder post, which is also broken. I’ll leave a message with Dick Kusleika.


  5. Fabrice says:

    Frank, don’t reinvent the wheel.

    There’s an Excel add-in for in-cell charting called “Sparklines for excel”.
    The code is open source, so no doubt you’ll find some inspiration there.

    Download at the top of the page



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