Many thanks both of you. Will try out these techniques and see how I get on.
Chuckles


"keepITcool" wrote:

Alternatively to Bernie's solution..

use a dictionary object from the Scripting runtime library.
iso storing your data in arrays you store them in dictionaries.
iso search the array you simply retrieve the data (and position)
by their key.

I've found Dictionary to be faster than vba's Collection.
plus it has advantage of CaseSensitivity, the possibility to read (and
change) the index. Also you can fill an array from a dictionary
by retrieving either the Keys or Index arrays, unlike Collections
where you'd need a loop.





--
keepITcool
| www.XLsupport.com | keepITcool chello nl | amsterdam


chuckles wrote :

I need a utility that has to do an enormous number of string
comparisons in arrays based on a text file. Ive written something
in VBA that does what I need but it is painfully slow. I think the
bulk of the time is spent on the string searching itself.

This is the function Im using:

pblic Function SequentialSearchStringArray(ByRef sArray() As String,
ByVal sFind As String) As Long
Dim i As Long
Dim iLBound As Long
Dim iUBound As Long
Dim astrFilter() As String

'Quick check - filter the current array to see if the value
matches. astrFilter = Filter(sArray, sFind)

'Unfortuantly this doesn't tell us where the match is so we then
have to do a search to find it
If UBound(astrFilter) < 0 Then
SequentialSearchStringArray = -1
Else
iLBound = LBound(sArray)
iUBound = UBound(sArray)

For i = iLBound To iUBound
If sArray(i) = sFind Then SequentialSearchStringArray = i:
Exit Function
Next i
End If

Can anyone come up with a way of really speeding this up? Is there a
way of treating each string as array of ints or something rather than
doing the endless casting. Ideas, code re-writes etc, greatly
appreciated.