Converting a formula to values

Excel has a built-in function (FormulaText) to display a formula as cell references, rather than the result value.  It would be useful to be able to display the value of each cell reference as well, but there is no built-in function to do that.

It turns out it’s not easy to do with VBA either.  I have added a macro to do the job to the Eval2 spreadsheet:

Sub Addr2Val()
Dim PrecCells As Variant, CellRng As Range, NumP As Long, i As Long, Form As String
Dim CellAddr() As String, CellVal() As Double, xCell As Range, OutCols As Long

    Set CellRng = Application.ActiveCell
    Form = CellRng.Formula
    Form = Replace(Form, "$", "")
    Set PrecCells = CellRng.Precedents
    NumP = PrecCells.Count
    ReDim CellAddr(1 To NumP, 1 To 1)
    ReDim CellVal(1 To NumP, 1 To 1)
    i = 1
    For Each xCell In PrecCells
        CellAddr(i, 1) = xCell.Address
        CellAddr(i, 1) = Replace(CellAddr(i, 1), "$", "")
        CellVal(i, 1) = xCell.Value
        i = i + 1
    Form = Eval(Form, CellAddr, CellVal, 0)
    OutCols = Selection.Columns.Count
    If OutCols > 1 Then OutCols = OutCols - 1
    CellRng.Offset(0, OutCols).Value = " " & Form
    Set CellRng = Nothing
    Set PrecCells = Nothing
End Sub

The macro uses the Range.Precedents method to return the address and value of each cell reference in the formula, then calls the Eval user defined function (UDF) to convert the cell addresses in the formula to their numerical values.  It would be convenient to put this in a function, which could be called from the spreadsheet, but when entered in a UDF the .Precedents method does not return information on the cell precedents, it returns the information for the cell itself.  If anyone has any suggestions for converting the macro to a working UDF, please leave a comment.

The macro has been set up to return the results in the cell to the right of the selected cell, or if three or more columns are selected, in the top right cell of the selected range.  Note that the macro will write over  any contents in the results cell.

An example of the macro in use (with instructions) is shown in the screen-shot below:

The updated spreadsheet, including full open-source code and the example above, can be downloaded from:


Posted in Excel, UDFs, VBA | Tagged , , , | 1 Comment

Excel to Alglib via xlwings

The Alglib library provides many valuable numerical analysis routines, but the VBA version is no longer actively maintained.  The latest versions are available in C++, C#, and Python, of which the Python version (in conjunction with xlwings) provides the easiest interface with Excel.

An Alglib/Python based spreadsheet has previously been presented here.  I have now updated this to the latest versions of xlwings and Alglib, added additional solver and linear algebra functions, and updated the Python code to make better use of the xlwings auto-import functionality, which generates the necessary VBA code at the touch of a button.  The new spreadsheet can be downloaded from:

As usual, the download files include full open source code.

To install the necessary files:

  1. Install Python, either version 2 or 3.  The Anaconda Python package will also install xlwings, but see below.
  2. Install or update to the latest version of xlwings (0.11.04 at the time of writing).  If the automatic install delivers an earlier version, see here.
  3. Install Alglib.
  4. Copy the contents of the xlAlglib zip file to any convenient location.
  5. Any problems, please let me know.

The screenshots below show the functions available in the new version.  Future posts will look in more detail at specific examples, and procedures for modifying the Python code.

List of functions:

Spline Functions:

Parametric Splines:

Rational Interpolation Function:

2D and 3D Interpolation:

Fit a spline to scattered data:

Polynomial fitting:

Non-Linear Fitting:

Fit Polynomial to scattered data:

Radial Basis Function Interpolation:

Levenberg-Marquardt optimisation:

Integration of Cubic Splines:

Differentiation of Cubic Splines:

Matrix algebra:

Posted in AlgLib, Curve fitting, Excel, Link to Python, Maths, Newton, Numerical integration, UDFs, VBA, xlwings | Tagged , , , , , , , , , | 1 Comment

Installing xlwings 0.11.4

The 0.11 versions of xlwings have some major changes to the arrangement of the required files, which will make installation and updating much easier, but for some reason Anaconda is very slow to upload the new versions, and the pdf documentation has some omissions, so here is a summary of procedures for downloading and installing the latest version.

Finding the latest version

The xlwings web site shows the latest version.  The source code may be downloaded from the GitHub page, but for easier installation use pip or conda:

pip install xlwings or conda install xlwings

The main conda install is still on version 0.10 however, so for those using the Anaconda Python package, and wanting to stick with conda for updates use:

conda install c condaforge xlwings

