3DFrame – 3D Frame analysis for Excel

I have now extended the Frame4 spreadsheet to deal with 3D frames.  As well as making the necessary changes to the input ranges and stiffness matrix generation routines I have revised the Sparse solver routine, added a new compiled solver, and changed to a more efficient VBA solver:

  • In the previous version a full stiffness matrix was generated, which was then converted to hash table format for solving with the sparse solver.  This was not only very inefficient, but also limited the size of problems that could be solved because of the large matrix, containing mostly zero values.
  • For similar reasons the VBA matrix solver routine has been replaced by a much more efficient one taken from “Programming the Finite Element Method” by Smith and Griffiths.
  • The new VBA solver has also been converted to a C# dll, as an alternative to the sparse solver.

The new spreadsheet (including full open-source code) can be downloaded from: 3DFrame.zip

The download zip file includes ALMatrixLib.dll, alglibnet2.dll and ALMatrixLib.tlb which must be installed to use the sparse solver or the compiled solver.  See https://newtonexcelbach.wordpress.com/2012/11/16/frame4-now-with-added-alglib/ for instructions on installing and linking to these files.

Warning:  This software has had only limited testing and is not fully documented.  Do not rely on the output from this spreadsheet for any application where incorrect, ambiguous, or misinterpreted results could have adverse consequences.

Example analyses:

Arch sections rotated about Z axis:

Skew arch units

Skew arch units

The bending moment (due to self-weight) about Beam Axis 2 is the same for each arch:

3DFrame1-2

Bending moment due to self-weight

Torsional moments are very close to zero for all arches:

3DFrame1-3

Torsion moment

Large frame model (2730 nodes and 7065 beams)

3DFrame1-4

Results for 3 strings of columns compared with Strand7 results

3DFrame1-5

Solution times for the three solvers:

Large Frame Solution Time (solver only)

Large Frame Solution Time (solver only)

These times are for solution of the frame stiffness equations only; setting up the matrix and extraction of the results is performed entirely in VBA and took about 10 seconds for the large frame model. Nonetheless the sparse solver gives dramatic reductions to the solution time, which would potentially make non-linear analysis of even very large models practicable with this spreadsheet.

This entry was posted in Beam Bending, Excel, Finite Element Analysis, Frame Analysis, Link to dll, Newton, Strand7, VBA and tagged , , , , . Bookmark the permalink.

