Asymmetric Catenary Function

Back in 2009, I posted a user defined function (UDF) to generate a catenary curve, that could be used together with the Excel Solver to generate a curve of a specified span and sag:

A catenary function

Following a discussion at Eng-Tips (Chain catenary vertical reaction help ) I have updated the spreadsheet to allow different levels at each end, and to plot the full catenary.

The revised spreadsheet can be downloaded from:

Catenary.zip

To use the spreadsheet enter the total horizontal span, the required sag at each end, and an estimate of the span from the left hand support to the low point (“XEnd1”).  Then press the “Adjust A and XEnd1” button.  The solver will adjust the catenary sag and the position of the low point to generate the required sag from each end.  If nothing happens:

  1. Try adjusting the XEnd1 value, then click the left hand button, followed by the right.
  2. Make sure Solver is enabled.
  3. See the previous post

 

Posted in Coordinate Geometry, Curve fitting, Excel, Maths, Newton, UDFs, xlwings | Tagged , , , , | Leave a comment

Using Excel Solver from VBA

Using the “record macro” icon in Excel to record the solver process, I had a couple of problems:

  • When I tried to run the recorded macro, I got the message “Compile Error – Sub or Function not defined”.
  • When that was fixed the solver worked, but stopped on the final solver screen, rather than returning to the spreadsheet.

The solutions to these problems and many more are given in a detailed article by John Peltier:

Using Solver in Excel VBA

The end result of my work, including open source code, will be covered in my next post.

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

Column buckling under multiple loads

Following another buckling discussion at Eng-tips I have modified my Frame4-buckle spreadsheet to allow input of multiple axial loads at any point along the column, along with any number of sections with different section properties.  Background on the method used in the spreadsheet, and installation of the Alglib solver (if desired) can be found at: Buckling of columns with varying cross section and Using Frame4Buckle with the Alglib solver and the new spreadsheet (Frame4buckle-multiload.xlsb) can be downloaded from:

Frame4Buckle.zip

Input and results for a column with a top load and one intermediate load are shown in the screen-shots below:

End conditions input and results:

Section properties and loads input:

Note that for multiple loads the input allowing varying section properties must be used.  The cell E24 in the Constant Section input (“Number of Segments”) must be blank.

Any consistent units may be used.  In the example above lengths are in inches and force in kips.

The calculated buckled shape may be plotted:

The example shown above was taken from a spreadsheet published by Alex Tomanovich, available from: StepCol

This spreadsheet uses a different analysis method, but gives very close results for the column effective lengths:
L1 = 19.25 ft = 231 in. (compared with 230.8 in.)
L2 = 29.08 ft = 349 in. (compared with 348.6 in.)

Posted in AlgLib, Beam Bending, Excel, Finite Element Analysis, Frame Analysis, Newton, UDFs, VBA | Tagged , , , , , , , , , | Leave a comment

Trinity Bridge and Bridge of the Month

Bill Harvey has been doing a monthly report on historical bridges for many years, but recent issues have the added interest of interactive 3D models of the selected bridges.

The Trinity Bridge at Crowland in South Lincolnshire is an excellent example, being a very old stone arch bridge of unique design.  See the 3D model below, or go to Bridge of the Month No. 108 for the full report.

Posted in Arch structures, Historic Bridges, Newton | Tagged , , , | Leave a comment

Python Traps

When converting existing VBA code to Python there are a number of obvious changes to the syntax that need to be made for the code to run at all, but there are also a number of not so obvious traps that may result in the code running, but giving incorrect results.  Three that regularly catch me unawares are illustrated in the screen shots below:

The first is the value of the counter at the completion of a for loop.  In VBA the counter runs from the specified start to stop values inclusive, then after the final loop the counter is incremented again.  In Python the counter runs until the last value lower than the specified stop value, and is not incremented after the last step.  The result is that the examples below generate the same number of steps (n), and the same value in the calculated quantity (j), but the final counter value (i) is one higher in the VBA code than in the Python:

 

Differences in application of the = operator when applied to arrays have been discussed previously (The meaning of = in Python).  Similar differences occur when variables are passed to other functions, with results that can be confusing and unintuitive (at least to me).  In the examples below:

  • A variable, x, is passes to a function.
  • A local variable y is assigned the value of x, then multiplied by n (sometimes in one operation).
  • x is multiplied by n.
  • y is returned to the calling function, which then returns x and y to the spreadsheet.

