Continuous Beam spreadsheet with moving load

Posted on 26/11/2013 by dougaj4

I have added a moving load function to the ConBeamU spreadsheet, allowing maximum load actions and deflections to be determined in a continuous beam  under the influence of a vehicle defined by a number of point loads.  The new version may be downloaded from ConBeamU Download, including full open-source code.

The procedure to use the new function is:

  • Define: Beam Segments, Supports, and fixed Distributed and Point Loads, as for the other beam analysis functions:
Beam segments, supports, and fixed loads

Beam segments, supports, and fixed loads

  • Define the moving vehicle loads (axle loads and axle spacing), load factors and output units:
Vehicle loads and axle spacing, load factors, and output units

Vehicle loads and axle spacing, load factors, and output units

  • Define the output actions or deflections required, and their location along the beam, with a two row range of as many columns as required.
  • Define the vehicle positions to be analysed with a column of positions of Axle 1 of the vehicle.
  • The loads generated for any specified vehicle position may be displayed by entering the vehicle position above the Loads graph:
Fixed load details, Output actions and positions, Vehicle positions, and output results

Fixed load details, Output actions and positions, Vehicle positions, and output results, Click for full size view

Bending moments, shear forces and deflections for a 3 span bridge deck under AS 5100 MS1600 loading are illustrated in the graphs below:

MovLoad4 MovLoad5 MovLoad6

For convenience, the input data areas used in the analysis shown have been shaded grey, but the input may be rearranged as desired, and then the MovLoadU function re-enetered, selecting the new ranges.

A summary of the input and output of this, and all the other functions included in the spreadsheet, may be found on the Functions sheet:

Click for full size view

Click for full size view

Posted in Beam Bending, Excel, Frame Analysis, Newton, UDFs, VBA | 17 Comments

Unit aware continuous beam spreadsheet update

Posted on 22/11/2013 by dougaj4

The ConbeamU spreadsheet provides Excel user defined functions (UDFs) for the analysis of continuous and simply supported beams and cantilevers using Macaulay’s method.  It allows complex combinations of any number of partial distributed loads, point loads and moments, with changes in section properties along the length of the beam, and partial restraint to translation and rotation at the supports.  All functions have a “unit aware” version, allowing input and output to be in any of a wide variety  of units.  Full details are included in the download file (ConbeamU.zip) and at: Continuous Beam Spreadsheet – with Units.

Recently using the spreadsheet I noticed that some features were not particularly friendly, such as if a blank column was selected for an optional input range, this caused the functions to return an error.  This has now been fixed, and some other features added for added convenience:

  • Any number of rows may be selected for each input range.  The ranges will automatically be adjusted to the length of the continuous block of data in the first column (see the previous post for details of the range selection functions).
  • Data in optional columns may now be deleted without requiring the input range to be reselected to remove the blank range.
  • The input areas in the examples have been rearranged to allow data ranges to be extended more conveniently without re-entering the functions (but note that the functions can be re-entered anywhere on the spreadsheet).
  • The examples checked against Strand7 results have been moved to a separate file.
  • Two functions to plot the input loads have been added.  PlotLoads and PlotMom allow the input loads to be plotted conveniently without re-adjusting the graph ranges.

Typical input and output is shown in the screenshot below.  The spreadsheet, including full open source code, may be downloaded from: ConbeamU.zip.

ConbeamU; click to view full-size

ConbeamU; click to view full-size

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

Selecting Ranges from a UDF

Posted on 20/11/2013 by dougaj4

In the process of tidying up the ConBeamU spreadsheet (which will be posted here in the next few days) I decided to revise the routine for converting input ranges to arrays.  The input for the continuous beam analysis functions consists of several ranges of variable length.  The functionality required of the routine to read the data is:

  • Extend or reduce the range size to the length of continuous data in the first column.
  • Return the number of rows and columns in this range.
  • Convert the range object to a variant array

This task is performed by the EndDown function below:

Function EndDown(InRange As Variant, Optional Vol As Boolean = False, Optional NumRows As Long = 0, Optional NumCols = 0) As Variant
Dim SelectRows As Long, NextRow As Variant, LastRow As Long, TopRow As Long

    If Vol = True Then Application.Volatile

    If TypeName(InRange) = "Range" Then
        SelectRows = InRange.Rows.Count
        NumCols = InRange.Columns.Count
        TopRow = InRange.Row

        '  Check for a single row
        NextRow = InRange.Offset(1, 0)(1, 1).Value2
        If IsEmpty(NextRow) = True Then
            NumRows = 1
            InRange = InRange.Resize(1).Value2
        ' Else use xlDown to return all rows to the first blank cell
        Else
            LastRow = InRange.End(xlDown).Row
            NumRows = LastRow - TopRow + 1
            InRange = InRange.Resize(NumRows).Value2
        End If

    Else
        NumRows = UBound(InRange)
        NumCols = UBound(InRange, 2)
    End If
    EndDown = InRange
