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 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