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



## Jon von der Heyden

Hope this isn't too stupid a question   

Looking at the typical code of the guru's I get the impression that clever folk favour cells(1,1) method over Range("A1") method.

Just wondering what the advantages of the one method is over the other?  I mean I can understand that for looping purposes (For i = 1 to 100) etc. it may be easier to construct.  

Any other distinct advantages?

Best regards
Jon


----------



## erik.van.geit

Hi,

first reason could be "*micro*-speed-optimization"


		Code:
__


Sub test()
Dim starttime As Double
Dim s As String
Dim i As Long

starttime = Timer

    For i = 1 To 10 ^ 5
    s = Range("A1").Value
    's = Cells(1, 1).Value
    Next i
    
MsgBox Timer - starttime

End Sub

when you disable the first "s = ..." line and enable the second, you will notice that the second is quite faster

kind regards,
Erik


----------



## Jon von der Heyden

Aah I see, thanks Erik.  I suppose it's always good to know that you're working to optimum efficiency however slight it may be 

I suppose the same principle applies with regards to Range("A65536").End(xlUp) vs. Cells(Rows.Count,1).End(xlUp) too then...  Because when would Rows.Count not equal 65536?


----------



## erik.van.geit

wait a moment...
my reply wasn't possibly the only answer!
perhaps a guru will tell more about it

as far as I understand this would be the logic:
Excel and VBA, computers in general like "numbers" not "strings"
I can imagine VBA performing some (luckily a quickworking) lookuptask to check out what "A" means in that context Range("*A*1")
I can imagine that the lookuptable is nothing more than the "default-names-table" (why else would "A1" be in the namebox?)
which explains the difference in speed

another obvious answer
Range("A1:D5") is easier to write than
Cells(1, 1).Resize(5, 4)

stil expecting other viewpoints ...


----------



## hatman

Personally, I use both syntaxes, based on my application.  I find the Range() method to provide better code readability, but it has some shortcomings...

If I am interacting with a single fixed cell:


		Code:
__


 a = Range("BA285").value


If I am parsing a column of information:



		Code:
__


For Cnt = 1 to 100
    range("BA" & cnt).value = Cnt
Next Cnt


If I am parsing rows and columns:



		Code:
__


For Cnt_Row = 1 to 100
    For Cnt_Col = 1 to 100
        cells(Cnt_Row,Cnt_Col).value = "Row " & Cnt_Row & ", Column " & Cnt_Col
    Next Cnt_Col
Next Cnt_Row


How else would you parse columns using Range()?  Up to Column Z, you can use the Chr() function:




		Code:
__


For Cnt_Row = 1 to 100
    For Cnt_Col = 65 to 90
        cells(Cnt_Row,chr(Cnt_Col)).value = chr(Cnt_Col) & Cnt_Row
    Next Cnt_Col
Next Cnt_Row


But this breaks down beyond column Z, and you would need something like this to get there:



		Code:
__


For Cnt_Row = 1 to 100
    For Cnt_Col = 1 to 256
        cells(Cnt_Row,alpha(Cnt_Col)).value = alpha(Cnt_Col) & Cnt_Row
    Next Cnt_Col
Next Cnt_Row

Function Alpha(num As Integer) As String

    b = Int(num / 26)
    
    a = num Mod 26
    
    If b = 0 Or (a = 0 And b = 1) Then
    
        Alpha = Chr(num + 64)
        
    Else
    
        If a = 0 Then
        
            Alpha = Chr(b + 63) & "Z"
        
        Else
            
            Alpha = Chr(b + 64) & Chr(a + 64)
            
        End If
        
    End If
    
End Function


----------



## ExcelChampion

> I suppose the same principle applies with regards to Range("A65536").End(xlUp) vs. Cells(Rows.Count,1).End(xlUp) too then...  Because when would Rows.Count not equal 65536?



If using an early version of Excel which only has about ~16000 rows, or the newest version of Excel which has ~1,000,000 rows...


----------



## Jon von der Heyden

Hatman - very helpful response, thanks   Certainly provides some good reasoning for using a combination of both. 

ExcelChampion & Erik - couple of good points there too!  Thanks 

Cheers
Jon


----------



## macleanb

Also, for completeness dont forget the "old" [A1] notation - which is slower still!

However that said, in my experience I would never use either range() or "[]" inside a loop, so its not an issue.  I did briefly use the range("A" & counter ) approach, but I stopped (not for performance reasons)

