I have just mended a broken link in the previous installment of “She moves Through the Fair”.

Here are two more, from Tir Eolas:

and Tara McNeill:

An Excel blog for engineers and scientists, and an engineering and science blog for Excel users.

I have just mended a broken link in the previous installment of “She moves Through the Fair”.

Here are two more, from Tir Eolas:

and Tara McNeill:

The input for the first version of the Arcspline function required the coordinates of the centre of each arc, as well as the angle of the tangents at each end of the arc. I have now added a new version with simplified input in a 4 column range.

The new file may be downloaded from: IP2.zip

The required inputs are:

- Row 1: XY coordinates of the start point
- For each arc: XY coordinates of the intersection point of the tangents at the ends of the arc, the arc radius, and the number of segments for each arc
- Last row: XY coordinates of the end point

Options:

- If the spline is a closed curve the end point may be omitted.
- If the spline is not closed, set the optional second argument (Closearc) to False. In this case the end point must be provided.
- The default return array is the coordinates of the spline curve. If the third argument (Out) = 1, details of each arc will be returned (see screen shot below for details).

Since the new function input will usually be much more convenient than the original version, it has been named ArcSpline, and the original function has been renamed ArcSpline2.

Input and output for an asymmetric I section with varying radius fillets:

Output with Out = 1:

Posted in Coordinate Geometry, Excel, Maths, Newton, UDFs, VBA
Tagged Arc-spline function, Excel, IP2.xlsb, UDFs, VBA
Leave a comment

I recently received a query about the 3DFrame spreadsheet failing to run. The problem was that the code was trying to Redim an array that had not been previously created with a Dim statement. This does not cause a problem with Excel versions up to 2003, and from 2013 to date, but in some cases in Excel 2007 and 2010 the routine will not compile.

The solution is simply to ensure that all arrays are created with a Dim statement, before you try to Redim (which is a good idea even if your Excel version doesn’t require it), but in researching this problem I found two rather surprising things about how it was reported on the Internet:

- A Google search on “Excel vba redim changed 2013 compile error” (without the “”) only came up with one relevant link, several pages down (at Daily Dose of Excel).
- The relevant bit was a comment to the main article that I had made myself back in 2009!

The new version of 3DFrame can be downloaded from:

or (as always) from the Downloads page here.

I recently discovered from a thread at Chandoo’s Excel Forum that the VBA Evaluate function can be used as an array function. As a simple example:

Range(“A11:A16”).Value = Evaluate(“= B11:B16 + C11:C16”)

will add the values in columns B and C, and return to column A.

This should not have come as a surprise, since Charles Williams mentioned it in his Excel Blog: Evaluate Functions and Formulas fun: How to make Excel’s Evaluate method twice as fast. As a rather more useful example than the one above, I have looked at options for calculating the distance between lists of pairs of coordinates. The code below shows four subroutines that read X and Y coordinates from a specified 4 column range, and return the distance between the two points on each row to the right of the table.

A sample spreadsheet with full open source code and examples may be downloaded from:

Sub Distance() With Sheet1.Range("B12").CurrentRegion.Columns .Item(6).Value2 = .Parent.Evaluate("=((" & .Item(3).Address & " - " & .Item(1).Address & ")^2 + (" & .Item(4).Address & "-" & .Item(2).Address & ")^2)^0.5") End With End Sub Sub Distance2() Dim Col1 As String, Col2 As String, Col3 As String, Col4 As String With Sheet1.Range("B12").CurrentRegion.Columns Col1 = "sheet1!" & .Item(1).Address Col2 = "sheet1!" & .Item(2).Address Col3 = "sheet1!" & .Item(3).Address Col4 = "sheet1!" & .Item(4).Address .Item(6).Value2 = Evaluate("=((" & Col3 & " - " & Col1 & ")^2 + (" & Col4 & "-" & Col2 & ")^2)^0.5") End With End Sub Sub Distance3() Dim XYData As Range Dim Col1 As String, Col2 As String, Col3 As String, Col4 As String Set XYData = Range("sheet1!B12:B111") With XYData.Columns Col1 = .Item(1).Address Col2 = .Item(2).Address Col3 = .Item(3).Address Col4 = .Item(4).Address .Item(6).Value2 = .Parent.Evaluate("=((" & Col3 & " - " & Col1 & ")^2 + (" & Col4 & "-" & Col2 & ")^2)^0.5") End With End Sub Sub Distance4() Dim XYData As Range, Res As Variant Dim Col1 As String, Col2 As String, Col3 As String, Col4 As String Set XYData = Range("sheet1!B12:B111") With XYData.Columns Col1 = .Item(1).Address Col2 = .Item(2).Address Col3 = .Item(3).Address Col4 = .Item(4).Address Res = .Parent.Evaluate("=((" & Col3 & " - " & Col1 & ")^2 + (" & Col4 & "-" & Col2 & ")^2)^0.5") .Item(6).Value2 = Res End With End Sub

In the first and third routine the Evaluate function is preceded by .Parent, which ensures that the addresses are treated as being on the same sheet as the range specified in the “With” statement. If this is omitted the addresses will be treated as being on whatever sheet is active when the routine is called. An alternative is to specify the sheet name with each range (as in Distance2 above). The fourth function is as Distance3, but the Evaluate results were written to an array for each iteration, and only written back to the spreadsheet once, after the last iteration. Benchmark results are shown below for these four routines with a range of column lengths and iterations:

