ConBeamU 4.08

The continuous beam spreadsheet has had another update to fix a problem when point loads were applied to cantilevers, and the first support position was not listed as an output point.  The new version (4.08) can be downloaded from:

The spreadsheet results have been checked against Strand7 results for 15 different span arrangements, each with 1 of 6 different support conditions:


The results are summarised in the file Check conbeamU28Nov15.xlsb (included in the download zip file).  Typical results are shown below for a 3 span beam with two cantilevers, showing near exact agreement with the Strand7 results.


Posted in Beam Bending, Excel, Finite Element Analysis, Frame Analysis, Newton, Strand7, UDFs, VBA | Tagged , , , , , , | Leave a comment

Announcing Excel Summit South 2016!

After 2 years of organising, Charles Williams recently announced:

Excel Summit South


This is a unique opportunity to:

  • Learn from six of the world’s leading Excel MVP’s as they discuss the Excel topics most useful to you.
  • Hear industry leading speakers from around the world give you the latest views on Financial Modelling best practices, standards and spreadsheet risk.
  • Shape the future of Excel: Interact with members of the Microsoft Excel Dev Team as you explore with them the future of Excel.
  • Choose the sessions that best suit your needs from 23 masterclass sessions over two days of twin tracks for modellers and analysts.



EarlyBird 20% discount available for registrations before December 31 2015.
Don’t miss out out on this unique Excel opportunity.

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

Paper bridge

Making a model bridge out of paper is pretty easy.

Making a full size bridge, strong enough to carry a Range Rover, out of just paper takes a bit of doing:

It seems the guy who built the bridge (Steve Messam) has been interested in paper bridges for some time:


Layers – part 1

Layers – part 2

Layers – part 3

Posted in Arch structures, Newton | Tagged , , , | Leave a comment

Importing Text Files; Unix Format

Text files generated on Unix systems have a different convention to denote the ends of lines to that used on Dos/Windows systems (see Newline for more details).  This was causing problems with the Text-in2 spreadsheet, with small files being imported with all text on one line, and large files causing overflow errors.  I have now added an option to convert Unix line endings to the Windows format, allowing these files to be imported successfully.  The new spreadsheet (including full open-source code) can be downloaded from:

Code for this routine is quite brief:

Sub ConvertUnix(FName As String, SName As String, FileSaved As Boolean)
    Dim WholeLine As String

    If SName = "" Then SName = FName
    Close #1
    Open FName For Input Access Read As #1
    Line Input #1, WholeLine
    If EOF(1) Then
        WholeLine = Replace(WholeLine, vbLf, vbCrLf)
        Close #1
        Open SName For Output Access Write As #1
        Print #1, WholeLine
        Close #1
        FileSaved = True
        FileSaved = False
    End If
End Sub

The routine reads the first line of the input file.  If this line includes the End of File (EOF) marker, this indicates that the file was in Unix format, and the VBA Replace function is used to replace all occurrences of the Line Feed character (vbLf) with Carriage Return/ Line Feed (vbCrLf).  The file is then saved back to disk, either over-writing the original file, or optionally writing to a new file.

Use of the new option is shown in the screen-shot below:


Click for full size view

Note that “Convert Unix to Windows” has been set to True (Cell B15), and a different file name has been entered for the converted file (Cell B16).

Other features of the spreadsheet are illustrated in the following screen-shots.

Criteria for import of text may be specified for a range of lines, or for the whole file.  In the screen-shot below only lines with values greater than 990 in column 3 are imported, starting from line 2 (so that the headings in line 1 are imported):


The ReadText Function returns text from the named file, with the added option to read specific line numbers, as illustrated below:


The SplitText function splits text into columns (at the specified separator):


The Text2Date function converts a date in text format into an Excel date value.  In the case of ambiguous dates, interpretation may be based on the local order (default), or mm/dd/yy (Date Order = 0):


The NumLeft and NumRight functions extract the first number from either the left or right hand end of a text string:


The ExtractNum and ExtractNums functions extract one or more numbers from anywhere in a text string, or range of strings:


The GetNumFormat and GetDType functions return the number format string and the data type of the contents of a cell or a range:


For more details see:

Importing text files with VBA – 2

Importing text files with VBA – 3

Importing tab delimited files and clearing large ranges

Combining text files

Importing selected rows from a text file

Extracting numbers from text strings

Extracting numbers with regular expressions

Dealing with dates 3: opening and saving csv files without data corruption

Posted in Excel, UDFs, VBA | Tagged , , , , , , , , | Leave a comment

John Renbourn; Trotto and English Dance

A YouTube video of a live performance of John Renbourn’s English Dance was removed some time ago.  The video below features a different performance, so I hope it survives:

And from the same source, a recording of a full concert in Japan in 1979 (as far as I know, not previously released):

Posted in Bach | Tagged , , | Leave a comment

The VBA Decimal data type

This is what the Microsoft on-line help says about using the Decimal data type in VBA


At least that is what the Microsoft search listed for me, and Google listed nothing from Microsoft related to VBA on the first page.

The VBA help takes you to the Visual Basic page on the same data type, which says.

Holds signed 128-bit (16-byte) values representing 96-bit (12-byte) integer numbers scaled by a variable power of 10. The scaling factor specifies the number of digits to the right of the decimal point; it ranges from 0 through 28. With a scale of 0 (no decimal places), the largest possible value is +/-79,228,162,514,264,337,593,543,950,335 (+/-7.9228162514264337593543950335E+28). With 28 decimal places, the largest value is +/-7.9228162514264337593543950335, and the smallest nonzero value is +/-0.0000000000000000000000000001 (+/-1E-28).

This would be OK if VB and VBA were essentially the same in their use of this data type, but they are not.  For instance:

  1. The VB help says that Decimals are declared with: Dim bigDec1 As Decimal, but this is not accepted in VBA.
  2. In VB appending the identifier type character @ to any identifier forces it to Decimal, but in VBA this forces the identifier to the Currency data type (which has only 4 decimal places).
  3. In VB appending the literal type character D to a literal forces it to the Decimal data type, but in VBA this generates an “Overflow” error message.

So can the Decimal data type actually be used in VBA?  Yes, it can (and it is reasonably straightforward), but there are a number of aspects that need to be handled carefully, and use in engineering and scientific calculations is greatly restricted.

To use a Decimal in VBA, declare it as a variant, then use the CDec() function to convert an input value into a decimal.  If data is being read from a spreadsheet cell it may be entered as a number if it has 15 or less significant figures, or as a text string if 16 or more figures are required.  The code below will add the two values a and b, and return the result as a string, or optionally as a Decimal. Returning a Decimal value to the spreadsheet will result in it being converted to a double, but if it is being used in another VBA routine keeping it as a Decimal will be more efficient.

Function DecAdd(a As Variant, b As Variant, Optional RtnString As Boolean = True) As Variant
    Dim Res As Variant
    Res = CDec(a) + CDec(b)
    If RtnString Then
        DecAdd = Str(Res)
        DecAdd = Res
    End If
End Function

This function will allow values (entered as text strings) with up to 28 significant figures to be added on the spreadsheet, and the Decimal.xlsb spreadsheet has similar short functions to subtract, multiply, divide, and find the maximum and minimum of two values.

There is also a function to find the square root of any input value (based on code taken from VBAExpress), but this brings us to the main drawback of this data type.  If any of the VBA maths functions are used on a Decimal, the result will be returned as a Double, unless you write your own function, using only the basic arithmetic operators (plus a small number of other simple functions, such as Abs()).

Fortunately there is an easier way of carrying out high precision calculations.  The XNumbers package will do arbitrary precision calculations, and is now available for Excel 2007 and later.  See XNumbers for more details and free download.

Posted in Excel, Maths, Newton, UDFs, VBA | Tagged , , , | Leave a comment

Footsteps of The Incredible String Band

I just thought I would ask Google who there is playing music these days developing the tradition of the Incredible String Band, and after wading through long lists of 40 year old albums I eventually found:

Trembling Bells

Sweet Death Polka

Waltz of the New Moon:

And something rockier, live this year:

Posted in Bach | Tagged , | Leave a comment