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()
    
    'Resizing
    ReDim testArray(testNumber - 1)
    
    'EXAMPLE
    MsgBox UBound(testArray) ' => returns: 10
    
    'Deletion
    Erase testArray
    
    'New resizing possible after deletion
    ReDim testArray(15)
    
    'EXAMPLE
    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
    Next
    
    'EXAMPLE
    MsgBox testArray(5) ' => returns: 5
    
    'Deleting the content of the array
    Erase testArray
    
    'EXAMPLE
    MsgBox testArray(5) ' => empty
    
End Sub

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