VBA Array Erase Function in Excel

VBA Array Erase Function in Excel. The Erase function is used to reset(reinitialize) the size of an array. It depends on type of an array . That is either static or dynamic array.In this tutorial we see examples, step by step instructions with screenshots.

Syntax of the Erase Function

Here is the Syntax of the Erase Function in Excel VBA.

Erase ArrayName

VBA Erase Function in Static Array

Let us see the example VBA macro code using array Erase function in Static
Array.

'VBA Erase Function in Static Array
Sub VBA_Erase_Function_Static_Array()

    'Declare Variables
    Dim sResult As String
    Dim aSubstrings(1) As String
    
    'Define an Array values
    aSubstrings(0) = "FName"
    aSubstrings(1) = "LName"
    
    'Reinitialized Each Element in an Array
    Erase aSubstrings
    
    'Display Output on the Screen
    MsgBox "The first and second Array Elements: " & aSubstrings(0) & "-" & aSubstrings(1), vbInformation, "VBAF1"
    
End Sub

Here is the output screenshot of the above macro code.

VBA Erase Function in Dynamic Array

Let us see the example VBA macro code using array Erase function in Dynamic Array. The below procedure gets an error, because specified array memory size is re initialized. so it displays an error.

'VBA Erase Function in Dynamic Array
Sub VBA_Erase_Function_Dynamic_Array()

    'Declare Variables
    Dim aDyArray() As Variant
    ReDim aDyArray(4)
    Dim aArraySize As Integer
    
    'Array Memory is Free
    Erase aDyArray
    
    aArraySize = UBound(aDyArray)
    
End Sub

Here is the output screenshot of the above procedure.

Instructions to Run VBA Macro Code or Procedure:

You can refer the following link for the step by step instructions.

Instructions to run VBA Macro Code

Other Useful Resources:

Click on the following links of the useful resources. These helps to learn and gain more knowledge.

VBA Tutorial VBA Functions List VBA Arrays VBA Text Files VBA Tables

VBA Editor Keyboard Shortcut Keys List VBA Interview Questions & Answers Blog