Update to ADRS curve generation functions … now works in Excel 2019 plus random side-tracking… (Part 5)

As pointed out here in a recent comment by user Jason on the last post in this series, the VBA code for generating ADRS curves did not work in Excel 2019 as it turns out Excel 2019 does not include all of the worksheet functions that are included in Excel 365.

Bad Microsoft!

Basically, they don’t want you to buy Excel 2019 I guess, or should I say they’re really providing less incentive to buy Excel 2019. They like you to be locked into the subscription model so they have a steady income.

Quite frankly it works out ok for me as work pays for it anyway, and the benefit of being a subscriber is that we poor users get the latest and greatest updates/features hot off the press.

You lot know if you have Excel 365 at work that it entitles you to install it on other computers right, like your personal laptop?

For the poor people who are die-hard perpetual licence aficionados stuck on Excel 2019 (or earlier), despite you owning something shiny, you’re sadly missing out on all the latest and greatest features. I believe Microsoft have said they never intend to update normal non-365 Excel to add the features being added to Excel 365.

The VBA code I’m talking about made use of the worksheet function SORT() to sort an array of periods into numerical order: –

'VBA stuff!
blah_blah_blah = WorksheetFunction.Sort(blah)

As opposed to implementing some custom sorting routine because quite frankly it was pretty simple. Why VBA has no in-built sorting routine is probably answered by the fact it has had no real love as a language since many years ago when it was last updated (unsure of exact date). I’m still holding out for Python to be officially integrated before Python dies, I think I’ll be waiting a while.

So back to the point of the post if there even was one….. one line of code calling a worksheet function vs 50+ lines of custom code, I know which one I’d rather see and implement.

On top of this to boot, the worksheet function is potentially faster than any custom code I might write because it’s probably implemented in another language in a DLL that is not written in VBA, and it’s only being called once anyway so who cares right!

Sidetracked….

Wherever possible, I’d try avoiding using worksheet functions in VBA, some things are darn slow by comparison but I’m not becoming a martyr to that cause on this day. Practicality wins!

If in doubt compare using min and max worksheet functions on an array vs a few lines of an if statement to do the same thing in VBA without the use of a worksheet function.

Here’s a quick (non-scientific) comparison based on finding the maximum values for a random array of values: –

Sub test_max()
    Dim timer_start As Double
    Dim worksheet_time As Double
    Dim VBA_time As Double
    Dim max_VBA As Double
    Dim max_worksheet As Double
    Dim arr()
    Dim i As Long
    Dim j As Long

    For j = 10000000 To 50000000 Step 10000000
        ReDim arr(1 To j)
    
        For i = 1 To UBound(arr)
            arr(i) = Rnd(1)
        Next i
    
        'the worksheet function way
        timer_start = Timer
        max_worksheet = WorksheetFunction.Max(arr)
        worksheet_time = Round(Timer - timer_start, 3)
    
        'the VBA way
        timer_start = Timer
        For i = 1 To UBound(arr)
            If arr(i) > max_VBA Then max_VBA = arr(i)
        Next i
        VBA_time = Round(Timer - timer_start, 3)
    
        Debug.Print j & " random numbers array length"
        Debug.Print "worksheet function execution time = " & worksheet_time
        Debug.Print "VBA function execution time = " & VBA_time
        Debug.Print "VBA function took " & Round(100 * VBA_time / worksheet_time, 2) & "% of worksheet function time"
        Debug.Print
    Next j
End Sub

While this isn’t the best way to time functions, the end result in the VBA editors intermediate window is the following showing roughly 75% of the time to execute the cobbled-together VBA MAX function vs using the equivalent worksheet MAX() function.

10000000 random numbers array length
worksheet function execution time = 0.535
VBA function execution time = 0.41
VBA function took 76.64% of worksheet function time

20000000 random numbers array length
worksheet function execution time = 1.074
VBA function execution time = 0.82
VBA function took 76.35% of worksheet function time

30000000 random numbers array length
worksheet function execution time = 1.621
VBA function execution time = 1.207
VBA function took 74.46% of worksheet function time

40000000 random numbers array length
worksheet function execution time = 2.117
VBA function execution time = 1.559
VBA function took 73.64% of worksheet function time

50000000 random numbers array length
worksheet function execution time = 2.625
VBA function execution time = 1.992
VBA function took 75.89% of worksheet function time

So anyway, to cut a long story short and to help those of you stuck on Excel 2019 (and probably earlier), I’ve updated the code to add an alternative means of sorting based on the divide and conquer algorithm.

The sorting algorithm code

This sorting routine can be stolen for your personal use and used for other array sorting tasks; you can sort a 2D array based on any column if you like, otherwise, the first array column is the default that all other columns will be sorted by: –

Function array_quicksort_2D(arr As Variant, Optional sort_column As Long = -1) As Variant
'Function that sorts a two-dimensional VBA array from smallest to largest using a divide and conquer algorithm
'sorting is undertaken based on the column specified, if no column is specified the lower bound column is used

    Dim lower_bound As Long
    Dim upper_bound As Long

    lower_bound = LBound(arr, 1)
    upper_bound = UBound(arr, 1)

    Call array_quicksort_2D_sub(arr, lower_bound, upper_bound, sort_column)

    'Return results
    array_quicksort_2D = arr

End Function