The returned results depend on the variable type, how the multiplication is carried out, and the language.  In the examples below the variable x is passed in Python first as a float then as a Numpy array, and in both cases the multiplication is carried out with x = x*n for x and y = x*n for y.  In VBA the multiplication was carried out with a for loop.

  • In the Python code y returns the multiplied values, but the operations on x are treated as local, and the values in the calling function remain unchanged, both for the single value x and the Numpy array.
  • in the VBA code in both cases the x variable is passed by reference, so any operations on x are reflected in the calling routine:

In the next 3 examples x is passed as a Numpy array in Python, and y is created with y = x*n.

  • The modified value of y is always returned to the calling routine.
  • When x is multiplied by directly modifying the value of the array elements the modified values are reflected in the calling routine, but using for a in x: creates multiplied values of a, but does not modify the values in the x array, so the values in the calling routine are also unchanged.
  • In VBA, options 3 and 5 are essentially the same as Python option 5, and return the same results.  VBA Option 4 uses a for each loop for both x and y, which does not modify either array, locally or in the calling routine.

In the final 3 Python examples x is passed as a list and y is set equal to x.

  • Where the multiplications are applied to the individual elements of either x or y, both arrays are modified, and the modified values are transferred to the calling array.
  • Where a for a in y loop is used neither array is modified, either locally or in the calling routine.

in the VBA examples:

  • In Option 6 y is created as a Range object and x is then converted to a variant array.  The multiplication of x is reflected in the calling routine, but y is unchanged.
  • Use of “for each a in y” in Option 7 does not change the values in either x or y.
  • In Option 8 y is set equal to x, then converted to a variant array and multiplied by n.  The changes in y are returned to the calling array, but x is unchanged.

The code for all the routines is listed below:

Python Code:

@xl_func
@xl_arg('start', 'int')
@xl_arg('stop', 'int')
def CountLoop(start, stop):
    n = 0
    j = start
    for i in range(start, stop):
        n = n+1
        j = j + 1
    return [[i, j, n]]

@xl_func
@xl_arg('val1')
@xl_arg('val2')
@xl_arg('rng1', 'numpy_array', ndim=1)
@xl_arg('rng2', 'float[]')
@xl_arg('out', 'int')
#@xl_return('numpy_array')
def PassArgs(val1, val2, rng1, rng2, out):
    if out == 1:
        rtn = mult1(val1, val2)
        return [rtn, val1]
    elif out == 2:
        rtn = mult1(rng1, val2)
        return [rtn, rng1]
    elif out == 3:
        rtn = mult2(rng1, val2)
        return [rtn, rng1]
    elif out == 4:
        rtn = mult3(rng1, val2)
        return [rtn, rng1]
    elif out == 5:
        rtn = mult4(rng1, val2)
        return [rtn, rng1]
    elif out == 6:
        rtn = mult5(rng2, val2)
        return [rtn, rng2]
    elif out == 7:
        rtn = mult6(rng2, val2)
        return [rtn, rng2]
    else:
        rtn = mult7(rng2, val2)
        return [rtn, rng2]

def mult1(x, n):
    y = x*n
    x = x*n
    return y

def mult2(x, n):
    y = x*n
    x[:] = x[:]*n
    return y

def mult3(x, n):
    y = x*n
    for a in x:
        a = a*n
    return y

def mult4(x, n):
    y = x*n
    for i in range(0, 2):
        x[i] = x[i]*n
    return y

def mult5(x, n):
    y = x
    for i in range(0, 2):
        x[i] = x[i]*n
    return y

def mult6(x, n):
    y = x
    for a in y:
        a = a*n
    return y

def mult7(x, n):
    y = x
    for i in range(0, 2):
        y[i] = y[i]*n
    return y

VBA Code:

Function vbCountLoop(start, last)

    n = 0
    j = start
    For i = start To last
        n = n + 1
        j = j + 1
    Next i
    vbCountLoop = Array(i, j, n)

End Function

