Super User is a question and answer site for computer enthusiasts and power users. Join them; it only takes a minute:

Sign up
Here's how it works:
  1. Anybody can ask a question
  2. Anybody can answer
  3. The best answers are voted up and rise to the top

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]

share|improve this question
    
You might find this to be worthwhile reading: stackoverflow.com/questions/1070863/hidden-features-of-vba – Pillgram Mar 1 '15 at 17:08
up vote 5 down vote accepted

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

Commenting your code

The best way to make the code look better is to use comments to describe how it works rather than what it does:

Sub Sweeter()

'Brackets are used as a syntax replacement for calling the range object
[B1:B2].Select

End Sub

and

Sub Sweetest()

Dim strA As String

'To append to a string, the ampersand (&) can be used for concatenation
strA = Range("A1") & " " & "World"
MsgBox (strA)

End Sub

not

Sub Sour()

'Select range
[B1:B2].Select

End Sub

or

Sub Sourest()

Dim strA As String

'Add a word to the string in A1
strA = Range("A1") & " " & "World"
MsgBox (strA)

End Sub
share|improve this answer
    
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

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
share|improve this answer
    
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

You must log in to answer this question.

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