## 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

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

## RE: Sectional Properties in Excel

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

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

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

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

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

## RE: Sectional Properties in Excel

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

## RE: Sectional Properties in Excel

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

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

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

## RE: Sectional Properties in Excel

## RE: Sectional Properties in Excel

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

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?):

' 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

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

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

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

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

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

## RE: Sectional Properties in Excel

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

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

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

## RE: Sectional Properties in Excel

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

Example:

Doug Jenkins

Interactive Design Services

http://newtonexcelbach.wordpress.com/

## RE: Sectional Properties in Excel

## RE: Sectional Properties in Excel

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

## RE: Sectional Properties in Excel

## RE: Sectional Properties in Excel

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/