Importing text files with VBA – 3

Posted on 27/06/2008 by dougaj4

The text import file has been modified to split large files into one or more ranges.

The maximum number of rows for each range is specified, together with a sufficient number of valid range names, which may be on the same sheet or different sheets. The modified file has been tested with a text file with about 1.4 million lines (43 MB). In Excel 2007 this is imported into two ranges in about 10 seconds, or about 75 seconds if it is split into 7 columns. In Excel 2000 the import stopped with a memory error after about 800,000 lines, so for very large files Excel 2007 does seem to have a clear advantage. On the down side, clearing large ranges in Excel 2007 is often excrutiatingly slow. Clearing the 1.4 million lines of data imported in 10 seconds took several minutes!

The ReadText UDF has also been modified to work with large files. It is now possible to specify a column with a list of line numbers, and only the specified lines will be imported. In this way the complete file may be imported into separate ranges, or parts of the file may be conveniently extracted. Note that if the UDF is used with a file exceeding the number of available lines in the spreadsheet the UDF will return an error if the line numbers have not been specified. For smaller files the line number list is optional.

The final refinement in this version is that the SplitText routines now allow the maximum number of columns to be specified. This is illustrated with a directory listing, with the file date and size details split into columns, but the name kept in one column, even if it includes spaces.

Download Text-in2.zip Ver1.30

1.4 million line text file imported into Excel 2007 in two ranges (about 10 seconds)
Read text from large file - output split into 2 ranges

Bottom of range 1 (just to prove it’s all there!)

Read text from large file - XL2007 bottom of range 1

1.4 million line text file split into columns (about 75 seconds)

Read and split text from a large file

ReadText function reading every 40th line from the 1.4 million line text file
ReadText and SplitText functions with specified row numbers

Reading a directory listing into 5 columns

Directory listing - split into 5 columns

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

Irish Graffiti

Posted on 25/06/2008 by dougaj4

On the 16th October 1843 the Irish mathematician William Hamilton was taking a walk with his wife, alongside the Royal Canal in Dublin, when the answer to a problem that he had been puzzling over came to him, and he was so excited by this discovery that he carved the equation:

I2 = j2 = k2 = ijk = -1

on a stone of the nearest bridge.

History does not record the reaction of his wife to this act, but judging by his recollection of his conversation with his sons:

Every morning in the early part of the above-cited month, on my coming down to breakfast, your (then) little brother William Edwin, and yourself, used to ask me: “Well, Papa, can you multiply triplets?” Whereto I was always obliged to reply, with a sad shake of the head: `No, I can only add and subtract them”.

I suspect that she was not unduly surprised.

The graffiti was soon worn away, but the event was later recorded more permenantly with a commemerative stone with the words:

Here as he walked by
on the 16th of October 1843
Sir William Rowan Hamilton
in a flash of genius discovered
the fundamental formula for
quaternion multiplication
i2 = j2 = k2 = ijk = -1
& cut it on a stone of this bridge

For more information look here: http://math.ucr.edu/home/baez/dublin/ for an account of a trip to the bridge by mathematician John Baez and friends, including some nice photographs of the structure itself.

Another site with more on the mathematical significance of the quaternion, and a quote from Hamilton himself: http://adaptivecomplexity.blogspot.com/2007/06/science-in-against-day-vectors-and.html

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

Drawing in Excel – 2

Posted on 22/06/2008 by dougaj4

Before getting down to basics, the attached file:

Animation demo

Illustrates how Excel shapes can be simply animated.

Screen shot:
Animation demo

The code for the animation (having previously drawn a circle and a rectangle with no fill, and noted their names):

Sub Animate()
Dim Start As Single, xInc As Single, yInc As Single, OvlWidth As Single, OvlHeight As Single
Dim OvlX As Single, OvlY As Single
Dim TopBox As Single, BottBox As Single, LeftBox As Single, RightBox As Single
Dim Pi As Double, TimeStep As Double, XV As Double, YV As Double
Pi = Atn(1) * 4

XV = Range(“hspeed”).Value
YV = Range(“vspeed”).Value

TimeStep = 0.01

With ActiveSheet.Shapes(“oval 2”)
OvlWidth = .Width
OvlHeight = .Height
End With

With ActiveSheet.Shapes(“rectangle 14”)
TopBox = .Top + OvlHeight / 2
BottBox = TopBox + .Height – OvlHeight
LeftBox = .Left + OvlWidth / 2
RightBox = LeftBox + .Width – OvlWidth
End With

xInc = XV * (RightBox – LeftBox) / 1000
yInc = YV * (BottBox – TopBox) / 1000


With ActiveSheet.Shapes("oval 2")
Do
.IncrementLeft xInc
.IncrementTop yInc
Start = Timer
Do While Timer < Start + TimeStep
DoEvents
Loop
OvlX = .Left + OvlWidth / 2
OvlY = .Top + OvlHeight / 2
If OvlX &LT LeftBox Or OvlX &GT RightBox Then xInc = -xInc
If OvlY &LT TopBox Or OvlY &GT BottBox Then yInc = -yInc
Loop
End With
End Sub

Where &LT and &GT indicate the “Less Than” and “Greater Than” symbols respectively.

Posted in Drawing, Excel, Newton | Tagged , , , , | 19 Comments

Reinforced Concrete Section Analysis – 4

Posted on 21/06/2008 by dougaj4

Previous post

The spreadsheet presented in the previous post in this series uses the basic equation given below to find the location of the neutral axis:

For a cross section with any applied axial load at eccentricity e, measured from the compression face, and depth of the neutral axis X below the compression face:

INA = QNA(X + e)

Where QNA and INA are the first and second moments of area about the neutral axis.

To illustrate this relationship the value of INA has been plotted for a range of neutral axis positions for a typical precast bridge beam, shown below, and compared with the value QNA(X + e) for 5 different values of e.

Super-T
The intersection of the INA and QNA(X + e) lines gives the depth of the neutral axis for the corresponding value of load eccentricity, e.  This is illustrated in the second graph, where the value of (INA – QNA(X + e)) is plotted against the depth of the neutral axis, X, for a load eccentricity of 1 metre above the compression face.  The intersection of this line with the X axis gives the depth of the neutral axis for the specified load eccentricity.

Ina and Qna(x+e) v Depth NA

Ina - Qna(X+e) v Depth NA

Posted in Beam Bending, Excel, Newton, UDFs, Uncategorized | Tagged , , , , | 1 Comment

Importing text files with VBA – 2

Posted on 15/06/2008 by dougaj4

Previous post

The text file import routines and functions presented in the previous post have been modified with the following additions:

Lines of text can be split into separate cells, at any designated character (e.g. a coma or a space)

The SplitText function can also be used as a UDF, to split lines of text previously imported.  In this case the most efficent way is to enter the UDF as an array function, operating on the complete range of text.  In this way the UDF is only called once, returning a 2D array, and the whole operation is much quicker.

Download Textin2.zip

Screen shot:

 Text-in2<

 

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