Removing

The only way to remove an item from an array is to construct a new array.
Constructing a new array every time though is not very efficient.
The following function can be used to remove individual items from an array.
This function only works with Fixed Arrays.

Public Function Array_RemoveItem(ByVal myArray As Variant, _ 
                                 ByVal iIndexNo As Integer) As Variant
Dim i As Integer

    For i = iIndexNo + 1 To UBound(myArray)
        myArray(i - 1) = myArray(i)
    Next i
    ReDim Preserve myArray(UBound(myArray) - 1)
    Array_RemoveItem = myArray
End Function

Public Sub Testing()
Dim myNewArray As Variant
Dim myArray() As String

   ReDim myArray(4)
   myArray(0) = "one"
   myArray(1) = "two"
   myArray(2) = "three"
   myArray(3) = "four"
   myArray(4) = "five"

   myNewArray = Array_RemoveItem(myArray, 2)
   Stop
End Sub

If you want to remove duplicates from an array, refer to this page.


Using Dummy Values

Instead of constructing a new array you could just change the value to a dummy value.
This function works with Fixed Arrays and Dynamic Arrays.

Public Function Array_ChangeItem(ByVal myArray As Variant, _ 
                                 ByVal iIndexNo As Integer) As Variant
    myArray(iIndexNo) = "#ignore#"
    Array_ChangeItem = myArray
End Function

Public Sub Testing()
Dim myFixedArray(4) As String

   myFixedArray(0) = "one"
   myFixedArray(1) = "two"
   myFixedArray(2) = "three"
   myFixedArray(3) = "four"
   myFixedArray(4) = "five"

   myNewArray = Array_ChangeItem(myFixedArray, 2) 'myNewArray = Array_RemoveItem(myArray, 2)
   Stop
End Sub

ERASE Statement

The ERASE statement can be used to remove all of the items.
This can be used with both Fixed Arrays and Dynamic Arrays.
This statement will reset all the elements to their default values.

Dim myArray(3) As String     'This is a FIXED array  
Debug.Print myArray(0) 'Default value is ""
myArray(0) = "one"
myArray(1) = "two"
myArray(2) = "three"
myArray(3) = "four"

Debug.Print myArray(0) '= "one"
Erase myArray
Debug.Print myArray(0) '= "" (resets to default value)

REDIM Statement

The REDIM statement can be used to remove all of the items.
This can only be used with Dynamic Arrays.

Dim myArray() As String      'This is a DYNAMIC array  
ReDim myArray(3)
myArray(0) = "one"
myArray(1) = "two"
myArray(2) = "three"
myArray(3) = "four"

Debug.Print myArray(0) '= "one"
Erase myArray
Debug.Print myArray(0) '= "" (resets to default value)

It is possible to use the Preserve keyword to remove only some of the values.


Collection Object

VBA does include a built-in Collection Object that allows items to be easily removed.

Dim myCollection As VBA.Collection 
Set myCollection = New VBA.Collection
myCollection.Add "Item 1"
myCollection.Remove "Item 1"

© 2022 Better Solutions Limited. All Rights Reserved. © 2022 Better Solutions Limited TopPrevNext