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()
    test_number = 11
    'Declaration of a dynamic array
    Dim test_array()
    ReDim test_array(test_number - 1)
    MsgBox UBound(test_array) ' => returns: 10
    Erase test_array
    'New resizing possible after deletion
    ReDim test_array(15)
    MsgBox UBound(test_array) ' => 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 test_array(10)
    'EXAMPLE: value attribution
    For i = 0 To 10
        test_array(i) = i
    MsgBox test_array(5) ' => returns: 5
    'Deleting the content of the array
    Erase test_array
    MsgBox test_array(5) ' => empty
End Sub

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