Executing a Function Procedure

In Chapter 1, you learned various ways to execute a subroutine procedure. Unlike a subroutine, a function procedure can be executed in just two ways. You can use it in a worksheet formula or you can call it from another procedure. Function procedures that you create in VBA cannot be accessed by choosing Tools | Macro | Macros in the Microsoft Excel window. And they cannot be run by pressing the F5 key when the mouse pointer is located inside the code of the function procedure. In the following sections, you will learn special techniques for executing functions.

Running a Function Procedure from a Worksheet

A custom function procedure is like built-in function. If you don't know the exact name of the function or its arguments, you can use the Insert Function dialog box to help enter the required function in a worksheet.

1. Switch to the Microsoft Excel window, and select any cell.

2. Click the Insert Function fx) button on the formula bar. Excel displays the Insert Function dialog box. The lower portion of the dialog box displays an alphabetical listing of all the functions in the selected category.

3. In the category drop-down box, select All or User Defined. Then scroll down in the function name box to locate and select the SumItUp function that was created earlier in this chapter.

When you highlight the name of the function in the function name box, the bottom part of the Insert Function dialog box displays the function's syntax: SumItUp(m,n).

Figure 4-2:

VBA custom function procedures appear in the same list as the built-in Microsoft Excel function.

Figure 4-2:

VBA custom function procedures appear in the same list as the built-in Microsoft Excel function.

Tip 4-4: Private Functions are Not Visible to Users

Functions declared with the Private keyword do not appear in the Insert Function dialog box. Private functions cannot be used in a formula. They can only be run from another VBA procedure.

Tip 4-5: Quick Access to Custom Functions

As soon as you create your first VBA function with the Public scope, Excel adds a User Defined category in the Insert Function dialog box. By selecting this category, you can gain quick access to your custom VBA functions.

Click OK to begin writing a formula. The Function Arguments dialog box appears, as shown in Figure 4-3. This dialog displays the name of the function and each of its arguments: m and n. Enter the values for the arguments as shown in Figure 4-3, or enter your own values. As you type the values in the argument text boxes, Excel displays the values you entered and the current result of the function. Because both arguments (m and n) are required, the function will return an error if you skip either of the arguments.

Figure 4-3:

The Formula Palette feature is helpful in entering any worksheet function, whether built-in or custom made with the VBA programming.

Figure 4-3:

The Formula Palette feature is helpful in entering any worksheet function, whether built-in or custom made with the VBA programming.

6. Click OK to exit the Function Arguments dialog box.

Excel enters the SumItUp function in the selected cell and displays its result. To edit the function, select the cell that displays the function's result and click the Insert Function button (fx). Select the function and click OK to access the Function Arguments dialog box. Type in different values for the function's m and n arguments and click OK. To edit the arguments' values directly in the cell, double-click the cell containing the function and make the necessary changes.

Tip 4-6: Ensure Availability of Your Custom Functions

Your custom VBA function is only available as long as the workbook where the function is stored is open. If you close the workbook, the function is no longer available. To make sure that your custom VBA functions are available every time you work with Microsoft Excel, you can do one of the following:

Store your functions in the Personal Macro Workbook.

Save the workbook with your custom VBA function in the XLStart folder. Set up a reference to the workbook containing your custom functions (please see Chapter 2 for information on setting up the reference to another project).

Running a Function Procedure from Another VBA Procedure

As mentioned earlier, you cannot run a function procedure from the Visual Basic Editor window by placing the mouse pointer within the code of the function procedure and pressing F5 or choosing Run | Run Sub/UserForm. To run a function, you must call the function name from another procedure. To execute a custom function, write a VBA subroutine and call the function when you need it.

The following procedure calls the SumItUp function and prints the result of the calculation to the Immediate window:

Sub RunSumItUp()

Dim m As Single, n As Single m = 370000 n = 3459.77

Debug.Print SumItUp(m,n)

MsgBox "Open the Immediate window to see the result."

End Sub

■ The above subroutine uses the Dim statement to declare the m and n variables, which will be used to feed the data to the function.

■ The next two statements assign the values to the variables.

■ Next, Visual Basic calls the SumItUp function and passes the values stored in the m and n variables to it. When the function procedure statement SumItUp = m + n is executed, Visual Basic returns to the RunSumItUp subroutine and uses the Debug.Print statement to print the function's result to the Immediate window.

■ The MsgBox function informs the user where to look for the result. To try out the above procedure example, follow these steps:

1. Type the RunSumItUp procedure in the same module where you entered the code of the SumItUp function.

Tip 4-7: A Quick Test of a Function

After you write your custom function, you can quickly try it out in the Immediate window. To display the value of a function, open the Immediate window and type a question mark (?) followed by the function name. Remember to enclose the function's arguments in parentheses. For example, type:

and press Enter. Your function procedure runs, using the values you passed for the m and n arguments. The result of the function appears on a line below:

421.24

0 0

Post a comment