New Tricks …

… or in some cases, old but forgotten tricks.

The first comes from a Quora question, asking for the most underutilised feature in Excel.

When de-bugging a VBA routine you can set a break point where the code will stop:

To jump to a different line you can right-click, select “set next statement”, and click the line you want to jump to, but a quicker way is to just click and drag the yellow arrow to where you want to go:


The next is a post from Jeff Weir at Daily Dose of Excel, looking at how to display tool-tips with user defined functions (UDFs).  If you enter a UDF name, including opening bracket, then press Ctrl-Shift-A, all the argument names are entered automatically, which can then be replaced with the actual range or value you want:

I had totally forgotten about this, but I must have known about it once, as I have posted two comments on it.

Finally, another tool-tip trick that I had forgotten about, but that has appeared here before.  If you hover over a variable whilst de-bugging a VB routine, the first 77 characters of the current value of the variable are displayed as a tool-tip:

If the value is a long string, and you want to see the last 77 characters, hold down the Ctrl key before you hover:


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

Double Bass

Reading the comments on a Davey Graham YouTube video:

I discovered that the bass player on the track was Danny Thompson, and checking the other artists playing on the album, “Large and Life and Twice as Natural”, I found they included Jon Hiseman – on drums, and Dick Heckstall-Smith – on saxophone.  These same two have  also played with Jack Bruce (more famous for electric bass), including  on “Things we Like”, which has appeared here before:

I have often wondered if there was any interaction over the years between Danny Thompson and Jack Bruce. I have still not seen or read any direct evidence of this, but given their interaction with Hiseman and Heckstall-Smith, it seems highly likely.

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

VBA vs Power Query vs Python

A recent post at Chandoo’s Excel Forum asked for a formula to extract all pairs of digits from a number with 4 digits.  This was soon extended to dealing with longer numbers, and the examples looked at here will work with at least all pairs of digits from a 14 digit number.

Examples and code shown below can be downloaded from:

Hui came up with a VBA UDF, making use of a dictionary:

Function Extract_Pairs(str As Variant, Optional sort As String = "None") As Variant

' Declare
Dim dict As Object
Set dict = CreateObject("Scripting.Dictionary")

Dim i As Integer, j As Integer, k As Integer
 Const sep As String = ";"

'Loop through input string and add pairs to collection
For i = 1 To Len(str)-1
   For j = i + 1 To Len(str)
     ' Add item
    If Not (dict.Exists(Mid(str, i, 1) & Mid(str, j, 1))) Then
       dict.Add Mid(str, i, 1) & Mid(str, j, 1), 1
     End If
   Next j
Next i

If sort = "xlAscending" Then
   Set dict = SortDictionaryByKey(dict, xlAscending)
ElseIf sort = "xlDescending" Then
   Set dict = SortDictionaryByKey(dict, xlDescending)
End If

'Extract elements out of Collection into string
For Each key In dict.keys
   tempstr = tempstr & key & sep

'Return string to function
Extract_Pairs = "{" & Left(tempstr, Len(tempstr) - 1) & "}"
End Function

See the download file (link above) for slightly modified version,  code for the SortDictionaryByKey function, and example output.

Lori came  up with a  lengthy on-sheet formula, and some examples of much shorter Python code (with a link to this blog):

>>> import itertools
 >>> list(set(itertools.combinations([1,2,3,4],2)))
 [(1, 2), (1, 3), (1, 4), (2, 3), (3, 4), (2, 4)]
 >>> list(set(itertools.combinations([5,6,6,8],2)))
 [(5, 6), (6, 8), (5, 8), (6, 6)]
 >>> list(set(itertools.combinations([5,7,7,7],2)))
 [(5, 7), (7, 7)]
 >>> list(set(itertools.combinations([7,7,7,7],2)))
 [(7, 7)]

I have used the examples above to write a short Python function (in two versions) that can be called from Excel, via xlwings:

import xlwings as xlw
import itertools

@xlw.arg('num',numbers = int)
def listcombs(vals, num=2):
    rtn = list(set(itertools.combinations(vals, num)))
    return sorted(rtn)