I still do use the [a1] notation though as I find it very readable and kinda reminds me this is a constant address (and old habbits die hard)

Seems like cells is ~10% faster than range(), and #VALUE!


----------



## Oorang

For my money I have come to prefer loading a range into a variant array via: 
	
	
	
	
	
	




		Code:
__


Sub Test()
Dim vArr As Variant
Dim lIndx As Long
vArr = Excel.ActiveSheet.UsedRange
    For lIndx = 1 To 3
        vArr(lIndx, lIndx) = "Something New"
    Next lIndx
Excel.ActiveSheet.UsedRange = vArr
End Sub

Why? Because if something goes wrong during execution and the program aborts... The workbook is still in it's original state


----------



## Jon von der Heyden

Mmmm, I'm gonna have to look into Variant later today, can't say I know what it's all about and I'm not entirely sure what the intended use of that code is.  Are you changing the UsedRange?

I'll have a read on the topic later this p.m.


----------



## Oorang

It's just air code to show you can pull a range (any range) in to a variant array. Change to data in the array and then plug the data back into the range (thereby changing the data in the range). I find that approach useful if I am making making many changes particularly conditional changes and/or changes that depend on data being gleaned from other sources. It just give you a little more control. You can work with the data in memory without changing the worksheet an then if you opt to change the worksheet you can do it all at one time. 
It's not a "do everytime" trick, it's just one way that can be useful in certain circumstances.


----------



## hatman

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.  In the olden days before Object Oriented Programming, Arrays of various dimensions were the preferred method for storing and manipulating large amounts of data (especially non-numeric) while processing.  

The range object contains evrything that a corresponding array contains, in terms of raw data, but it also carries with it the full fidelity of the Methods and Properties that go with that object.

Also remember that a Variant is simply a Miscellaneous, or undetermined, Variable type, which VBA coerces to a specific data type at runtime, based on the type of data that is being assigned to it.  Variants require slightlly more memory than variable types that are declared at Design-Time, but variants are about the only way to jam the raw data from an object into an array in one step without the processor hit of a loop.  In the same way, you can assign data from a Range Object to a ListBox or Combobox (which store data as an arrays) using the following syntax:



		Code:
__


Private Sub UserForm_Initialize()
    
    Me.ListBox1.ColumnCount = 2

    Me.ListBox1.List = Range("A1:B10").Value

End Sub("A1:B12")


Although perhaps this isn't a good example, since the .List property is already pre-defined as an array and requires the specific assignment of the .Value property of the Range Object.


----------



## Oorang

One behaviour to be aware of... This:


		Code:
__


Dim vTmp as Variant
vTmp = ActiveSheet.UsedRange
ActiveSheet.UsedRange.Value =  vTmp

Will replace your formulas with values. I actually use this in preference to copy/pastespecial values as it prevents me from having to hijack the clipboard. But if you didn't WANT to replace your formulas you should know about it.


----------



## ExcelChampion

> One behaviour to be aware of... This:
> 
> 
> Code:
> __
> 
> 
> Dim vTmp as Variant
> vTmp = ActiveSheet.UsedRange
> ActiveSheet.UsedRange.Value =  vTmp
> 
> Will replace your formulas with values. I actually use this in preference to copy/pastespecial values as it prevents me from having to hijack the clipboard. But if you didn't WANT to replace your formulas you should know about it.



As well, though, its slower than using Copy/Paste Special Values.


----------



## macleanb

it is? Seems paste special is slower in my code


----------



## macleanb

