Understanding Procedure Types

 < 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:

  • Sub procedures

  • Function 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:

  • From another procedure

  • From the Access user interface

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 > 

    Automating Microsoft Access with VBA
    Automating Microsoft Access with VBA
    ISBN: 0789732440
    EAN: 2147483647
    Year: 2003
    Pages: 186

    Similar book on Amazon

    flylib.com © 2008-2017.
    If you may any questions please contact us: flylib@qtcs.net