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.
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!
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
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): –
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): –
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.
Hi , thanks for the post. Must first admit I am a bit of a sheep when it comes to VBA. However, I tried copying your code into VBA 2019 and just running the function =Loading_generate_period_range(5, 0.015). I’m still getting a #NAME error. Am I missing something if I simply just wanted to generate that array?
Hi, as per your comments on the previous post I don’t think it still works on 2019 due to the lack of dynamic array support in Excel 2019, or there’s an error in there somewhere that isn’t playing nice with 2019. I’ll try test again over the next few days if I can find an older copy of excel, and see if I can get it working.
Hi Jason, I’ve updated the post with further information on how to create the required array formulas in non-excel 365 versions, I suspect that’s your issue. Works fine for me anyway in Excel 2019 by creating the array formulas in this manner.
Hi, Craig? Joe?
Nah never mind . OK, I am pretty into what you are doing here. I am not good at coding, However, I guess we sort of having the similar “goal”? automate to the death so we can do something else fun. and having a vision how this industry is moving into. Are you keen to meet up sometime for a coffee or something? I am sure we will both enjoying our conversation hahaha.