ConBeamU and Strand7 check update

Posted on 05/12/2025 by dougaj4

Results of the continuous beam spreadsheet were last checked against Strand7 results 10 years ago. I have now updated this check with the latest version (4.19), which can be downloaded from:

ConBeamU.zip

The download file now includes:

  • ConBeamU.xlsb: The complete spreadsheet, including examples of all functions
  • ConBeamU-CheckDec25.xlsb: As above, also including the input for all the Strand7 checks
  • Check conbeamU-Dec25.xlsb: Summary of check results, including ConBeam and Strand7 results and graphs for all 90 cases.
  • ConBeamU-Template.xlsb: Spreadsheet including all VBA code and required units data, but with all examples removed.

The Strand7 check has 15 different span arrangements, each with 1 of 6 different support conditions:

The span arrangements and support conditions are listed on Sheet1 of the summary spreadsheet:

On the ConBeamU-CheckDec25 spreadsheet the Conbeam Check tab is set up with all 15 span arrangements, and the support restraint type can be selected by entering 1-6 in the “Run Type” cell (F2):

The results for the 6 different support conditions are copied to Sheets 1-6 of the Check conbeamU-Dec25 spreadsheet, together with the equivalent Strand7 results:

Both Conbeam and Strand7 results for shear, moment, slope and deflection are plotted for any selected “Span Type” (1-15):

The main ConBeamU spreadsheet has help on each of the available functions on the Functions tab:

The Strand7 checks used the ConBeam function, which requires consistent units. The ConBeamU function allows a wide range of different units to be used:

See the “Ext Unit List” tab for a list of all recognised units, and see Using ConbeamU for more information on adding to the units list, and more details on using the available functions.

Posted in Beam Bending, Excel, Frame Analysis, Newton, UDFs, VBA | Tagged , , , , , , , , , | 1 Comment

More on TrimRange and the Drop Function

Posted on 20/11/2025 by dougaj4

A comment on my previous post links to a helpful YouTube video on using TrimRange and the Dot operator:

It also looks at using the Drop Function to exclude a specified number of rows and/or columns from the top or left of the selected range.

I have added the Drop Function to some examples from the previous post, also adding some text in one cell above the data range:

In Column G Drop has been used in conjunction with the Dot operator excluding empty rows from both the top and bottom of a single column (B.:.B). This removes the first two rows of data, which is not what we want. If the Dot operator is used to exclude only the empty rows at the end (B:.B), Drop then removes Rows 1 and 2, and returns all the data from Row 3 to 15. In Column K the number of rows to be excluded has used the Row function, so that if the data is moved, or if rows are inserted above the data, the formula will still return the data from the top of the table, excluding all new rows above.

The Drop function can also be used to remove columns, and/or rows, in conjunction with the TrimRange function:

In column U the Dot operator is used to return Columns A to E, with empty rows excluded. The Drop function then removes the top 2 rows, and 1 column from the left (Column A), leaving only rows with at least one column containing data.

In Column Z the same result has been achieved using the TrimRange function, rather than the Dot operator, again removing 2 rows and 1 column with the Drop function.

Posted in Arrays, Excel | Tagged , , , , | Leave a comment

The TrimRange Function

Posted on 05/11/2025 by dougaj4

I have just discovered the TRIMRANGE function, and its very useful shortcut version, which were introduced to Excel 365 about a year ago. The function excludes all empty rows and/or columns from the outer edges of a range or array, and returns the remainder of the array​​​​​​​​​​​.

The function arguments are:

  • The range to be trimmed, in this case entered as the complete column B:
  • Optional “Row_trim_mode” and “Col_trim_mode” (see below for details).

The shortcut is to simply enter the range as =B.:.B. Some examples are shown below:

  • Either the left or right period may be omitted from the shortcut symbol, so that either only the rows below and columns to the right will be trimmed using B:.B, or only the rows above and columns to the left will be trimmed using B.:B.
  • The range may also be a 2D range, as shown in Column K.

Options to trim only to the left and above, or only to the right and below, are shown below:

Using the optional arguments with the full function name allows more options on the ranges to trim:

  • 0 – None
  • 1 – Trims leading blank rows or columns
  • 2 – Trims trailing blank rows or columns
  • 3 – Trims both leading and trailing blank rows or columns (default) 

In the examples above:

  • =TRIMRANGE(A1:E100,2,2) trims trailing rows and columns
  • =TRIMRANGE(A1:E100,1,2) trims leading rows and trailing columns
  • =TRIMRANGE(A1:E100,3,1) trims both leading and trailing rows, but only leading columns.

Using the shortcut notation:

  • =A1.:E100 is equivalent to: =TRIMRANGE(A1:E100,1,1)
  • =A1:.E100 is equivalent to: =TRIMRANGE(A1:E100,2,2)
  • =A1.:.E100 is equivalent to =TRIMRANGE(A1:E100,3,3) or just =TRIMRANGE(A1:E100)
Posted in Arrays, Excel | Tagged , , , | 2 Comments

py_UMom 1-05

Posted on 26/10/2025 by dougaj4

Following the previous post the OptShearCap3600 function has been modified to give correct results with negative bending moments. The revised spreadsheet and Python code can be downloaded from:

py_UMom.zip

Typical output with negative moment input:

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

py_UMom 1.04

Posted on 19/10/2025 by dougaj4

The py_UMom spreadsheet has been updated with further revisions to the py_OptShearCap3600 function. The new version can be downloaded from:

py_UMom.zip

For more information on the other functions in the spreadsheet see: py_UMom spreadsheet and OptShearCap3600 function.

The changes to the py_OptShearCap 3600 function are:

  • There is a new option to calculate the longitudinal force due to shear using the AS 5100.5 equation, with all other calculations to AS 3600.
  • The input has been modified so that axial force, moment, shear and torsion are specified for the critical load case, and a range of M/V (moment/shear ratio) values are entered to generate the interaction diagram.
  • The option to adjust axial load has been removed. The input axial load will be applied to all load cases.

The revised input and typical output is shown in the screenshots below:

Examples comparing the different longitudinal force options with different levels of shear reinforcement are shown below. The 4 options in each graph are:

  • Longitudinal shear force to AS 3600 with no adjustment of the compression strut angle, Theta.
  • As above with the compression strut adjusted to minimise the longitudinal shear force.
  • Longitudinal shear force to AS 5100.5 with the vertical force in the shear steel, Vus, limited to V*.
  • As above with no limit to Vus.

The first case has shear reinforcement just adequate for the input V*:

Increasing the shear reinforcement greatly increases the shear range with unconservative unreduced bending capacity when the AS 5100 calculation is used with no limit on Vus:

When the shear reinforcement is reduced below the level required for the input shear force the critical forces are all in the range where shear capacity governs the failure mode, and all four options give the same shear capacity:

Posted in Beam Bending, Concrete, Excel, Link to Python, Newton, PyXLL, UDFs | Tagged , , , , , , , | 1 Comment