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 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
    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).