Passing Arguments

So far you've created simple VBA procedures that carried out specific tasks. These procedures did not require that you provide additional data before they could be run. However, in real life, procedures (both subroutines and functions) often take arguments. Arguments are one or more values needed for a procedure to do something. Arguments are always entered between parentheses. Multiple arguments are separated with commas.

Having used Excel for a while, you already know that Excel's built-in functions can produce different results based on the values you supply to them. For example, if cells A4 and A5 contain numbers 5 and 10, respectively, the Sum function =SUM(A4:A5) will return 15, unless you change the values entered in the specified cells. Just like you can pass any values to Excel built-in functions, you can pass values to custom VBA procedures.

Now let's see how you can pass some values from a subroutine to the SumItUp function that you created earlier in this chapter. The purpose of this custom function is to get the sum of characters in a person's first and last name.

1. Type the following NumOfCharacters subroutine in the same module (Sample1) where you entered the SumItUp function.

Sub NumOfCharacters() Dim f As Integer Dim l As Integer f = Len(InputBox("Enter first name:")) l = Len(InputBox("Enter last name:")) MsgBox SumItUp(f,l) End Sub

InputBox("Enter first name:")

3. Enter any name, and press Enter or click OK. Visual Basic takes the text you entered and supplies it as an argument to the Len function. The Len function calculates the number of characters in the supplied text. Visual Basic places the result of the Len function in the f variable for further reference. After that, Visual Basic displays the next input box, this time asking for the last name.

4. Enter any last name, and press Enter or click OK.

Visual Basic passes the last name to the Len function to get the number of characters. Then that number is stored in the l variable. What happens next? Visual Basic encounters the MsgBox function. This function tells Visual Basic to display the result of the SumltUp function. However, because the result is not yet ready, Visual Basic jumps quickly to the SumltUp function to perform the calculation using the values saved earlier in the f and l variables. Inside the function procedure, Visual Basic substitutes the m argument with the value of the f variable and the n argument with the value of the l variable. Once the substitution is done, Visual Basic adds up the two numbers and returns the result to the SumItUp function. There are no more tasks to perform inside the function procedure, so Visual Basic returns to the subroutine and provides the SumltUp function's result as an argument to the MsgBox function. Now the message appears on the screen displaying the total number of characters.

5. Click OK to exit the Message box.

You can run the NumOfCharacters procedure as many times as you'd like, each time supplying a different first and last name.

Let's look at another example of passing arguments using variables.

1. Add a new module to the MyFunctions (Chap04.xls) project and change the module's name to Sample2.

2. Activate the Sample2 module and enter the EnterText subroutine:

Sub EnterText()

Dim m As String, n As String, r As String m = InputBox ("Enter your first name:") n = InputBox("Enter your last name:") r = JoinText(m, n) MsgBox r End Sub

3. Enter the following function procedure:

Function JoinText(k,o)

As Visual Basic executes the statements of the procedure, it collects the data from the user and stores the values of the first and last names in the variables m and n. Then these values are passed to the JoinText function. Visual Basic substitutes the variables' contents for the arguments of the JoinText function and assigns the result to the name of the function (JoinText). When Visual Basic returns to the EnterText procedure, it stores the function's value in the r variable. The MsgBox function then displays the contents of the r variable in a message box. The result is the full name of the user (first and last name separated by a space).

Tip 4-8: What Function Procedures Cannot Do

Functions cannot perform any actions. For instance, they cannot include statements for inserting, deleting, or formatting data in a worksheet, opening files, or changing the way the screen looks. \_/

To pass a specific value from a function to a subroutine, assign the value to the name of the function. For example, the NumOfDays function shown below passes the value of 7 to the subroutine DaysInAWeek.

Function NumOfDays() NumOfDays = 7

End Function

Sub DaysInAWeek()

MsgBox "There are " & NumOfDays & " days in a week."

End Sub

0 0

Post a comment