VBA: How to Exclude Numbers from a List


Alright, I ran short of time this week, so I don’t have a tutorial. But I DO have another fun UDF (User-Defined Function)–this one takes two parameters. Both need to be strings made of comma separated numbers. The function excludes the second set of numbers from the output.

For instance:

The Exclusion Function

Here’s a breakdown of it’s five basic steps:

Step By Step

The Copy/Paste Version:

Function NumbersToExclude(full As String, exclude As String)

full = Replace(full, " ", "")
exclude = Replace(exclude, " ", "")

Dim FinalString As String
FinalString = ""

Dim ExcludeDic As Object
Set ExcludeDic = CreateObject("Scripting.Dictionary")

‘Create a string array with the full list of numbers
‘Convert that into an integer array
Dim numberArray() As String
numberArray() = Split(full, ",")
Dim intFullArray() As Integer, size As Integer, a As Integer
size = UBound(numberArray)
ReDim intFullArray(size)
For a = 0 To UBound(numberArray)
intFullArray(a) = CInt(numberArray(a))
Next a

‘Make a string array for the numbers to exclude
‘Convert those io integers
‘Push each excluded number into an exclusion dictionary
Dim excludeArray() As String
excludeArray() = Split(exclude, ",")
Dim intExcludeArray() As Integer, sizeE As Integer, e As Integer
sizeE = UBound(excludeArray)
ReDim intExcludeArray(sizeE)
For e = 0 To UBound(excludeArray)
intExcludeArray(e) = CInt(excludeArray(e))
ExcludeDic.Add Key:=intExcludeArray(e), Item:="1"

For each number in the full array, if it exists in the exclusion dictionary, do nothing
Otherwise append it to a new array
For y = 0 To UBound(intFullArray)
If ExcludeDic.Exists(intFullArray(y)) Then
FinalString = FinalString & Str(intFullArray(y)) & ","
End If
Next y

FinalString = Replace(FinalString, " ", "")
NumbersToExclude = Left(FinalString, Len(FinalString) - 1)

End Function




Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out /  Change )

Google+ photo

You are commenting using your Google+ account. Log Out /  Change )

Twitter picture

You are commenting using your Twitter account. Log Out /  Change )

Facebook photo

You are commenting using your Facebook account. Log Out /  Change )

Connecting to %s