Understanding VBA language elements, including variables, data types, constants, and arrays
Using VBA built-in functions
Manipulating objects and collections
Controlling the execution of your procedures
VBA Language Elements: An Overview
If you've used other programming languages, much of the information in this chapter may sound familiar. However, VBA has a few unique wrinkles, so even experienced programmers may find some new information.
This chapter explores the VBA language elements, which are the keywords and control structures that you use to write VBA routines.
To get the ball rolling, take a look at the following VBA Sub procedure. This simple procedure, 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 Long, i As Long 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 the following:
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)
You will explore all of these language elements 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 isn't 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 x + y + z End Sub
Most programmers agree that code is easier to read if you use one instruction per line.
Sub MultipleLines() x = 1 y = 2 z = 3 MsgBox x + 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. However, reading very long lines of code while having to scroll is not a particularly pleasant. For lengthy lines, it's considered a best practice to use VBA's line continuation sequence: a space followed by an underscore (_). Here's an example:
Sub LongLine() SummedValue = _ Worksheets("Sheet1").Range("A1").Value + _ Worksheets("Sheet2").Range("A1").Value End Sub
When you record macros, Excel often uses the line continuation sequence to break a long statement 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 the following:
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 the following:
Result = ActiveSheet.Range("a1").Value = 12
Notice that text within quotation marks (in this case, "a1") isn't changed.
Because VBA variable names aren't case-sensitive, the VBE, 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), the 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. In the Visual Basic Editor, choose the 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).
Comments
A comment is descriptive text embedded in your code and ignored by VBA. It's a good idea to use comments liberally to describe what you're doing because an instruction's purpose isn't always obvious.
You can use a complete line for your comment, or you can insert a comment after an instruction on the same line. A comment is indicated by an apostrophe. VBA ignores any text that follows an apostrophe—except when the apostrophe is contained within quotation marks—up until the end of the line. For example, the following statement doesn't contain a comment, even though it has an apostrophe:
Msg = "Can't continue"
The following example shows a VBA procedure with three comments:
Sub CommentDemo() ' This procedure does nothing of value x = 0 'x represents nothingness ' Display the result MsgBox x End Sub
Although the apostrophe is the preferred comment indicator, you can also use the Rem keyword to mark a line as a comment. Here's an example:
Rem -- The next statement prompts the user for a filename
The Rem keyword (short for Remark) is essentially a holdover from older versions of BASIC, and it is included in VBA for the sake of compatibility. Unlike the apostrophe, Rem can be written only at the beginning of a line, not on the same line as another instruction.
The following are a few general tips on making the best use of comments:
Use comments to describe briefly the purpose of each procedure that you write.
Use comments to describe changes you make to a procedure.
Use comments to indicate you're using functions or constructs in an