Interesting (or I'm being dumb)  Take a look at the below code.  If the variant block is first in the sub, then runtime is very fast (~1 second).  HOwever if its second then it runs in a similar time to the pastespecial - any ideas?



		Code:
__


Private Sub CommandButton1_Click()

Dim i As Long
Dim stime As Date
Dim time1 As String
Dim time2 As String
Dim varShite As Variant

stime = Now()
varShite = Range("A1:Z40")
For i = 1 To 500
    Range("A51:Z90") = varShite
Next i
time1 = Format(Now() - stime, "nn:ss")

stime = Now()
Range("A1:Z40").Copy
For i = 1 To 500
    Range("A51:Z90").PasteSpecial xlPasteValues
Next i
time2 = Format(Now() - stime, "nn:ss")

Debug.Print time1 & "/" & time2

End Sub


Private Sub CommandButton1_Click()

Dim i As Long
Dim stime As Date
Dim time1 As String
Dim time2 As String
Dim varShite As Variant

stime = Now()
Range("A1:Z40").Copy
For i = 1 To 500
    Range("A51:Z90").PasteSpecial xlPasteValues
Next i
time2 = Format(Now() - stime, "nn:ss")

stime = Now()
varShite = Range("A1:Z40")
For i = 1 To 500
    Range("A51:Z90") = varShite
Next i
time1 = Format(Now() - stime, "nn:ss")



Debug.Print time1 & "/" & time2

End Sub


----------



## NateO

Code:
__


varShite = Range("A1:Z40") 
For i = 1 To 500 
    Range("A51:Z90") = varShite 
Next i

LOL!

The PasteSpecial Method is generally slower than passing Values. Not only do you have to access the clipboard but look at what it's doing; I mean literally watch it. It's actually selecting ranges, and generating a fair amount of screen redraw, etc... These aren't especially fast operations or desirable.

Why would the 2nd procedure be bogged down relative to the first? Let's revisit that clipboard... Did you clear it out, or are you chewing up memory? Memory is a pretty important factor when executing code...

Original question? Yes, the Cells() property is relatively faster than Range("String"), Strings are interpreted at run-time and, as such, bind pretty late. [Anything] or [Evaluate], is a relatively sophisticated Method, even capable of parsing CSE functions and will be quite a bit slower... While they say short is sweet, and it is quite maintainable, in my opinion.

I'm not sure one can say with any certainty that Range() or Cells() is superior; they're slightly different ways of referring to a Range Object. They both have differing benefits, pending your goals/intent.


----------



## macleanb

CSE functions ????


----------



## NateO

Control-Shift-Enter, or Array-entered, functions, if you prefer.

Here's an example of the Evaluate Method parsing a CSE function:

http://www.mrexcel.com/board2/viewtopic.php?p=1230105#1230105

What appears to be a simple Range reference is actually a pretty sophisticated Method call which requires quite a bit of coercion; you will pay a price for this level of sophistication.

Simple logic generally binds tighter than complex logic.


----------



## macleanb

*Nosey*

Sorry for hijacking this thread, especially with an OT question - but....

Nate, I notice at the end of the code in the example you gave me that you finish off thus:



		Code:
__


Set xlWs = Nothing:             Set xlWb = Nothing 
Set xlApp = Nothing 
End Sub


Now I'm just being nosey/curious here - why do you use the ":" as a seperator for one line and not the other.  I only use ":" for labels (oh god did I admit that I sometimes use labels - error handlers only honest guv)  I didnt even realise you could use colons as separators!  

I'm guessing its just a style thing but wanted to make sure.

cheers...


----------



## NateO

Hello,

I don't think a brief interruption will hurt... 

See the following:

http://msdn.microsoft.com/library/en-us/veendf98/html/defstatement.asp

I'll quote, to be sure:



> statement
> A syntactically complete unit that expresses one kind of action, declaration, or definition. A statement generally occupies a single line, although you can use a colon ( : ) to include more than one statement on a line. You can also use a line-continuation character (_) to continue a single logical line onto a second physical line.


It's a technique for combining two lines of code into one. You've probably seen this in action more than you may think; it's commonly used with the Else condition of an If Statement, and following Do in a Do/Loop Loop.

There's the madness to the method. 


*Edited by NPO:* Added a space, the colon coerced to a smiley. =/


----------



## macleanb

I understood what it was doing - I was just trying to understand why you chose to combine two of the three statements, rather than all three or none.


----------



## NateO

Oh...

It's simply a matter of preference, i.e., I have my own style. I prefer to space my code in a certain manner, etc... 

I can generally spot my own code from a mile away!


----------



## macleanb

cheers...

And as you seem such a knowledgable chap:

I am not in the habit of destroying objects (local objects that is, and those without quit/close methods) do you do this to "keep yourself honest", or are there actual run time implications of allowing VB to take them out when they fall out of scope.


----------



## NateO

Thanks. We aim to please. 

I got in the habit some time ago... I consider it 'best-in-class' practice, to be honest.

It's a subject of debate, and it's not terribly fashionable to do this in Excel... I was actually reprimanded for doing this at one point... But, as you may note, it didn't stick... 

Keep in mind that we're binding with Excel from Access in the aforementioned example.

There are certain Object Variables that I don't explicitly terminate, e.g., a Range Object in a For Each/Next Loop. Having taken a peek at them following the Loop, they're not initialized, so in this case, you're beating the dead horse, or memory assignment, or whatever you will.

What opened my eyes to explicitly terminating initialized Object Variables was my research and experimentation with DAO and ADO. They have had some buggy-bugs over the years. For example, see Michael Kaplan's post, here:

http://groups.google.com/group/comp.databases.ms-access/msg/88c1db39de3e9f23

Michael's a former Access MVP and Microsoft employee. Is he right? Not sure... If you want, feel free to argue with him.

ADO's not perfect, either, e.g.,

http://support.microsoft.com/kb/319998

Unfortunately, trying to terminate that specific object by setting it to Nothing doesn't help, it's hung memory in spite of what you attempt, aside from terminating the instance of Excel (it seems to come around at this point).

So, I terminate my Object Variables, I don't worry about intrinsic data types. And be very careful about the order in which you build and terminate your Objects, build from the ground-up and terminate from the top-down. I could [seriously] answer 10 posts a day about an Excel instance being hung in memory from a error in judgment regarding an automation attempt from Access... 

Helpful?


----------



## ExcelChampion

Given the two examples below, paste special runs faster on my machine (20 seconds for Pastespecial, 28 seconds for Value.)



		Code:
__


Sub PasteSpecial()
    Dim strt As Date, endtime As Date
    Dim i As Long
    strt = Time
    For i = 1 To 100
        Range("A2:A50000").Formula = "=SUM(RC2)"
        Range("A2:A50000").Copy
        Range("A2:A50000").PasteSpecial xlPasteValues
    Next i
    endtime = Time
    MsgBox Format(endtime - strt, "hh:mm:ss")
End Sub

Sub Value()
    Dim strt As Date, endtime As Date
    Dim i As Long
    strt = Time
    For i = 1 To 100
        Range("A2:A50000").Formula = "=SUM(RC2)"
        Range("A2:A50000").Value = Range("A2:A50000").Value
    Next i
    endtime = Time
    MsgBox Format(endtime - strt, "hh:mm:ss")
End Sub


Would I do this normally, no, because there are faster ways.  But in terms, at least in regards to what I was talking about, Pastespecial always runs faster.


----------



## ExcelChampion

This took 30 seconds:



		Code:
__


Sub vTmp()
    Dim vTmp As Variant
    Dim strt As Date, endtime As Date
    Dim i As Long
    Application.ScreenUpdating = False
    strt = Time
    For i = 1 To 100
        Range("A2:A50000").Formula = "=SUM(RC2)"
        vTmp = ActiveSheet.UsedRange
        ActiveSheet.UsedRange.Value = vTmp
    Next i
    endtime = Time
    Application.ScreenUpdating = True
    MsgBox Format(endtime - strt, "hh:mm:ss")
End Sub


----------



## NateO

Okay, please allow me to qualify my post with some tests and see if we can agree. 

Take the following:



		Code:
__


Public Declare Function QueryPerformanceFrequency _
    Lib "kernel32.dll" ( _
    lpFrequency As Currency) As Long

Public Declare Function QueryPerformanceCounter _
    Lib "kernel32.dll" ( _
    lpPerformanceCount As Currency) As Long

Sub TimerTime()
Dim Ctr1 As Currency, Ctr2 As Currency, Freq As Currency
Dim Overhead As Currency, a As String, i As Long
QueryPerformanceFrequency Freq
QueryPerformanceCounter Ctr1
QueryPerformanceCounter Ctr2
Overhead = Ctr2 - Ctr1 ' determine API overhead
QueryPerformanceCounter Ctr1 ' time loop

For i = 1 To 100
    Call foo
    'Call bar
Next i

QueryPerformanceCounter Ctr2
Debug.Print (Ctr2 - Ctr1 - Overhead) / Freq
End Sub

Sub Foo()
With Range("A1:B10000")
    .Formula = "=2+2"
    .Copy
    .PasteSpecial xlPasteValues
End With
End Sub

Sub Bar()
With Range("A1:B10000")
    .Formula = "=2+2"
    .Value = .Value
End With
End Sub

Foo() is clocking in around 3.9 seconds, on average, on my machine, while Bar() is clocking in around 5.5 seconds. The PasteSpecial Method is throttling the marshalling of Values approach in this case, over a fairly large Range.

Now change B10000, in Foo() and Bar() to B10 and note the rather big difference: Foo() is clocking around .53 seconds while Bar() is annihilating it, clocking in around .19 seconds.

So, the real answer may be more of the usual... What are you attempting, again? This may vary with the actual size of the Range you're working with. Shame on me for trying to post a hasty generalization... When in doubt, use a timer.


----------



## ExcelChampion

Code:
__


"Now change B10000, in Foo() and Bar() to B10. Not the rather big difference: Foo() is clocking around .53 seconds while Bar() is annhilating it, clocking in around .19 seconds. "


Huh?  Wow, that is a big difference.  But, on the other hand, humanly speaking, there is no difference.

But, I guess my question is, "why?"


----------



## macleanb

Have you tried foo-ing & bar-ing then bar-ing and foo-ing? (its a serious question but I am not expecting an answer)

On the destroying variables - I am not sure what version of my post you saw as I edited it.  I am always explicit with automation variables, I guess I use a limited number of automation/complex objects and know which ones I have to be careful with.  I guess if I was doing more varied work, I might develop the habit/ be more thorough.  Thanks for all the input - I'm going to try and be more "best practice" - as in a large corparate - we might get Excel with .NET in a just 4 or five years from now


----------



## NateO

Hello again,



> Huh?  Wow, that is a big difference.  But, on the other hand, humanly speaking, there is no difference.


Respectfully, I dissent. I'm not saying to discard the PasteSpecial Method, to any extent, but I am less than enamored with the fact that it actually changes the selected Range Object 'for you'. This bothers me to some extent...

And over a small range, it's well over twice as fast. So, in the case of working with small Range Objects, there are two benefits.



> But, I guess my question is, "why?"


We're left to speculate, here. My conclusion? Working the Clipboard has a fair amount of overhead that is exposed when working with a small Range. But it's efficient enough where, over a large range, it can outpace Excel natively coercing a Range of data to a Variant Array in memory, etc... 

So, I guess it does depend on what we are doing. Concur?


----------



## Oorang

Just another tip because I don't see anyone doing it in these posts.... Recalculate before loading your array
Turn calulation off
Turn Events Off
Load the array
Do your stuff
Plug your array back in
Turn the events back on
Turn the calculation back on

Don't think it matters? Try taking about 1000 records with and formulas. Run subtotal with and without calculation. I have seen differences of over 30 seconds.


----------



## macleanb

Good point - I know it matters, and I think we all must do this in our real work - but the noise this overhead puts into the results is major and should be excluded from the timings.

Still cant figure out why foo then bar is different from bar then foo?

Well I'm of down t'pub - have a good weekend chaps, by the time I come back this thread should have got to world peace/global warming - I am looking forward to it already


----------



## NateO

> Just another tip because I don't see anyone doing it in these posts.... Recalculate before loading your array
> Turn calulation off
> Turn Events Off
> Load the array
> Do your stuff
> Plug your array back in
> Turn the events back on
> Turn the calculation back on
> 
> Don't think it matters? Try taking about 1000 records with and formulas. Run subtotal with and without calculation. I have seen differences of over 30 seconds.


Quite right, it matters. I didn't mean to imply that we shouldn't do these things with production-level code/applications by omitting these techniques from my posts to this thread... In fact, if your project is distributed, I'd read the end-user's calculation setting into memory before toggling it in any direction.

My goal, in this case, was actually to expose the amount of screen redraw that the PasteSpecial Method was generating, thus inflating its resulting times, not to optimize. But, it seems that it's pretty efficient, even while performing this seemingly extraneous business, over a large Range.

So, I concur with the aforementioned advice. 

Okay, this is in the Lounge (serious questions belong the Excel forum). macleanb, which pub are you going to? I celebrated St. George's Day, on Monday, at the Shepherd's Market in Mayfair.  Cheers, mate.


----------



## macleanb

Cat & Canary, Canary Wharf (If I can get this trader of my back) - they serve a nice ale called fullers, "proper" bitter (well its not micro brewery/boutique, but a darn fine comercial ale)


----------



## macleanb

> London Pride is a smooth and astonishingly complex beer, which has a distinctive malty base complemented by a rich balance of well developed hop flavours from the target, challenger and northdown varieties in the brew. At 4.1% a.b.v in cask (4.7% a.b.v in bottles)London Pride is an ideal session-strength premium ale.



I am not sure what "session strength" means, but I am about to try and find out!

mustn't get too larupped mind you...


----------



## NateO

Fullers, eh? I have heard of it, but I've yet to try it... I was near the Tower of London, the other day, in an older pub called 'Drawn and Quartered', I believe, and they were big on Fullers. Aside from serving it, they had Fullers ashtrays, etc...

I went with old faithful; Ice Cold Guinness. 

First time in London... That's quite a town you have there. I think I only had about 10 near-death experiences while crossing the streets.  

Have a nice weekend, everyone!


----------



## ExcelChampion

> Hello again,
> 
> 
> 
> 
> Huh?  Wow, that is a big difference.  But, on the other hand, humanly speaking, there is no difference.
> 
> 
> 
> Respectfully, I dissent. I'm not saying to discard the PasteSpecial Method, to any extent, but I am less than enamored with the fact that it actually changes the selected Range Object 'for you'. This bothers me to some extent...
> 
> And over a small range, it's well over twice as fast. So, in the case of working with small Range Objects, there are two benefits.
> 
> 
> 
> 
> But, I guess my question is, "why?"
> 
> Click to expand...
> 
> We're left to speculate, here. My conclusion? Working the Clipboard has a fair amount of overhead that is exposed when working with a small Range. But it's efficient enough where, over a large range, it can outpace Excel natively coercing a Range of data to a Variant Array in memory, etc...
> 
> So, I guess it does depend on what we are doing. Concur?
Click to expand...


I completely concur...always have. 

What do you mean by this, though:- "actually changes the selected Range Object 'for you'"


----------



## Oorang

> I went with old faithful; Ice Cold Guinness.


Don't you know you're supposed to drink that warm?!  I mean if you're that masochistic you should go the whole way


----------



## erik.van.geit

wow, you did post a lot here today
and only 70% off-topic   



> What do you mean by this, though:- "actually changes the selected Range Object 'for you'"


when you perform pastespecial the range where the data are pasted gets selected



		Code:
__


Sub huh()
Range("A1").Select
Range("A1").Copy
Range("B1").PasteSpecial xlPasteValues
Application.CutCopyMode = False
MsgBox Selection.Address
End Sub

NOTE to all:
don't consider this as good code, it's only meant to show that A1 is not selected anymore: B1 is now


----------



## 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:


		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:



		Code:
__


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)
		
