Here’s a nice explanation of arrays
On the Stackoverlow Podcast, Joel and Jeff agree that arrays are obsolete. There are far better data structures, most of which are built on arrays.
I still use arrays quite a bit. But considering I’m using an obsolete language (VBA = VB6), I guess that’s not a surprise to anyone. There’s surely no better way to read and write to an Excel range that with an array.
But I use it for simple lists too.
Dim vaInclude As Variant ‘holds variant array
Dim lMatch As Long
vaInclude = Array(“chiefs”, “bengals”, “broncos”)
On Error Resume Next
lMatch = Application.WorksheetFunction.Match(“packers”, vaInclude, False)
On Error GoTo 0
Debug.Print “Is in list: “ & CBool(lMatch > 0)
One thing that I plan to change about my coding habits is Redimming arrays. I’m the type of guy who only adds one memory location at a time to his arrays so that the array is only as big as I need it. I know that’s inefficient and I’m going to stop it. Joel recommended doubling the upper bound whenever you need to increase the allocation. I’d never heard that before. Is that what most people do? Or do they just add an arbitrarily large number? I need to come up with some best practices around Redimming, like how to test if an array is initialized, how much to add, how to only loop through what’s there, etc.