The Python match case statement was introduced in Python 3.10. It provides similar functionality to the VBA Select Case statement, but also has additional features. For detailed background with examples see:
The example below illustrates the basic functionality using code called from Excel with pyxll.
@xl_func()
def SelectVal(n):
match n:
case 1:
return 'first odd'
case 2:
return 'first even'
case 3 | 5 | 7:
return 'odd'
case 4 | 6 | 8:
return 'even'
case _ if 8 < n < 12:
return 'between 9 and 11'
case _ :
return 'more than 11'
The code shows options for:
Matching a single value (1 or 2)
Matching 2 or more values with the or operator, e.g. 3 | 5 | 7:
Matching any other value in a specified range using _ if condition :
I have recently been catching up with new Python features, starting with Data Classes which were introduced in Python version 3.7. For information and code examples I have referred to:
The link provides detailed information on the basics of data classes, leading on to more advanced features and examples, including:
I have adapted this code to create Excel UDFs that will find the distance between two locations either defined by latitude and longitude, or by city names linked to a table with latitude and longitude. A spreadsheet with examples and location data for Australian cities can be downloaded from:
Note that the position data is defined in the order of Latitude, Longitude, which is the standard order, rather than Longitude, Latitude as used in the Real Python code.
The Sphere_dist function has input of the latitude and longitude of two points and returns the spherical distance between them.
The City_dist function has input of a table of cities with position and other data (which must be in the same format as in the spreadsheet), and two city names. It returns the names of the two cities with their latitude and longitude, and the distance between them.
The results of the two functions are approximate, because the Earth is not an exact sphere. A more accurate approximation can be found with the Vincenty method; see Spherical Geometry and Vincenty’s Formulae.
Ashley Hutchings 80th Birthday Bash – Who Knows Where The Time Goes? Encore #2. Kellie While – lead vocal. Richard Thompson – lead guitar. Plus the ensemble. Birmingham Town Hall – 18.04.2025.
The lead singer, Kellie While, has appeared here before accompanying Martin Simpson, but without mention either in my post or on You Tube:
Following the previous post, the py_RC Design spreadsheet has been updated with improved versions of the py_UMomp and py_UMomBiax functions. The revised version can be downloaded from:
The py_UMomp function is based on the Umom function, with the following changes:
The Eurocode 2 parabolic stress block is applied over the full range of axial loads and includes the required restrictions on maximum strain for neutral axis positions outside the concrete section. It may also be applied for calculations otherwise following the AS 3600 or AS 5100.5 codes.
The reinforcement may now be distributed over any number of layers.
The current version allows only for rectangular sections, but future versions will also have the option to specify any number of trapezoidal concrete layers.
py_UMomp input:
The results from the new function have been compared with the py_UmomBiax function (with the neutral axis angle set to zero) and also the VBA version of the UMom function.
With rectangular stress blocks used for all versions there is excellent agreement over most of the range, other than for high axial loads where the VBA version uses a different procedure for linear interpolation when the NA position is outside the concrete section:
Comparing results using the parabolic-linear stress block with the rectangular stress block, but applying Australian code stress limits in both cases, there are significant differences for axial loads above the balance load, especially for AS 3600:
The differences are increased when the maximum strain is increased to the Eurocode 2 limit of 0.0035:
The difference between the AS 3600 and AS 5100.5 results is largely due to the difference in the default reduction factors. In the graph below the results for both codes have the AS 3600 values: 0.85 for zero axial load, reducing to 0.65 for sections where the concrete compression controls the design capacity.
With 30 MPa concrete, results up to the balance load are now very close, with the AS 3600 results with a parabolic-linear stress block being higher than AS 5100.5 because of differences in the application of the transition of the reduction factors from 0.85 to 0.65. Above the balance load the two codes give identical results with the parabolic-linear stress block, but AS 3600 is significantly more conservative with the rectangular stress block.
With 50 MPa concrete the differences are similar but the differences between two stress blocks are increased, and the difference between AS 3600 and AS 5100.5 with the rectangular stress block is also increased.
Finally with 90 MPa concrete all four results are very close over the full range.
The py_RC Design spreadsheet provides Python versions of the reinforced concrete design functions in the RC Design Functions spreadsheet. The current version can be downloaded from:
For details of the pyxll app required to connect the Python code to Excel see Python and pyxll.
The focus of this post will be the new py_UmomBiax function that provides similar functionality to the equivalent VBA function, see Biaxial bending update 2.
The function allows for irregular cross-sections, defined by coordinates, with any number of reinforcement layers:
The neutral axis can be adjusted by the spreadsheet so that the angle of the reaction bending moment is equal to the direction of the input loads. The output format is similar to the VBA version:
The py_UmomBiaxA function allows the generation of interaction diagrams. The first example plots bending moments about the X axis against axial load for 4 different code options:
Eurocode 2 with parabolic-rectangular stress block.
Eurocode 2 with linear-rectangular stress block.
Eurocode 2 with rectangular stress block.
AS 3600 with rectangular stress block.
For rectangular stress block calculations the moment capacity is calculated for all axial loads where the neutral axis is inside the section, then linear interpolation is used for results with higher axial load. For parabolic-rectangular and linear-rectangular stress blocks the strain-based calculation is used for all axial loads, using the Eurocode 2 procedure where the maximum compressive strain is reduced as axial loads increase:
The next example shows moment capacity about the Y axis for different angles of the neutral axis. For each angle results are shown with a parabolic-rectangular and rectangular stress blocks:
Similar results are plotted below for the resultant bending moment capacity, using the same stress blocks:
Finally results are plotted for constant axial load with the neutral axis angle between 0 and 90 degrees:
Newton Excel Bach, not (just) an Excel Blog 