Click to expand...

*
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!


----------



## ExcelChampion

Personally, I like to name my ranges and use the names rather than cell addresses.  Yes, I relaize sometimes you don't need to do it like if selecting A1 at the end of the procedure to make sure the activecell is where the user can clearly see it, etc...in which case I don't name it.  In this case I'd use Range.


----------



## Boller

> 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.



firefytr

You twice mention "more confusing" without giving any explanation.
Also "easiest to understand"  - again no explanation.

I don't see that either method is either more or less confusing than the other, nor easier/less easy to understand.

You probably find it "more confusing" to look at because you don't use it.
In the same way, presumably the Range("A1") notation could well be "more confusing" to someone who normally uses [A1].

And it matters not at all what the macro recorder "spits out"


----------



## ExcelChampion

I think it absolutely matters what the macro recorder spits out in the context firefytr said it, which is helping others, especially if the perosn is new to macros. Hence the comment about the macro recorder.

Newbies typically use the macro recorder to learn.  Record a macro and then go look at what the code looks like.  I also think it would be confusing for someone just learning, since they would be used to seeing Range("..")...etc.


----------



## Zack Barresse

> 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.
> 
> 
> 
> 
> firefytr
> 
> You twice mention "more confusing" without giving any explanation.
> Also "easiest to understand"  - again no explanation.
> 
> I don't see that either method is either more or less confusing than the other, nor easier/less easy to understand.
> 
> You probably find it "more confusing" to look at because you don't use it.
> In the same way, presumably the Range("A1") notation could well be "more confusing" to someone who normally uses [A1].
> 
> And it matters not at all what the macro recorder "spits out"
Click to expand...