Private Sub array_quicksort_2D_sub(ByRef arr As Variant, lower_bound As Long, upper_bound As Long, Optional sort_column As Long = -1)
'Sub-procedure that sorts a two-dimensional VBA array from smallest to largest using a divide and conquer algorithm
'sorting is undertaken based on the column specified, if no column is specified the lower bound column is used

'called from array_quicksort_2D function, but can be used independantly of this incompassing function

    Dim temp_low As Long
    Dim temp_high As Long
    Dim pivot_value As Variant
    Dim temp_arr_row As Variant
    Dim temp_sort_column As Long

    If sort_column = -1 Then sort_column = LBound(arr, 2)
    temp_low = lower_bound
    temp_high = upper_bound
    pivot_value = arr((lower_bound + upper_bound) \ 2, sort_column)

    'Divide data in array
    While temp_low <= temp_high
        While arr(temp_low, sort_column) < pivot_value And temp_low < upper_bound
            temp_low = temp_low + 1
        Wend
        While pivot_value < arr(temp_high, sort_column) And temp_high > lower_bound
            temp_high = temp_high - 1
        Wend

        If temp_low <= temp_high Then    'swap rows if required
            ReDim temp_arr_row(LBound(arr, 2) To UBound(arr, 2))
            For temp_sort_column = LBound(arr, 2) To UBound(arr, 2)
                temp_arr_row(temp_sort_column) = arr(temp_low, temp_sort_column)
                arr(temp_low, temp_sort_column) = arr(temp_high, temp_sort_column)
                arr(temp_high, temp_sort_column) = temp_arr_row(temp_sort_column)
            Next temp_sort_column
            Erase temp_arr_row
            temp_low = temp_low + 1
            temp_high = temp_high - 1
        End If
    Wend

    'Sort data in array in iterative process
    If (lower_bound < temp_high) Then Call array_quicksort_2D_sub(arr, lower_bound, temp_high, sort_column)
    If (temp_low < upper_bound) Then Call array_quicksort_2D_sub(arr, temp_low, upper_bound, sort_column)

End Sub

TLDR…..

Check out the full updated code for the NZS1170.5 Seismic Coefficient functions on Github. Should now work for Excel 2019 and earlier.

Use the above code to sort in VBA.

Avoid using Worksheetfunction.blah functions in VBA unless you have to write 50+ lines of code to replicate whatever one simple line of code does….

A further update…. just to be sure

To get this working correctly in Excel 2019 (or possibly earlier versions), you need to enter the Loading_generate_period_range() & Loading_ADRS() functions as array formulas.

This is the way on-sheet arrays used to be before Microsoft introduced dynamic arrays. Dynamic arrays made dealing with array formulas about 1000 times less shit. Anyone who has used array formulas a lot knows the pain I’m talking about and how bloody annoying they can be.

But let’s go through it step by step: –

Firstly, let us address the basics of creating an array formula for the Loading_generate_period_range function. The thing with array formulas is you need to select the cells you want to apply the formula to before writing the formula in the formula bar. For the number of rows to select it needs to be a number equal to 12 + period range/period step. These are basically some points of interest where the spectral shape functions change formulas and all the regularly spaced period steps you want a result at to give you a nice smooth ADRS curve.

So, for say a period range of up to 5 seconds, reporting results at 0.015 second steps, the number of rows to select in a single column would be 12 + 5 / 0.015 = 345.33, so select a range of 345 rows by 1 column.

Then select the formula bar and enter the following, where cell A10 in this case contains the parameter T_site (this is optional, refer to previous posts for definition): –

=Loading_generate_period_range(5,0.015,A10)

Then this is the key bit, instead of just pressing ENTER like a normal person, press CTRL + SHIFT + ENTER and curse your IT department for not investing in Excel 365. Congratulations, you just created an array formula. If you select any of the cells in the array formula range, you will note that squiggly brackets around the entire formula which denote an array formula.

You should at this point see a range of 345 rows with the period range, in my case this range is B10:B354: –

If you make a mistake, select the entire range again and make your changes and CTRL + SHIFT + ENTER. The issue is sometimes when working with array formulas is that you don’t know the extent of an array, which will drive you nuts…. use the function in Find & Select > Go to special > Current array to select the entire array, you can then delete it or amend it as required.

The next thing you need to do is to do a similar process for the Loading_ADRS function, this function returns a 2-column array with spectral displacement and acceleration ordinates, so this time we need to select a range that is 2 columns wide and the same number of rows as the previously generated array formula. Then select the formula bar and enter the following, I’ve hardcoded some of the parameters for demo purposes (got lazy, I just want this array formula business to be over), replace with the relevant cell references for your variables and preferably using named cells (see earlier posts for definitions). Note here for the T_1 variable that we reference the entire range for the period array we created previously (give it a named range also): –

=Loading_ADRS((B10:B354),"c",0.23,1,20,0.9,15,FALSE,$A$10)

Once you’ve completed the formula in the formula bar, press CTRL + SHIFT + ENTER to enter as an array formula again.

You should at this point see a range of a further 345 rows by two columns with the displacement and acceleration ordinates magically appearing: –

Plotting this deflection vs acceleration data should yield you an ADRS curve!

The downside of using Excel 2019 (or earlier) and array formulas is that the array you’ve created is a fixed length/width, so there is no dynamic-ness like in Excel 365 in terms of say changing the length or number of rows of the data being generated (for example by changing the period range from 5 seconds to 10 seconds).

If this is a problem, get Excel 365. Get it anyway.