Function vbPassArgs(val1, val2, rng1, rng2, out)
    If out = 1 Then
        rtn = mult1(val1, val2)
        vbPassArgs = WorksheetFunction.Transpose(Array(rtn, val1))
    ElseIf out = 2 Then
        rtn = mult1a(rng1, val2)
        vbPassArgs = (Array(rtn, rng1))
    ElseIf out = 3 Then
        rtn = mult2(rng1, val2)
        vbPassArgs = (Array(rtn, rng1))
    ElseIf out = 4 Then
        rtn = mult3(rng1, val2)
        vbPassArgs = (Array(rtn, rng1))
    ElseIf out = 5 Then
        rtn = mult4(rng1, val2)
        vbPassArgs = (Array(rtn, rng1))
    ElseIf out = 6 Then
        rtn = mult5(rng2, val2)
        vbPassArgs = (Array(rtn, rng2))
    ElseIf out = 7 Then
        rtn = mult6(rng2, val2)
        vbPassArgs = (Array(rtn, rng2))
    Else
        rtn = mult7(rng2, val2)
        vbPassArgs = (Array(rtn, rng2))
    End If

End Function


Function mult1(x, n)
    y = x * n
    x = x * n
    mult1 = y
    End Function


Function mult1a(x, n)
    x = x.Value2
    y = x
    For i = 1 To 2
    y(1, i) = x(1, i) * n
    x(1, i) = x(1, i) * n
    Next i
    mult1a = y
    End Function


Function mult2(x, n)
    x = x.Value2
    y = x
    For i = 1 To 2
    y(1, i) = x(1, i) * n
    x(1, i) = x(1, i) * n
    Next i
    mult2 = y
    End Function
    

Function mult3(x, n)
    x = x.Value2
    y = x
    For Each a In y
        a = a * n
    Next a
    For Each a In x
        a = a * n
    Next a
    mult3 = y
    End Function
    
Function mult4(x, n)
    x = x.Value2
    y = x
    For i = 1 To 2
        y(1, i) = y(1, i) * n
        x(1, i) = x(1, i) * n
    Next i
    mult4 = y
    End Function

Function mult5(x, n)
    Set y = x
    x = x.Value2
    For i = 1 To 2
        x(1, i) = x(1, i) * n
    Next i
    mult5 = y
End Function

Function mult6(x, n)
    Set y = x
    For Each a In y
        a = a * n
    Next a
    mult6 = y.Value2
End Function

Function mult7(x, n)
    Set y = x
    y = y.Value2
    For i = 1 To 2
        y(1, i) = y(1, i) * n
    Next i
    mult7 = y
End Function
Posted in Arrays, Excel, Link to Python, Newton, NumPy and SciPy, PyXLL, UDFs, VBA | Tagged , , , , , , , , | 1 Comment

Excel Dynamic Arrays

Previews of dynamic arrays in Excel have been available for some time through the “Insider” programme.  They are now being published to general subscribers through Office 365 monthly updates:

Further information is given at:

Easier array formulas
and
Dynamic array formulas vs. legacy CSE array formulas

Most of my spreadsheets make extensive use of user defined functions (UDFs) that often return arrays of data, that up till now needed to be entered by selecting the output range and pressing Ctrl-Shift-Enter.  These “legacy” CSE functions continue to work as they did before, but if you want to change to using the new dynamic arrays the procedure is:

Select the entire output range of the CSE array.  Note that it displays in the edit line surrounded by {} :

Press F2 to edit, then convert the function to text by entering ‘ before the = sign, then enter with Ctrl-Shift-Enter:

Now delete the text from all the cells other than the top left:

Finally press F2, delete the ‘ to return the cell to an active function, and press enter (not Ctrl-Shift-Enter).  The entire array will now display:

Note that the function only occupies the top left cell.  You can enter data in any other cell of the array output range, but if you do the function will display as SPILL!:

As far as I know, there is no way to return part of an array using the new functionality, other than the top left cell, that can be returned with the Single() function.  Fortunately the old Ctrl-Shift-Enter method still works.

Posted in Arrays, Excel, UDFs | Tagged , , , , , | Leave a comment

Clive James and Pete Atkin

Following The Barrow Poets, more poetic music from the early 70’s:

Clive James finally succumbed to a long illness this week.  He was mainly known for his humorous work on TV and in writing, but he was also a prolific poet, and in his early career teamed up with Pete Atkin to produce musical versions of his work.  The sample below is from their first album, Beware the Beautiful Stranger:

Posted in Bach | Tagged , , | Leave a comment