There are many instances when one wants to compare numbers that are separated by delimiters. Examples of such numbers are IP addresses, which look like 192.168.0.9, and 192.168.0.10 or book section identifiers, which look like 2.1, 2.1.2, and 3.2.4, and maybe even Dewey Decimal Classification numbers While a person would sort 192.168.0.9 before 192.168.0.10 quite naturally, it required the person to decompose each compound number into its elements (192, 168, 0, 9 and 192, 168, 0, 10, respectively) and do a mental comparison going element by element until one number was different from another. Unfortunately, this exercise, incredibly simple for a human brain, is anything but for a computer.
Most computer languages would treat each of the compound numbers above as a "string" literal and use string comparison rules when comparing the strings. Consequently, 192.168.0.9 would be considered as being greater than 192.168.0.10. You can see for yourself in Excel or in VBA.
The code below lets one compare delimited integers (like the examples above). Put the code in a standard module of an Excel workbook. One can then use the function DelimIntMin either as a function called by other code (as in the test routine testDelimIntMin) or in an Excel worksheet as a User Defined Function (also called a UDF). When used as a function in an Excel worksheet, each argument must be a string or a single cell. For example, if J2 contains 192.168.0.9, and K2 contains 192.168.0.10, the minimum can be found with =DelimIntMin(J2,K2) but not =DelimIntMin(J2:K2). Hopefully, at some point in the future I will enhance the code to accept range arguments. Future plans also include writing the function DelimIntMax, which will return the maximum value for a set of delimited integers.
The function DelimIntMin allows for an optional delimiter as the last argument. The way the function figures out one is present is by checking if the last argument has a length of 1. If so, it assumes that the argument is a delimiter. If none is provided, the default is the period character.
Option Explicit Option Base 0
Private Function Min(ParamArray X() As Variant)
Min = Application.WorksheetFunction.Min(X)
End FunctionPrivate Function ArrMin(Arr1, Arr2) As Variant
Dim I As Integer
For I = 0 To Min(UBound(Arr1), UBound(Arr2))
If Arr1(I) < Arr2(I) Then
ArrMin = Arr1: Exit Function
ElseIf Arr1(I) > Arr2(I) Then
ArrMin = Arr2: Exit Function
End If
Next I
'arr1 and arr2 are equal to the number of common elements
ArrMin = IIf(UBound(Arr1) < UBound(Arr2), Arr1, Arr2)
End Function
Private Function convertToInteger(ByVal Arr)
Dim I As Integer, Rslt
ReDim Rslt(UBound(Arr))
For I = LBound(Arr) To UBound(Arr)
Rslt(I) = CInt(Arr(I))
Next I
convertToInteger = Rslt
End Function
Public Function DelimIntMin(ParamArray DelimNbr() As Variant)
Dim Separator As String, SeparatorPresent As Boolean
If Len(DelimNbr(UBound(DelimNbr))) = 1 Then
Separator = DelimNbr(UBound(DelimNbr))
SeparatorPresent = True
Else
Separator = "."
End If
Dim Arr, I As Integer
ReDim Arr(UBound(DelimNbr) - IIf(SeparatorPresent, 1, 0))
For I = LBound(Arr) To UBound(Arr)
Arr(I) = convertToInteger(Split(DelimNbr(I), Separator))
Next I
Dim Rslt
Rslt = Arr(LBound(Arr))
For I = LBound(Arr) + 1 To UBound(Arr)
Rslt = ArrMin(Rslt, Arr(I))
Next I
DelimIntMin = Join(Rslt, Separator)
End Function
Private Sub testDelimIntMin()
MsgBox DelimIntMin("3.3.9", "3.3.10", ".")
MsgBox DelimIntMin("1.2", "1.1.9")
MsgBox DelimIntMin("4.1", "3.3.10")
MsgBox DelimIntMin("1.2", "1.2.9")
MsgBox DelimIntMin("4.1", "3.3.10", "3.3.9", "3.1", ".")
MsgBox DelimIntMin("4,1", "3,3,10", "3,3,9", "1,1,1,1,1", ",")
End Sub