INTELLIGENT WORK FORUMS
FOR ENGINEERING PROFESSIONALS

Log In

Come Join Us!

Are you an
Engineering professional?
Join Eng-Tips Forums!
  • Talk With Other Members
  • Be Notified Of Responses
    To Your Posts
  • Keyword Search
  • One-Click Access To Your
    Favorite Forums
  • Automated Signatures
    On Your Posts
  • Best Of All, It's Free!

*Eng-Tips's functionality depends on members receiving e-mail. By joining you are opting in to receive e-mail.

Posting Guidelines

Promoting, selling, recruiting, coursework and thesis posting is forbidden.

Jobs

Sectional Properties in Excel

Sectional Properties in Excel

(OP)
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

RE: Sectional Properties in Excel

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

RE: Sectional Properties in Excel

(OP)
I have looked at the spreadsheet on this link:
http://newtonexcelbach.wordpress.com/2008/03/25/se...

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.

RE: Sectional Properties in Excel

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/he...

If you are not familiar with array functions and UDFs you might also like to look at:
http://newtonexcelbach.wordpress.com/using-array-f...

Any questions, please ask.

Doug Jenkins
Interactive Design Services
http://newtonexcelbach.wordpress.com/

RE: Sectional Properties in Excel

(OP)
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

RE: Sectional Properties in Excel

(OP)
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!

RE: Sectional Properties in Excel

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.

RE: Sectional Properties in Excel

2
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:

CODE -->

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/

RE: Sectional Properties in Excel

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

RE: Sectional Properties in Excel

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.

RE: Sectional Properties in Excel

(OP)
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!

RE: Sectional Properties in Excel

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/he... 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?):

Quote:

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/

RE: Sectional Properties in Excel

(OP)
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

RE: Sectional Properties in Excel

(OP)
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

RE: Sectional Properties in Excel

Quote:

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/

RE: Sectional Properties in Excel

Quote:

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/

RE: Sectional Properties in Excel

(OP)
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?

RE: Sectional Properties in Excel

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

RE: Sectional Properties in Excel

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/

RE: Sectional Properties in Excel

(OP)
This is some new code i have been writing:

Option Explicit
Option Base 1
Dim row, col, i As Long
Dim seg As Integer
Dim np, ns As Long
Dim X, Y, L As Double
Dim SinB, CosB, Sin2B As Double

Function SectionalProperties()

ReDim X(np)
ReDim Y(np)
ReDim seg(ns, 2)

np = 5
ns = 4

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

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

For i = 1 To ns
row = 22 + (i - 1)
col = 6
seg(ns, 1) = ThisWorkbook.Sheets("TWSAOptions").Cells(row, col).Value
Next i

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

For i = 1 To ns
L(i) = (X(seg(i, 2)) - X(seg(i, 1))) ^ 0.5
Next i

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

RE: Sectional Properties in Excel

(OP)
I get an error for the ReDim seg(ns, 2)

It says expected array but im not sure what that means. I am trying to create a 2D array in which the cells can be read from based on a segment number 'ns' and either columns 1 or 2

RE: Sectional Properties in Excel

(OP)
Solved it guys!

RE: Sectional Properties in Excel

Quote:


Solved it guys!

It's a good idea to tell us how you solved it. It might help someone else.

I hope you declared seg() as double, rather than integer!

Some comments on the code:
1. If you want to declare each variable with a type (which you should), you need to add "as ..." after each variable name, not just at the end of the line. If you don't all but the end variable will be variants.

2. I don't know of any situation where using "integer" is better than "long". Just use long when dealing with integers (and make sure you don't declare as long when it should be double)

3. It is both easier and much quicker to import a range of data into a variant array and work with that, rather than reading the values cell by cell from the spreadsheet (see example below).

4. I prefer to use range names, but if you must use cell addresses and offsets, declare them as constants at the top of the code, so you can change them easily later, without going through your entire code.

5. If you are doing a number of operations on the same range you can use a With, End With block to save some typing:

CODE --> vba

Sub GetRange()
Dim DatArray As Variant, i As Long, j As Long
Const SheetName As String = "Sheet2"
Const RowOff As Long = 0, ColOff As Long = 0
Const NumRows As Long = 10, NumCols As Long = 5

    With Worksheets(SheetName).Range("A1")
        ' Read range data into DatArray
        DatArray = .Offset(RowOff, ColOff).Resize(NumRows, NumCols).Value2
        'Do something with Datarray
        For i = 1 To NumRows
            For j = 1 To NumCols
                DatArray(i, j) = DatArray(i, j) * 2
            Next j
        Next i
        ' Write Datarray back to Sheet2, 10 columns to the right
        .Offset(RowOff, ColOff + 10).Resize(NumRows, NumCols).Value2 = DatArray
    End With
