Sectional Properties in Excel 2

[KDorka]

Hi everybody,

Im doing a project where i plot co-ordinates of a an arbitrary cross section within Excel and i am able to work out properties such as ixx, iyy etc. I have already seen spreadsheets like this but wondered how it would be possible to create my own? I am new to VBA as well so don't understand much of the code.

I am stuck on the general solution part that would work for any shape

Any assistance would be greatly appreciated

Thanks
Karan

 

[MintJulep]

I would suspect that a general solution would take advantage of the superposition principle and infinitesimally small square elements.

 

[KDorka]

I have looked at the spreadsheet on this link:

http://newtonexcelbach.wordpress.com/2008/03/25/section-properties-of-defined-shapes-spreadsheet/

I am wanting to do the exact same but not yet able to understand how the shapes have been combined or how the co-ordinate data is being read to find sectional properties.

Please could you help.

 

[IDS]

KDorka - NewtonExcelBach is my blog. For your purposes, have a look at the Coords sheet, which has a VBA User Defined Function (UDF) that will return the 14 section properties listed in the example. It is set up to copy shapes from the DefShapes sheet, but don't worry about that, you can just enter coordinates in columns B and C (under X and Y) and the function will update automatically.

To see the code press F11, select the mCoords module in the Project Explorer window on the left, then select SecProp from the drop down list at top right.
The code should be self explanatory, other than the GetArray at the top, which calls another routine, to transpose input data if it is in rows rather than columns. To keep things simple you can replace this with:
xy_range = xy_range.value2
which converts the input data from a range object to an array.

You might like to browse the blog, which has a few posts on getting started with VBA, such as:

http://newtonexcelbach.wordpress.com/2008/02/21/hello-world-in-a-udf/

If you are not familiar with array functions and UDFs you might also like to look at:

http://newtonexcelbach.wordpress.com/using-array-functions-and-udfs-and-following-links/

Any questions, please ask.

Doug Jenkins
Interactive Design Services

http://newtonexcelbach.wordpress.com/

 

[KDorka]

I am not very familiar with VBA, it is all relatively new to me so i dont understand what much of the coding means.
I am however interested in the formulas/equations you have used in order to determine each of the sectional properties.

For example, how have you made a program that allows co-ordinate points to be plotted, and the area being worked out for any shape. This is the basis of the project i am trying to carry out.

If you could provide me with some equations, that would be very helpful.

Thanks in advance

 

[KDorka]

I have managed to find Ixx and Iyy from these equations:

http://bridgeautomation.com/blog/?p=130

Any clarification on how to find Ixy and Ixyc would be greatly appreciated!

 

[cowski]

Perhaps wikipedia can help?

http://en.wikipedia.org/wiki/Second_moment_of_area#Any_polygon

 

[Denial]

You don't actually NEED to use VBA.

You have the (x,y) coordinates of the vertices of a polygon. Traverse the polygon in a consistent direction (preferably anticlockwise). Each edge can be used as part of an elemental area, either a triangle if you subtend it to the origin (or any other convenient constant point), or a trapezium if you drop a pair of perpendiculars to a fixed base line (probably one of the coordinate axes). I have used both approaches successfully, but prefer the triangle approach.

For each of these elemental shapes you know the sectional properties (or can find them in text books, or can work them out). Calculate them and lay them out in tabular form. Then all you have to do is "sum" them, using the parallel axis theorem to allow for their differing CoG locations.