@xlw.arg('num',numbers = int)
def listcombs2(vals, num=2):
    rtn = list(set(itertools.combinations(vals, num)))
    return sorted(rtn)

The first version uses the xlwings table decorator to adjust the size of the output array. This is currently slow, and sometimes does not fully update, so the second version uses my VBA array function re-size macro.

Finally Peter Bartholomew posted a solution using Power Query.  A spreadsheet with open code and documentation can be downloaded from:

challenge-n-digit PQ.xlsx

Examples of the results using the different approaches are shown in the screen shots below:

Hui’s original VBA code returned all different 2 digit numbers, for instance both 23 and 32.  I  have modified  it to return only the lower value when two values have the same two digits, to be consistent with Peter Bartholomew’s results:

Output from Peter Bartholomew’s Power Query spreadsheet is shown below.  See the download link for details:

The Python function requires the input digits in separate cells, and returns the results as a multi-column array:

The Python functions will work on an input range of any length, and extract groups of up to 9 digits.  The output array re-sizes automatically if any of the input data is changed:


Posted in Arrays, Excel, Link to Python, UDFs, VBA, xlwings | Tagged , , , , , , , | Leave a comment

Cattle at the Melbourne International Film Festival

The Melbourne International Film Festival, started in 1952, is one of the oldest film festivals in the World.

The Accelerator Program is a collection of short films by emerging filmmakers from Australia and New Zealand, which this year features a work by my daughter, Kerinne Jenkins:

The unspoken fears 16-year-old Sarah has about losing her mother to cancer are heightened when unexplainable and disturbing occurrences happen on the family farm.

Cattle will be showing in the Accelertor2 sessions on 12th and 19th August.
See full programme

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

Units and solvers with Pint and Sympy

Following the previous post I have adapted the units code to use the Pint package, and added a function using the Sympy equation solver, with Pint units.  I have applied the functions to finding the depth of neutral axis of a reinforced concrete beam, under combined bending and axial load.  A similar example was previously presented using VBA code.

The spreadsheet and associated Python code can be downloaded from:

The screen-shot below shows the same examples as previously presented using the Unyt package:

As before, the Excel exponentiation symbol may be used both in functions to be evaluated and in unit names, and is converted automatically to the Python **.

The next example shows input for a rectangular reinforced concrete section with two layers of reinforcement.  The input is a 3 column range with symbols, values, and units.  Input values and units are shaded grey.:

The first example finds the depth of the neutral axis at the Ultimate Limit State, assuming a rectangular stress block for the concrete in compression, with a strain of 0.003 at the compression face.  The tension steel is assumed to be at the yield stress, and the compression steel in the elastic range.  With these assumptions, a quadratic equation is formed, relating the nett force on the section to the depth of the neutral axis, x.  This equation is then solved using the user defined function (UDF) SolveU, that calls the Sympy Eq function.  The UDF result is checked on the right using the standard formula for quadratic equations:


The SolveU function arguments are:

  • Solveu(lhs, rhs, datrange, var_sym = ‘x’, rtnunit = ”, out = 0, adddat = []):
  • lhs, rhs: Text strings for the equation Left and Right Hand Sides, to be equalised
  • datrange: Three column range with equation constant symbols, values, and units
  • var_sym: Optional variable symbol; default = ‘x’
  • rtnunit: Optional units of the return value; default = base SI units
  • out: Optional output index; default = 0, return all values as an array (complex numbers are returned as two values in adjacent columns)
  • adddat: Optional additional data.  Three column range.  Default = none.

A second ULS example is shown below, with the axial load increased so that the compression steel is past the yield point, but the tension steel  is in the elastic range:

The third example finds the neutral axis depth for linear elastic behaviour in both steel layers, and the concrete in compression, with a given axial force and applied bending moment.

