Updated versions of my 2D and 3D frame analysis spreadsheets are now available for download from:

3DFrame.zip

Frame4.zip

The download files include full open-source VBA code, and dll files for compiled solvers for much better performance with large models. See https://newtonexcelbach.wordpress.com/2012/11/16/frame4-now-with-added-alglib/ for instructions on installing and linking to the dll files. The VBA code does not require any special installation.

Changes common to both spreadsheets are:

- The check for a reference to the compiled solver dll files previously caused the VBA solver to crash if the dll files were not found. This has now been fixed so that the VBA solver will run if the compiled solver files are not installed.
- A check has been added that the resultant reaction forces are equal to the total resultant applied loads.

In addition the following changes have been made to individual spreadsheets:

**Frame4:**

- The three solvers available in 3DFrame (compiled Alglib sparse solver, compiled skyline solver, and VBA solver) are now also available in Frame4.

**3DFrame:**

- Calculation of shear deflections is now supported.
- Member end releases are now supported.
- The plot functions have been updated for easier use and better performance
- Specified support displacements may now be input.
- Members may now be specified as truss elements, transferring axial load only.

Note that specified support displacements are modelled by adding a virtual large stiffness in the direction of the displacement, and this solution method is not compatible with the iterative solution method used in the Alglib sparse solver. For this reason models with specified support displacements should be solved with the compiled solver or the VBA solver.

Examples of input and output using the new features in 3DFrame are shown in the screenshots below:

Support reactions without specified deflections or truss members:

3dFrame results compared with Strand7 results

Change Property Number 3 to truss element (set I1, I2 and J to zero).

Reaction results

Add support displacements

Reaction results

Equilibrium check for Alglib sparse solver used with specified support displacements:

Correct results with compiled solver option:

### Like this:

Like Loading...

*Related*

How about adding a loadcase managment in loads definition? So you can group several loads in a loadcase and switch between results in any loadcases. That would make your program the most useful engenering excel app ;p

LikeLike

Hi. Thank you for your update on the spreadsheet. This is briilliant!

However, I can’t seem to get the files for 3Dframe and Frame4 as I get a “Page not found” error…

Thanks again.

LikeLike

Andre – Thanks for letting me know; now fixed

LikeLike

dougaj4, i am in need of your services if you are available.

nathan

LikeLike

Nathan – If you have any general queries regarding the spreadsheet, please post details here.

If you are looking for more extensive help (for a fee) please contact me on Gmail (dougaj4).

LikeLike

Pingback: Converting from global to local coordinates (and vice versa) | Newton Excel Bach, not (just) an Excel Blog

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

Hi Doug,

I’m trying to use 3DFrame v. 1.03.

I would like to check the model I have made but I cannot understand how to plot it. At least I do not understand why the viewing angles do not work well for me.

I mean the cells B3:B14 at “plot” sheet for which, some of them, do not have variables assigned to them.

Cell B13 for instance is mean to be the “View angle – rotn” but when you click it Excel shows “Deflect_Mag”.

“Center Z” is not assigned to cell B8?

Is there something wrong?

Thank you very much indeed for your attention!

Edson

LikeLike

Edson – the range names need a bit of tidying up, which I’ll do in the next release, but in the current version the names used by the plot routine are:

VertAx: B3

PlotScale: B4

CentXYZ: B6:B8

ViewXYZ: B10:B13

DefScale: B14

If you have those names defined as listed the plot routine should work OK. If you are not getting a correct plot some other things to check are:

Is the correct vertical axis entered in cell B3?

Check the selected data ranges. The node coordinate range should include just the coordinates (not the node numbers). The beam connections range includes the beam numbers. In the next version I will make the selection of the data ranges fully automatic.

Remember that with the Y axis as vertical the Z axis is towards the viewer.

If you are still having problems please let me know with details. If you would like to send a file to dougaj4 at gmail I would be happy to have a look at it.

LikeLike

Any prevision for the next version?

LikeLike

“Real soon now”🙂

There is a lot of overlap with the continuous beam spreadsheet, so what I am doing is tidying up both so they use the same routines wherever possible. I am also modifying the end releases in the frame spreadsheets so you can specify a spring release at a beam end, rather than just on or off. All that is taking longer than I had hoped, but I am making progress on it.

