Creating a Function Procedure

Like Excel functions, function procedures perform calculations and return values. The best way to learn about functions is to create one. So let's get started. After setting up a new VBA project, you will create a function procedure that sums up two values.

1. Open a new Excel workbook and save it as Chap04.xls.

2. Switch to the Visual Basic Editor window and select VBAProject (Chap04.xls).

4. Select MyFunctions (Chap04.xls) in the Project Explorer window, and choose Insert | Module.

5. In the Properties window, change the Modulel name to Sample1.

6. In the Project Explorer window, click Sample1 and choose Insert | Procedure. The Add Procedure dialog box appears, as shown in Figure 4-1.

7. Make the following entries in the Add Procedure dialog box:

Name: SumItUp Type: Function Scope: Public

Figure 4-1:

When you use the Add Procedure dialog box, Visual Basic automatically creates the procedure type you choose.

Figure 4-1:

When you use the Add Procedure dialog box, Visual Basic automatically creates the procedure type you choose.

Tip 4-1: About Function Names

Function names should suggest the role that the function performs and must conform to the rules for naming variables.

Tip 4-2: Scoping VBA Procedures

In the previous chapter you learned that the variable's scope determines which modules and procedures it can be used in. Like variables, VBA

procedures have scope. A procedure scope determines whether it can be called by procedures in other modules. By default, all VBA procedures are public. This means they can be called by other procedures in any module. Since procedures are public by default, you can skip the Public keyword if you want. If you replace the Public keyword with the Private keyword, your procedure will be available only to other procedures in the same module, not to procedures in other modules.

Public Function SumItUp() End Function

The first statement declares the name of the function procedure. The Public keyword indicates that the function is accessible to all other procedures in all other modules. The Public keyword is optional. Notice the keyword Function followed by the name of the function (SumItUp) and a pair of empty parentheses. In the parentheses, you will list the data items that the function will use in the calculation. Every function procedure ends with the End Function statement.

9. Modify the function declaration as follows:

Public Function SumItUp(m,n)

End Function

The purpose of this function is to add up two values. Don't pass the actual values to the function. To make the function flexible, provide the function with the arguments in the form of variables. This way your custom function will be able to add up any two numbers that you supply. The variables each represent a value. You will supply the values for each of these variables when you run this function.

Tip 4-3: Some Reasons for Using Functions

Custom VBA functions can be used to:

■ Analyze data and perform calculations

■ Modify data and report information

■ Take a specific action based on supplied or calculated data

10. Type the following statement between the Public Function and End Function statements:

This statement says to add the value stored in the n variable to the value stored in the m variable and return the result to the SumItUp function. To specify the value that you want the function to return, type the function name followed by the equal sign and the value you want it to return. In the statement above, set the name of the function equal to the total of m + n. The completed custom function procedure is shown below:

Public Function SumItUp(m,n)

SumItUp = m + n End Function

Congratulations! You have now created your first function. However, a function procedure is useless unless you know how to execute it. The next section shows you how to put your new function to work.

0 0

Post a comment