When working with arrays in Excel VBA, there comes a time when you might want to reset an array back to its uninitialized state. This is where the Erase statement comes in handy. By putting the word “Erase” in front of an array’s name, you’re able to wipe it clean, removing all the values previously stored in it. I’ve found this particularly useful in my macros when I need to start over with an array without closing the workbook or resetting the whole project.

Imagine you’ve set up an array with several elements, and you’ve populated it with some data. If you want to clear out that data without affecting the size of the array itself, you just issue an Erase command followed by the array name. What this does is pretty straightforward – it removes all the data from the array elements, essentially leaving you with a blank slate. In my coding experience, this functionality has been essential for creating flexible and reusable macros in Excel.

Clear Dynamic Array

When I’m working with a dynamic array in VBA and I need to modify its size, I often use the ReDim statement. If I want to clear an element without losing the rest of the array, I’ll combine ReDim with Preserve.

Here’s how I’d do it:

  • I declare a dynamic array with Dim myArray() As Long.
  • I initially size it using ReDim myArray(1 To 4), filling it with values.

If I want to remove the last item but keep the first three, my code looks like this:

ReDim Preserve myArray(1 To 3)

And if later I decide I need five elements after all, I simply resize again:

ReDim Preserve myArray(1 To 5)

Remember, resizing with Preserve keeps the existing values intact, unless they fall outside the new bounds. My trusty MsgBox lets me confirm the values are as expected.

Important Points

In working with VBA arrays, I’ve noted a few critical behaviors regarding the Erase statement, based on the type of array and data it holds:

  • Strings:
    • Variable length: elements become empty strings ""
    • Fixed length: elements turn to zero
  • Numerics: each element resets to 0
  • Variants: each element changes to Empty
  • Objects: each element adjusts to Nothing

For dynamic arrays, using Erase helps free up memory by clearing the space the array occupies. Remember, a proper declaration (Dim, ReDim, Static) and understanding array types—affects how to correctly manage array lifecycles.

Data Type After Erase
String "" or 0
Numeric 0
Variant Empty
Object Nothing

Example to declare and clear a simple array:

Dim myArray(5) As Integer
'Erase the entire array
Erase myArray

Keep these points in mind while manipulating arrays for smoother coding experiences.

Similar Posts