Install the add-in

  1. If you have the old xlwings addin installed, find the location and remove it or overwrite it with the new version (see next step). If you installed it via the xlwings command line client, you should be able to do: xlwings addin remove.
  2. Close Excel. Run xlwings addin install from a command prompt. Reopen Excel and check if the xlwings Ribbon appears. If not, copy xlwings.xlam (from your xlwings installation folder under addin\xlwings.xlam manually into the XLSTART folder. You can find the location of this folder under Options > Trust Center > Trust Center Settings… > Trusted Locations, under the description Excel default location: User StartUp. Restart Excel and you should see the add-in.

Upgrade existing workbooks

  1. Make a backup of your Excel file
  2. Open the file and go to the VBA Editor (Alt-F11)
  3. Remove the xlwings VBA module
  4. Add a reference to the xlwings addin, see Installation
  5. If you want to use workbook specific settings, add a sheet xlwings.conf, see Workbook Settings

For more details see the xlwings documentation.  For the current version (0.11.4) the pdf documentation has the “Migrate to Version 0.11” and “installation” sections missing.  This will be fixed in future versions, but for now use the on-line docs.

Posted in Excel, Link to Python, VBA, xlwings | Tagged , , | 1 Comment

Using the Frame Analysis Spreadsheets

Following some recent questions in the comments, here are some answers:

How can nodal moments be created?

The current versions only allow for forces to be applied to beams, but any point load or moment applied at the start or end of a beam will in effect be applied at the node, so set “Position” to zero for any beam starting at the node, or to the beam length for a beam ending at the node.  In Frame 4 (the 2D version) all moments are applied about the Z axis.  In 3DFrame moments are applied about the global axis specified in “Direction XYZ” (Column C).

How can hinge or spherical joints, or bushing joints be created?

Both Frame4 and 3DFrame allow either end of a beam to be given a rotational spring stiffness.  If the stiffness is set to a very low value the beam ends will in effect be free to rotate.  In Frame4 rotations are about the Z axis.  In 3DFrame the rotations are relative to the beam principal axes, which are described here:  3D Frames, axes and stiffness matrices.

3DFrame also allows beams ends to be allocated a translational spring release along any of the three principal axes.

The example frame in each of the download files illustrates the use of the beam end springs.

How can multiple point restraints be applied?

Nodes can in effect be restrained to move together by connecting with very stiff members, with any required end hinge conditions, to provide the required restraint.


Posted in Excel, Finite Element Analysis, Frame Analysis, Newton, VBA | Tagged , , , , | Leave a comment

Installing Frame4 and 3DFrame

Following a recent comment I have added a VBA only version to the download files of the frame analysis spreadsheets:

The VBA versions should work on any version of Excel from 2007, with macros enabled, without any further installation.

The standard versions link to compiled versions of the solver code, that provide much better performance with large frames.  The download file includes the required dll files, and the installation process is summarised here:

Installing C# dll files, reminder

Any problems, please leave a message here.


Posted in AlgLib, Excel, Frame Analysis, Link to dll, Newton, VBA | Tagged , , , , , , | Leave a comment

Tam Lin

Tam Lin is a Scottish folk tale and song dating from 1549 or earlier. It is set at Carterhaugh (not Carter Hall), which is a real place.


The song was widely re-interpreted in the British folk revival, most notably by Fairport  Convention in their 1969 album Liege and Lief.  The recording below is an excellent live version recorded for the BBC’s Peel Sessions:

The song was also recorded by The Pentangle (for a 1972 film that somehow entirely escaped my attention at the time), but not released until many years later.  The You Tube video has been viewed just 52 times up to today:

Finally a very different version from Steelye Span, which sounds better each time I listen:

Lyrics of the Fairport Convention version:

“I forbid you maidens all that wear gold in your hair
To travel to Carter Hall for young Tam Lin is there

None that go by Carter Hall but they leave him a pledge
Either their mantles of green or else their maidenhead”

Janet tied her kirtle green a bit above her knee
And she’s gone to Carter Hall as fast as go can she

She’d not pulled a double rose, a rose but only two
When up there came young Tam Lin says “Lady, pull no more”

“And why come you to Carter Hall without command from me?”
“I’ll come and go”, young Janet said, “and ask no leave of thee”

Janet tied her kirtle green a bit above her knee
And she’s gone to her father as fast as go can she

Well, up then spoke her father dear and he spoke meek and mild
“Oh, and alas, Janet,” he said, “I think you go with child”

“Well, if that be so,” Janet said, “myself shall bear the blame
There’s not a knight in all your hall shall get the baby’s name

For if my love were an earthly knight as he is an elfin grey
I’d not change my own true love for any knight you have”

Janet tied her kirtle green a bit above her knee
And she’s gone to Carter Hall as fast as go can she

“Oh, tell to me, Tam Lin,” she said, “why came you here to dwell?”
“The Queen of Faeries caught me when from my horse I fell

And at the end of seven years she pays a tithe to hell
I so fair and full of flesh and feared it be myself

But tonight is Hallowe’en and the faery folk ride
Those that would their true love win at Miles Cross they must buy

So first let past the horses black and then let past the brown
Quickly run to the white steed and pull the rider down

For I’ll ride on the white steed, the nearest to the town
For I was an earthly knight, they give me that renown

Oh, they will turn me in your arms to a newt or a snake
But hold me tight and fear not, I am your baby’s father

And they will turn me in your arms into a lion bold
But hold me tight and fear not and you will love your child

And they will turn me in your arms into a naked knight
But cloak me in your mantle and keep me out of sight”

In the middle of the night she heard the bridle ring
She heeded what he did say and young Tam Lin did win

Then up spoke the Faery Queen, an angry queen was she
Woe betide her ?ill-fought? face, an ill death may she die

“Oh, had I known, Tam Lin,” she said, “what this knight I did see
I have looked him in the eyes and turned him to a tree”

Read more: Fairport Convention – Tam Lin Lyrics | MetroLyrics

Posted in Bach | Tagged , , , | Leave a comment

Stress increments in prestressed concrete beams

The two main Australian Standards covering prestressed concrete structures (AS 3600, Concrete Structures, and AS 5100 Part 5, Concrete Bridges) put a limit on the allowable increase in stress in reinforcement and prestressing strands and tendons, when the load increases from the moment that results in zero stress at the tensile concrete face to the maximum moment under Serviceability Limit State loads.

The current versions of my beam design spreadsheets do not directly provide the value of this stress increment, but it is easy to calculate, using the Excel Goal Seek function.

The examples below can be downloaded from:

Composite Design Functions-Stress

The screen shots below show an example using the Beam Design Functions spreadsheet, with a standard pre-tensioned “Super-T” bridge beam:

To find the reinforcement stresses when the stress at the bottom concrete face is zero, open the Goal Seek dialog (under the Data tab) and enter:

  • Set cell = C10 (Depth of Neutral Axis)
  • To value = 1800 (Overall depth of the section)
  • By changing cell = A6 (Applied Moment)

Click OK, then OK again when Goal Seek has found the moment (in cell A6) that results in a Neutral Axis depth of 1800 mm (in cell C10):

The resulting reinforcement stresses are found on the “Elastic 1 Out” sheet, in Column C (for the top and bottom layers) and Column L (for all layers).  This data can be displayed on the “Elastic1 Input” sheet with a simple formula: =’Elastic1 Out’!L13.  Only the value for the bottom layer is required, but the screen shot above shows the results for all steel layers in Column O.  When the Goal Seek process is complete the steel stress(es) in Column O should be copied to Column P, using Copy and Paste-Special-As Values.  In Column Q add a formula for the difference between Columns O and P:

The stress change for any applied bending moment can then be found simply by entering the required moment in Cell A6.  Alternatively, Goal Seek can be used to find the bending moment that will result in the maximum allowable stress increase:

  • Set cell = Q25 (Stress Increase for the bottom layer)
  • To value = -200 (or the specified stress in MPa, tension negative)
  • By changing cell = A6 (Applied Moment)

Super-T beams are of course usually used in composite construction, with a reinforced concrete top slab.  The process described above can also be carried out on a composite section using the Composite Design Functions spreadsheet, with the following changes:

  • The composite output does not currently have a full list of stresses in each layer, so use the stress in the bottom layer (and top if desired) from column C, Stage 2 results.  We are interested in the stress in the bottom layer of the precast, so the required value is in cell =’ElasticComp Out’!C33 ( Stage 1 Bottom Steel).
  • To find the moment for zero stress at the bottom face, the value in cell D13 (Combined Depth NA, Stage 2) must be set to the depth of the composite section (2000 mm in the example) with Goal Seek, by adjusting cell B7:

The reinforcement stress at this bending moment is copied and pasted as value to cell Q27:

The stress increase, in cell R27, is then adjusted with Goal Seek to -200 (or the required value), by again adjusting the Stage 2 bending moment in cell B7.

The total maximum SLS applied bending moment is then given in cell B8, being the sum of the moment applied to the precast only, plus the maximum additional load on the composite section:

The output sheet gives additional details of stresses, strains, forces and moments, as well as a strain diagram for loads on the precast and composite sections:

Posted in Beam Bending, Concrete, Excel, Newton, UDFs, VBA | Tagged , , , , , , | 1 Comment