Square Brackets - Vba Notation

Re: Square Brackets - Vba Notation

I can tell you at least one disadvantage - A1 will be A1 on the active sheet.

Now that may or may not be what you want but generally I would say it's better to reference worksheets explicitly in code.

Re: Square Brackets - Vba Notation

Anytime you don't qualify a range reference using whatever notation then VBA will use the active sheet.

I've lost count of the number of problems that have been solved by just correctly referencing things.

And doing that isn't hard, it could be as simple as adding the . dot qualifier.

And using With...End With can be handy too.

Re: Square Brackets - Vba Notation

You may want to read through this post for some additional insight.

Regards,

Re: Square Brackets - Vba Notation

You're welcome.

Re: Square Brackets - Vba Notation

Yes, activesheet is assumed for both bracket and Range notation. Maybe these examples will help. Press Ctrl+G to view the results in the Immediate window.

Sub t()


Worksheets("Sheet2").Activate


'Set A1=2, B1=A1+1=3.
Worksheets("Sheet1").[A1].Value = 2
Worksheets("Sheet1").[B1].ClearContents
Worksheets("Sheet1").[B1].Value = Worksheets("Sheet1").[A1].Value + 1
Debug.Print Worksheets("Sheet1").[B1] '3


Worksheets("Sheet1").Range("A1") = 4
Worksheets("Sheet1").Range("B1").ClearContents
Worksheets("Sheet1").Range("B1").Value = Worksheets("Sheet1").Range("A1").Value + 2
Debug.Print Worksheets("Sheet1").Range("B1")  '6


Worksheets("Sheet1").Activate
[A1] = 2
[B1].ClearContents
[B1] = [A1] + 5
Debug.Print [B1] '7


Range("A1") = 4
Range("B1").ClearContents
Range("B1") = Range("A1") + 10
Debug.Print Range("B1")  '14
'Immediate window should show: 3, 6, 7, 14


SumRangePlus100 [A1:B1]
Rem SumRangePlus100 (Range("A1:B1")) 'Error 424, Object req'd
Call SumRangePlus100(Range("A1:B1")) 'Call is needed if Range Object in ()'s
SumRangePlus100 Range("A1:B1")
End Sub


Sub SumRangePlus100(r As Range)
  Debug.Print "ShowA1Value=" & WorksheetFunction.Sum(r) + 100
End Sub

Re: Square Brackets - Vba Notation

[ ] represents Evaluate method

[A1] = Evaluate("A1")

so, you can write like

[Sheet1!A1]

and you could do something like this with evaluate method

Sub test()
With Range("a1:A10")
     .Value = [{1;2;3;4;5;6;7;8;9;10}]
     .Offset(,1).Value = [A1:A10+5]
     .Offset(,1).Resize(1).Value = Join([transpose(a1:a10)],",")
End With
End Sub

Re: Square Brackets - Vba Notation

I guess I would interpret []'s to mean both Range and Evaluate depending on use.

Using jindon's example, we can use more brackets.

Sub test2()
    With [a1:A10]
        .Value = [{1;2;3;4;5;6;7;8;9;10}]
        .Offset(, 1).Value = [A1:A10+5]
        .Offset(, 1).Resize(1).Value = Join([transpose(a1:a10)], ",")
    End With
End Sub