Items
How you add and remove items is exactly the same for both fixed and dynamic arrays.
Adding
Once the array has been declared it can be populated.
Dim myArray(3) As Long
myArray(0) = 50
myArray(1) = 100
myArray(2) = 150
myArray(3) = 200
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.
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, use the Array_RemoveDuplicatesSingle code snippet.
Removing All - 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)
Removing All - 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.
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
Collection Object
VBA does include a built-in Collection Object that allows items to be easily removed.
Updating
To refer to an item in an array you need to use the index number.
You can change an item in an array by assigning a new value to it.
The syntax for changing an existing value is the same as assigning a value for the first time.
Fixed Array - String Data Type Array
Dim myArray(3) As String
myArray(0) = "mon"
myArray(1) = "tue"
myArray(2) = "wed"
myArray(3) = "thu"
' changes the value at index position 2
myArray(2) = "fri"
' changes the value at index position 3
myArray(3) = "sat"
Fixed Array - Variant Data Type
Dim myArray As Variant
myArray = Array(10, 20, 30, 40)
' changes the value at index position 2
myArray(2) = "300"
' changes the value at index position 3
myArray(3) = "400"
Copying Arrays
To assign one array to another, you must individually assign the elements, for example
If ArrayName1 and ArrayName2 are both defined as arrays then both the following are valid:
Dim ArrayName1() As String
Dim ArrayName2() As String
ArrayName1 = ArrayName2
ArrayName1() = ArrayName2()
Copying to a Collection
A Collection object can also be used although to populate the collection you need to loop through the array.
This might be a good solution if you need to perform multiple searches in the same data set.
Public Function UsingCollection(ByVal arValues As Variant, _
ByVal sFindValue As String) _
As Boolean
Dim MyCollection As VBA.Collection
Dim lposition As Long
Dim sreturn As String
On Error GoTo AnError
Set MyCollection = New VBA.Collection
For lposition = 0 To UBound(arValues)
MyCollection.Add arValues(lposition), arValues(lposition)
Next lposition
sreturn = MyCollection.Item(sFindValue)
UsingCollection = True
Exit Function
AnError:
UsingCollection = False
End Function
JOIN(sourcearray [,delimiter])
You can use the JOIN to return a text string containing all the elements in an array.
This function can be used to very quickly check if two arrays are identical
This comparison method is case sensitive.
If (VBA.Join(array1, "||") = VBA.Join(array2, "||")) Then
MsgBox "Identical"
Else
MsgBox "Different"
End If
Looping Through
You can loop through each array and compare each value to every value in the other array.
Public Sub Testing()
Dim myArray1(1 To 4) As String
Dim myArray2(1 To 4) As String
Dim myArray3 As Variant
myArray1(1) = "one1"
myArray1(2) = "two3"
myArray1(3) = "three5"
myArray1(4) = "four7"
myArray2(1) = "one1"
myArray2(2) = "two3"
myArray2(3) = "different"
myArray2(4) = "four7"
myArray3 = Comparing_TwoArrays(myArray1, myArray2)
Stop
End Sub
Public Function Comparing_TwoArrays(ByVal vCheckItems As Variant, _
ByVal vMasterList As Variant) _
As Variant
Dim vItemsNotInMaster As Variant
Dim isMatch As Boolean
Dim i As Integer
Dim j As Integer
Dim k As Integer
ReDim vArray3(1 To UBound(vCheckItems, 1) + UBound(vMasterList, 1))
k = 1
For i = LBound(vCheckItems, 1) To UBound(vCheckItems, 1)
isMatch = False
For j = LBound(vMasterList, 1) To UBound(vMasterList, 1)
If vCheckItems(i) = vMasterList(j) Then
isMatch = True
Exit For
End If
Next j
If (isMatch = False) Then
vItemsNotInMaster(k) = vCheckItems(i)
k = k + 1
End If
Next i
If (k > 1) Then
ReDim Preserve vArray3(1 To k - 1)
Else
vArray3 = Empty
End If
Comparing_TwoArrays = vArray3
End Function
Finding
If your array contains less than 100,000 elements then looping through once is very efficient.
Exactly which method you use will depend on the following questions:
1) Is the array 1 dimensional or multi-dimensional.
2) Are you looking to conduct an exact match or just a partial match.
3) Do you need to know the position of the item if it exists.
4) How many searches do you need to do, it might be worth sorting the array.
5) How often is the data changing, you may need to keep re-sorting.
Exact Match
If you are using Excel VBA you can use the Excel MATCH worksheet function.
Public Function OneDimensional_Exact_One( _
ByVal arValues As Variant, _
ByVal sFindValue As String) _
As Boolean
On Error GoTo ErrorHandler
OneDimensional_Exact_One = Not IsError(Application.WorksheetFunction.Match(sFindValue, arValues, 0))
Exit Function
ErrorHandler:
OneDimensional_Exact_One = False
End Function
You can use the FILTER function to tell if an item exists in a one-dimensional array.
This function returns an array of any elements that contain a given text string.
This function takes a string array, text string and returns a one-dimensional array containing all the elements that match the search string.
This method does not distinguish between complete matches and substring matches.
This method does not work with numerical arrays since all the numbers have to be implicitly converted to text first
Public Function OneDimensional_Exact_Two( _
ByVal arValues As Variant, _
ByVal sFindValue As String) _
As Boolean
OneDimensional_Exact_Two = (UBound(VBA.Filter(arValues, sFindValue)) > -1)
End Function
You can use the INSTR function after converting the array to a string concatenation
This method also uses the JOIN function to return a text string containing all the elements in an array (String).
Public Function OneDimensional_Exact_Three( _
ByVal arValues As Variant, _
ByVal sFindValue As String) _
As Boolean
Dim stextconcat As String
Dim sdelimiter As String
sdelimiter = "--"
stextconcat = sdelimiter & VBA.Join(arValues, sdelimiter) & sdelimiter
OneDimensional_Exact_Three = VBA.InStr(1, stextconcat, sdelimiter & sFindValue & sdelimiter)
'use this line for case insensitive searches
'OneDimensional_Exact_Three = VBA.InStr(1, stextconcat, sdelimiter & sFindValue & sdelimiter, vbTextCompare)
End Function
You can loop through the array using a For Next - Each loop.
Public Function OneDimensional_Exact_Four( _
ByVal arValues As Variant, _
ByVal sFindValue As String) _
As Boolean
Dim sItem As Variant
For Each sItem In arValues
If sItem = sFindValue Then
OneDimensional_Exact_Four = True
Exit Function
End If
Next sItem
End Function
Partial Match
This function uses a 1-dimensional array starting at 1.
Public Sub Testing()
Dim myArray(1 To 4) As String
Dim bfoundMatch As String
myArray(1) = "one1"
myArray(2) = "two3"
myArray(3) = "three5"
myArray(4) = "four7"
bfoundMatch = OneDimensional_Partial(myArray, "two")
Debug.Print bfoundMatch
End Sub
Public Function OneDimensional_Partial(ByVal arValues As Variant, _
ByVal sFindValue As String) _
As Boolean
Dim lrow As Long
Dim lcolumn As Long
For lrow = LBound(arValues, 1) To UBound(arValues, 1)
If (VBA.InStr(arValues(lrow), sFindValue) > 0) Then
OneDimensional_Partial = True
Exit Function
End If
Next lrow
End Function
Multi Dimensional - Partial Match
This function uses a 2-dimensional array starting at 1.
Public Sub Testing()
Dim myArray(1 To 4, 1 To 2) As String
Dim bfoundMatch As String
myArray(1, 1) = "one1"
myArray(1, 2) = "one2"
myArray(2, 1) = "two3"
myArray(2, 2) = "two4"
myArray(3, 1) = "three5"
myArray(3, 2) = "three6"
myArray(4, 1) = "four7"
myArray(4, 2) = "four8"
bfoundMatch = TwoDimensional_Partial(myArray, "two")
Debug.Print bfoundMatch
End Sub
Public Function TwoDimensional_Partial( _
ByVal arValues As Variant, _
As Boolean
Dim lrow As Long
Dim lcolumn As Long
For lrow = LBound(arValues, 1) To UBound(arValues, 1)
For lcolumn = LBound(arValues, 2) To UBound(arValues, 2)
If (VBA.InStr(arValues(lrow, lcolumn), sFindValue) > 0) Then
TwoDimensional_Partial = True
Exit Function
End If
Next lcolumn
Next lrow
End Function
Binary Search
Also known as half-interval search, logarithmic search, or binary chop.
A binary search is a search algorithm that can find the position of a value within a sorted array.
The function does not return the position, it just returns whether a match was found or not.
Your array must be sorted in Ascending Order for this function to work.
Public Sub BinarySearch()
Dim myArray(0 To 3) As String
Dim bfoundMatch As String
myArray(0) = "four"
myArray(1) = "one"
myArray(2) = "three"
myArray(3) = "two"
bfoundMatch = Array_Find(myArray, "three")
Debug.Print bfoundMatch
End Sub
Public Function Array_Find(ByRef theArray() As String, _
ByVal target As String) _
As Boolean
Dim bfind As Boolean
Dim low As Integer
Dim high As Integer
Dim middle As Integer
low = 0
high = UBound(theArray)
bfind = False
Do While low <= high
middle = (low + high) / 2
If target = theArray(middle) Then
bfind = True
Exit Do
ElseIf target < theArray(middle) Then
high = (middle - 1)
Else
low = (middle + 1)
End If
Loop
Array_Find = bfind
End Function
© 2026 Better Solutions Limited. All Rights Reserved. © 2026 Better Solutions Limited TopPrevNext