Recently (well a month ago) a comment was left at Continuous Beams with Shear Deflections asking for a tutorial on how to use the ConBeam User Defined Function (UDF).  In this post I will give detailed instructions for the ConBeam function, but similar procedures are applicable to all the other spreadsheets using UDFs, which is nearly all of them.  The spreadsheet may be downloaded from Macaulay.zip.

Documentation for the functions is given on Sheet1 (Functions):

ConBeam documentation, click for full size view

This lists the function input parameters and describes the output results. To use the function it is simply necessary to enter the required data anywhere in the workbook, then select these ranges in a similar way to using a built-in Excel function. As an example I have used the continuous beam shown below:

Continuous beam example

Examle results; maximum values

This is taken from: http://www.soft4structures.com/Beam/st_examples.html

The required input parameters consist of 5 ranges listing:

• Beam flexural stiffness, EI, (1 row for each segment with a different stiffness)
• Output points, distance from the left hand end
• Support positions and optionally support translational and rotational stiffness
• Distributed load details
• Point load details
• An “Out” parameter may also be entered controlling whether the function returns beam actions, slopes, and deflections (Out = 1, default), or support reactions (Out = 2)

Input for the example problem is shown below:

Example problem input

• The beam is of constant cross-section, and for the purposes of the example the stiffness is taken as 1, so only 1 row of input is required for the Segments range.
• Output points are listed in cells A32:A62, at 0.1 increments.  The selected range will normally be immediately to the left of the range chosen for the output table, but this is not a requirement.
• The supports are fixed against translation, and have no flexural stiffness, so only the position of each support is listed in the Supports range.
• There is a single distributed load over the final cantilever segment, varying from -1 to 0, note that downward loads are negative.
•  There is a point force of -1 at the centre of the first span, and a moment of 1 (anti-clockwise) at the second support.

Having entered the required data the function may be entered using the function wizard, in a similar way to built-in functions.  Enter “=ConBeam(” in the top-left corner of the desired output range, and then click on the function wizard icon, to the left of the edit line:

Entering the function using the Function Wizard

When the function is complete click OK:

Completed function, displaying the first output value only

The first output value only will be displayed.  To display the complete array, select the complete output range, then press the F2 (Edit) function key:

Select the entire output range and press F2

Finally enter the function as an “array function” by pressing Ctrl-Shift-Enter:

Completed function displaying Shear Force, Moment, Slope and Deflection for each selected output point

It can be seen that all values are in exact agreement with those shown in the example.

To return support reactions, rather than beam actions and deflections, enter the function in the same way as before, but terminate with the number 2 for the “Out” parameter.  In this case the output array will have one row for each support and will list support position, reaction force, and if support stiffnesses have been specified, reaction moments.

The download spreadsheet includes further examples:

• Beams with varying cross section
• Beams with spring supports, and a function to calculate the rotational stiffness of a pinned cantilever
• Beam analysis including shear deflections
• A function for analysis of single span beams (SSSpan), optionally with cantilevers at one or both ends
• A function to calculate fixed end actions (FEA)
• A function to calculate restrained end actions, for beams with partial end restraint (REA)
• A function for cantilever beams (Cantilever)

Use of all these functions is similar to the Conbeam function described above.

If anything is not clear, or does not work as expected, please post a comment.