Find the minimum of delimited numbers
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
References
|
|
