Continue to Site

Eng-Tips is the largest engineering community on the Internet

Intelligent Work Forums for Engineering Professionals

Register Log in
  • Congratulations waross on being selected by the Eng-Tips community for having the most helpful posts in the forums last week. Way to Go!

Sectional Properties in Excel 2

Status
Not open for further replies.
KDorka

KDorka

Aerospace
Feb 18, 2014
19
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
 
Replies continue below

Recommended for you

Sort by date Sort by votes
I would suspect that a general solution would take advantage of the superposition principle and infinitesimally small square elements.
 
Upvote 0 Downvote
Upvote 0 Downvote
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:

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

Any questions, please ask.

Doug Jenkins
Interactive Design Services
 
Upvote 0 Downvote
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
 
Upvote 0 Downvote
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.
 
Upvote 0 Downvote
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
 
Upvote 0 Downvote
Doug's algorithm is implementing the "trapezium" approach I outlined above, using the x-axis as the "base line".
 
Upvote 0 Downvote
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.

 
Upvote 0 Downvote
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!
 
Upvote 0 Downvote
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 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?
Click to expand...
' 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
 
Upvote 0 Downvote
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
 
Upvote 0 Downvote
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
 
Upvote 0 Downvote
This is the code i have written to find the length of each segment, it gives me correct answers which is good:
Click to expand...

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
 
Upvote 0 Downvote
Dim row, col, i As Integer
Click to expand...

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
 
Upvote 0 Downvote
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?
 
Upvote 0 Downvote
I get that message sometimes and other times the program runs fine!
 
Upvote 0 Downvote
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
 
Upvote 0 Downvote
Status
Not open for further replies.

Similar threads

BhagyeshVashi
  • Locked
  • Question
Need a rename macro for excel
Replies
11
Views
1K
sdz
sdz
3DDave
  • Locked
  • Question
Python now integrated into Excel - apparently
Replies
8
Views
1K
IDS
IDS
Kenja824
  • Locked
  • Question
excel Formula Find help
Replies
5
Views
1K
Kenja824
Kenja824
jhn1611
  • Locked
  • Question
Compass question excel 4
2 3 4
Replies
62
Views
2K
IDS
IDS
Histor
  • Locked
  • Question
Split sentence in Excel 2
Replies
10
Views
624
Histor
Histor

Part and Inventory Search

Sponsor

Back
Top Bottom