Evaluate can also be used in this way from a user defined function (UDF). The code below shows three alternatives.

Function DistF(XYData As Variant) Dim DistA() As Double, i As Long, NRows As Long, j As Long XYData = XYData.Value2 NRows = UBound(XYData) ReDim DistA(1 To NRows, 1 To 1) For j = 1 To NRows DistA(j, 1) = ((XYData(j, 3) - XYData(j, 1)) ^ 2 + (XYData(j, 4) - XYData(j, 2)) ^ 2) ^ 0.5 Next j DistF = DistA End Function Function DistF2(XYData As Range) Dim DistA As Variant Dim Col1 As String, Col2 As String, Col3 As String, Col4 As String With XYData.Columns Col1 = .Item(1).Address Col2 = .Item(2).Address Col3 = .Item(3).Address Col4 = .Item(4).Address DistA = .Parent.Evaluate("=((" & Col3 & " - " & Col1 & ")^2 + (" & Col4 & "-" & Col2 & ")^2)^0.5") End With DistF2 = DistA End Function Function DistF3(XY_1 As Range, XY_2 As Range) Dim DistA As Variant Dim Col1 As String, Col2 As String, Col3 As String, Col4 As String, EvalTxt As String With XY_1.Columns Col1 = .Item(1).Address Col2 = .Item(2).Address End With With XY_2.Columns Col3 = .Item(1).Address Col4 = .Item(2).Address EvalTxt = "=((" & Col3 & " - " & Col1 & ")^2 + (" & Col4 & "-" & Col2 & ")^2)^0.5" DistA = .Parent.Evaluate(EvalTxt) End With DistF3 = DistA End Function

The first does not use Evaluate, converting the input data to a variant array, then looping through each row, writing the results to an array which is returned to the spreadsheet at completion. The second is similar to Option 3 in the subroutines, except the results are written to an array, which is returned at completion. The third option has two separate input arrays, so the function can be used on lists of coordinates that are not in adjacent columns. It also creates the string to be evaluated in a separate operation, which makes checking of the text easier. Benchmark results for the three functions are shown below:

The two functions using evaluate had very similar performance, and were 2-3 times faster than the routine that looped through the coordinate arrays. They were also faster than the subroutines, probably because the time do not include the time to write the results to the spreadsheet.

Posted in Arrays, Coordinate Geometry, Excel, Maths, UDFs, VBA
Tagged array functions, Evaluate, Excel, UDFs, VBA
2 Comments

I have added two user defined functions (UDFs) to the IP2 spreadsheet to generate coordinates for a single arc, or a series of arcs connected (if necessary) by straight lines. The new version may be downloaded from:

As an example of the use of the new functions, in conjunction with the IP (intersection) function, I have generated an animation showing the movement of two circular bearings, relative to a rotating tri-lobed shaft. The tri-lobe is generated using the ArcSpline function, by defining the centre, radius, start and end angles, and number of segments for each of the six arcs:

A second arc-spline is then generated at 5 mm outside the first curve, representing the path that bearings of 5mm radius would follow, running around the outside of the inner spline. The intersection point of this outer spline can then be found using the IP function, and circles generated at these points, using the Arc function:

Full input for the two splines and two arcs is shown below.

The animation is then generated by recalculating the angular limits and centres of the spline curves, and the coordinates of the intersection of the circles and the X axis, for a series of small angle increments:

… or in some cases, old but forgotten tricks.

The first comes from a Quora question, asking for the most underutilised feature in Excel.

When de-bugging a VBA routine you can set a break point where the code will stop:

To jump to a different line you can right-click, select “set next statement”, and click the line you want to jump to, but a quicker way is to just click and drag the yellow arrow to where you want to go:

The next is a post from Jeff Weir at Daily Dose of Excel, looking at how to display tool-tips with user defined functions (UDFs). If you enter a UDF name, including opening bracket, then press Ctrl-Shift-A, all the argument names are entered automatically, which can then be replaced with the actual range or value you want:

I had totally forgotten about this, but I must have known about it once, as I have posted two comments on it.

Finally, another tool-tip trick that I had forgotten about, but that has appeared here before. If you hover over a variable whilst de-bugging a VB routine, the first 77 characters of the current value of the variable are displayed as a tool-tip:

If the value is a long string, and you want to see the last 77 characters, hold down the Ctrl key before you hover:

Reading the comments on a Davey Graham YouTube video:

I discovered that the bass player on the track was Danny Thompson, and checking the other artists playing on the album, “Large and Life and Twice as Natural”, I found they included Jon Hiseman – on drums, and Dick Heckstall-Smith – on saxophone. These same two have also played with Jack Bruce (more famous for electric bass), including on “Things we Like”, which has appeared here before:

I have often wondered if there was any interaction over the years between Danny Thompson and Jack Bruce. I have still not seen or read any direct evidence of this, but given their interaction with Hiseman and Heckstall-Smith, it seems highly likely.

Posted in Bach
Tagged Bach, Bruton Town, Danny Thompson, Davey Graham, Dick Heckstall-Smith, Double Bass, Jack Bruce, Jon Hiseman
Leave a comment