Page 1 of 7 123 ... LastLast
Results 1 to 10 of 63

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

This is a discussion on Cells(1,1) vs Range("A1") within the Lounge v.2.0 forums, part of the The Lounge category; Hope this isn't too stupid a question Looking at the typical code of the guru's I get the impression that ...

  1. #1
    MrExcel MVP
    Moderator

    Jon von der Heyden's Avatar
    Join Date
    Apr 2004
    Location
    Blackboys, East Sussex, UK
    Posts
    9,898

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

    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
    Regards,
    Jon von der Heyden

    - Posting guidelines, forum rules and terms of use

    - Post your sheet using HTML tables - why?

    - Read the FAQs

    - Always use CODE tags when posting code, e.g. [CODE]insert code here[/CODE]

    - My Excel Blog

  2. #2
    MrExcel MVP erik.van.geit's Avatar
    Join Date
    Feb 2003
    Location
    Belgium 3272 Testelt
    Posts
    17,765

    Default

    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

  3. #3
    MrExcel MVP
    Moderator

    Jon von der Heyden's Avatar
    Join Date
    Apr 2004
    Location
    Blackboys, East Sussex, UK
    Posts
    9,898

    Default

    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?
    Regards,
    Jon von der Heyden

    - Posting guidelines, forum rules and terms of use

    - Post your sheet using HTML tables - why?

    - Read the FAQs

    - Always use CODE tags when posting code, e.g. [CODE]insert code here[/CODE]

    - My Excel Blog

  4. #4
    MrExcel MVP erik.van.geit's Avatar
    Join Date
    Feb 2003
    Location
    Belgium 3272 Testelt
    Posts
    17,765

    Default

    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("A1")
    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 ...

  5. #5
    Board Regular hatman's Avatar
    Join Date
    Apr 2005
    Location
    Palmer, MA
    Posts
    2,610

    Default

    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
    (XL2010 on Windows 7 Professional SP 1)

    H0mogenized pasteurized milk from confinement animals, and butter, cheeses, and yogurts made from such milk, may look the same and carry the same name, but the inference that they are the same as [raw unpasteurized milk from pasture-fed cows] is part of the insidious deception that has been perpetrated on (us) the consumers of industrialized food.

    -Ron Schmid, "The Untold Story of Milk"

  6. #6
    Board Regular ExcelChampion's Avatar
    Join Date
    Aug 2005
    Location
    Detroit, MI
    Posts
    952

    Default

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

  7. #7
    MrExcel MVP
    Moderator

    Jon von der Heyden's Avatar
    Join Date
    Apr 2004
    Location
    Blackboys, East Sussex, UK
    Posts
    9,898

    Default

    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
    Regards,
    Jon von der Heyden

    - Posting guidelines, forum rules and terms of use

    - Post your sheet using HTML tables - why?

    - Read the FAQs

    - Always use CODE tags when posting code, e.g. [CODE]insert code here[/CODE]

    - My Excel Blog

  8. #8
    Board Regular macleanb's Avatar
    Join Date
    Dec 2004
    Location
    London
    Posts
    714

    Default

    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 [range] twice as slow as cells:
    ttfn benm
    Ben Maclean - London
    Win2k / Excel 2002 SP3

    Why do people say "It goes without saying" or "With the greatest of respect"

  9. #9
    Board Regular Oorang's Avatar
    Join Date
    Mar 2005
    Posts
    2,071

    Default

    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
    • Get better answers! Include your version of Office in your post.

  10. #10
    MrExcel MVP
    Moderator

    Jon von der Heyden's Avatar
    Join Date
    Apr 2004
    Location
    Blackboys, East Sussex, UK
    Posts
    9,898

    Default

    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.
    Regards,
    Jon von der Heyden

    - Posting guidelines, forum rules and terms of use

    - Post your sheet using HTML tables - why?

    - Read the FAQs

    - Always use CODE tags when posting code, e.g. [CODE]insert code here[/CODE]

    - My Excel Blog

Page 1 of 7 123 ... LastLast

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
  •  


DMCA.com