I'm not sure I agree with anything you said.  What I meant to point out is that in my experience, which is not necessarily the  experience anybody else has had, shorthand notation more often than not tends to confuse people.  This is not written in a manual anywhere, it is my personal observation.  You may be one of those people who thinks this method is intuitive, flashy, easier to follow, shorter to type, or any of probably a dozen other reasons.  Does it make you wrong?  Certainly it does not.  As Nathan said, it is mostly a matter of opinion.  My opinion is I like the Cells() method best.  I think it is faster for me to type, execute, debug, troubleshoot, and so on.  Next is the Range() method, followed (quite some ways off, to the point of I'll Never Use It Again'ness) by shorthand notation.

Take care.


----------



## Norie

I avoid the [] notation.

One major drawback is that you can't use it with concatenation as far as I'm aware anyway.

As to speed/'efficiency I think that's negligible.

I can think of far more methods that impact on that. eg Selecting/Activating etc


----------



## Boller

> As to speed/'efficiency I think that's negligible.



Quite right. Just like all the arguments offered in this thread for/against [A1] versus Range - they're negligible.



		Code:
__


One major drawback is that you can't use it with concatenation as far as I'm aware anyway.


Hardly a "major drawback".
The workaround is not to use [A1] with concatenation


----------



## NateO

Just a note on timing...

