|< Day Day Up >|
VBA code is organized into statements. A statement is a single line of code, for example:
intCount = 6
This statement sets the value of the intCount variable to 6. Although you can execute individual statements in the Immediate window, you can't store individual statements in a VBA module. Within a module, statements must be organized into procedures: groups of statements that perform a task together. Although you've already seen a few procedures in the brief bits of code demonstrated in earlier chapters, this chapter gives you a much more in-depth look at creating and using procedures.
To start, you need to understand the difference between two types of procedures:
There are also two specialized types of procedures that arent covered in this chapter: property procedures and event procedures. For information on these types of procedures, see "Reading and Setting Properties," p. 116 and "Working with Events," p. 126.
Creating and Using Sub Procedures
A sub procedure is a procedure that does not return a value (you learn more about returning values in the next section of this chapter). Here's a very simple sub procedure:
Sub Procedure1() ' Print a message to the immediate window Debug.Print "Hello" End Sub
A sub procedure starts with the keyword Sub, followed by the name of the procedure (in this case, Procedure1) and an empty pair of parentheses.
The sub procedure ends with the keywords End Sub. In between, you can have as many statements as you like. In Procedure1, there's a comment followed by a Debug.Print statement (recall that Debug.Print prints whatever follows on the line to the Immediate window).
You can run a sub procedure from the Immediate window simply by typing the name of the procedure and pressing Enter, as shown in Figure 4.1.
Figure 4.1. Running a sub procedure from the Immediate window.
Procedures can contain more than one statement. In this case, VBA executes each statement in turn, starting at the top of the procedure. For example, consider this sub procedure:
Sub Procedure2() ' Add two numbers together Dim intFirst As Integer Dim intSecond As Integer intFirst = 4 intSecond = 7 Debug.Print intFirst + intSecond End Sub
If you run this procedure from the Immediate window, you'll see that it prints 11, the sum of the two numbers.
Procedures wouldn't be much use if they could only be run from the Immediate window, though. Fortunately, there are two other ways to run a procedure:
To run a sub procedure from another procedure, you include the name of the called procedure as a statement within the calling procedure, as in this example of two procedures:
Sub Procedure3() ' Display execution of another procedure Debug.Print "In Procedure3" Procedure4 Debug.Print "Back in procedure3" End Sub Sub Procedure4() ' Print a message Debug.Print "In Procedure4" End Sub
Figure 4.2 shows the result of running Procedure3 from the Immediate window. As you can see, it prints the first message in Procedure3, and then switches to Procedure4. When Procedure4 is done with its work, execution returns to Procedure3.
Figure 4.2. A sub procedure calling another sub procedure.
You learn about calling procedures from the Access user interface in Chapter 10, "Working with Forms," p. 145.
Creating and Using Function Procedures
The second type of procedure you'll use is the function procedure. Function procedures are almost identical to sub procedures, except that function procedures return a value. Here's a simple function procedure to introduce the idea:
Function Procedure5() Procedure5 = "Hello" End Function
Notice that a function procedure begins with the keyword Function and the name of the procedure, and ends with End Function. Between these two lines, as in a sub procedure, you can have any number of statements. But there's a new type of statement that can appear only in a function procedure: one that specifies the return value of the procedure. In this case, the only statement in the procedure gives the procedure the return value of "Hello" by assigning that string to the name of the procedure. Every function procedure has a return value. If you don't assign a return value explicitly, the return value is the special value Null, which is a sort of empty value.
Figure 4.3 shows how you can call this function procedure from the Immediate window.
Figure 4.3. Calling a function procedure from the Immediate window.
Note the use of the question mark before the function procedure name. This tells VBA that you want it to run the procedure and then print the return value. As you can see, the Immediate window shows the return value from the procedure even though it does not contain a Debug.Print statement.
You can use the question mark operator in the Immediate window to perform simple calculations. For example, if you type ?12 * 12 in the Immediate window and press Enter, it prints the answer (144).
Like sub procedures, function procedures can contain more than one statement, and can be called from other procedures, as in this example:
Function Procedure6() ' Display execution of another procedure Debug.Print "In Procedure6" Procedure7 Debug.Print "Back in Procedure6" End Function Function Procedure7() ' Print a message Debug.Print "In Procedure7" End Function
One of the more useful things that you can do with a function procedure is to use its return value within another procedure, as in this example:
Function Procedure8() ' Use a value from another procedure Dim i As Integer i = Procedure9 Debug.Print i End Function Function Procedure9() Procedure9 = 5 End Function
If you run Procedure8 from the Immediate window, it prints the return value of 5. This happens because Procedure8 assigns the return value from Procedure9 to the variable i, and then prints the value contained in that variable.
|< Day Day Up >|