VBA Language Elements: An Overview | Excel 2007 Power Programming with VBA (Mr. Spreadsheets Bookshelf)

In Chapter 7, I present an overview of objects, properties, and methods , but I don't tell you much about how to manipulate objects so that they do meaningful things. This chapter gently nudges you in that direction by exploring the VBA language elements, which are the keywords and control structures that you use to write VBA routines.

To get the ball rolling, I start by presenting a simple VBA Sub procedure. The following code, which is stored in a VBA module, calculates the sum of the first 100 positive integers. When the code finishes executing, the procedure displays a message with the result.

 Sub VBA_Demo() '   This is a simple VBA Example     Dim Total As Integer, i As Integer     Total = 0     For i = 1 To 100         Total = Total + i     Next i     MsgBox Total End Sub

This procedure uses some common VBA language elements, including:

A comment (the line that begins with an apostrophe)
A variable declaration statement (the line that begins with Dim )
Two variables ( Total and i )
Two assignment statements ( Total = 0 and Total = Total + i )
A looping structure ( For-Next )
A VBA function ( MsgBox )

All these language elements are discussed in subsequent sections of this chapter.

Note	VBA procedures need not manipulate any objects. The preceding procedure, for example, doesn't do anything with objects. It simply works with numbers .

Entering VBA Code

VBA code, which resides in a VBA module, consists of instructions. The accepted practice is to use one instruction per line. This standard is not a requirement, however; you can use a colon to separate multiple instructions on a single line. The following example combines four instructions on one line:

 Sub OneLine()     x= 1: y= 2: z= 3: MsgBox  + y + z End Sub

Most programmers agree that code is easier to read if you use one instruction per line:

 Sub OneLine()     x = 1     y = 2     z = 3     MsgBox  + y + z End Sub

Each line can be as long as you like; the VBA module window scrolls to the left when you reach the right side. For lengthy lines, you may want to use VBA's line continuation sequence: a space followed by an underscore (_).For example:

 Sub LongLine()     SummedValue = _       Worksheets("Sheet1").Range("A1").Value + _       Worksheets("Sheet2").Range("A1").Value End Sub

When you record macros, Excel often uses underscores to break long statements into multiple lines.

After you enter an instruction, VBA performs the following actions to improve readability:

It inserts spaces between operators. If you enter Ans=1+2 (without spaces), for example, VBA converts it to
```
 Ans = 1 + 2 
```
It adjusts the case of the letters for keywords, properties, and methods. If you enter the following text: Result=activesheet.range("a1").value=12

VBA converts it to
```
 Result = ActiveSheet.Range("a1").Value = 12 
```
Notice that text within quotation marks (in this case, "a1" ) is not changed.
Because VBA variable names are not case-sensitive, the interpreter by default adjusts the names of all variables with the same letters so that their case matches the case of letters that you most recently typed . For example, if you first specify a variable as myvalue (all lowercase) and then enter the variable as MyValue (mixed case), VBA changes all other occurrences of the variable to MyValue . An exception occurs if you declare the variable with Dim or a similar statement; in this case, the variable name always appears as it was declared.
VBA scans the instruction for syntax errors . If VBA finds an error, it changes the color of the line and might display a message describing the problem. Choose the Visual Basic Editor Tools Options command to display the Options dialog box, where you control the error color (use the Editor Format tab) and whether the error message is displayed (use the Auto Syntax Check option in the Editor tab).