Working With Arrays 2

In this blog post we will be expanding on the capabilities of arrays in VBA.

One of the headaches with arrays is that they are static blocks of memory and are not designed to change in size.  If we want to take an array that can hold 10 items and modify it so that it holds 20 items, we have to re-declare it (which will wipe the memory)! Never fear, VBA has a way to handle this.

ReDim and Preserve

VBA offers the ReDim function which performs much of the leg-work involved in changing an array’s size.  ReDim also has a useful keyword Preserve which preserves the data in your array as you change its size.

Dim myIntegerArray() as Integer ‘ define array variable
ReDim myIntegerArray(10)        ‘ set array size and memory allocation
myIntegerArray(0) = 22          ‘ set (0) to 22
ReDim Preserve myIntegerArray(20)‘ extend array preserving (0)=22

The standard ReDim function would destroy the old array and make a new one; with the Preserve keyword included VBA creates the new array of the new size and copies over the previous arrays values, making them available to us.

Erasing an Array

Erasing an array is so important that VBA – a language that usually makes things easy for programmers – provides a dedicated function to release memory held by an array.  If you do not remove an array VBA will garbage collect memory space left when variables go out of scope, but you are advised to explicitly erase array structures when finished with them.  Once erased, the variable must be ReDim’d.

Dim myVariableArray() As Variant
ReDim myVariableArray(10)
myVariableArray(0) = 1
myVariableArray(1) = 2
myVariableArray(2) = 3

Erase myVariableArray ‘ myVariableArray has no more data and must be ReDim’d to be used

Split Function

The split function splits a string into an array of strings based on some delimiter.  The following example demonstrates splitting a string based on spaces.  Execute it in the Immediate window.

Sub SplitFunction()
Dim i As Integer
Dim myArray() As String
       myArray = Split("here;we;go;again!", ";")
       For i = LBound(myArray) To UBound(myArray)
           Debug.Print myArray(i)
      Next i
End Sub

The output of the above code will be:

Join Function

Join does the exact opposite of split; it requires an array and a delimiter and returns a single string.

Sub JoinFunction()
Dim myArray() As Variant
myArray() = Array("here", "we", "go", "again", "!")
Debug.Print Join(myArray(), " ")
End Sub

The output of the above code will be:

Mastering arrays is an important part of learning to code advanced VBA.

Related Posts