Using Master Procedures and Subprocedures

When your VBA procedure gets larger, it may be difficult to maintain its many lines of code. To make your program easier to write, understand, and change, you should use a structured approach. When you create a structured program, you simply break a large problem into small problems that can be solved one at a time. In VBA, you do this by creating a master procedure and one or more subordinate procedures. Because both master procedures and subordinate procedures are subroutines, you declare them with the Sub keyword. The master procedure can call the required subroutines and pass arguments to them. It may also call functions.

The following example shows the AboutUser procedure. The procedure requests the user's first and last name and then extracts the first and last name from the full name string. The last statement displays the user's last name followed by a comma and the first name. As you read further, this procedure will be broken down into several tasks to demonstrate the concept of using master procedures, subprocedures, and functions.

Sub AboutUser()

Dim fullName As String Dim firstName As String Dim lastName As String Dim space As Integer

'get input from user fullName = InputBox("Enter first and last 'get first and last name strings space = InStr(fullName, " ") firstName = Left(fullName, space - 1) lastName = Right(fullName, Len(fullName) 'display last name, first name MsgBox lastName & ", " & firstName

End Sub name:")

The AboutUser procedure can be divided into smaller tasks. The first task is obtaining the user's full name. The next task requires that you divide the user-supplied data into two strings: last name and first name. These tasks can be delegated to separate functions (for example: GetLast and GetFirst). The last task displays a message showing the reordered full name string. Now that you know what tasks you should focus on, let's see how you can accomplish each task.

1. Add a new module to your current VBA project and rename it Sample9.

2. Enter the following AboutUserMaster procedure in the Sample9 module window.

Sub AboutUserMaster()

Dim first As String, last As Call GetUserName(full) first = GetFirst(full) last = GetLast(full) Call DisplayLastFirst(first, End Sub

The master procedure shown above controls the general flow of your program by calling appropriate subprocedures and functions. The master procedure begins with the declaration of variables. The first statement, Call GetUserName (full), calls the GetUserName subroutine (see step 3) and passes it an argument—the contents of the full variable.

Because the variable full is not assigned any value prior to the execution of the Call statement, it has the value of an empty string (" "). Notice that the name of the subprocedure is preceded by the Call statement. Although you are not required to use the Call keyword when calling a procedure, you must use it when the call to the procedure requires arguments. The argument list must be enclosed in parentheses.

3. Enter the following GetUserName subroutine:

Sub GetUserName(fullName As String)

fullName = InputBox("Enter first and last name:") End Sub

The procedure GetUserName demonstrates two very important Visual Basic programming concepts: how to pass arguments to a subprocedure and how to pass values back from a subprocedure to a calling procedure.

In the master procedure (see step 2), you called the GetUserName procedure and passed it one argument: the variable full . This variable is received by a fullName parameter declared in the GetUserName subprocedure's Sub statement. Because at the time Visual Basic called the GetUserName subprocedure the variable full contained an empty string, the fullName parameter receives the same value—an empty

String, full As String last)

string (" "). When Visual Basic displays the dialog box and gets the user's last name, this name is assigned to the fullName parameter. A value assigned to a parameter is passed back to the matching argument after the subprocedure is executed. Therefore, when Visual Basic returns to the master procedure, the full variable will contain the user's last name.

Arguments passed to a subprocedure are received by parameters. Notice that the parameter name (fullName) is followed by the declaration of the data type (As String). Although the parameter's data type must agree with the data type of the matching argument, different names may be used for an argument and its corresponding parameter.

Tip 4-15: Arguments Versus Parameters

■ An argument is a variable, constant, or expression that is passed to a subprocedure.

■ A parameter is simply a variable that receives a value passed to a subprocedure.

4. Enter the following GetFirst function procedure:

Function GetFirst(fullName As String) Dim space As Integer space = InStr(fullName, " ") GetFirst = Left(fullName, space - 1) End Function

The second statement in the master procedure (see step 2), first = GetFirst(full), passes the value of the full variable to the GetFirst function. This value is received by the function's parameter—fullName. To extract the first name from the user-provided full name string, you must find the location of the space separating the first name and last name. Therefore, the function begins with a declaration of a local variable—space.

The next statement uses the VBA built-in function InStr to return the position of a space character (" ") in the fullName string. The obtained number is then assigned to the variable space. Finally, the Left function is used to extract the specified number of characters (space -1) from the left side of the fullName string. The length of the first name is one character less than the value stored in the variable space. The result of the function (user's first name) is then assigned to the function's name. When Visual Basic returns to the master procedure, it places the result in the variable first.

5. Enter the following GetLast function procedure:

Function GetLast(fullName As String) Dim space As Integer space = InStr(fullName, " ") GetLast = Right(fullName, Len(fullName) - space) End Function

The third statement in the master procedure (see step 2), last = GetLast(full), passes the value of the full variable to the GetLast function. This function's purpose is to extract the user's last name from the user-supplied fullName string. The GetLast function uses the built-in Len function to calculate the total number of characters in the fullName string. The Right function extracts the specified number of characters (Len(fullName) - space) from the right side of the fullName string. The obtained string is then assigned to the function name, and upon returning to the master procedure, it is stored in the variable last.

6. Enter the following DisplayLastFirst subroutine:

Sub DisplayLastFirst(firstName As String, lastName As String)

MsgBox lastName & ", " & firstName End Sub

The fourth statement in the master procedure (see step 2), Call DisplayLastFirst(first, last), calls the DisplayLastFirst subroutine and passes two arguments to it: first and last. To receive these arguments, the DisplayLastFirst subprocedure is declared with two matching parameters—firstName and lastName. Recall that different names can be used for arguments and their corresponding parameters. The DisplayLastFirst subprocedure then displays the message box showing the user's last name followed by the comma and the first name.

Tip 4-16: Advantages of Using Subprocedures

■ It's easier to maintain several ■ subprocedures than one large procedure.

■ A task performed by a subprocedure ■ can be used by several other procedures.

Each subprocedure can be tested individually before being placed in the main program. Several people can work on individual subprocedures that constitute a larger procedure.

0 0

Post a comment