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

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.

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/

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

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!

Perhaps wikipedia can help?
http://en.wikipedia.org/wiki/Second_moment_of_area...

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.

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:


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/

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

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.

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!

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/

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

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

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/

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/

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?

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

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/

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

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

Solved it guys!

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:


Example:

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

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

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?

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.

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

Yes, coordinates should be listed clockwise, or anti-clockwise if you want to generate a void inside a solid shape.

I have attached the output from your coordinates, re-ordered in a clockwise direction, as a check for your results.

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

• http://files.engineering.com/getfile.aspx?folder=f8dce895-2b8c-42dc-a70c-a8