The Ultimate Python Guide for VBA Developers

Posted on 15/04/2026 by dougaj4

I recently discovered “The Ultimate Python Guide for VBA Developers” which is a free book available from the pyxll site at https://www.pyxll.com/learn-python.html.

The pyxll site says:

This book has been written specifically for VBA and Excel users like you. Fast track your Python journey and take your productivity to a whole new level.

Here are just some of the topics you will learn about in this book

Getting started with Python
Key differences between VBA and Python
Choosing a Python IDE
All about functions, modules and packages
Python datatypes and collections
Scientific computing with NumPy, Pandas and SciPy
IPython and Jupyter Notebooks
Excel integration and writing Excel add-ins

I found it to be an excellent introduction to (and reminder of) the basics of getting started with Python.

The link above also has many other resources, including:

The VBA to Python Cheat Sheet
Use our free Python to VBA Cheat Sheet to accelerate learning Python.
Keep it as a handy reference and you’ll be proficient with Python in no time!

which is a great reminder of the Python basics

Posted in Computing - general, Excel, Link to Python, Python Pandas, PyXLL, UDFs, VBA | Tagged , , | Leave a comment

… and finally py_xlCBA 0.6 with trapezoidal loads

Posted on 11/04/2026 by dougaj4

The latest version of the Python continuous beam analysis program pyCBA is 0.7, allowing for specification of trapezoidal distributed loads, and this is now available with pip. I have updated the py-xlCBA spreadsheet and associated code to use the latest version, and the revised files can be downloaded from:

py_xlCBA.zip

As before the spreadsheet requires Python to be installed, as well as pyCBA (version 0.7 or later), and pyxll to handle the Excel/Python interface. See Python and pyxll for more details of pyxll, and a discount code for those opening a new pyxll account.

The distributed load input now has four columns, allowing for the load/m to be defined at the start and end of the load:

If the fourth column is not selected or left blank the load will be treated as rectangular. To define a triangular load a zero must be entered in the start or end column.

All analysis options are now available with the py_CBAcache function, which returns a single cell cache object, from which results may be extracted with the py_CBARes or py_CBAReact functions:

The Matplotlib graphics generated by pyCBA may be displayed in Excel using the py_CBAcache function (as shown above), or using py_CBA.

The detailed check against Strand7 results has been updated with trapezoidal loads:

The results of the 15 different span arrangements, each with 6 different support conditions can be seen in the file Check py_CBA-4Apr26-2.xlsb included in the download, showing very close agreement in all cases:

This file can be viewed without access to Python or any of the associated packages. Enter 1-15 in the “Span Type” cell (Y2) to view results from Strand7 and pyCBA on any of the 6 worksheets.

Posted in Beam Bending, Excel, Frame Analysis, Link to Python, Newton, PyXLL, Strand7, UDFs | Tagged , , , , , , , , , , , , | Leave a comment

… and then py_xlCBA 0.5

Posted on 05/04/2026 by dougaj4

Following the previous post, more detailed checking found that the code was returning an error for beams with a support at X = 0. This has now been fixed, and the revised code and spreadsheets can be downloaded from:

py_xlCBA.zip

I have also added the files used for the detailed check, analysing 15 different span arrangements, each with 6 different support conditions, analysed in the file py_CBA-Check26-4.xlsb, and compared with Strand7 results for the same span arrangements and support conditions (Check Beam-pyCBA-all-Apr26.st7). The spreadsheet and Strand7 results are copied to Check py_CBA-4Apr26.xlsb, which is all numerical data and can be opened in Excel without Python.

The spreadsheet calculation of the 15 beams is shown below, with split-screen view (click image for full-screen view):

The segment definition, with a total beam length of 32 m, and the applied loading are the same for all cases, but the spreadsheet has 15 different ranges defining support locations, and each support has 6 different options for support type, including pinned, fixed, specified deflections and/or rotations or deflection and rotational stiffness. For each of the 6 support conditions the py_CBA function results for the 15 beams were copied to the summary spreadsheet:

The Strand7 results are also copied to the summary spreadsheet, where the plots show near identical results, with both analyses appearing as a single line. The results for each of the 15 different support arrangement can be viewed by entering 1-15 in cell Y2.

Posted in Beam Bending, Excel, Finite Element Analysis, Frame Analysis, Link to Python, Newton, PyXLL, Strand7, UDFs | Tagged , , , , , , , , , , | Leave a comment

py_xlCBA 0.04

Posted on 03/04/2026 by dougaj4

The pyCBA code has now been updated to allow for supports with specified displacements (release 0.6 or later). My py_xlCBA spreadsheet and associated functions have been updated to call the pyCBA code for displacement analysis, rather than using my add-on functions. This has the advantages that it now handles rotations as well as vertical displacements, specified displacements may now be called from the py_CBACache function, as well as py_CBA, and the overall code is much simpler. The updated spreadsheet and Python code can be downloaded from:

py_xlCBA.zip

The function input is as before except that the restraint displacement range has an additional column for specified rotations:

The next release of pyCBA (0.7) will allow for trapezoidal distributed loads, so the distributed load Load/m input has two columns to allow for that. For uniform distributed loads the second column may be left blank.

Results are shown below for a 3 span continuous beam with cantilevers at both ends and specified displacements and rotations at some supports:

Analysis in Strand7 give near exact agreement for all output:

The py_CBAcache function returns the pyCBA results as a cache object, from which the desired results may be xtracted with py_CBARes or py_CBAReact, without running the analysis again. The Python graphical output generated by pyCBA may also be transferred to Excel by setting the Plot_res input to TRUE:

The Strand7 data and results files for the example beam are included in the download file, and typical output is shown below:

Posted in Beam Bending, Excel, Finite Element Analysis, Frame Analysis, Link to Python, Maths, Newton, PyXLL, Strand7, UDFs | Tagged , , , , , , , , , , | 1 Comment

2DFrame-py

Posted on 27/03/2026 by dougaj4

… and 3DFrame-py update.

I have added code to the 3DFrame-py module to allow simplified input for 2D frame analysis. The revised code and example spreadsheets can be downloaded from:

3DFrame-py.zip

There are also miscellaneous minor revisions to the 3DFrame code, which is now version 0.60.

The revised input is shown in the screen-shots below.

Section bending and shear stiffness properties are only required for bending about the Z axis and shear in the XY plane:

Node coordinates, and node restraints are required in the XY plane only:

Beam connections do not require an axis rotation angle, and beam end releases are reduced to those applicable to the XY plane:

The load input table has the same format as the 3D spreadsheet, but loads are in the XY plane only, and point moments are always about the Z axis.

Moment-curvature tables for non-linear analysis require only 1 table for each property:

Output results are reduced to deflections and forces in the XY plane, and rotations and moments about the Z axis:

The download file includes results for an analysis with geometric non-linearity included, compared with Strand7 results:

Graphical output is currently limited. The graphs of beam actions and deflections work, but the code for plotting the frame is yet to be updated.

Posted in Beam Bending, Excel, Finite Element Analysis, Frame Analysis, Link to Python, Newton, PyXLL, Strand7, UDFs | Tagged , , , , , , , , | Leave a comment