LikeLike

Hi Doug

I have read on this website that the method of analysis used in your spreadsheets is the same as Strand 7, could you please let me know what this method of analysis is called?

Regards

Anthony

LikeLike

Not just Strand7, most FEA programs use essentially the same method. The generic name is the Direct Stiffness Method or Stiffness Matrix Method. See:

http://en.wikipedia.org/wiki/Direct_stiffness_method

LikeLike

Thank you Doug, good work on the spreadsheets, they’re great.

LikeLike

Hi Doug,

This frame analysis programme is excellent.

but I encounter an error message as following when I run it with sparse solver:

Run time error ‘7’

out of memory

I try to debugg it and the error happens at:

ReDim KVsi2(0 To MaxRows, 0 To 1)

My model has 1283 nodes and 2441 elements, I check the value of MaxRows is 50513399.

My computer is win 7 64 bit with 8gb ram, excel is office 2007 version.

please help on this.

LikeLike

Did it work with the compiled solver?

I can’t check it out at the moment, but I am working on a new version that will use the Python sparse solvers which seem to be more stable than the Alglib one, and will also have a more efficient routine for setting up the sparse matrix.

I’ll get a Python version up as soon as I can.

LikeLike

It’s good news that you will further develop this programme.

The error happens before going to solver. It happens in the function sparsekv()

redim array KVsi2 and IndexA requires a lot of memory in my case, KVsi2 is of dimension (50513399 X 2)and IndexA is of (7959 X 6329).

So it runs out of memory.

LikeLike

Jeffrey – the current version doesn’t do anything to minimise the bandwidth so it’s possible that it will run if you re-number the nodes so the maximum difference in node number along any beam is reduced.

This shouldn’t be an issue with the Python version where the sparse matrix will only contain non-zero terms, regardless of the node numbering.

LikeLike

I’m quite expecting your new version.

Hopefully in the new version, also the offset of beam will be included.

Thanks for your excellent programme.

LikeLike

Looking forward to seeing non linear analysis or dynamic analysis, Pdelta etc.

LikeLike

Hi Doug,

This frame analysis programe is excellent.I have a simple question here. While converting Global Loads to local coordinate system . I am using costheta=u.v/UBar.vbar and find the angle. Then resolve loads into axial and perpendicular direction . Once I get perpendicular load how to resolve this load into local major and minor axis of the member. Your help on this is highly appreciated.

Regards,

Chandrakant

LikeLike

Does this help?:

https://newtonexcelbach.wordpress.com/2014/01/08/converting-from-global-to-local-coordinates-and-vice-versa/

LikeLike

NO

LikeLike

I’m tempted to say “I can’t help then”, but that would be about as useful as just saying “NO”.

The link provides functions for converting global forces to local, or the other way round, which is what I thought you wanted to do.

If you are talking about input for my frame programs, this is all global axes anyway. The program converts to the local system.

LikeLike

Hi Doug

Excellent work!! It’s exhilarating to see someone pushing the limits of Excel that far!

Well, I just had the chance to play around for a little while with your spreadsheet. Everything as expected so far with the “usual” frameworks. However, as soon as I try truss elements with I1, I2 and J set to zero, the spreadsheet stopps working and a runtime error appears.

I tried searching your vba code for any clues to no succes as my programming skills are very close to nil.

Could you give any advise what I should best check for?

Thankd for you help and please keep up your fantastic work!

Regards

Chris

LikeLike

Chris – it seems that the truss analysis in the 3D spreadsheet has stopped working. I will need to work through it when I have time. In the mean time, it still works in the 2D version if you apply a moment release to both ends of every beam, or enter a very small (not zero) I value. Note that if you have loads applied anywhere other than node locations the beam end-release method will give better results. Also the frame needs sufficient cross bracing to be stable.

See: https://newtonexcelbach.wordpress.com/2013/02/21/using-frame4-for-truss-analysis/

LikeLike

Doug

Many thanks for the rapid answer! I shall check out your link and try to work my way through your code. I am sure I’ll learn a lot from that.

Best wishes

Chris

LikeLike