19 Responses to 3DFrame – 3D Frame analysis for Excel

  1. napster says:

    Good day sir,
    may I request for a new link for the download? the one I download seems not to open. thank you very much!

    Like

    • dougaj4 says:

      The download file seems OK. Are you using Excel 2003 or earlier? I have added an xls version that should run in 2003 (but I haven’t tested it).

      Or did you have trouble opening the zip file?

      Please let me know.

      Like

  2. Pingback: Daily Download 5: Frame Analysis | Newton Excel Bach, not (just) an Excel Blog

  3. Tom says:

    Hey Doug
    Sorry to be tedious, but the count functions in Input 1 and Input 2 seem to have lost their references in the excel file, so nels, nn, numLoads, go undefined. A quick fix and it works.
    Are you planning on developing the program further, or is it more of an (awesome) academic exercise? I’m going to enjoy playing with it.
    Thanks!

    Like

    • dougaj4 says:

      Tom – thanks for letting me know. I had set the count range in the .xlsb file to the .xls limit, but I must have inserted a row at some stage, so when I saved as an xls it lost the range references. I have uploaded a corrected copy, although I’d suggest using the xlsb version if you can anyway.
      I do intend to keep developing it, although I’d say it was more than an academic exercise as it stands. The main benefit I see in having a frame analysis running in Excel is that it is very easy to set up custom applications using on-sheet formula and/or a bit of VBA.

      Like

  4. Pingback: 3DFrame v1.01 | Newton Excel Bach, not (just) an Excel Blog

  5. Pingback: Frame Analysis with Excel | Newton Excel Bach, not (just) an Excel Blog

  6. Pingback: 6 Year Report | Newton Excel Bach, not (just) an Excel Blog

  7. isidoros says:

    Bro thats insane how long did it take you to program the FEM

    Like

  8. tre826 says:

    Hi
    i am student and i have question
    how can i model roller and pin support in this excel sheet ?

    Like

    • dougaj4 says:

      For learning purposes you might like to look at the 2D version:
      http://interactiveds.com.au/software/Frame4.zip
      For more links see:
      https://newtonexcelbach.wordpress.com/2012/09/21/daily-download-5-frame-analysis/

      In answer to your question, it works much the same in 2D and 3D. In the Node Restraints Table enter F for any direction or rotation that is fixed, and leave any other cell blank. For a roller allowing movement in the X direction, and a pin at Node 1 in 2D that would be:
      Number X Y M
      1, blank, F, blank

      In 3D, for a roller in the X direction, with a pin allowing rotation about the Z axis, and everything else fixed:
      Number, X, Y, Z, MX, MY, MZ
      1, blank, F, F, F, F, blank

      You might like to try a simple example in 2D and 3D and make sure you get the same results.

      Like

  9. bear says:

    Hi,
    Your work is impressive. I am trying the 3DFrame excel and I would like to ask about the theoretical calculation for some guidance.
    for 3D beam elements structure, the main formula is:
    [stiffness matrix]x[displacement]=[force]
    Substitute known force can solve all the displacement of element nodes,
    but how to further calculate the stress of element nodes?

    Like

    • dougaj4 says:

      Did you ask the same question on Eng-tips?

      To solve [stiffness matrix]x[displacement]=[force] you have to invert the stiffness matrix to find the displacements, knowing the applied forces at each node.

      If you know all the displacements at both ends you can find the end forces for any beam from the multiplication [stiffness matrix]x[displacement], where [stiffness matrix] is the 12×12 matrix for the beam you are looking at.

      I’d recommend working in 2D (or even 1D) until you have a good understanding of the process. The principles are exactly the same as 3D, but the matrices are much simpler.
      Have a look at:
      https://newtonexcelbach.wordpress.com/2009/01/31/frame-analysis-with-excel-1-single-beam/

      Like

  10. NaN says:

    Hi,
    Thank you very much for your work.

    Would you have advices/recommendations for a user who would like to modelize “springs” as boundaries conditions ?

    Best regards,

    Like

  11. I appreciate your work .can you please tell me weather a user manual is available or not .

    Like

    • dougaj4 says:

      Not at the moment. For now the best option is to either search the blog for 3D Frame, or select Frame analysis from the View by category list.
      Feel free to ask if you have any specific questions.

      Like

  12. Mark Firkins says:

    Hi .I guess I’m speaking with dougaj4
    I’m Mark Firkins and am interested in using the spreadsheet 3D Frame. I have downloaded and unzipped the files. I am having problems with correctly loading the .dll and .tlb files into the appropriate registry/folder. I have Windows 10 on my laptop which may account for the problem.
    I have taken screen shots of the ‘command’ sequence and error message plus the file listing for RegAsm files on my laptop. If I could email them to you maybe you would be kind enough to take a look.
    Also what is meant by the ‘Sparse’ solver ?
    Regards Mark.

    Like

    • dougaj4 says:

      Hi Mark, my e-mail can be found under “about NewtonExcelBach” at the top of the page.
      I can have a quick look to see if there is anything obvious, but if you haven’t already done so have a look at:
      https://newtonexcelbach.com/2016/11/04/installing-c-dll-files-reminder/
      and the links at the bottom of that page.
      Unfortunately the process of installing the dlls has a number of catches, depending on which software is 32 or 64 bit, so you really need to work through it on your own machine.

      The “sparse” matrix solver converts the stiffness matrix (which is mostly zeros) into a form that stores only the non-zero terms, along with a list of the locations. It allows a much be matrix to be formed and solved, without running into memory overflow problems.

      Like

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 )

Google photo

You are commenting using your Google 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.