As I mentioned in the title – arrays are awesome. This is one of the main things You should learn working with Excel VBA. In case of data, I think I can safely say, that arrays are amazing alternative for tables in VBA. To prove that statement I’d like to start with 1-dimension array.
But first things first – You may ask:
What the heck are thooose arrays?!
I’m glad You asked, because I am prepared to share some info with You 😉
I will not share You definitions from other websites or from books kinda What is VBA? I’ll just show You how I understand arrays and how am I using them to deal with Excel cases and… other stuff.
So, getting to the point I already mentioned above: 1-dimension array – what is that? In the simple words, it’s like a 1 column table, but virtual.
Let’s compare looping in standard Excel table and in array to prove why are they awesome.
Code for looping test in Excel table:
Sub test1() Dim tajm As Double Dim i As Long tajm = Timer With ThisWorkbook.Sheets(1) For i = 1 To x .Range("A" & i) = i Next End With Debug.Print Round(Timer - tajm, 3) End Sub
where x stands for rows number, same for Sub test2 (You’ll see in the table down below).
Code for looping test in array table:
Sub test2() Dim arr As Variant Dim tajm As Double Dim i As Long tajm = Timer arr = ThisWorkbook.Sheets(1).Range("A1:A" & x) 'e.g. "A1:A1000" etc For i = LBound(arr) To UBound(arr) arr(i, 1) = i Next i ThisWorkbook.Sheets(1).Range("A1:A" & x) = arr Debug.Print Round(Timer - tajm, 3) End Sub
In second case first of all I had to set the range of array, then loop on it and in the end insert results into the Excel range back again.
I made a few tests with those simple codes (inserting the number of iteration into cells) for couple different row numbers. The results are shown in the table below in seconds.
As You can see for the small amount of data (rows) there is no visible difference for user in those methods, so in that case there is no need to make it harder in code – to use array.
But even that, based on tests, we can clearly see the multiplication in time for the same rows.
That ‘visible’ difference is in the 100 000 rows and above. I mean the time for standard table increased above a second.
It is a huuge gap between the results – arrays are about 40times quicker! And that gap You would feel, as a user or as a developer, unbelievable difference. It really means a lot!
Don’t You think now that arrays are the best tables alternative in VBA?!
Remember also that was a simple task in every iteration. That time would be much higher having conditionals and others stuff inside loops.
Keep that in mind building a code what would suits more your data.