In this case the strain of the concrete at the compression face is initially unknown.  To enable a closed form solution:

  • The strain at the compression face is assumed to be equal to the neutral axis depth, x
  • A cubic equation for x is solved, such that the internal Force/Moment ratio is equal to Force/Moment of the applied loads, assuming linear behaviour of the steel and the compression concrete.
  • The nett internal force is calculated assuming unit stress at the compression face.
  • This stress is then factored so that the internal force is equal to the applied axial force, and the internal moment calculated, to check that it is equal to the applied moment.

In the example below the cubic equation for the neutral axis depth is solved using the SolveU function, and compared with the results using the VBA Cubic UDF:

Having found the depth of the neutral axis, the internal force and moment are calculated assuming a unit stress (Sigc) at the compression face:

Finally the compression stress is scaled to generate a reaction force equal to the applied load of 500 kN (25. 46 MPa), and the internal moment about the concrete centroid is checked against the applied moment.   The calculated stress may then be copied to the Sigc value (cell D108), to find the actual forces and moments in the steel and concrete under the applied load:

Posted in Beam Bending, Concrete, Excel, Link to Python, Maths, Newton, UDFs, VBA, xlwings | Tagged , , , , , , , , , , , | Leave a comment

Working with units in Excel – using VBA and Python

One of the most frequent reasons given for not using Excel for engineering and scientific applications is its very limited  facilities for dealing with calculations involving units.  It is possible to overcome this deficiency with VBA (see  Daily Download 24: Units for Excel and Evaluating text with units and finding the neutral axis), but linking to unit related libraries in Python has several potential advantages:

  • Using existing libraries greatly reduces the coding required.
  • Python libraries such as Sympy have sophisticated methods for dealing with parsing of non-standard units, not available in VBA.
  • Linking to other Python packages such as Numpy and Scipy offers potential performance advantages.
  • The method used to evaluate functions passed as strings in VBA is limited to 255 characters.  Linking to Python does not have this restriction.

There are many unit related Python libraries, apparently offering similar functionality.  A good review of three of the most popular is given at: Quantities and Units in Python.  This suggests that the Pint package would be most suitable, however a recent Stackoverflow reply links to a paper reviewing Unyt, which offers a convenient interface to the unit handling facilities in the Sympy package.  Unyt docs.

I have set up  a small spreadsheet using xlwings to link to both Unyt and directly to the units facilities in Sympy.  Download, including open source Python and VBA code from:

As well as Excel the spreadsheet requires Python, xlwings, Numpy, Sympy and Unyt.

I usually recommend the Anaconda package to install the required libraries, but in this case I found that it installed an old version of Sympy, and did not include Unyt.  After using Anaconda to install Python, xlwings and Numpy, the final two packages can be installed from the command line with pip:

  • pip install sympy
  • pip install unyt

I found that this installed the required packages with no problems.

The download zip file also includes a file “” that should be copied to the Unyt folder  (..\Anaconda3\Lib\site-packages\unyt\).

In the spreadsheet I have created three user defined functions:

The Planet_year1 function calls the Sympy unit handling and function solving routines:

def Planet_year1(rad, runit, smass, sunit):
    T = symbols("T")
    a = su.Quantity("planet_a")
    a.set_dimension(su.length, "SI")
    lunit = getattr(su, runit)
    a.set_scale_factor(rad*lunit, "SI")
    M = su.Quantity("solar_mass")
    M.set_dimension(su.mass, "SI")
    munit = getattr(su, sunit)
    eq = Eq(T, 2*pi*(a**3/(su.G*M))**0.5)
    q =solve(eq, T)[0]
    pdays = su.convert_to(q,
    return float(pdays.args[0]),str(pdays.args[1])

The return unit in this function is hard-coded todays.

The Unyt code performs the same calculation, but allows the return unit to be specified as a function argument:

def Planet_year2(rad, runit, smass, sunit, rtnunit):
    lunit = getattr(un, runit)
    semimajor_axis = rad*lunit
    munit = getattr(un, sunit)
    smass = smass*munit
    period = 2*np.pi*(semimajor_axis**3/(un.G* smass))**0.5
    period =
    return float(period.value), str(period.units)

The Unyt code is considerably simpler:

  • The input strings passed as the arguments “runit” and “sunit” are converted to unit objects using getattr().
  • The float values are assigned units by multiplying with the associated unit.
  • The resulting unit values can be combined with Numpy and Unyt constants (np.pi and un.G) and evaluated as usual.
  • The return unit is specified as a text string: period =
  • For return to Excel, the result value are extracted as a separate float and string

The Evalu function evaluates a function passed as text from the spreadsheet:

@xlw.arg('syms', ndim=1)
@xlw.arg('fvals', ndim=1)
@xlw.arg('funits', ndim=1)
def Evalu(func, syms, fvals, funits, rtnunit):
    # Convert ^ to ** and remove leading =
    func = exp_py(func)
    for funit in funits:
        if type(funit) == str and funit != '':
            funit = getattr(un, funit)
            fvals[i] = fvals[i]*funit
    f = eval('lambda ' + ', '.join(syms) +': ' + func )
    rtn = f(*fvals)    
    rtn =
    return float(rtn.value), str(rtn.units)  
  • The input arrays may be a single cell, so the xlwings arg decorator is used to specify that they should always be treated as a 1D array.
  • Any Excel/VBA exponent operators (^) are converted to **
  • The function is converted from a string to a lambda function.
  • The return unit is applied, and the return value and units are extracted as in the Planet_year2 function.

The second Evalu example illustrates the procedure for dealing with functions with incompatible units.  The tensile strength of concrete is specified in design codes as 0.6 times the square root of the compressive strength in MPa, and therefore has units of square root MPa.  If the input value is multiplied by MPa units: (fc*un.MPa) it will give the result the correct stress dimensions, so the input value can be specified with any valid stress units.


Posted in Excel, Link to Python, Maths, Newton, NumPy and SciPy, UDFs, VBA, xlwings | Tagged , , , , , , , | 4 Comments

Scipy Statistics Functions – coding and getting help

The Python Scipy library currently has 84 statistics functions.  I have now updated the xlwSciPy3 spreadsheet to access all of them directly from Excel.  The new version can be downloaded from:

As usual, the download includes full open-source code.  As well as Excel, the spreadsheet requires Python, Scipy, Numpy and xlwings to be installed.  The Anaconda Python installation includes all the required files.

The Scipy statistics functions  have a variable number of required and optional arguments.  To allow all the functions to be called from a single interface function the following procedure is used:

  1. Both required and optional arguments are passed to VBA using the ParamArray argument, which will accept any number of separate arguments.  Optional arguments are passed as a pair of separate arguments; the name followed by the value, which may be a single cell or a range.
  2. The VBA xl_Stats function reads the number of required arguments, and converts the required number to a single variant array.  The remaining pairs of arguments are converted to another variant array, and the two arrays are passed, together with the function name, to Python, via the VBA function “xl_callfuncSt0”.
  3. In Python the optional argument array is converted to a dictionary, and together with the required argument array passed to the required stats function.

For the functions to work correctly from Excel it is essential that all the required arguments are provided, and that any optional arguments are passed as a name/value pair.  To help identifying the correct input two VBA functions provide help:

The Get_Args function lists all argument names, together with default values for optional arguments:

These arguments can then be used in the xl_Stats function.  The examples below call the binned_statistic function, using the function name in cell K11.  The first  example passes only the two required arguments: x (K19:O19) and values(K20:O20).  In the second the optional “bins” argument is set to 2, and in the third both optional arguments are provided:

The full help documentation can be called from Excel using the Get_Doc function, as shown below.  The output range for this function can be re-sized by selecting the top-left corner (cell U7) and  pressing Ctrl-Alt-S:

Finally all 84 statistics functions are listed on the spreadsheet, with a brief description of the function output:

The xlw_SciPy3 spreadsheet also links to a wide range of other Scipy functions, as listed at: xlwSciPy update for Python 3.



Posted in Excel, Link to Python, Maths, Newton, NumPy and SciPy, UDFs, VBA, xlwings | Tagged , , , , , , , | Leave a comment