Hi All,
I often see square brackets around ranges in code. eg [A1]
Can anyone tell me if there are any advantages/disadvantages with using this notation, apart from the obvious its shorter?
Cheers.
Hi All,
I often see square brackets around ranges in code. eg [A1]
Can anyone tell me if there are any advantages/disadvantages with using this notation, apart from the obvious its shorter?
Cheers.
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
Hi Norie,
Thanks for the reply.
So you are saying if you dont use worksheet("ShtNm") in front of it then it will select the activesheet. Wont doing that with range do the same thing?
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
Thanks Norie.
Re: Square Brackets - Vba Notation
Thanks Brandtrock,
Very interesting.
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
Display More
Re: Square Brackets - Vba Notation
Thanks Kenneth
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
Re: Square Brackets - Vba Notation
Making more and more sense.
I was confused at the start because I thought the brackets were just a shortcut for writing Range. It actually means evaluate.
Thanks Jindon
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.
Don’t have an account yet? Register yourself now and be a part of our community!