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




"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