4

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]

1

2 Answers 2

6

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
4
  • Thanks. For string concatenation I use my StringFormat() that works like C# style. github.com/makah/ExcelHelper/blob/master/Helper.bas - Sub comments in portuguese :-(
    – Makah
    Jan 28, 2014 at 14:10
  • Something for Sheets() ?
    – Makah
    Jan 28, 2014 at 14:11
  • @Makah sheets can be referred to as the sheet they are by default (Sheet#) rather than title, if you refer to the object. See my edit. Jan 28, 2014 at 14:19
  • You can also assign a range to an array variable and vice-versa. Jan 29, 2014 at 2:52
6

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
2
  • I did not know this and it's AWESOME +1 Jan 29, 2014 at 1:39
  • Uuuh, this is nice!
    – nixda
    Jan 29, 2014 at 21:14

You must log in to answer this question.

Not the answer you're looking for? Browse other questions tagged .