Dealing with annoying Excel defaults

Posted on 27/04/2026 by dougaj4

Mynda Tracey’s MyonlineTrainingHub has a recent post on how to change 15 Excel default settings at:

Excel Settings You Need to Change

Raise your hand if Excel’s default settings have ever driven you just a little bit crazy.

Maybe your invoice numbers lose their leading zeros…
Maybe you’re tired of Excel shoving GETPIVOTDATA into your formulas…
Maybe the new Copilot icon is following your every move!

You’re not alone. Excel’s defaults are made for average users. But you? You’re here to level up. So let’s fix those annoyances and make Excel work for you.

Here are 15 simple but game-changing Excel settings you’ll wish you’d changed sooner

The link includes links to a YouTube video and a text version, plus links to a cheat-sheet and other resources from the site. Warning: the site has plenty of ads, and links to external downloads of commercial products, so be careful where you click!

Posted in Excel | Tagged , , | Leave a comment

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