In Chapter 7, I present an overview of objects, properties, and methods. But I didn'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'll 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 integers. When the code finishes executing, the procedure displays a message with the result.
This is a simple VBA Example
Dim Total As Integer, i As Integer
MsgBox Total End Sub
This procedure uses some common VBA language elements, including a comment (the line preceded by the apostrophe), two variables (Total and i), two assignment statements (Total = 0 and Total = Total + i), a looping structure (For-Next), and a VBA statement (MsgBox). All these are discussed in subsequent sections of this chapter.
VBA procedures need not manipulate any objects.The preceding procedure, for example, doesn't do anything with objects. It simply works with numbers.
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:
Most programmers agree that code is easier to read if you use one instruction per line:
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: an underscore (_) preceded by a space. For example:
SummedValue = _
Worksheets("Sheet1").Range("A1").Value + _ Worksheets("Sheet2").Range("A1").Value
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
♦ VBA adjusts the case of the letters for keywords, properties, and methods. If you enter the following text:
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).
Was this article helpful?