VBA Array Erase function

VBA Array Erase Function

VBA Array Erase Function in Excel. The Erase function is used to reset(reinitialise) the size of an array. It depends on type of an array . That is either static or dynamic array.

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

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 initialised. 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.

VBA Erase Function

Instructions to use Macro

Here are the instructions to use above macro in Visual basic editor.

  • Open Visual Basic Editor(VBE) by clicking Alt +F11
  • Go to code window by clicking F7
  • Copy above specified macro or procedure
  • Paste above copied code in code window
  • Run macro by clicking F5 or Run command
  • You can see output on the screen
  • Find above specified output screenshot.

Other related VBA Arrays articles

You may also like the related VBA Array articles.

Back to VBA Arrays




Leave a Comment

Your email address will not be published. Required fields are marked *