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:
Here’s a breakdown of it’s five basic steps:
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"
Next
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
Else
FinalString = FinalString & Str(intFullArray(y)) & ","
End If
Next y
FinalString = Replace(FinalString, " ", "")
NumbersToExclude = Left(FinalString, Len(FinalString) - 1)
End Function
gifGuide2Code 