Getting a range

How do you get a range in code?

If its a marker range (a single cell where I start my code from) I tend to name it at worksheet level then access it via

sheet1.[StartPoint].row/.column/.value etc

If I am looping through cells I use

.cells(r,c).value/.formula etc

I almost never use range(“A” & r) as it has a (part) fixed text cell reference that often ends in tears if someone modifies the sheet.

Bob P was outraged that I used [StartPoint] instead of Range(“StartPoint”), he now thinks I am a total cowboy. What do you think? if you don’t like [], why not? I was going to test the performance but its completely irrelevant as I only access these once.

I wish worksheet level defined names popped up in intellisense as a worksheet property, like public code thats added to a sheet class. This [] approach seems like a reasonable compromise to me. Obviously I’m not going to be wasting time wrapping a cell ref in a property (or variable) just to make intellisense do what I want. (Or should I?)

Whats your preference?



17 Responses to “Getting a range”

  1. Rick Williams Says:

    I like [], paticularly for short references – I loath to type Range(“a1”).value when I can just use [a1].value, something related to having to use the shift key for the quotes and parentheses. The only time I use the Range() syntax is when I wish to use strings as cell references.
    My most common approaches are to use either a named ranges for the table, etc I am referencing:
    -> (DataTable=C1:G450)
    -> [DataTable].cells(i,j)
    and recently have discovered the usefulness of dynamic named ranges for this purpose (see …
    or to use:

    Is there an advantage to using cells(r,c) over offset(r,c)?

  2. jonpeltier Says:

    I don’t use the square brackets except within the Immediate Window. It’s one of those lazy shortcuts (no offense, guys) that can come back to bite you in the backside.

    Aside from that, the use of a name really makes referencing easy and reliable. Dynamic names add flexibility. To prevent user interference, I often use hidden names.

  3. Johan Nordberg Says:

    I never use []. Don’t really know why, but I guess it’s the same reason I don’t use Dim v$ to define a string instead of Dim v As String.

    I always use named ranges when I know in design time what cells to use. Otherwise I use Cells(), for rendering a big sheet from a db query for instance.

    I’ve never really understood why I need to qualify a reference to a named range with a sheet reference since the name is workbook global (unless defined local).

    For bigger projects I often create a module called Globals and have properties for the named ranges I use alot. I have a wrapper to work with named ranges that doesn’t really care where the named ranges is located or if it’s a reference to a cell or a formula for constant values, like =20.

    It’s a little more code, but I get intellisense, it’s really easy for me to change the worksheets, move things around, have a named range visible or hidden, global or local, switching to use XML mapped cells instead of named ranges, etc, etc without changing any code other than the Globals module. It’s more separation between presentation and logic.

    Also, this is quite similar to how it’s done in VSTO so the transition between VBA and VB.Net is less scary.

    I think the Range method is great to create a region, like Range(Cells(1,1), Cells(10,10) would create Range(“A1:J10”) without the pain of concatenating strings…

    When creating Word templates and solutions I think it’s even more important with the Globals module, since there can be so many ways to persist information in the document. Yoiu have bookmarks (like named ranges), document variables, document properties, form field, xml nodes and content controls. Not having to care how the data is persisted makes it easier to write code and easier to chane the presentation without breaking the code.

    // Johan

  4. Marcus Says:

    A slightly different take; I try to reference ranges as little as possible especially looping through large ranges.

    I’ll typically pass the contents to an array (1 line of code), process the array and then pass the array back to the range (another 1 line of code) or persist the data elsewhere (database, text file).

    But I never use [StartPoint] and where possible named ranges – this is regularly not possible when dealing with foreign files (CSV extracts etc.).

    Regards – Marcus

  5. jonpeltier Says:

    I agree with Johan: I don’t use the suffixes for declaring variable types. Probably because the only one I remember reliably is $ for string. For readability I find it’s better to spell out the type, also since MS has said for a long time that the practice is obsolete.

    I don’t agree about referencing names and ranges, however. If it’s locally defined, the name should be referenced by sheet; if it’s globally defined, it should be referenced by workbook. I do so much work with multiple workbooks open (settings are saved in one workbook, user data in another, with customers on one sheet and products on another). Referencing makes the code both reliable and readable.

    The globals module is a good approach. I haven’t developed it as well as I should. The objective should be a Globals module that has most of what each project needs and little that it doesn’t, so it can be dropped in and implemented right away. Typically now I go to a previous project and pull out its module, then scurry around to find the other bits I need. One day, but not today, I’ll just export the stupid module once and for all. I guess this is one place where managed code would show its superiority.

  6. Charlie Hall Says:

    I don’t use [] for referencing range names, and am not sure why – I guess early in my coding days there appeared to be a performance difference, but I agree if it is done infrequently, then it shouldn’t be a problem. However, using Range() or Names() is certainly more obvious. I think if I ran into brackets in someone else’s code, I would have to remind myself what [] do and don’t do.

    I do use the shortcut suffixes (only int, long, string and double) – I guess I am lazy – and I wish intellisense would get to the right one with less key strokes. Since the variable has (in my case) a suffix in the name to indicate what type the varible is (eg. count_i, name_s), typing in the “type” seems linke double entry.

    I would love to know more about Johan’s global module techniques – sounds useful – Johan, could you share some code?

    Great topic

  7. Biggus Dickus Says:

    I use range(“xxx”) rather than [] but for no particular reason I guess.
    I do find it useful when I need “dynamic” ranges as mentioned in a earlier message (i.e. Range(“Rangename” & x).value ). I guess since Range() can be used in every circumstance I like to use it – because it’s consistent throughout all my code.


  8. Chris Says:

    I don’t like the [] stuff either. However, I didn’t realise that you could do Sheet1.[myname] – I thought you could only do [myname] and then you have this nasty business with active worksheets.

    I’ve also always avoided Range(“mynamedrange”) and instead done Workbook.Names(“mynamedrange”).RefersToRange. Not exactly sure why that was either now.

    The very fact that you suggested using Range(“A” & n) has significantly boosted your cowboy rating in my eyes..


  9. Biggus Dickus Says:

    “The very fact that you suggested using Range(”A” & n) has significantly boosted your cowboy rating in my eyes..”

    Thanx (I think) …. Yahooo !!! (that’s what cowboys say)

    I have found times where it is necessary to have sequentially numbered ranges (or I have inherited them) and I just cycle through them this way
    Another thing I use is Range(“‘” & Thisworkbook.Name &”‘!RangeName”).value so I can refer back to the calling workook regardless of whether the file name has a space or spaces in it.
    Surely there are other ways to do this, but again it keeps my code consistent by using Range() which makes it easier for me to debug and to write (it’s always faster to do things certain ways so I don’t argue with myself on how to do things (?) )
    …but I could be wrong ;-)…


  10. Harlan Grove Says:

    I’ve been told [A1] is slower than Range(“A1”) because (and Excel VBA help does state this) [A1] is treated as shorthand for Evaluate(“A1”). I figure Evaluate involves more error checking than Range because all Range has to do is determine whether its argument is a valid range address or defined name evaluating to a range. Similar to the performance penalty from assigning objects to Variants rather than variables of the specific class.

    There’s also a syntactic difference. The token between the square brackets is effectively a string constant, so it’s static. OTOH, Range can accept any string EXPRESSION that evaluates to a valid range reference.

    As for myself, I’m pigheadedly explicit. I use defined names for interfaces to VBA code, and I set range class variables as

    Set rng = workbookref.Names(nameref).RefersToRange

    I try to avoid unqualified use of Range, and I never use [..] any more. Even using the shortcuts it provides, VBA isn’t a terse language, so I go to the opposite extreme – logorrheic coding.

  11. Simon Says:

    Yee-hah is the cowboy shout that comes to my mind.
    Chris – I’ve never used range(“A”&..) – I thought I saw that in your code actually ;-)
    I have certainly seen plenty of code like that.

    I don’t use all that dim s$, n# stuff, as like most people its too hard to remember, and its too cryptic for anyone under 35 who didn’t have the misfortune to work with whatever version of VB made you do it that way. (me neither BTW)

    Maybe I should do more like Johan and wrap them, but that seems like gold plating when I will use them exactly once (usually) to set up a range variable. I guess I could afford a few extra keystrokes, and a bit more RSI, to type range(“…”).

    Rick I use .cells manly as its easier to decipher when debugging, if you use offset you then have to add the start point coordinates to your variables. No idea on performance.

  12. Biggus Dickus Says:

    “Yee-hah is the cowboy shout that comes to my mind.”

    Canadian cowboys say Yahooo (I guess)…..

  13. jonpeltier Says:


    Range(”‘” & Thisworkbook.Name &”‘!RangeName”)

    Isn’t that the same as


    Of course, you should use RefersToRange.Value, not just .Value.

    Simon: Those of us over 45 who had the misfortune to use the $-etc suffixes are hindered by not being able to remember.

  14. Biggus Dickus Says:

    “Range(”‘” & Thisworkbook.Name &”‘!RangeName”)

    Isn’t that the same as


    Yes – I use it because it’s consistent with my other usage of range() ….. Habit I guess……


  15. Charles Williams Says:

    [… ] is a shortcut for Application.Evaluate(…) and so suffers from the Evaluate limitations.
    Probably best to avoid it for that reason, altho most of the limitations dont apply to evaluating range names.

  16. Dominik Says:

    Simon (and others),

    if you use the Name Manager Add-in, there is a dropdown added to the VBE with all the names used in the worksheet. Adding the name in your code is just a click away…


  17. AlexJ Says:

    Although this thread is now somewhat dormant, I just found it. I’ll add my two cents:
    1. I use [rangename] to refer to named ranges only, usually ones that hold constants or other goodies
    2. Biggus Dickus: Everyone knows that us Canadian cowboys just say “Sorry”


Leave a Reply

Please log in using one of these methods to post your comment: Logo

You are commenting using your account. Log Out /  Change )

Google photo

You are commenting using your Google account. Log Out /  Change )

Twitter picture

You are commenting using your Twitter account. Log Out /  Change )

Facebook photo

You are commenting using your Facebook account. Log Out /  Change )

Connecting to %s

This site uses Akismet to reduce spam. Learn how your comment data is processed.

%d bloggers like this: