VBA Tip: Delete an Array

If you want to reset an array to change its size and content, use the Erase instruction.

Example with a dynamic array:

Sub test()
    testNumber = 11
    'Declaration of a dynamic array
    Dim testArray()
    ReDim testArray(testNumber - 1)
    MsgBox UBound(testArray) ' => returns: 10
    Erase testArray
    'New resizing possible after deletion
    ReDim testArray(15)
    MsgBox UBound(testArray) ' => returns: 15
End Sub

The Erase instruction can be very useful when your dynamic array is declared as a Public or Global variable...

Example with a fixed-size array:

Sub test()
    'Declaration of the array
    Dim testArray(10)
    'EXAMPLE: value attribution
    For i = 0 To 10
        testArray(i) = i
    MsgBox testArray(5) ' => returns: 5
    'Deleting the content of the array
    Erase testArray
    MsgBox testArray(5) ' => empty
End Sub

With a fixed-size array, items are reset (emptied).