End Function

This function, and two related functions, EndRight and EndBoth, can be downloaded from GetRange.xlsb, including full open source code.

These functions can also be used on the spreadsheet, as shown in the screenshot below:

EndDown, EndRight and EndBoth functions combined with Sum function

EndDown, EndRight and EndBoth functions combined with Sum function

Note that by default any changes to data outside the selected range (outside the yellow range in the screenshot) will not cause the functions to recalculate. To change this behaviour, so that a change to a cell value anywhere on the spreadsheet will trigger a recalculation, set the optional second function argument to TRUE, as shown in cells H11 and I11.

Posted in Excel, UDFs | Tagged , , , | 3 Comments

The Incredible String Band

Posted on 18/11/2013 by dougaj4

The Incredible String Band was a late 1960’s/ early 70’s musical group often categorised as “psychedelic folk”, and dismissed as a peculiarity of the era of no great interest beyond that.

That is a pity, because the two key members of the group, Robin Williamson and Mike Heron, combined traditional British and World Music with their own words and musical styles to create some of the most poetic, emotive, and original songs written in the English language.

The first two examples below are taken from their 1971 album “The Liquid Acrobat, as Regards the Air”, and the last one from the 1968 album “The Hangman’s Beautiful  Daughter”.

Posted in Bach | Tagged , , , , | 1 Comment

Xlls, Maths and Plots; some useful links

Posted on 11/11/2013 by dougaj4

Two from regular commenter here, Keith Lewis:

Excel xll add-in library:

This is a library for creating xll add-ins for Excel from 97 through 2013. It makes every feature of the latest Excel SDK available to you including the big grid, wide character strings, and asynchronous functions. It is the easiest way to integrate your C and C++, or even Fortran, code into Excel to achieve the highest possible performance. You can also generate native documentation using the same tool Microsoft uses for their help files.

If you need a small, fast, portable, and self contained way to extend Excel’s functionality, this is the library for you. Just hand someone the xll and chm help file that you create and they are ready to go. No need to figure out what version of .Net they run, no Primary Interop Assemblies to worry about, no managed code that forces you to marshal data back and forth from Excel. There are also no automagic code generators, no proprietary markup languages to learn, and no wizards that hide things behind your back. Everything is just pure, modern, and readable C++.

 xll add-in library   :

Check out xllblog for the latest goodies in the pipeline.

And two maths and plotting links from Alfred Vachris:

Geometry Algorithms:

Welcome to the GeomAlgorithms.com website. The full list of Algorithm Titles is shown below, and active links indicate the algorithms that have been posted and are now accessible.

The purpose of this site is to provide practical geometric algorithms for the software developer. That is, algorithms that are:

  • Relevant – they solve significant geometric problems for real world applications
  • Correct – they give accurate solutions for the problems
  • Robust – they tolerate small numerical errors and avoid overflow within constraints
  • Efficient – they are fast in practice for typical applications, both small and large
  • Conservative – they use few resources, such as storage space
  • Maintainable – they are straightforward to implement and troubleshoot
  • Elegant – one can understand why they work, which gives confidence in their use.

Flowing Data
R plotting package ggplot2 ported to Python

Those who use the ggplot2 package in R and do everything else in Python will appreciate this Python port of the package from yhat.

Excel makes some great looking plots, but I wouldn’t be the first to say that creating charts in Excel involves a lot of manual work. Data is messy, and exploring it requires considerable effort to clean it up, transform it, and rearrange it from one format to another. R and Python make these tasks easier, allowing you to visually inspect data in several ways quickly and without tons of effort.

The preeminent graphics packages for R and Python are ggplot2 and matplotlib respectively. Both are feature-rich, well maintained, and highly capable. Now, I’ve always been a ggplot2 guy for graphics, but I’m a Python guy for everything else. As a result, I’m constantly toggling between the two languages which can become rather tedious.

Once you get the Python library installed (and its dependencies), you’ll be able to use the same layered graphics approach as the R package, with a similar syntax.

Also another couple of links from Alfred that have featured here before, but well worth another look:

Axel Vogt

Several files for numerical or financial Math, free for download and with no warranty.

Excellaneous:

This website is meant for scientists and engineers who want to use the ubiquity, convenience and power of Excel, including its flexibility to go beyond the functionality already provided by Microsoft. It contains many freely downloadable, open-access add-in functions and macros for Excel associated with my book, Advanced Excel for scientific data analysis, 3rd ed., Atlantic Academic 2012, as well as additional information that arrived too late tobe incorporated in the printed version or that required color.

Posted in Coordinate Geometry, Excel, Link to dll, Maths, Newton | Tagged , , , | 3 Comments