Best/fastest way to reference a cell: .cell vs .range

  • Hey gurus,


    I found this: VBA Golden Rules #19: Use Named Ranges Over Cell Addresses. here: http://www.ozgrid.com/forum/showthread.php?t=76234


    I'm guessing that means the .cells function is a subroutine of the .range function. Therefore


    Code
    dim iRow as integer 'row indexiRow = 3with mySheet  .Range("B" & iRow)end with 'mySheet


    should be faster than


    Code
    dim iRow as integer 'row indexiRow = 3with mySheet  .cells(iRow , 2)end with


    The string operator ("B" & iRow) to get the named range function seems like it would take longer than using the numerical references in .cells().


    Thanks!
    Nate

  • Re: Best/fastest way to reference a cell: .cell vs .range


    Named ranges *DOES NOT* refer to the range method per se. Cells and ranges are both objects. Cells are not subroutines of ranges but methods.


    The Named Ranges referenced in the rule are just that - - named ranges, as with Range("MyRange") which has been defined either in the worksheet/workbook or using VBA to create it.


    Range Object - Object Model


    Chip Pearson - Optimizing VBA


    Cells Within Ranges

  • Re: Best/fastest way to reference a cell: .cell vs .range


    Quote from AAE;633876


    Found a line in the Object Model:

    Quote


    Although you could use Visual Basic string functions to alter A1-style references, it's much easier (and much better programming practice) to use the Cells(1, 1) notation.


    Thank you for the links in your reply. This is what I needed to know.


    Cheers!
    Nate

Participate now!

Don’t have an account yet? Register yourself now and be a part of our community!