Take the 2-minute tour ×
Super User is a question and answer site for computer enthusiasts and power users. It's 100% free, no registration required.

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

2 Answers 2

up vote 4 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 at 14:10
    
Something for Sheets() ? –  Makah Jan 28 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 at 14:19
    
You can also assign a range to an array variable and vice-versa. –  andy holaday Jan 29 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 at 1:39
    
Uuuh, this is nice! –  nixda Jan 29 at 21:14

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

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