Add-ins:
Excel
PowerPoint

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

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 Function

Private 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