I ended up comparing the two with the following:



		Code:
__


Public Declare Function QueryPerformanceFrequency _
    Lib "kernel32.dll" ( _
    lpFrequency As Currency) As Long

Public Declare Function QueryPerformanceCounter _
    Lib "kernel32.dll" ( _
    lpPerformanceCount As Currency) As Long

Sub Faster()
Dim tmpStr As String
Let tmpStr = Range("A1").Value
End Sub

Sub Slower()
Dim tmpStr As String
Let tmpStr = [a1].Value
End Sub

Sub foo()
Dim Ctr1 As Currency, Ctr2 As Currency, Freq As Currency
Dim Overhead As Currency
Dim i As Long
QueryPerformanceFrequency Freq
QueryPerformanceCounter Ctr1
QueryPerformanceCounter Ctr2
Overhead = Ctr2 - Ctr1 ' determine API overhead
QueryPerformanceCounter Ctr1 ' time loop
For i = 1 To 10000
    Call Faster
    'Call Slower
Next
QueryPerformanceCounter Ctr2
Debug.Print (Ctr2 - Ctr1 - Overhead) / Freq
End Sub

Using Slower(), I came up with an average time of 0.2605 seconds, and Faster() returned 0.1699 seconds.

In absolute time, the difference may seem small, even more so in a single reference. In relative time, with this test, Slower() represents a 53.3% variance over Faster().  

