Using variant array instead of range object.

I have a worksheet with part numbers in rows 1 to 10 of column a. The code in macro 1 finds what row part AB919 is on. It uses the find method on a range object.

Macro 1 works. Macro 2 does not. In Macro 2 I try to write similiar code using a variant array. Can anyone tell me if it is possible to do this?



Sub Macro1()
'Get row number that part is on
Dim rng As Range
Dim cell As Range
Dim spartno As String
Dim r As Integer
Set rng = Worksheets(1).Range("a1:a100")
spartno = "AB919"
Set cell = rng.Find(spartno)
r = cell.Row

End Sub


Sub Macro2()
'Get row number that part is on
Dim rng As Range
Dim cell As Variant
Dim spartno As String
Dim r As Integer
Dim vararray As Variant
vararray = Worksheets(1).Range("a1:a100")
spartno = "AB919"
cell = vararray.Find(spartno)
r = cell.Row

End Sub

Sure it's possible, although not quite in the way you tried it. Objects (like Range) have methods (like Find), so they can "do things on their own", while arrays are pure data structures, so you need to operate on them.
In your example case, there seems to be little point in using an array. Could you explain the background of your question a little better? :)

In the program I am working on I will be downloading 20 xml files on a worksheet I call download. I use 2 dimensional variant arrays for my worksheets so that I can process things faster. The downloaded data will have about 700 accounts. I will read the downloaded data form the xml file into a variant array. I will then search and find 50 accounts and their values and write these to a different variant array. I will then write the information from the last array into a worksheet called data.

This can be a very slow program so I am trying to speed things up by doing as little processing to the worksheets as I can, and instead do it to variant arrays which I write back to the worksheet.

So to conclude I am trying to find out on what rows certain acct numbers are on in the variant array that I use for my data worksheet. I can find the row number on the worksheet, but using a range object on a worksheet and searching on the worksheet will slow things down. Can I find out what row an account number is on the the variant array.

This probably sounds more complicated than it is.

I can find the row number on the worksheet, but using a range object on a worksheet and searching on the worksheet will slow things down.
Thanks for the explanation.

I'm not convinced that you're right, though. It's quite possible that using .Find is as fast or faster than a manual search in an array. The best thing would be to time it for yourself and see how it comes out.

To search an array, you need to loop over its dimensions:
Dim nRow As Long, nCol As Long
For nRow = LBound(a, 1) To UBound(a, 1)
For nCol = LBound(a, 2) To UBound(a, 2)
If a(nRow, nCol) = Target Then 'found target
Next nCol
Next nRow

a() is the array, target is the value you're looking for, LBound (http://msdn.microsoft.com/en-us/library/aa445057(VS.60).aspx) and UBound (http://msdn.microsoft.com/en-us/library/aa263396(VS.60).aspx) tell you what the extent of the array in each dimension is.

To compare the speed of the two approaches, you can use the Timer (http://msdn.microsoft.com/en-us/library/aa263387(VS.60).aspx) function:
Dim xStart As Double: xStart = VBA.Timer
'do something
Dim xEnd As Double: xEnd = VBA.Timer

Debug.Print xEnd - xStart

The output will be the time it took to "do something", in seconds. Generally, you'll want to repeat "do something" a hundred or a million times, so it takes a decent amount of time (a few seconds), in order to suppress random fluctutations.

Happy testing! :)

Heyjoe, what does the data look like? Is it delimited in a structured way?

If so, you could load your search data into a string, then just use 'InStr' to locate the position of the value you're after, provided position of the value you need can be anticipated in relation to the search value. So say your data looks like this:
AB910 1, AB911 2, AB912 3, ... AB919 20
Then 'InStr' will find the position of "AB919" from where you can derive the value "20". You can then load the results from your search into an array and assign that to your Range :)

Good point, Timbo. A direct text search in the source xml files would definitely be faster than importing them in bulk and then searching the result. :)

Thank you both. Your answers have certainly contributed to my understanding of VBA, and have given me different approaches to my problem.

Regarding Timbo's solution. Column A has 50 account numbers in it. I tried to read these into an array by:




myarray =range("a1:a51").
answer = instr(myarray, "A100")



The second line of code gives me a type mismatch error. What does Timbo mean by "loading my search data into a string?

Heyjoe,

the fundamental issue in this thread are data types. A dataset can be stored in different ways - in an Excel Range object, in an array, in a delimited string, etc. To retrieve data from a structure, VB offers many different functions, most of which can only operate on one kind of structure. In addition, object datatypes offer their own properties and functions. It is important to be aware of the datatypes involved when writing code. The VB Help (F1) and Object Browser (F2) are very useful in that respect. For example, this is an excerpt from the help entry for InStr:

Syntax

InStr([start, ]string1, string2[, compare])

The InStr function syntax has these arguments:

string1 Required. String expression being searched.
string2 Required. String expression sought.

This explains the error you are getting: the argument expected is a string expression, you are trying to use an array instead.

I'd suggest you read the MSDN chapter on Variables and Data Types (http://msdn.microsoft.com/en-us/library/aa242127(VS.60).aspx). Without that background, it's hard to communicate about the thread topic properly. :)