Page 6 of 7 FirstFirst ... 4567 LastLast
Results 51 to 60 of 63

Thread: Cells(1,1) vs Range("A1")
Thanks Thanks: 0 Likes Likes: 0

  1. #51
    Board Regular ExcelChampion's Avatar
    Join Date
    Aug 2005
    Location
    Detroit, MI
    Posts
    976
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

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

  2. #52
    Banned
    Join Date
    Apr 2006
    Posts
    2,328
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    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"

  3. #53
    Board Regular ExcelChampion's Avatar
    Join Date
    Aug 2005
    Location
    Detroit, MI
    Posts
    976
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

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

  4. #54
    MrExcel MVP Zack Barresse's Avatar
    Join Date
    Dec 2003
    Location
    Oregon, USA
    Posts
    10,724
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    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.
    Regards,
    Zack Barresse
    My Book on Excel Tables
    (If you would like comments in any code, please say so.)

  5. #55
    Board Regular Norie's Avatar
    Join Date
    Apr 2004
    Location
    Stirling, Scotland
    Posts
    75,152
    Post Thanks / Like
    Mentioned
    61 Post(s)
    Tagged
    6 Thread(s)

    Default

    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
    If posting code please use code tags.

  6. #56
    Banned
    Join Date
    Apr 2006
    Posts
    2,328
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    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

  7. #57
    Legend NateO's Avatar
    Join Date
    Feb 2002
    Location
    Minneapolis, Mn, USA
    Posts
    9,700
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

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

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

  8. #58
    Board Regular
    Join Date
    Dec 2005
    Location
    Basingstoke (UK)
    Posts
    2,390
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    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!
    Never give way to anger - otherwise in one day you could burn up the wood that you collected in many bitter weeks.

  9. #59
    Legend NateO's Avatar
    Join Date
    Feb 2002
    Location
    Minneapolis, Mn, USA
    Posts
    9,700
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    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.

  10. #60
    Board Regular brian.wethington's Avatar
    Join Date
    Jul 2006
    Location
    Irving TX
    Posts
    1,739
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    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

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

    Start by doing what's necessary; then do what's possible; and suddenly you are doing the impossible.
    St. Francis of Assisi

Some videos you may like

User Tag List

Like this thread? Share it with others

Like this thread? Share it with others

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •