For reasons that will become apparent in a later post, I need to rotate irregular polygons, defined by the coordinates of each corner, by a specified angle, and then split the resulting shape into horizontal trapezoidal layers. I have written an Excel User Defined Function (UDF) to perform this task, and incorporated it with some section properties functions. The spreadsheet (including full open source code) can be downloaded from Section Properties-Layers.
A summary of the functions provided in the spreadsheet is shown in the screenshot below:
The screenshot below shows coordinates defining the cross section of a precast bridge girder (Columns B,C), section properties calculated from coordinates (Column F), coordinates for the section rotated about the centroid by 30 degrees (Columns H, F), and section properties for the rotated shape calculated from the trapezoidal layers (Column K) and directly from coordinates (Column L):
Output from the function splitting the section into layers is shown below:
The chart below shows the rotated section plotted directly from coordinates, overlain with the trapezoidal layers generated by the HIP_2 function:
The second sheet (Func Index) provides details of the included UDFs, listing input and output for each.
All of the included functions return results as an array. See Using Array Functions and UDFs for details of how to display the full function output, if you wish to use them elsewhere.
Interesting. Really appreciate this. Thanks for sharing.
Reblogged this on SutoCom Solutions.
Pingback: Reinforced Concrete – ULS capacity under combined axial load and biaxial bending | Newton Excel Bach, not (just) an Excel Blog
Thanks for your work.
I get debug error after changing coordinates to the simple rectangular section.Obviously I have less coordinates in new shape. Error occure because range of “Rotated coordinates” is bigger than “XYRange” (array formula with “RotateC” function stay in cells from previous section where were more coordinates). So I have to clear old cells for “Rotated coordinates” and again put array function by Ctrl-Shift-Enter with function “rotatec” in smaller range. Clearing cells with “Rotated coordinates” is simple by VBA but is it possible to put array function using VBA not Ctrl-Shift-Enter? Or maybe I have to put new formula by changing “Result” index in “RotateC(Rcoords, Rotation, RotnPt, Axis, Result)” function?
Thanks for the feedback.
I have modified the SecProp and HIP_2 functions so that they check the number of rows with numeric data, and ignore all rows after that. This means you get a lot of rows displaying #NA if you enter a rectangle or other simple shape, but at least they give the right answer.
I will probably modify the routines to use VBA subs, rather than UDFs, so I can adjust the ranges as required, but that might be some time off.
You can download the updated spreadsheet from the original link.
Thanks for update.
When try to open biax spreadsheet got error in module mCoords Compile Error: Type mismatch.
(dPi As Double =)
Previous comment was for these thread, Sorry: