microsoft excel - VBA Syntactic Sugar

I use VBA in my work every day, but I didn't know about Range syntactic sugar. I just knew last month when I saw MSDN Range page. I tried to find an article with this features, but I didn’t find any.

Does someone know if VBA has more syntactic sugar than this

Range:Range("A4:C100") to [A4:C100]

or

Range: Range("MY_DATE") to [MY_DATE]

Answer

For ranges

These all do the same thing:

Range(Cells(1, 2), Cells(2, 2)).Select

Range("B1:B2").Select

Dim rngB As Range
Set rngB = Range("B1:B2")
rngB.Select

[B1:B2].Select

---

For strings

You don't have many options

Dim strA As String
strA = "hello"

strA = strA + "world" and strA = strA & "world" do the same thing (ampersand is preferred)

strA &= "world" and strA += "world" don't work.

---

For worksheets

You can usually work with the actual default sheet with number, rather than what the sheet is named:

Worksheets("Name").Activate
Sheets("Name").Activate
'Worksheet "Name" is Sheet1 object
Sheet1.Activate

---

For worksheet formulas/functions

From Excellll's Answer for completeness:

Another syntactic shortcut is for accessing and evaluating worksheet functions. You can use brackets to evaluate a formula as if it were on the worksheet rather than stringing together a cumbersome VBA statement.

Sub sugartest()
'long version
MsgBox Application.WorksheetFunction.Average(ActiveSheet.Range("A1:D1"))
'short version
MsgBox [AVERAGE(A1:D1)]
End Sub