Newton Excel Bach, not (just) an Excel Blog

Updated 1st Feb 2025.

As mentioned in the previous post, the installation process for the 3DFrame-py spreadsheet has changed with the new version. Also there have been some significant changes to the code to fix installation problems. Updated files are at:

3DFrame-py.zip

The frame analysis uses the Scipy linear algebra functions called from pyLinalgfuncs3, other Scipy functions called from pyScipy3, and a beam analysis function called from BeamAct_2, which must all be available on the Python path. BeamAct_2 is included in the download file. The other two are included in:

pySciPyzip

The Scipy functions include very recent updates, so download the latest version even if you already have it installed.

The 3DFrame-py download file includes:

• 3DFrame-py-comb.xlsb: Spreadsheet with added provision for factored load combinations and saving multiple load cases and results.
• 3DFrame-py.xlsb: The basic spreadsheet, including data for a small example frame
• 3DFrame-py Strand7 check.xlsb: Results for the small frame compared with Strand7 results.
• 3DFrame-py-vvbig.xlsb: Spreadsheet with input and results for a much larger frame
• 3DFrame-py-Mastan.xlsb, 3DFrame-py-Mastan steel1.xlsb, 3DFrame-py-Strand7 Steel1.xlsb: Results compared with the Mastan frame analysis package, and Strand7 using steel sections.
• Load3DFrame.py: Python code to load the 3DFrame-py code at startup of the associated spreadsheet
• py_3DFrame1_2.py: The 3DFrame-py code, using the Numba just-in-time compiler.
• py_3DFrame1_2-nojit.py: The same code but without any calls to Numba jit compilation.
• BeamAct_2py: Beam analysis functions

In addition to the download files, the following modules are used:
Required:

• pyxll
• Numpy and Scipy
• ctypes

Optional:

• Numba
• pyPardiso (can now be installed with pip)

The spreadsheet has VBA code to automatically load the Python frame analysis code when the file is opened:

Public Sub Workbook_Open()
Dim iErr As Long

Application.EnableEvents = True
iErr = Run("Load3DFrame")
End Sub

The Load3DFrame module should either be added to the list of files opened at startup, in the pyxll.cfg file, or the code below should be added to an existing startup file:

import pyxll
from pyxll import xl_menu, xl_macro, xl_app, xlcAlert
import importlib as imp

@xl_macro
@xl_menu("Frame Analysis", menu="Load")
def Load3DFrame():
    xl = xl_app()
    usejit = xl.Range("Usejit").Value
    try:
        usejit = xl.Range("Usejit").Value
    except:
        usejit = 0
    try:
        if usejit == 0:
            imp.import_module('py_3DFrame1_2-nojit')
        else:
            imp.import_module('py_3DFrame1_2')

        pyxll.rebind()
        xlcAlert('Frame Analysis module import completed')
    except ImportError as e:
        xlcAlert(f'Module not imported: {e}')
    except Exception as e:
        xlcAlert(f'Error: {e}')
    return 0

Note that:

• The Load3DFrame function has added error handling to return a message if any modules fail to load. If the Frame Analysis module is imported successfully, but the analysis does not work, have a look at the pyxll log file, which should indicate the source of the problem.
• This is still a work in progress, and all results must be carefully checked.