End Sub 

Example:

Doug Jenkins
Interactive Design Services
http://newtonexcelbach.wordpress.com/

RE: Sectional Properties in Excel

(OP)
I forgot to define 'seg' as an array in my declarations

RE: Sectional Properties in Excel

(OP)
Hi Doug, when using the following co-ordinates, im not sure the results are correct?

x y
1.037181486 0.001523559
1.035589476 0.001828478
1.030824855 0.002737012
1.022915625 0.00423568
1.011910532 0.006300626
0.997878028 0.008900735
0.980902121 0.011998673
0.961087561 0.015548803
0.938556732 0.019504455
0.913446535 0.023813772
0.885911505 0.028421785
0.856120696 0.0332756
0.824256644 0.038315063
0.790515368 0.043485206
0.755105334 0.048724837
0.718242267 0.053973803
0.680153301 0.0591678
0.641072828 0.064240452
0.601239392 0.069124344
0.560899832 0.073747914
0.520298912 0.078036488
0.479687621 0.081918503
0.439313835 0.085317211
0.399426468 0.088163123
0.360269248 0.090388825
0.322081753 0.091934164
0.285029926 0.092746245
0.248925009 0.092653939
0.214015913 0.091388628
0.180659417 0.088761553
0.149225777 0.084682481
0.120081106 0.079168008
0.093567659 0.072331181
0.069988281 0.064371131
0.049592923 0.055548181
0.032565159 0.046158953
0.019024259 0.036502142
0.009024155 0.026857777
0.002562773 0.017459215
0.000414856 0.008474471
0 0
0.003612359 -0.007672762
0.010206495 -0.014312532
0.019632023 -0.020002282
0.031736484 -0.024856097
0.046381938 -0.029004657
0.063449114 -0.032585902
0.082844669 -0.035733622
0.104508449 -0.038569162
0.128411415 -0.041190015
0.154545269 -0.043667743
0.182912085 -0.046035533
0.213508752 -0.04827783
0.246311416 -0.050324107
0.281258885 -0.052037462
0.318162401 -0.053211505
0.356376861 -0.053729038
0.395598384 -0.053620138
0.435581169 -0.05293044
0.476079411 -0.051714912
0.516841088 -0.050031634
0.557613135 -0.047939722
0.598144567 -0.045500369
0.638183356 -0.042772691
0.677480591 -0.039815805
0.715794617 -0.036685716
0.752886892 -0.033433245
0.788528208 -0.030112323
0.822498691 -0.026770658
0.854587803 -0.023458033
0.884597449 -0.020219044
0.912340944 -0.017103476
0.93764716 -0.014155924
0.960359489 -0.011422023
0.980336879 -0.00894637
0.997454875 -0.006769413
1.011607687 -0.004929526
1.022708197 -0.003461973
1.030685878 -0.002392682
1.035493022 -0.001742395
1.037098514 -0.001523559
1.037181486 0.001523559

I get a negative area, Ixx and and Iyy. Surely these values cannot be negative?

RE: Sectional Properties in Excel

A negative area means you have defined the section in the wrong sense, ie. either clockwise or anticlockwise. Try reordering the co-ordinates so that the section is defined in the opposite sense.

RE: Sectional Properties in Excel

(OP)
Thanks for your response, i tried reversing the co-ordinates in a clockwise direction and i got the correct results

Red Flag This Post

Please let us know here why this post is inappropriate. Reasons such as off-topic, duplicates, flames, illegal, vulgar, or students posting their homework.

Red Flag Submitted

Thank you for helping keep Eng-Tips Forums free from inappropriate posts.
The Eng-Tips staff will check this out and take appropriate action.

Reply To This Thread

Posting in the Eng-Tips forums is a member-only feature.

Click Here to join Eng-Tips and talk with other members!


Resources


Close Box

Join Eng-Tips® Today!

Join your peers on the Internet's largest technical engineering professional community.
It's easy to join and it's free.

Here's Why Members Love Eng-Tips Forums:

Register now while it's still free!

Already a member? Close this window and log in.

Join Us             Close