How to use Erase statement in VBA?

The Erase statement in VBA (Visual Basic for Applications) is used to reset an array. It removes all the elements from a dynamic array and frees up the memory allocated for it. The Erase statement works differently for static and dynamic arrays: Dynamic Arrays: When used with a dynamic array, Erase deallocates the memory used by the array. After using Erase, the array is empty and its size is reset. To use the array again, you’ll need to reinitialize it with the ReDim statement. Example:

VBA

Dim myArray() As Integer
ReDim myArray(5)

' ... use the array ...

Erase myArray
' Now myArray is uninitialized and you need to use ReDim before using it again.

Static Arrays: When used with a static array, Erase sets all elements to their default values. For numeric arrays, this means setting all elements to 0. For string arrays, all elements are set to an empty string. For object arrays, all elements are set to Nothing. Example:

VBA

Dim myArray(5) As Integer

' ... use the array ...

Erase myArray
' Now all elements of myArray are set to 0.

Usage Considerations:

Use Erase to free up memory resources when a large dynamic array is no longer needed.
Remember that after using Erase on a dynamic array, you must reinitialize it with ReDim before using it again.
For static arrays, Erase is a quick way to reset all elements to their default values.

Erase with Multidimensional Arrays: Erase also works with multidimensional arrays, whether they are static or dynamic. Example for a dynamic multidimensional array:

VBA

Dim myArray() As Integer
ReDim myArray(3, 5)

' ... use the array ...

Erase myArray
' myArray is now uninitialized.

In summary, the Erase statement is a useful tool in VBA for managing arrays, especially when working with dynamic arrays that require manual memory management. Remember to reinitialize dynamic arrays with ReDim after using Erase.