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:

http://www.interactiveds.com.au/software/SecProps-array.zip

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: http://www.dailydoseofexcel.com/archives/2006/02/05/in-cell-charting/

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

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

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

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?

LikeLike

Frank – on the scaling question, have a look at http://www.dailydoseofexcel.com/archives/2006/09/13/scaled-in-cell-charting/

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.

LikeLike

Thanks! I’ll take a look and let you know how it turns out.

LikeLike

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.

Sparklines-excel.blogspot.com

Download at the top of the page

Cheers

LikeLike