Dividing shapes into layers

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:

Functions included in Section Properties-Layers

Functions included in Section Properties-Layers

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.


This entry was posted in Coordinate Geometry, Excel, Maths, UDFs, VBA and tagged , , , , , . Bookmark the permalink.

8 Responses to Dividing shapes into layers

  1. Interesting. Really appreciate this. Thanks for sharing.


  2. Pingback: Reinforced Concrete – ULS capacity under combined axial load and biaxial bending | Newton Excel Bach, not (just) an Excel Blog

  3. andre says:

    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?


    • dougaj4 says:

      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.


  4. andre says:

    Thanks for update.


  5. metroxx says:

    When try to open biax spreadsheet got error in module mCoords Compile Error: Type mismatch.
    (dPi As Double =)


Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s