Referring to Range() using shortcut notation

[MrKowz]

I have noticed in many of the Excel help files that the developers liked to use shortcut notation for Range reference in VBA.

For example, Range("A1:B10") would be [A1:B10].

I was doing a bit of testing with this, and was not able to make the shortcut notation work with a variable. Does the shortcut method have the capabilities to do the equivalent of Range("A1:B" & LR)?

If it does have that capability, is it just due to force of habit that we always use Range() to refer to ranges, or would there be a more in-depth reason.

 

[Richard Schollar]

The shortcut notation is actually a shortcut to the evaluate function:

Evaluate("A1:A10") = "SomeString"

'equivalent to:

[A1:A10] = "SomeString"

and as you have found, you can't build use variables inside the square brackets (you can inside of Evaluate(), but then you may as well write out Range() instead).

 

[MrKowz]

Then the programmers need to get their help files straight!

They have the following code in the "Referring to Cells by Using Shortcut Notation"

Sub ClearRange()
    Worksheets("Sheet1").[A1:B5].ClearContents
End Sub

along with the following description:

"You can use either the A1 reference style or a named range within brackets as a shortcut for the Range property. You don't have to type the word "Range" or use quotation marks, as shown in the following examples."

 

[Richard Schollar]

Well, what they have written isn't wrong, but it isn't the full picture either

 

[Kenneth Hobson]

Range is the method that I would recommend. It is easy to read and enables intellisense where other methods do not.

Sub test()
  Dim i As Integer
  i = 5
  Evaluate("A" & i) = i + 1
  Range("A" & i + 1) = i + 2
  Cells(i + 2, "A") = i + 3
  [A8] = i + 4
End Sub

 

[RoryA]

I don't particularly like the square brackets method, though I do use it on rare occasions if I'm feeling very lazy. I just don't see the benefit to it unless you really are writing a quick and dirty routine to be used once only. Plus you have to remember that this:

[A1,B5].value = 10

is equivalent to:

Range("A1,B5") = 10

and not:

Range("A1","B5") = 10

 

[Richard Schollar]

It does have its uses in the Immediate Window though - just means less typing

 

[RoryA]

Oh yeah - for quick and dirty, it's great. I would never put it in 'production' code though.

 

[Norie]

Keith

In 'real' code I would suggest you don't use the [] notation.

It is a bit lazy, will work but could also cause problems.

For example if you don't have a worksheet reference then the range referred to will be on the active sheet as VBA sees it.

PS I know you used a worksheet reference in post #3 but normally when I see this notation used it's missing.

 

[Kenneth Hobson]

To add to Rory's post earlier:

[A1.B5] = 10

or

[A1..B5] = 10

or your earlier example with a colon:

[A1:B5] = 10

is the same as:

Range("A1","B5") = 10

Of course bracket notation is not just limited to ranges. I used it for quick arrays too. I guess one can consider an array as a range in some cases.

Sub PutArrayIntoRange()
  Dim a() As Variant
  a() = [{2,3;6,9;4,5;-1.1,2.34}]
  Range("B8").Resize(UBound(a, 1), UBound(a, 2)) = a()
End Sub