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

ExcelChampion

Well-known Member
Joined
Aug 12, 2005
Messages
976
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.
 

Some videos you may like

Excel Facts

Copy formula down without changing references
If you have =SUM(F2:F49) in F50; type Alt+' in F51 to copy =SUM(F2:F49) to F51, leaving the formula in edit mode. Change SUM to COUNT.

Boller

Banned
Joined
Apr 11, 2006
Messages
2,328
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

Well-known Member
Joined
Aug 12, 2005
Messages
976
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

MrExcel MVP
Joined
Dec 9, 2003
Messages
10,881
Office Version
365, 2010
Platform
Windows, Mobile, Web
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

Well-known Member
Joined
Apr 28, 2004
Messages
75,599
Office Version
365
Platform
Windows
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

Banned
Joined
Apr 11, 2006
Messages
2,328
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 :rolleyes:
 

NateO

Legend
Joined
Feb 17, 2002
Messages
9,700
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

Well-known Member
Joined
Dec 26, 2005
Messages
2,390
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

Legend
Joined
Feb 17, 2002
Messages
9,700
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

Well-known Member
Joined
Jul 20, 2006
Messages
1,739
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 :rolleyes:

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.
 

Watch MrExcel Video

Forum statistics

Threads
1,101,774
Messages
5,482,837
Members
407,365
Latest member
Leah Ashley

This Week's Hot Topics

  • Finding issue in If elseif else with For each Loop
    Finding issue in If elseif else with For each Loop I have tried this below code but i'm getting in Y column filled with W005. Colud you please...
  • MsgBox Error
    Hi Guys, I have the below error show up when i try and run my macro in File1 but works fine if i copy and paste the same code into file2. [ATTACH...
  • CELL FORMAT - IF CONDITION
    My Cell Format is [B]""0.00" Cr". [/B]But in the cell, it is showing 123.00 for editing. (123 is entry figure). (Data imported from other...
  • Show numbers nearly the same
    Is this possible. I have a number that can change very time eg 0.00001234 Then I have a lot of numbers 0.0000001, 0.0000002, 0.00000004...
  • Please i need your help to create formula
    I need a formula in cell B8 to do this >>if b1=1 then multiply ( cell b8) by 10% ,if b1=2 multiply by 20%,if=3 multiply by 30%. Thank you in...
  • Got error while adding column and filter
    Got error while adding column and filter In column Z has some like "Success" and "Error". I want to add column in AA if the Z cell value is...
Top