Function SubtractArray(arrBigAs Variant, arrLittleAs Variant) As Variant

The SubtractArray function's return value is a variant array. This array will contain the elements of arrBig minus elements of arrLittle. You must assign the return to a variant variable.

Function Syntax

SubtractArray(arrBig, arrLittle )

Formal Parameters

arrBig

The primary array containing elements to keep.

arrLittle

The array that contains the elements that will be removed from arrBig

Return value

The SubtractArray function's return value is a variant array. This array will contain the elements of arrBig minus elements of arrLittle. You must assign the return to a variant variable.

Function

Function SubtractArray(arrBig As Variant, arrLittle As Variant) As Variant
Dim arrNew As Variant
Dim x As Integer
x = 0
Redim arrNew(x)
Dim nCount As Integer

Forall v In arrBig
nCount = 0 '-- Counter for arrBig elements
For i = Lbound(arrLittle) To Ubound(arrLittle) '-- Loop through array
If arrLittle(i) = v Then '-- Check current arrNew to ensure there are no duplicate values
nCount = nCount + 1 '-- bump counter if duplicate is found
End If
Next i '-- End Loop check for duplicate members
'-- If there are no duplicate values then assign element to arrNew
If nCount = 0 Then '-- Counter was never bumped then no duplicate exist so add new element to arrNew
Redim Preserve arrNew(x) '-- Redim arrNew to make room for new element
arrNew(x) = v '-- Assign new element to arrNew
x = x + 1 '-- Bump arrNew counter for future redim
End If
End Forall
SubtractArray = arrNew
End Function

Usage

The valid values for the arrBig elements are alphanumeric characters. This will remove all arrLittle elements from the array arrBig. Assign the return to a variant variable.

Example

This example will take an array of elements containing duplicate names. This will remove all arrLittle elements from the array arrBig. Assign the return to a variant variable. The array will then be formated into a string variable for display in messagebox.

Sub Click(Source As Button)
Dim BigArray(8) As String
BigArray(0) = "Tim"
BigArray(1) = "Gina"
BigArray(2) = "Paul"
BigArray(3) = "Tom"
BigArray(4) = "Gina"
BigArray(5) = "Ken"

Dim LittleArray(3) As String
LittleArray(0) = "Tim"
LittleArray(1) = "Gina"


Dim ViewArray As String
ViewArray = ""

Forall v In BigArray
'-- Concatenate each element in array to sViewCust for display to screen via messagebox
If ViewArray = "" Then
ViewArray = v
Else
ViewArray = ViewArray & Chr$(13) & v
End If
End Forall
Messagebox "Array Elements: " & Chr(13) & Chr(13) & ViewArray, , "Elements in Array before call to SubtractArray"

Dim sNewArray As Variant
sNewArray = SubtractArray(BigArray, LittleArray)

ViewArray = ""

Forall q In sNewArray
'-- Concatenate each element in array to sViewCust for display to screen via messagebox
If ViewArray = "" Then
ViewArray = q
Else
ViewArray = ViewArray & Chr$(13) & q
End If
End Forall

Messagebox "Array Elements: " & Chr(13) & Chr(13) & ViewArray, , "Elements in Array After call to SubtractArray"

End Sub