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