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