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

[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"

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.

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:

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:

Dim lngNameCol as Long
lngNameCol = 7

and then use the name in the 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:

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:

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.

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

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.