Macro Speed Question

[Goshute]

I'm thinking about an analytic macro that runs a number of internal loops. Generally I set typed ranges in these kind of macros, but some of them are running against up to 100,000 lines of data and the one I'm contemplating has a couple of internal loops and I expect will take a little while. Would there be any advantage in reading the data into a two dimensional array and running the analysis against the array?

[HansV]

I performed a quick test with a 100000 rows by 2 columns range. Using a two-dimensional array, the macro was almost 10 times faster than using a Range object.

[Goshute]

Using a two-dimensional array, the macro was almost 10 times faster than using a Range object.

That's pretty darned conclusive! Thanks, Hans.

[Goshute]

Using a two-dimensional array...

An observation and two questions:

Sub Range2Array()
Dim varRange As Variant

varRange = Sheets("Sheet1").Range("A1:F100")
Debug.Print varRange(5, 4) ' returns content of cell D5
End Sub

This is a 1-based array - I was expecting a zero based array. Am I missing something?

In playing with this I found that it works the same if I Set the array as an object

Set varRange = Sheets("Sheet1").Range("A1:F100")

If I don't use Set is still it good practice to set Variants to nothing when they are equivalent to objects, or does VBA do that for me?

[HansV]

1. Rows and columns start counting at 1, so the array is one-based.
2. If you use

varRange = Sheets("Sheet1").Range("A1:F100")

varRange will be of type variant array, but if you use

Set varRange = Sheets("Sheet1").Range("A1:F100")

varRange will be of type Range.

3. All local variables will be set to Nothing automatically when the macro ends, so it's only really necessary to set them to Nothing explicitly if you want to release memory during the course of a macro.

[Goshute]

(Next time remind me to use TypeName() )

2. If you use

Set varRange = Sheets("Sheet1").Range("A1:F100")

varRange will be of type Range.

In which case I'd lose the speed advantage, right?

[HansV]

Yes, I fear so.