How to clear the entire array?

I have an array like this:

Dim aFirstArray() As Variant

How do 开发者_开发问答I clear the entire array? What about a collection?

---

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

---

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!

---

It is as simple as :

Erase aFirstArray

---

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

---

[your Array name] = Empty

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

---

ReDim aFirstArray(0)

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

---

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

---

Only use Redim statement

 Dim aFirstArray() As Variant

Redim aFirstArray(nRows,nColumns)