67

I have an array like this:

Dim aFirstArray() As Variant

How do I clear the entire array? What about a collection?

Bhargav Rao
  • 50,140
  • 28
  • 121
  • 140
Alex Gordon
  • 57,446
  • 287
  • 670
  • 1,062

8 Answers8

123

You can either use the Erase or ReDim statements to clear the array. Examples of each are shown in the MSDN documentation. For example:

Dim threeDimArray(9, 9, 9), twoDimArray(9, 9) As Integer
Erase threeDimArray, twoDimArray
ReDim threeDimArray(4, 4, 9)

To remove a collection, you iterate over its items and use the Remove method:

For i = 1 to MyCollection.Count
  MyCollection.Remove 1 ' Remove first item
Next i
Cody Gray - on strike
  • 239,200
  • 50
  • 490
  • 574
Sarfraz
  • 377,238
  • 77
  • 533
  • 578
  • Just a quick note, at least for VBA, you can't ReDim an array that was declared with dimensions. – KevenDenen Jun 11 '10 at 03:49
  • 2
    +1 @KevenDenen that's true, but the array in the original question wasn't declared with dimensions. @Sarfraz great answer but your link is for VB.Net. I can't find a link for VBA. Here's the link for the VB6 manual http://msdn.microsoft.com/en-us/library/aa243360(v=VS.60).aspx – MarkJ Jun 11 '10 at 11:45
  • 1
    Sarfraz, is there a reason you wouldn't just say: MyCollection = new Collection. Why iterate over it? – Sam Johnson Sep 27 '11 at 18:27
  • 2
    what if I simply call `ReDim`? Does it simply reset to zero the values or does it allocate a while new array? My concerns are about time-performances when calling `ReDim` to reset a big array in a loop – Cavaz Feb 22 '13 at 13:34
  • note : the last line (redim) is not needed if the array is module-wide declared. – Patrick Lepelletier Apr 20 '16 at 21:39
  • 2
    This all don't work with the test "IsEmpty(aFirstArray)". I added an answer for that purpose – Rafiki Feb 07 '18 at 11:00
29

For deleting a dynamic array in VBA use the instruction Erase.

Example:

Dim ArrayDin() As Integer    
ReDim ArrayDin(10)    'Dynamic allocation 
Erase ArrayDin        'Erasing the Array

Hope this help!

Malachi
  • 3,205
  • 4
  • 29
  • 46
Andres Sommerhoff
  • 299
  • 3
  • 2
  • 4
    What was the reason to post an answer two years after the question was asked, provided this very answer has already been posted? – GSerg Mar 22 '12 at 15:04
  • @GSerg that is a very well-phrased sentence. – Alex Gordon Mar 22 '12 at 16:33
  • 13
    Worthless comments to the both of you. No answer demonstrates Erase. Furthermore, it's almost a year after Andres posted his answer and it actually gave me the one-line code solution I was looking for. If I could downvote both of your comments, I would. – oscilatingcretin Feb 13 '13 at 13:51
  • 3
    Added comments are useful for those of us who look at them at a later date - such as my using this answer in Dec of 2018. Thank you Andres for taking the time to post. – JimT Dec 03 '18 at 21:13
7

It is as simple as :

Erase aFirstArray
Jaanus
  • 16,161
  • 49
  • 147
  • 202
4

Find a better use for myself: I usually test if a variant is empty, and all of the above methods fail with the test. I found that you can actually set a variant to empty:

Dim aTable As Variant
If IsEmpty(aTable) Then
    'This is true
End If
ReDim aTable(2)
If IsEmpty(aTable) Then
    'This is False
End If
ReDim aTable(2)
aTable = Empty
If IsEmpty(aTable) Then
    'This is true
End If
ReDim aTable(2)
Erase aTable
If IsEmpty(aTable) Then
    'This is False
End If

this way i get the behaviour i want

Rafiki
  • 604
  • 6
  • 19
3
[your Array name] = Empty

Then the array will be without content and can be filled again.

Paul
  • 4,160
  • 3
  • 30
  • 56
Koke
  • 31
  • 1
  • 1
    This doesn't work. You get a "Compile error: Can't assign to array" – seadoggie01 Nov 20 '17 at 15:21
  • for an array of variant your solution is better than the erase, because using your solution I can check later on if array is empty by doing if isempty(MyArray), while if we use erase it won't work. – JustGreat Aug 17 '22 at 13:03
1
ReDim aFirstArray(0)

This will resize the array to zero and erase all data.

SUNIL KUMAR
  • 117
  • 5
0

i fell into a case where clearing the entire array failed with dim/redim :

having 2 module-wide arrays, Private inside a userform,

One array is dynamic and uses a class module, the other is fixed and has a special type.

Option Explicit

Private Type Perso_Type
   Nom As String
   PV As Single 'Long 'max 1
   Mana As Single 'Long
   Classe1 As String
   XP1 As Single
   Classe2 As String
   XP2 As Single
   Classe3 As String
   XP3 As Single
   Classe4 As String
   XP4 As Single
   Buff(1 To 10) As IPicture 'Disp
   BuffType(1 To 10) As String
   Dances(1 To 10) As IPicture 'Disp
   DancesType(1 To 10) As String
End Type

Private Data_Perso(1 To 9, 1 To 8) As Perso_Type

Dim ImgArray() As New ClsImage 'ClsImage is a Class module

And i have a sub declared as public to clear those arrays (and associated run-time created controls) from inside and outside the userform like this :

Public Sub EraseControlsCreatedAtRunTime()
Dim i As Long
On Error Resume Next
With Me.Controls 'removing all on run-time created controls of the Userform :
    For i = .Count - 1 To 0 Step -1 
        .Remove i
    Next i
End With
Err.Clear: On Error GoTo 0

Erase ImgArray, Data_Perso
'ReDim ImgArray() As ClsImage ' i tried this, no error but wouldn't work correctly
'ReDim Data_Perso(1 To 9, 1 To 8) As Perso_Type 'without the erase not working, with erase this line is not needed.
End Sub

note : this last sub was first called from outside (other form and class module) with Call FormName.SubName but had to replace it with Application.Run FormName.SubName , less errors, don't ask why...

Patrick Lepelletier
  • 1,596
  • 2
  • 17
  • 24
0

Only use Redim statement

 Dim aFirstArray() As Variant

Redim aFirstArray(nRows,nColumns)
Moreno
  • 608
  • 1
  • 9
  • 24