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]

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
  • 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 '14 at 14:10
  • Something for Sheets() ? – Makah Jan 28 '14 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. – Raystafarian Jan 28 '14 at 14:19
  • You can also assign a range to an array variable and vice-versa. – andy holaday Jan 29 '14 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
  • I did not know this and it's AWESOME +1 – Raystafarian Jan 29 '14 at 1:39
  • Uuuh, this is nice! – nixda Jan 29 '14 at 21:14

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.