Sub Procedures

Although all procedures are really sub (short for subroutine) procedures, I will use the term to refer to those procedures created entirely by the programmer. The basic syntax and operation of a sub procedure is the same as for an event procedure. You define the procedure with the scope using the Public or Private keywords, followed by the keyword Sub, the procedure name, and the parameter list (if any). Sub procedures end with the End Sub statement. You can either type in the procedure definition or use the Insert/Procedure menu item to bring up the Add Procedure dialog box, as shown in Figure 3.4.

Private Sub myProcedure(parameter list)

'Sub procedure code is listed here. End Sub

The Add Procedure dialog box.

Private Sub myProcedure(parameter list)

'Sub procedure code is listed here. End Sub

The Add Procedure dialog box.

Sub procedures differ from event procedures in that:

• the programmer defines the procedure name and any variable names in the parameter list.

• the programmer decides how many (if any) variables are in the parameter list.

• they can be placed in both object and standard modules.

• execution begins when they are "called" using code from other parts of the program and cannot be automatically triggered.

The following program collects two numbers from the user, adds them, and outputs the result. This program can reside in any module. For simplicity, I tested this program by running it directly from the VBA IDE. To begin program execution from the VBA IDE, first insert the mouse cursor within the procedure to be executed, and then press F5 or select the appropriate icon from the Standard toolbar or Run menu, as shown in Figure 3.5.

The Run-Sub/UserForm menu selection

The Run Macro button

Running a program from the VBA IDE.

The Run-Sub/UserForm menu selection

The Run Macro button

Running a program from the VBA IDE.

Option Explicit Dim answer As Integer

Private Sub Main()

Dim num1 As Integer Dim num2 As Integer numl = Val(InputBox("Please enter the first operand", "First operand")) num2 = Val(InputBox("Please enter the second operand", "Second operand"))

Call AddUserInput(numl, num2) SendResult End Sub

Private Sub AddUserInput(numl As Integer, num2 As Integer)

answer = numl + num2 End Sub

Private Sub SendResult()

MsgBox ("The answer is " & Str(answer)) End Sub

First, variable declaration is required with Option Explicit and a module level variable (answer) is declared.

The majority of the program is listed in the sub procedure Main(). The sub procedure Main() is declared as Private and serves as the central procedure for the program. Two procedure-level integer variables (numl and num2) are declared and assigned to the return value of input boxes. The Val() function is used to convert the string type return value from the InputBox() function to a numerical value.

After two values are input by the user, the program makes the calls to the sub procedures AddUserInput() and SendResult(). The Call keyword is used to send program execution to AddUserInput() and the variables numl and num2 are passed to this procedure. The Call keyword is required when passing parameters enclosed in parentheses; otherwise it is unnecessary (for example, AddUserInput numl, num2 is an identical statement). After the AddUserInput() procedure executes, program execution resumes in the Main() procedure where it left off. The line SendResult is another procedure call and sends program execution to the SendResult() sub procedure. As no parameters are passed, the Call keyword is omitted (although you may include it if you like). The Main() procedure, and consequently the program, terminates after program execution returns from the SendResult() procedure. The AddUserInput() procedure's only purpose is to accept the two addends from the Main() procedure, add them together, and store the result in the module level variable answer. Note that I used the same variable names for the two addends when defining the AddUserInput() procedure. This is perfectly legitimate, as this is outside the scope of the original numl and num2 variables. Finally, the SendResult() procedure is used to output the answer using a basic message box. A Str() function is used to convert the numerical variable answer to a string before it is concatenated to the rest of the message.

Keep your procedures as short as possible. You will find that as your procedures get longer, they get harder to read and debug. As a general rule I try to keep my procedures to a length such that all of the code is visible on my monitor. If your procedure gets much longer than one screen, break the procedure into two or more procedures.

Biorhythm Awareness

Biorhythm Awareness

Who else wants to take advantage of biorhythm awareness to avoid premature death, escape life threatening diseases, eliminate most of your life altering mistakes and banish catastrophic events from your life.

Get My Free Ebook


Post a comment