Erase Statement

The Erase Statement can be used to reinitialise arrays.
This statement can be used on both Fixed Size and Dynamic Size arrays.
Erase frees the memory for dynamic size arrays.
Erase recovers no memory for fixed size arrays.
When you first dimension a fixed-length string, it contains null characters.
Erase behaves differently for fixed or dynamic arrays.


Numeric Arrays

Keeps the items and resets them all to zero.

Public Numeric_FixedSize(5, 5) As Integer 

Public Sub Numeric_FixedSize_1()
   Numeric_FixedSize(1, 1) = 10
   Erase Numeric_FixedSize
End Sub

Removes all the elements.

Public Numeric_DynamicSize() As Integer 

Public Sub Numeric_DynamicSize_2()
   ReDim Numeric_DynamicSize(5, 5)
   Numeric_DynamicSize(1, 1) = 10
   Erase Numeric_DynamicSize
End Sub

String Arrays

Values set to zero-length string in a variable array.
Values set to 0 in a fixed array.
Keeps the items and resets them all to empty string "".

Public StringVariableLength_FixedSize(5, 5) As String 

Public Sub StringVariableLength_FixedSize_3()
   StringVariableLength_FixedSize(1, 1) = "text"
   Erase StringVariableLength_FixedSize
End Sub

Removes all the elements.

Public StringVariableLength_DynamicSize() As String 

Public Sub StringVariableLength_DynamicSize_4()
   ReDim StringVariableLength_DynamicSize(5, 5)
   StringVariableLength_DynamicSize(1, 1) = "text"
   Erase StringVariableLength_DynamicSize
End Sub

Keeps the items and resets them all to empty string "".

Public StringFixedLength_FixedSize(5, 5) As String * 10 

Public Sub StringFixedLength_FixedSize_5()
   StringFixedLength_FixedSize(1, 1) = "text"
   Erase StringFixedLength_FixedSize
End Sub

Removes all the elements.

Public StringFixedLength_DynamicSize() As String * 10 

Public Sub StringFixedLength_DynamicSize_6()
   ReDim StringFixedLength_DynamicSize(5, 5)
   StringFixedLength_DynamicSize(1, 1) = "text"
   Erase StringFixedLength_DynamicSize
End Sub

Variant Arrays

Keep all the items and resets them all to Empty.

Public Variant_FixedSize(5, 5) As Variant 

Public Sub Variant_FixedSize_7()
   Variant_FixedSize(1, 1) = "text"
   Erase Variant_FixedSize
End Sub

Removes all the elements.

Public Variant_DynamicSize() As Variant 

Public Sub Variant_DynamicSize_8()
   ReDim Variant_DynamicSize(5, 5)
   Variant_DynamicSize(1, 1) = "text"
   Erase Variant_DynamicSize
End Sub

Object Arrays

Keep all the items and resets them all to Nothing.

Public Object_FixedSize(5, 5) As Object 

Public Sub Object_FixedSize_9()
   Set Object_FixedSize(1, 1) = ThisWorkbook
   Erase Object_FixedSize
End Sub

Removes all the elements.

Public Object_DynamicSize() As Object 

Public Sub Object_DynamicSize_10()
   ReDim Object_DynamicSize(5, 5)
   Set Object_DynamicSize(1, 1) = ThisWorkbook
   Erase Object_DynamicSize
End Sub

Important

Fixed arrays keep their dimensions after erasing.
Dynamic arrays lose their dimensions after erasing.
The Erase statement cannot be used at class or module level. It can only be used inside a subroutine or function.
The Erase statement is equivalent to assigning the Nothing keyword to each array variable.


© 2023 Better Solutions Limited. All Rights Reserved. © 2023 Better Solutions Limited TopPrevNext