Cells(1,1) vs Range("A1")

[ExcelChampion]

Ohhhh! I interpreted it to mean something else...duh.

 

[Oorang]

One other note... You can only do MyArray = .Value to a Variant array. But you can do .Value = MyArray to Any kind of array (at least that I have tested). So you could also load the data to a variant array and recast the array to another (May or may not net speed gains depending on what you are doing). The biggest help of this knowlede is that if you are pullling info in from an ADODB recordset you can load the recordset into the array and then the array into the cells many times faster then loading the recordset directly to the cells. (Note: All the output codde I have seen on the web for this goes straight to cells. This tests much slower for me.)

 

[Boller]

There are some comments in this thread that offer the ubiquitous claim that [A1] notation is slower that other methods.

I have never been able to come up with a model that supports this claim.

Can anyone refer me to an example that shows [A1] notation is slower?

 

[Lewiy]

There are some comments in this thread that offer the ubiquitous claim that [A1] notation is slower that other methods.

I have never been able to come up with a model that supports this claim.

Can anyone refer me to an example that shows [A1] notation is slower?

I have just tested this theory with the following code:

Private Sub CommandButton1_Click()
Dim Ct As Long
Dim TStart As Double, TEnd As Double
TStart = Timer
For Ct = 1 To 100000
    [A1].Select
Next Ct
TEnd = Timer
MsgBox TEnd - TStart
End Sub

And compared to the same using Range notation:

Private Sub CommandButton2_Click()
Dim Ct As Long
Dim TStart As Double, TEnd As Double
TStart = Timer
For Ct = 1 To 100000
    Range("A1").Select
Next Ct
TEnd = Timer
MsgBox TEnd - TStart
End Sub

Results were that using [A1] the timer returned and average runtime of 20.57 seconds and the Range method returned average runtime of 18.84 seconds.

In conclusion, I would say that I support the theory that [A1] notation is slower. However, what I would like to know is why?

 

[Richard Schollar]

Presumably because the implicit Evaluate function used in [A1] incurs some overhead.

 

[Boller]

Lewiy

Yes, that shows [A1] is slower. Thanks.

However, for the extra 1.73 seconds run-time for 100,000 pieces of code execution, I'll just stick to using [A1] notation.

When I used your procedures on my computer, the average of just 3 runs each was 10.375 and 9.161 (an extra 1.214 seconds).
Perhaps it's time for you to upgrade?

Another point that may or may not be of interest to someone, is that using the Cells method with your code produced an average run-time of 9.43733 seconds.
This is only 0.27633 secondosios faster than the Range method - so it's really not worth worrying about which one to use.

 

[Lewiy]

Perhaps it's time for you to upgrade?

Ahh, if only it were my choice!!!

However, for the extra 1.73 seconds run-time for 100,000 pieces of code execution, I'll just stick to using [A1] notation.

I guess, like most things, the difference in runtime between the two methods is very small with tiny bits of test code, however, if you were using it in a very large project it might be noticable. Just because this example gives a couple of seconds difference in 100,000 lines, doesn't mean that the difference would not be more noticable if you were using it in another way (i.e. not just selecting a cell)

 

[Boller]

doesn't mean that the difference would not be more noticable if you were using it in another way (i.e. not just selecting a cell)

And it doesn't necessarily mean that the difference would be more noticeable if you were using it in a different way.
Perhaps it might even be less noticeable.

With any project, if run-times are considered unacceptable, ways to speed-up the process are normally sought.
Avoiding [A1] notation is a possibility, but would not be high on my list.

 

[Richard Schollar]

The Evaluate method can be used to initialize 2-dimensional arrays with a default value - eg each element having "This slot is empty" (I'll post the code when get access to it again). Since it does this via a pretty convenient shorthand, I thought it might make a useful UDF. However, I did think I should test it against simply declaring the array and then using loops to populate the elements with values.

A result of my testing was that evaluate was only 50% the speed of processing the array via loops. With very large arrays holding very large string values, this performance differential actually fell, but I didn't get to the point where the Evaluate method was faster. This may have had something to do with the processor I was running this on (this was at work with an old & slow Pentium 1.6GHz chip), so I may try doing it again at home on my slightly faster processor here...

 

[Zack Barresse]

Th eother advantage to working in an array is that it contains ONLY the .Value property of the source range, hence it can be manipulated more quickly, potentially with less code.

I definitely agree here. I love array's.

As far as setting objects equal to Nothing when you're code routine is nearing completion, I used to do this as well as I thought it "cleaned up" my code. In most languages (that I've seen) this is a good thing. Although in VBA it is not needed, as the objects are destroyed when the routine completes and loses focus anyway. So I stopped writing it out as it was being done in the next operation anyway (exiting the routine).

As far as shorthand notation goes (i.e. [A1]), I do not like it. There are some people who swear by it, but I find it more confusing to look at and overall more confusing to illustrate in helping others. The strings ("A1") are usually the easiest to understand. Heck, that is what the macro recorder spits out. Mostly I prefer the numerics.

Take care all!