I'll probably stick with Range("A1") over [A1] for simple Range referencing... :wink: 

Which isn't to say that I think the Evaluate method isn't very useful, from time-to-time.


----------



## Derek Brown

I was beginning to despair reading through all six pages ... until I came to ExcelChampion's comment:


> Personally, I like to name my ranges and use the names rather than cell addresses. Yes, I relaize sometimes you don't need to do it like if selecting A1 at the end of the procedure to make sure the activecell is where the user can clearly see it, etc...in which case I don't name it. In this case I'd use Range.


I was looking for someone to mention 'maintainability' and 're-usability' of code (or have I missed such a comment?).
I only ever refer to Range("A1") in A1 notation and, in all other instances  I use variable assignments.
For example, the column with the "Name" heading might look like:


		Code:
__


Dim lngNameCol as Long
lngNameCol = 7

and then use the name in the code:


		Code:
__


ws.Range(cells(lngRowKount,lngNameCol),cells(lngRowKount,lngnameCol))="xyz"

The variables would be declared at the beginning of the code and the setting of values for all such variables would be grouped together (also at the beginning of the code).
So when the boss comes along to ask for an additional column to be added, I don't have to search through a lot of code to determine what needs changing.
I would find "lngNameCol" more meaningful than referring to Column G in the above example and my code becomes more portable to other workbooks.
Perhaps I am getting too old and the 'tried and tested' programming techniques are no longer considered relevant today.
As already indicated by some of the earlier posts, in many cases little processing time is gained.
And ... Hey! If it is taking too much time, go for a cup of tea!


----------



## NateO

Speaking of maintainability...

Be careful with your syntax. If you really want to make your Range reference Worksheet-specific, you really should qualify your use of the Cells Property with the Worksheet, too.

E.g., in a brand new, 3-Worksheet Workbook, select the 2nd Worksheet. Now try the following:



		Code:
__


Sub foo()
MsgBox Worksheets(1).Range(Cells(1, 1), Cells(10, 10)).Parent.Name
End Sub

Kaboom! Because you're using Ranges in one Worksheet (the active one) to reference Ranges in another Worksheet, which doesn't fly, internally.

