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

 

[KDorka]

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

 

[KDorka]

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

 

[KDorka]

Solved it guys!

 

[IDS]

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:

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/

 

[KDorka]

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

 

[KDorka]

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?

 

[corus]

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.

 

[KDorka]

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

 

[IDS]

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/