Update to Glob_to_loc3 and Loc_to_glob3 functions

Following a comment here I have updated the Glob_to_loc3 and Loc_to_glob3 functions to deal with some problems when the local longitudinal axis is parallel to the global Y or Z axes.  The IP2 spreadsheet, including the revised functions, can be downloaded from:


The screen shots below show documentation for the functions, and an example of results with a beam parallel to the Z axis:

The documentation has been edited to clarify the difference between the axis type options:

The same example is used for both functions, with a beam parallel to the global Z axis, with the direction from Node 1 to Node 2 in the negative Z direction:


The function also has an option to return the rotation matrix, R0 (Out = 1):

The download file has the input for the Glob_to_loc function linked to the Glob_to_loc3 input, and the associated Gamma angle, to show that the results are consistent.  The input for these (or any other example functions) may of course be freely modified.

This entry was posted in Coordinate Geometry, Excel, Finite Element Analysis, Frame Analysis, Newton, UDFs, VBA and tagged , , , , , , . Bookmark the permalink.

6 Responses to Update to Glob_to_loc3 and Loc_to_glob3 functions

  1. Soren Hauch says:

    Thank you for all the good work presented on this site. I have been using the IP2 code successfully, but I am looking into expanding it from 3 data points to fit a cluster of 3D points. I found the below linked site providing exactly that, but programmed in Python. Would there be an interest in converting this code into VBA (Way above my programming skills)?


    Note to IP2: If the 3 data points are on a straight line then AC^2 – AD^2=0 and the program returns an error message for the code line: CD = (AC^2 – AD^2)^0.5


    • dougaj4 says:

      Hi Soren, thanks for the link, looks good. I doubt if I will have time to convert to VBA, but I will look at running it from Excel via xlwings.


  2. Soren Hauch says:

    That would be awesome! I am looking forward to trying it out 🙂


  3. Pingback: Fitting 3D circles to scattered points | Newton Excel Bach, not (just) an Excel Blog

  4. Soren Hauch says:

    Thanks dougaj4, this works great! I installed the Anaconda python package as recommended and the spreadsheet worked flawlessly and it ran fast. I did a few additional verification runs against IP2 – ArcCenP3 and the results came out spot on 🙂 Thanks again 🙂


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 )

Connecting to %s

This site uses Akismet to reduce spam. Learn how your comment data is processed.