Clearing or Releasing Arrays In Excel VBA

The Erase statement in VBA serves the following purposes:

• For static arrays, it resets all elements to their default values, but the array itself still exists in memory and continues to occupy space.
• For dynamic arrays, it releases the entire memory allocated for the array, making the memory available for other variables or arrays.

Example: Clearing a Static Array

Sub ClearStaticArray()
    Dim T(1 To 5) As Integer
    T(1) = 10
    MsgBox T(1)       ' Displays 10
    Erase T           ' Resets all elements to default values
    MsgBox T(1)       ' Displays 0 (default for Integer)
End Sub

Explanation:

• The static array T has 5 elements.
• The first element is assigned and displayed as 10.
• After the Erase T statement, the array elements are reset to their default values.
• The first element now displays 0, the default initial value for an Integer.

Note:
Elements of string arrays are reset to «  » (empty string).

Example: Releasing a Dynamic Array

Sub ReleaseDynamicArray()
    Dim T() As Integer
    On Error GoTo ErrorHandler
    ReDim T(1 To 5)
    T(1) = 10
    MsgBox T(1)       ' Displays 10
    Erase T           ' Releases the dynamic array's memory
    MsgBox T(1)       ' Causes a runtime error since array no longer exists
    ReDim T(1 To 5)   ' Re-dimension array to reuse it
    T(1) = 10
    MsgBox T(1)       ' Displays 10
    Exit Sub
ErrorHandler:
    MsgBox Err.Description
    Resume Next
End Sub

Explanation:

• The dynamic array T is declared without size.
• ReDim sets its size to 5.
• The first element is assigned and displayed as 10.
• The Erase T statement releases the array’s memory.
• Attempting to access T(1) after Erase causes a runtime error, caught by the error handler.
• Later, the array is resized again with ReDim and can be reused normally.