Go Back  Xtreme Visual Basic Talk > Legacy Visual Basic (VB 4/5/6) > VBA / Office Integration > Excel > Excel VBA Cells() vs. Range()


Reply
 
Thread Tools Display Modes
  #1  
Old 08-26-2009, 12:34 AM
Josh Hazel Josh Hazel is offline
Senior Contributor
 
Join Date: May 2008
Posts: 805
Default Excel VBA Cells() vs. Range()


Ive noticed most people reference ranges in vba using Cells(x,x) rather than Ranga(xx) ... is one faster than the other, because it seems Range() is much easier to read.
__________________
Josh

If Google = NoHelp Then PostHere = True
Reply With Quote
  #2  
Old 08-26-2009, 03:15 AM
Burningslash12's Avatar
Burningslash12 Burningslash12 is offline
Junior Contributor
 
Join Date: Sep 2008
Location: Singapore
Posts: 303
Default

Hi! Personally i use Cells(row, col) because i find it easier to use inside a loop.

I can easily loop the Row, Column or Both by using variables. For me it is more a convenience...
__________________
Burningslash12
"We may rise and fall, but in the end, we'll meet our fate together..."

Don't Click Me!
Reply With Quote
  #3  
Old 08-26-2009, 03:54 AM
Colin Legg's Avatar
Colin Legg Colin Legg is offline
Out Of Office

Retired Moderator
* Expert *
 
Join Date: Mar 2005
Location: London, UK
Posts: 3,399
Default

There is some overlap where they can be used interchangeably, but they have a number of differences. I'll get the ball rolling with a few that immediately spring to mind:
  • Worksheet.Cells() will only ever return a reference to a single cell on the worksheet or all cells on the worksheet. For example, you can't reference A1:A10 using Worksheet.Cells() on its own, but you can using Worksheet.Range()
  • You don't get intellisense when using Worksheet.Cells() if you pass in a RowIndex or ColumnIndex.
  • Worksheet.Cells() is particularly useful when you need to reference the ColumnIndex using a number, such as in a loop like BurningSlash mentioned.

So I use both - which one I choose depends on the situation. If a situation arises where either could be used with equal ease then I tend to use Worksheet.Range() because of the intellisense benefit.
__________________
RAD Excel Blog
Reply With Quote
  #4  
Old 08-26-2009, 04:27 AM
kassyopeia kassyopeia is offline
Junior Contributor
 
Join Date: Mar 2009
Posts: 301
Default

Quote:
I'll get the ball rolling
I'll keep it rolling with a few tidbits:
  • Both .Cells() and .Range() return a Range-class object. The object comes in three "flavours", Cells, Rows, and Columns. We discussed that in depth here. It seems that .Range() always returns the Cells flavour (Excel-2002-sized Sheet):
    Code:
    Sheet1.Range("1:1").Count '256
    Sheet1.Range("A:A").Count '65536
    However, I'd say it's good practice to use .Cells() in situations in which the flavour matters, rather than rely on this non-obvious fact.
  • When I loop over ranges, I usually do it using .Rows() and .Columns() as needed, rather than using .Cells(). Sample:
    Code:
        Dim rangeRow As Range
        For Each rangeRow In Me.Range("3:5").Rows
            rangeRow.Columns(2).Value = "B"
            rangeRow.Columns(4).Value = "D"
        Next
  • The intellisense issue is not specific to .Cells(), it's a general VBIDE bug related to multiple indeces:
    Code:
    Private someArray(1 To 2, 1 To 2) As Range
        
    someArray.        'intellisense
    someArray(1,1).   'no intellisense
    Weird.
Reply With Quote
  #5  
Old 08-27-2009, 03:22 PM
tken22 tken22 is offline
Regular
 
Join Date: Feb 2009
Posts: 54
Default

Lets take it a step further.

Like this:

Standard
Quote:
Range("A1:B3").
Embedded
Quote:
Range(Cells(1,1), Cells(3,2)).
Ultimately you get the same results, but as burning stated above loops function smoother with solid numbers. You don't have to have to Asc(Column Letter-64) each time.
Reply With Quote
  #6  
Old 08-27-2009, 03:37 PM
Colin Legg's Avatar
Colin Legg Colin Legg is offline
Out Of Office

Retired Moderator
* Expert *
 
Join Date: Mar 2005
Location: London, UK
Posts: 3,399
Default

One thing we haven't touched yet is when Cells() and Range() are being used as properties of a range object. So far we've only considered Worksheet.Range() and Worksheet.Cells(), which we have shown to have distinct differences. Since Josh didn't state either way, I think this remains on topic.

I find Range.Cells() useful whilst I find Range.Range() nothing short of confusing. ie. Which cell does this refer to?
Code:
Debug.Print Range("E23").Range("D4").Address
I don't think it's particularly obvious. Does anyone use Range.Range()?!
Of course, in this situation Offset() also comes into the equation, but I think that's best left for another thread.
__________________
RAD Excel Blog

