Finding the maximum in an array of strings (Excel VBA)

[deltaquattro]

Hi,

as stated in the object, I need to find the maximum in an array of
strings, using VBA in Excel 2000. Of course I could use a (slooow)
linear search, i.e. a simple loop over all the elements, comparing
each element with the last biggest and updating if the new element is
bigger. But, since Excel provides so many quick worksheet functions
for a lot of tasks, I'd like to use one of them. I tried to use
LOOKUP, but it doesn't work, since it expects the array to be in
ascending order, which of cours isn't the case, otherwise I wouldn't
need to LOOKUP at all (I'd just pick the first element of the array).
Can you help me?

Thanks,

Best Regards

Sergio Rossi

 

[Peter T]

You could of course sort the strings, do your Lookup etc, then restore the
original order (need to start with a helper column 1,2,3 etc then restore
sorting on the helper column). However unless you have a very extremely
large number of strings don't give up on VBA. Even though normally slower
than Excel methods, the time to call say an Excel worksheet function is
relatively slow. Maybe you can adapt one of the ideas from the following

Sub test()
Dim i&, j&
Dim s As String
Dim sMaxXL As String, sMaxVB As String

s = String(25, " ")

ReDim arr(1 To 10000, 1 To 1) As String

For i = 1 To UBound(arr)
For j = 1 To Len(s)

Mid$(s, j, 1) = Chr(Int(Rnd() * 26) + 65)
Next
arr(i, 1) = s
Next
' clock starts her
Stop
With Range("A1:A" & UBound(arr))
.Value = arr
Stop ' time to dump values to cells
.Sort Key1:=.Item(1), Order1:=xlDescending, Header:=xlNo
sMaxXL = .Item(1)
' .Clear
End With
Stop ' time for Excel to sort

sMaxVB = getMax(arr)
Stop ' time for VB to get max

MsgBox sMaxXL & vbCr & sMaxVB

End Sub

Function getMax(arr() As String) As String
Dim i As Long, idx As Long
Dim sMax As String

sMax = arr(1, 1)

For i = 2 To UBound(arr)
If StrComp(arr(i, 1), sMax, vbTextCompare) = 1 Then
sMax = arr(i, 1)
idx = i '
End If
Next

getMax = sMax

End Function

The test seems to find the Max string of 10k x 25 length strings pretty
fast in my old system.

Be aware strings "compare" slightly differently in Excel & VBA with certain
characters, so you might get different results.

Regards,
Peter T

 

[Rick Rothstein]

You are looking to find the "maximum" what...

The longest string?...

The string with the highest alphabetically leading letters?

Something else?


Rick Rothstein (MVP - Excel)


"deltaquattro" wrote in message

Hi,

as stated in the object, I need to find the maximum in an array of
strings, using VBA in Excel 2000. Of course I could use a (slooow)
linear search, i.e. a simple loop over all the elements, comparing
each element with the last biggest and updating if the new element is
bigger. But, since Excel provides so many quick worksheet functions
for a lot of tasks, I'd like to use one of them. I tried to use
LOOKUP, but it doesn't work, since it expects the array to be in
ascending order, which of cours isn't the case, otherwise I wouldn't
need to LOOKUP at all (I'd just pick the first element of the array).
Can you help me?

Thanks,

Best Regards

Sergio Rossi

 

[joeu2004]

as stated in the object, I need to find the maximum in an array of
strings, using VBA in Excel 2000. Of course I could use a (slooow)
linear search [....]
I tried to use LOOKUP, but it doesn't work, since it expects the
array to be in ascending order, which of cours isn't the case,
otherwise I wouldn't need to LOOKUP at all (I'd just pick the first
element of the array).

I interpret that to mean that the array is not ordered at all, even in
descending order.

In that case, you must use a linear search to find the max or min of
anything.

VLOOKUP and HLOOKUP can do linear searches, as can MATCH. Whether or
not they are faster than a VBA implementation, I cannot say with
impunity.

It might depend, in part, on what you want to find the max of. String
comparisons? Longest string? Something else?

 

[deltaquattro]

Hi, Pete,

great suggestion, thanks. I tried your tip using the helper column
and found out that the difference in time, compared with the plain VBA
For loop, is not as big as to justify the extra programming effort and
the reduced readibility of the code. I had read on many sites on the
Web that using VBA loops slows down the code a lot with respect to
using Excel worksheet functions, but it looks like this is not really
(or not always) the case.

 

[deltaquattro]

Hello Rick,

right now I have strings like:

0000001
1231123
0002341
....

and I need to find their maximum. But in the future I may have
something like
0000001
1231123
A00002
.....

In this case the numbers are considered "bigger" than letters.

Best Regards

Sergio

 

[deltaquattro]

as stated in the object, I need to find the maximum in an array of
strings, using VBA in Excel 2000. Of course I could use a (slooow)
linear search [....]
I tried to use LOOKUP, but it doesn't work, since it expects the
array to be in ascending order, which of cours isn't the case,
otherwise I wouldn't need to LOOKUP at all (I'd just pick the first
element of the array).

I interpret that to mean that the array is not ordered at all, even in
descending order.

In that case, you must use a linear search to find the max or min of
anything.

VLOOKUP and HLOOKUP can do linear searches, as can MATCH.  Whether or
not they are faster than a VBA implementation, I cannot say with
impunity.

It might depend, in part, on what you want to find the max of.  String
comparisons?  Longest string?  Something else?

Hi,

you're perfectly right, the array is not ordered in any sense. My max
is defined in the sense of String comparison: I mean that for me,

"013" < "023"

and

"A03" > "003"

in accordance to the Strcomp function.

Thanks,

Sergio