One of the key features of VBA is its ability to work with arrays, which are used to store related data in a structured manner. The Erase statement is an important command in VBA that is used to clear the contents of an array.
Syntax of VBA Erase Statement
Its syntax is as follows:
Erase arrayname
Here, ‘arrayname’ refers to the name of the array that you want to erase. This statement removes all the elements in the array and resets the array to its default state, which is an empty array with no elements.
Examples of Using the VBA Erase Statement
1. Erasing an Array in a Subroutine
The Erase statement is often used in subroutines, which are blocks of code that perform a specific task. In this example, we have declared an array named ‘numbers’ and initialized it with a few values. Then, we use the Erase statement to clear the contents of the array. This is useful when we want to reuse the same array without having to declare and initialize it again.
Sub EraseArray()
Dim numbers(1 To 5) As Integer
numbers(1) = 10
numbers(2) = 20
numbers(3) = 30
numbers(4) = 40
numbers(5) = 50
'Clearing the contents of the array
Erase numbers
'Code to process the empty array goes here
End Sub
2. Erasing an Array with a Dynamic Size
Sometimes, the size of an array may not be known beforehand and can change during the program execution. In such cases, we can use the Erase statement to clear the contents of the array and reset its size to 0. This allows us to use the same array with a different size without having to declare it again.
Sub Dynamicsize()
Dim numbers() As Integer
Dim i As Integer
'Assigning a dynamic size to the array
ReDim numbers(1 To 3)
'Adding values to the array
numbers(1) = 10
numbers(2) = 20
numbers(3) = 30
'Code to process the array goes here
'Clearing the contents of the array and resetting its size to 0
Erase numbers
'Assigning a new dynamic size to the array
ReDim numbers(1 To 5)
'Adding values to the array
For i = 1 To 5
numbers(i) = i * 10
Next i
'Code to process the array goes here
End Sub
3. Erasing Multi-Dimensional Arrays
Arrays in VBA can have multiple dimensions, which means they can store data in a table-like structure. To clear a multi-dimensional array, we need to use the Erase statement on each dimension separately. In this example, we have a 2-dimensional array named ‘table’ with 3 rows and 3 columns. We use a nested loop to assign values to the elements of the array and use the Erase statement to clear it.
Sub EraseMultiDimArray()
Dim table(1 To 3, 1 To 3) As String
Dim i, j As Integer
'Assigning values to the array
For i = 1 To 3
For j = 1 To 3
table(i, j) = "Row " & i & ", Column " & j
Next j
Next i
'Displaying the values in the array
For i = 1 To 3
For j = 1 To 3
Debug.Print table(i, j)
Next j
Next i
'Clearing the contents of the array
Erase table
'Code to process the array goes here
End Sub
4. Using Erase with Dynamic Arrays and Objects
In VBA, we can also use the Erase statement to clear the contents of an array or object that has been dynamically assigned or created. This means that the array or object has not been declared explicitly, but rather has been assigned using functions or object references. In the example below, we have used the Erase statement to clear the contents of an array with a dynamic size and an object that has been assigned using the ‘CreateObject’ function.
Sub DynamicArrayAndObject()
Dim numbers() As Long
Dim obj As Object
'Assigning a dynamic size to the array
ReDim numbers(1 To 5)
'Assigning an object
Set obj = CreateObject("Scripting.Dictionary")
'Code to add values to the array and object
'Clearing the contents of the array and object
Erase numbers
Erase obj
'Code to process the empty array and object goes here
End Sub
5. Erasing Arrays within Arrays
Arrays in VBA can also contain other arrays as elements, which is known as a jagged array. To clear such an array, we need to use the Erase statement on each element array. In this example, we have declared an array named ‘jagged’ and assigned three arrays as its elements. Then, we use a loop to clear the contents of each element array using the Erase statement.
Sub JaggedArray()
Dim jagged() As Variant
Dim i As Integer
'Assigning arrays to the elements of the jagged array
ReDim jagged(1 To 3)
jagged(1) = Array("Apple", "Banana", "Cherry")
jagged(2) = Array("Dog", "Cat")
jagged(3) = Array("Laptop", "Phone", "Tablet")
'Displaying the elements of the jagged array
For i = 1 To 3
Debug.Print "Element " & i & ": "
For j = LBound(jagged(i)) To UBound(jagged(i))
Debug.Print jagged(i)(j)
Next j
Next i
'Clearing the contents of the arrays within the jagged array
For i = 1 To 3
Erase jagged(i)
Next i
'Code to process the empty jagged array goes here
End Sub
Important Notes and Remarks
- The Erase statement works only on arrays and not on variables. To clear the contents of a variable, we can simply assign it a new value.
- The Erase statement does not delete the array, but rather removes all the elements and resets the size of the array to 0. This means that the array can still be used, but it will not contain any data.
- The Erase statement can be used on both static and dynamic arrays, as well as multi-dimensional arrays.
- When using Erase on a multi-dimensional array, it is important to use it on each dimension separately to avoid any unexpected results.
- The Erase statement can also be used on arrays containing non-variant or user-defined data types, such as integers or custom objects.
The Erase statement in VBA is a useful tool for clearing the contents of an array and resetting it to its default state. It allows us to reuse the same array without having to declare it again, as well as work with dynamically assigned arrays and objects. With its simple syntax and versatility, the Erase statement is an important feature of VBA and can greatly improve the efficiency of our code.
I hope this blog post has provided a comprehensive understanding of the purpose, syntax, examples, and important notes of the VBA Erase statement.
Please share your feedback and views on this post in the comments section below. Thank you for reading!