Last edited by Colin Legg; 08-27-2009 at 03:46 PM.
Reply With Quote
  #7  
Old 08-27-2009, 07:33 PM
Josh Hazel Josh Hazel is offline
Senior Contributor
 
Join Date: May 2008
Posts: 805
Default

I appreciate all the feedback and will take these points under advisement in future coding. I can see the primary advantage beging the looping of Columns.
__________________
Josh

If Google = NoHelp Then PostHere = True
Reply With Quote
  #8  
Old 08-28-2009, 02:23 AM
kassyopeia kassyopeia is offline
Junior Contributor
 
Join Date: Mar 2009
Posts: 301
Default

Quote:
Does anyone use Range.Range()?!
One might be tempted to use the (Cell1 As Range, Cell2 As Range) parameter version, but one really shouldn't, as the effects are quite insidious.
Code:
    With Range("B2:E5")
        .Value = "abc"
        
        Range(Cells(2, 2), Cells(3, 3)).Font.Bold = True               'refers to B2:C3
        .Range(Cells(2, 2), Cells(3, 3)).Font.ColorIndex = 3           'refers to C3:D4
        Range(.Cells(2, 2), .Cells(3, 3)).Font.Italic = True           'refers to C3:D4
        .Range(.Cells(2, 2), .Cells(3, 3)).Font.Strikethrough = True   'refers to D4:E5 (!)
    
    End With
As you mentioned, Excel gives us .Offset() and .Resize(), and I'll usually use those for creating sub-Ranges.
Reply With Quote
  #9  
Old 09-09-2009, 09:11 AM
Colin Legg's Avatar
Colin Legg Colin Legg is offline
Out Of Office

Retired Moderator
* Expert *
 
Join Date: Mar 2005
Location: London, UK
Posts: 3,399
Default

Quote:
Originally Posted by kassyopeia View Post
I'll keep it rolling with a few tidbits:
  • Both .Cells() and .Range() return a Range-class object. The object comes in three "flavours", Cells, Rows, and Columns. We discussed that in depth here. It seems that .Range() always returns the Cells flavour (Excel-2002-sized Sheet):
A late thought. A fourth flavour, Areas?
__________________
RAD Excel Blog
Reply With Quote
  #10  
Old 09-09-2009, 09:55 AM
kassyopeia kassyopeia is offline
Junior Contributor
 
Join Date: Mar 2009
Posts: 301
Default

Areas seem to be implemented differently:
Code:
Dim r As Range: Set r = Excel.Application.Union(Sheet1.Range("B2:C3"), Sheet1.Range("E5:F6"))
    
Debug.Print TypeName(r.Cells)   'Range
Debug.Print TypeName(r.Rows)    'Range
Debug.Print TypeName(r.Columns) 'Range
Debug.Print TypeName(r.Areas)   'Areas
Quote:
Areas Collection

A collection of the areas, or contiguous blocks of cells, within a selection. There’s no singular Area object; individual members of the Areas collection are Range objects. The Areas collection contains one Range object for each discrete, contiguous range of cells within the selection. If the selection contains only one area, the Areas collection contains a single Range object that corresponds to that selection.
Reply With Quote
  #11  
Old 09-09-2009, 12:16 PM
Colin Legg's Avatar
Colin Legg Colin Legg is offline
Out Of Office

Retired Moderator
* Expert *
 
Join Date: Mar 2005
Location: London, UK
Posts: 3,399
Default

Ah, of course you're right. My bad.
__________________
RAD Excel Blog
Reply With Quote
  #12  
Old 09-09-2009, 01:40 PM
kassyopeia kassyopeia is offline
Junior Contributor
 
Join Date: Mar 2009
Posts: 301
Default

Quote:
My bad.
Not at all, thanks for bringing it up. I don't remember considering discontiguous ranges in this respect before, so I learned something new by checking on your thought.
Reply With Quote
Reply


Currently Active Users Viewing This Thread: 1 (0 members and 1 guests)
 
Thread Tools
Display Modes

Posting Rules
You may not post new threads
You may not post replies
You may not post attachments
You may not edit your posts

BB code is On
Smilies are On
[IMG] code is Off
HTML code is Off

Forum Jump

Advertisement:





Free Publications
The ASP.NET 2.0 Anthology
101 Essential Tips, Tricks & Hacks - Free 156 Page Preview. Learn the most practical features and best approaches for ASP.NET.
subscribe
Programmers Heaven C# School Book -Free 338 Page eBook
The Programmers Heaven C# School book covers the .NET framework and the C# language.
subscribe
Build Your Own ASP.NET 3.5 Web Site Using C# & VB, 3rd Edition - Free 219 Page Preview!
This comprehensive step-by-step guide will help get your database-driven ASP.NET web site up and running in no time..
subscribe
 
 
-->