If your vertex coordinates are relative to an origin that is miles away from the vertices (as can happen with some CAD systems, your will need to introduce a new temporary origin that is much closer. Otherwise you risk introducing numerical problems.

 

[IDS]

KDorka - let's take it a step at a time. For the time being if you have a list of coordinates for a shape you can use an XY chart to plot the shape.

My section properties spreadsheet has lots of code which might look difficult, but the Secprop function is all you need for now, and that is very straightforward, and contains all the formulas you need. I have pasted a copy below with some extra comments. Please try it out and let me know if you have any problems:

Function SecProp(xy_range As Variant, Optional Out As Long) As Variant

    Dim XYcells As Variant
    Dim N As Long, NumX As Long
    Dim X1 As Double, X2 As Double, Y1 As Double, Y2 As Double, XD As Double, YD As Double, YSum As Double
    Dim PropArray(1 To 14, 1 To 1) As Double
    Dim Area As Double, Ax As Double, Ay As Double, IXO As Double, IYO As Double, IXYO As Double, Xbar As Double, Ybar As Double
    Dim IXC As Double, IYC As Double, IXYC As Double, IU As Double, IV As Double, Theta As Double, A As Double
    Dim IXYORel As Double, IXYCRel As Double
    Const RelTol As Double = 0.000000000001
    
    xy_range = xy_range.value2  ' Convert the data range to an array.  Access values using X = xy_range(row number, column number)
    NumX = UBound(xy_range)
    

    'Iterate index from 1 to 1 less than number of members
    For N = 1 To NumX - 1
' Get X and Y values for each end of each segment
        X1 = xy_range(N, 1)
        X2 = xy_range((N + 1), 1)
        Y1 = xy_range(N, 2)
        Y2 = xy_range((N + 1), 2)
        XD = X2 - X1
        YD = Y2 - Y1
        YSum = Y2 + Y1
' Calculate section properties
        Area = Area + XD * YSum / 2
        Ax = Ax + XD / 2 * (Y1 ^ 2 + YD * (Y1 + YD / 3))
        Ay = Ay - YD / 2 * (X1 ^ 2 + XD * (X1 + XD / 3))
        IXO = IXO + XD * (Y1 ^ 3 / 3 + YD ^ 3 / 36 + YD / 2 * (Y1 + YD / 3) ^ 2)
        IYO = IYO - YD * (X1 ^ 3 / 3 + XD ^ 3 / 36 + XD / 2 * (X1 + XD / 3) ^ 2)
        IXYO = IXYO - X1 ^ 2 * YD * (Y1 + Y2) / 4 - XD ^ 2 * YD ^ 2 / 72 - XD * YD * (2 * X1 + X2) * (2 * Y2 + Y1) / 18
    Next N
' The remaining section properties are found from the 6 calculated above
    Xbar = Ay / Area
    Ybar = Ax / Area
    IXC = IXO - Area * Ybar ^ 2
    IYC = IYO - Area * Xbar ^ 2
    IXYC = IXYO - Area * Xbar * Ybar
' Check IXYO if IXC and IYC are very nearly equal   
    If IXC > IYC Then IXYORel = IXYO / IXC Else IXYORel = IXYO / IYC
    If Abs(IXYORel) < RelTol Then IXYO = 0
    
    A = ((IXC - IYC) * (IXC - IYC) / 4 + IXYC ^ 2) ^ 0.5
    IU = (IXC + IYC) / 2 + A
    IV = (IXC + IYC) / 2 - A
' Find Theta with check if IXC and IYC are almost equal
    If IXC > IYC Then IXYCRel = IXYC / IXC Else IXYCRel = IXYC / IYC
    If Abs(IXYCRel) > RelTol Then
        Theta = 0.5 * (Atn2((IXC - IYC), 2 * IXYC))
    Else
        IXYC = 0
        Theta = 0
    End If
    Theta = Theta * 180 / dPi
' Copy section properties to the PropArray array
    PropArray(1, 1) = Area
    PropArray(2, 1) = Ax
    PropArray(3, 1) = Ay
    PropArray(4, 1) = IXO
    PropArray(5, 1) = IYO
    PropArray(6, 1) = IXYO
    PropArray(7, 1) = Xbar
    PropArray(8, 1) = Ybar
    PropArray(9, 1) = IXC
    PropArray(10, 1) = IYC
    PropArray(11, 1) = IXYC
    PropArray(12, 1) = IU
    PropArray(13, 1) = IV
    PropArray(14, 1) = Theta

' Return the whole array, or if Out is not equal to zero, return one section property value
    If Out = 0 Then
        SecProp = PropArray
    ElseIf Out > 0 Then
        SecProp = PropArray(Out, 1)
        
    End If

End Function

Copy and paste this code into a VBA module, then anywhere on any worksheet enter:
=SecProp(datarange, 1)
where datarange is the address with the coordinates for your shape. This will return the area. For any other property change the Out value, or for the whole list omit Out (or use 0) and enter the function as an array function (see previous links if you don't know how to do that).

Doug Jenkins
Interactive Design Services

http://newtonexcelbach.wordpress.com/

 

[Denial]

Doug's algorithm is implementing the "trapezium" approach I outlined above, using the x-axis as the "base line".

 

[corus]

In my sheet I don't use VBA code at all but instead use a single line within the spreadsheet. For example Iyy about the original axis is defined as =-SUM((OFFSET(Y,1,0)-Y)*(X+OFFSET(X,1,0))/24*((OFFSET(X,1,0)+X)^2+(OFFSET(X,1,0)-X)^2)). This is then converted to produce the value about the centroid and then principal axis. I don't know if that's any different from the code shown.

 

[KDorka]

I am still relatively new to VBA so cant understand where your xyrange is being defined? Also what do x1, x2, y1, y2, XD, and YD represent?

My program allows a user to specify the number of co-ordinates to be inputted, the VBA code then reads the co-ordinates from a specific row and column number until the number of points is reached. Please advise me on how to go about this as it is the starting point and will help me code further.

Many thanks!

 

[IDS]

Routines in VBA can either be Subs (sub-routines) or Functions. I guess you are familiar with subs, which are run by pressing Alt-F8, or by assigning the sub to a button or a key combination, but not with using functions. One way to use them is to call the function from a sub which reads the coordinate data from the spreadsheet, something like:

Sub SectionPropSub()
Dim xy_range as range, SecPropRes as variant
Set xy_range = Range("range name or address inside quotes")
' Call the function
SecPropRes = SecProp(xy_range)

' Write SecPropRes back to the spreadsheet, using a named range is the easiest way (you need to create the named range before running the sub):
Range("SecPropRange").Value2 = SecPropRes
End Sub.

You can also use a function directly from the spreadsheet, just like a built in function. In this case xy_range is the range you selected when you enter the functions.
See

http://newtonexcelbach.wordpress.com/2008/02/21/hello-world-in-a-udf/

for some simple examples.

As for your other questions, the answers are in the code! (you know that any line starting with a ' is a comment?):

what do x1, x2, y1, y2, XD, and YD represent?

' Access values using X = xy_range(row number, column number)
'Iterate index from 1 to 1 less than number of members
' Get X and Y values for each end of each segment
So x1, x2, y1, y2 are the x and y values for end 1 and end 2 of each segment of the shape. XD and YD are what the code says they are.


Doug Jenkins
Interactive Design Services

http://newtonexcelbach.wordpress.com/

 

[KDorka]

This is the code i have written to find the length of each segment, it gives me correct answers which is good:

Function SectionalProperties()

np = 4

For i = 1 To np
row = 22 + (i - 1)
col = 3
X1(i) = ThisWorkbook.Sheets("TWSAOptions").Cells(row, col).Value
Next i

For i = 1 To np
row = 22 + (i)
col = 3
X2(i) = ThisWorkbook.Sheets("TWSAOptions").Cells(row, col).Value
Next i

For i = 1 To np
row = 22 + (i - 1)
col = 4
Y1(i) = ThisWorkbook.Sheets("TWSAOptions").Cells(row, col).Value
Next i

For i = 1 To np
row = 22 + (i)
col = 4
Y2(i) = ThisWorkbook.Sheets("TWSAOptions").Cells(row, col).Value
Next i

For i = 1 To np
L(i) = ((X2(i) - X1(i)) ^ 2 + (Y2(i) - Y1(i)) ^ 2) ^ 0.5
Next i

For i = 1 To np
row = 22 + (i - 1)
col = 7
ThisWorkbook.Sheets("TWSAOptions").Cells(row, col).Value = L(i)
Next i

End function

 

[KDorka]

These are my declarations:

Option Explicit
Option Base 1
Dim row, col, i As Integer
Dim np As Integer
Dim X1(1 To 1000), Y1(1 To 1000), X2(1 To 1000), Y2(1 To 1000), L(1 To 1000) As Double

 

[IDS]

This is the code i have written to find the length of each segment, it gives me correct answers which is good:

Why do you want the length of each segment?

Also it's easier and much faster to import the data as a variant array and work with that, rather than reading from the spreadsheet for each cell value. It also allows you to change the location of the data range just by redefining a range name, rather than going through the code and changing every hard coded cell offset.

Doug Jenkins
Interactive Design Services

http://newtonexcelbach.wordpress.com/

 

[IDS]

Dim row, col, i As Integer

This declares i as an integer, but row and col as variants. You need to put the "as type" after each variable.

Also use longs, rather than integers. VBA converts them to longs anyway, and declaring as integer just slows down the code.

Doug Jenkins
Interactive Design Services

http://newtonexcelbach.wordpress.com/

 

[KDorka]

My program is for thin-walled structures instead of enclosed structures and so finding the length has been useful when carrying out other calculations. How would i go about creating a variant array?

Also when going to the next step:

For i = 1 To np
SinB(i) = (Y2(i) - Y1(i)) / L(i)
Next i

For i = 1 To np
row = 22 + (i - 1)
col = 11
ThisWorkbook.Sheets("TWSAOptions").Cells(row, col).Value = SinB(i)
Next i

I am getting an error message on the SinB(i) as runtime error 6 (overflow), any ideas?

 

[KDorka]

I get that message sometimes and other times the program runs fine!

 

[IDS]

To create a range object in VBA:
Dim xy_range as range
Set xy_range = Range("range name or address inside quotes")

To convert that into a variant array:
Dim xy_array as variant
xy_array = xy_range.Value2 ' .Value will also work, but .Value2 is quicker

To create the array without creating the range:
Dim xy_array as variant
xy_array = Range("range name or address inside quotes").Value2

Your Sin(I) is probably dividing by zero. You can step through the code in the Visual Basic editor by pressing the F8 key, or set a break point inside the loop (click in the left hand margin) and press F5 to run up to the break point. Look at the value of L(I) at each step.


Doug Jenkins
Interactive Design Services

http://newtonexcelbach.wordpress.com/