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()
   
    'Resizing
    ReDim test_array(test_number - 1)
   
    'EXAMPLE
    MsgBox UBound(test_array) ' => returns: 10
   
    'Deletion
    Erase test_array
   
    'New resizing possible after deletion
    ReDim test_array(15)
   
    'EXAMPLE
    MsgBox UBound(test_array) ' => returns: 15
   
End Sub

The Erase instruction can be very useful when your dynamic array is declared as a 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
    Next
   
    'EXAMPLE
    MsgBox test_array(5) ' => returns: 5
   
    'Deleting the content of the array
    Erase test_array
   
    'EXAMPLE
    MsgBox test_array(5) ' => empty
   
End Sub

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