Now try this:



		Code:
__


Sub bar()
With Worksheets(1)
    MsgBox .Range(.Cells(1, 1), .Cells(10, 10)).Parent.Name
End With
End Sub

It's always safer when qualifying Objects with their Parent to qualify all them with Parent.


----------



## brian.wethington

> As to speed/'efficiency I think that's negligible.
> 
> 
> 
> 
> Quite right. Just like all the arguments offered in this thread for/against [A1] versus Range - they're negligible.
> 
> 
> 
> Code:
> __
> 
> 
> One major drawback is that you can't use it with concatenation as far as I'm aware anyway.
> 
> 
> Hardly a "major drawback".
> The workaround is not to use [A1] with concatenation
Click to expand...



If one thing does not work that works with the other methods, it is a shortcoming and it can cause some issues and understanding problems. Here the shortcomings of the method are not a "major drawback" to you but to someone like me who did not know that you could not concatenate using the notation it would be a major drawback as I have now written code to do just that and I can't figure out why the code is not working.

I simply have not used to the notation of [A1] very much so that when I do it is hard for me to read or manage the code (I do have others reasons but none of them really matter at all as there is always another way to skin the cat).  I am more used to use of Range and Cells, therefore I am able to work through my code more quickly.  It would not make much sense for me to introduce a third way in my code, especially if there is no "major contribution" that I don't already have available to me from Cells or Range.  Simply a matter of opinion.  For others, the evaluation method is quicker to type, quicker to read, and they know the shortcomings of the method so they know how to approach those already.  

By the way, it is usually a good idea to speak to people in terms they already understand (classic communication stance).  In an extreme example, if you speak English and someone asked you a question in English would you answer them in Japanese (this excludes John Mayer types, if anyone knows that story)?  I venture to say that you would answer in English in most cases. Thus, it is safe to say that if you are trying to help someone and they have been using Range then you should continue the use of Range unless there is a "major benefit" to using another method.

In summary, we have all said it is a matter of preference.  It does not mean that [A1] notation is inferior.  That would be like saying a tank is better than a jet.  You would have to define under what circumstance it was better before the statement makes any sense whatsoever.


----------



## Ivan F Moala

> 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).
> 
> 
> Take care all!



I disagree with all those who purport to that. i.e. setting objects = nothing is NOT required. I beleive it is good pratice to Release/Destroy objects on closing, in order of creation FCFD (First created First Destroyed) especially when automating COM objects eg DAO and ADO. Yes COM objects have calls to AddRef and Release on objects (it actually keeps count of refs set), this is a part of the COM contract. Even if the the documentation says it is destroyed upon closing I would still do this.

The cost to add just a few lines of code is nothing, as opposed to the cost of a left over instance(s) in the ROT or ghost threads with there subsequent drain on resources (memory leaks and process time)

The same is true of API programing where you must use the corresponding Destroy/Release/Delete API
eg
LoadBitmapA > DeleteObject 
CreateBitmap > DeleteObject 
CreateSolidBrush > DeleteObject 
CreateCursor > DestroyCursor 
CreateCompatibleDC > DeleteDC 
GetDC > ReleaseDC 
CreateFontA > DeleteObject 
CreateIcon > DestroyIcon 
ExtractIconA > DestroyIcon 
CreateMetaFileA > CloseMetaFile 
CopyMetaFileA > DeleteMetaFile


----------



## Oorang

Hi Ivan, I use the practive you are recomending. But just to play devil's advocate, shouldn't the Garbage collector automaticlly dispose of those objects as they fall out of scope at the end of the sub?


----------



## Greg Truby

1. @ d3p2j5b: I do the same thing, but I stick the column defs at the very top of the module inside an ENUM/END ENUM.  Makes them easier to spot.  And for essentially the same reason.  But in my case it's usually that my code is working with mainframe extracts and my boss has had IS add a column to the report w/o telling anybody but IS and one day code that has worked well for years is suddenly way the he... off in left field somewhere and I'm trying to figure out what happened. 

2. @ Ivan: FCFD?  I had always assumed that I needed to do FCLD.  For example, if one creates a WB object, then a WS object, then a Range object.  If one kills the WB first, would not the WS & Range still have some type of "implied" connection to the WB that might result in it hanging open?  I assumed it would be better to kill the child object first and work my way up the container structure.  I assume FCFD is the standard practice then?


----------

