1. Welcome to Chandoo.org Forums. Short message for you

    Hi Guest,

    Thanks for joining Chandoo.org forums. We are here to make you awesome in Excel. Before you post your first question, please read this short introduction guide. When posting or responding to questions please remember our values at Chandoo.org are: Humility, Passion, Fun, Awesomeness, Simplicity, Sharing Remember that we have people here for whom English is not there first language and we need to allow for this in our dealings.

    Yours,
    Chandoo
  2. Hi All

    Please note that at the Chandoo.org Forums there is Zero Tolerance to Spam

    Post Spam and you Will Be Deleted as a User

    Hui...

  3. When starting a new post, to receive a quicker and more targeted answer, Please include a sample file in the initial post.

VBA Range Discussion

Discussion in 'The Lounge' started by chirayu, May 11, 2018.

  1. chirayu

    chirayu Well-Known Member

    Messages:
    924
    Hi All,

    This is just a general discussion on the use of a range in VBA. Specifically with regards to Range("A1") / [A1]

    I was experimenting and realized that this: Range("A3:A10").Formula = "=A2+1"
    Is the same as this: [A3:A10] = "=A2+1"

    Does the same job. So I wondered why I see Range("A1") being used more often than [A1] in a lot of code online. Is there a specific reason behind it i.e. pros/cons etc.?
    ThrottleWorks likes this.
  2. Chihiro

    Chihiro Excel Ninja

    Messages:
    5,052
    Peter Bartholomew likes this.
  3. Lori

    Lori Active Member

    Messages:
    163
    Interesting topic. From object browser, Range is a property that returns a range object whereas Evaluate (or [.]) is a method that returns a variant. Because of this each has pros and cons, according to the context in which they are applied...

    1. Assignment. Since Range("a") is an object property it supports intellisense, and either needs to be assigned to a variable or placed as a function argument that accepts range objects. On the other hand [a] is a variant and can be assigned to a range object for intellisense but it can also be used as a single command without any assignment.

    2. Scope. For both Evaluate and Range, if used outside a sheet module it's recommended practice to prefix by the codename of the sheet Sheet1.[a] or Sheet1.Range("a") which avoids any dependency on active sheet - though for testing it's often convenient to drop this prefix.

    3. Item. With a range object we get implicit indexing - since item is the default property. With variant we do not get any default property and need to convert to range. So for the first cell in Name we use either of:

    Range("a")(1)
    [a].Cells(1)

    4. Error handling. Range("a") raises an unhandled exception if "a" is not a valid range. On the other hand [a] does not throw but instead returns a variant error that can be tested for with IsObject([a]) avoiding the need for On Error handling.

    5. Expressions. Both methods support expressions that return range objects like:

    [Index(A:A,B1)]
    Range("Index(A:A,B1)")

    Evaluate is the more flexible as it also allows expressions like [+a] that return values or arrays.

    6. User defined functions. If a user defined function is shared with a defined name then Evaluate("a") and Range("a") return the name whereas [a] returns the result of the udf. For example if a=Sheet1!$A$1 and

    Code (vb):
    Function a()
    Set a=Range("a2")
    End Function
    then [a] returns A2 whereas Evaluate("a") and Range("a") return A1.

    This is one reason to avoid the square bracket notation as you might have the same name in a function that is part of an add-in which gets loaded at start up. However, i don't know if this behaviour is fully consistent?
  4. Peter Bartholomew

    Peter Bartholomew Well-Known Member

    Messages:
    446
    At least I am consistent; I have never used a direct reference as a parameter within Range or Evaluate. Points 6 and 3 are particularly interesting and not that obvious.

    I am not convinced about Point 2 as shown. If "a" is a globally-scoped name then it will reference the same range regardless of the sheet prefix? If it represents a sequence of locally-scoped names then I presume the syntax is Range("Sheet1!a") where the sheet specifies the scope of the name and not the range; not that I can remember ever doing that!
    NARAYANK991 likes this.
  5. Lori

    Lori Active Member

    Messages:
    163
    Indeed, I firmly agree one should always use names in sheet references in anything other than throwaway code. Aside from the lack of meaning of cell references, all it takes is for a row/column to be added/deleted to break the code.

    For the other point, using workbook names like Range("a") in VBA code without any scope means code is dependent on the workbook being active during runtime; if there is another workbook open and in the foreground, the code will look for the name "a" in that workbook instead.

    For example suppose you need to write a UDF that evaluates an expression. If the code is going to function the same no matter which workbook is active when recalculation occurs, you would need to use something along the lines of:

    Function Eval(Formula)
    Eval = Application.ThisCell.Worksheet.Evaluate(Formula)
    End Function

    While I have seen the sheet prefix applied in larger development projects, online sources nearly always overlook it in my experience.
    NARAYANK991 likes this.
  6. Peter Bartholomew

    Peter Bartholomew Well-Known Member

    Messages:
    446
    Lori

    Guilty as charged! The overwhelming majority of my workbooks are used in isolation (occasionally the VBA is in a separate workbook so ThisWorkbook and ActiveWorkbook are needed) so my view tends to be somewhat blinkered.

    I might not mend my ways entirely but to be aware of the risks and limitations is important, so a thank you is in order!

    Meanwhile, please keep an eye on the discussion
    https://chandoo.org/forum/threads/t...spreadsheet-design-discuss.38885/#post-232474
    and chip in when you feel the time is right.

Share This Page