Useful Array Functions for VBA, Part 1

The following is a series of articles demonstrating some useful array functions which you can hopefully use to make working with arrays easier.

My inspiration comes from PHP, which has a series of native functions for working with arrays. It seems like PHP has a lot of built-in functionality absent from VBA (MD5 hashing is one of them)

So my goal here is to document these functions, then convert them into an addin so I can use them as if they were native.

Creating Arrays

Sizing an array can be tricky business. Do you size the array when you declare it, or use Redim and do it later? And what if you need to change the size? I'm one of the people that never gets this right the first time.

So I wrote a function that does the work for me. I specify the size of the array, and my function sizes it and returns it to me. I don't have to worry about doing it wrong.

Function Array_Create(numRows As Long, Optional numColumns As Long, _
                      Optional zeroBased As Boolean = True) As Variant
  Dim tempArray As Variant

  If zeroBased Then
    If numColumns = 0 Then
      ReDim tempArray(numRows - 1)
    Else
      ReDim tempArray(0 To numRows - 1, 0 To numColumns - 1)
    End If
  Else
    If numColumns = 0 Then
      ReDim tempArray(1 To numRows)
    Else
      ReDim tempArray(1 To numRows, 1 To numColumns)
    End If
  End If

  Array_Create = tempArray
End Function

We can create four different types of arrays with this function:

1. One based, one dimensional
2. One based, two dimensional
3. Zero based, one dimensional
4. Zero based, two dimensional

In this function I assume that the array is always going to be zero based. Change True to False if you feel otherwise.

Sample Usage

To create a zero based, two dimensional array, simply specify the number of "rows" and "columns" you want.

Dim arr As Variant
arr = Array_Create(5, 2)

The Locals Window shows us an array with five rows and two columns:

Adding False as the third parameter makes the array one based:

Dim arr As Variant
arr = Array_Create(5, 2, False)

One dimensional arrays are the same, except you leave out the second parameter:

Zero based:

Dim arr As Variant
arr = Array_Create(5)

One based:

Dim arr As Variant
arr = Array_Create(5, , False)

Now my arrays are sized and ready to hold whatever data I need to store. Next article we'll fill those arrays.