Writing a Simple Function

The object of this exercise is to create a function to accept two numbers and multiply them together and return the result. The function will have the name Multiply . The following table cites the four main mathematical operators that you will use when writing functions and subroutines in VBA.

Add	+
Subtract	‚
Multiply	*
Divide	/

The code for this function is as follows :

 Function Multiply(a, b) 
 
 Multiply = a * b 
 
 End Function 

It should look like Figure 3-1. As with the subroutine, you must have at a bare minimum the function line and the end function line (header and footer).

Figure 3-1: Creating a simple multiply function

The header introduces two parameters, a and b , by showing them in parentheses after the title of the function. A comma separates the two arguments. These arguments represent the two numbers to be multiplied ‚ they could be called anything, as long as the variable name is consistent throughout the function.

The name of the function is Multiply , and this is used as a variable to return the answer. This is the only way to return the answer back to the routine that called the function. Note that the name of the function now appears in the pull-down on the top-right of the code window. This is because it is now an official function within both your VBA code and Excel.

You can now use this function in two ways: by calling it directly on the spreadsheet as a function or by using it within your VBA code. To call it directly on the spreadsheet, you do so in the same way that you would use any other function ‚ for example, SUM . That's right, you have just written your first practical extension to Excel, and it's all your own work!

Click the spreadsheet and enter =multiply(3,4) into a cell. The answer 12 will appear in the cell . You can see how easy it is to write your own formula into Excel. The difficult calculation that you put together last week can now be turned into a custom function and used like any other Excel function.

Now, for the second way to use the function: calling it from within your VBA code. For the sake of simplicity, next you will call your new function from the same event that you called the Hello World example from.

Click the ThisWorkbook object and return to the initial Hello World example in Chapter 1. Turn the ‚“Hello World ‚½ statement into a comment by putting a single quote (') character before it and enter the following code so that it looks like this:

 Private Sub Workbook_NewSheet(ByVal Sh As Object) 
 'MsgBox "Hello World" 
 x = Multiply(3, 5) 
 MsgBox x 
 End Sub 

Note that when you type the word Multiply and open the brackets, VBA automatically displays the parameters it is expecting by name. By inserting the function into the code, you are forcing a call to that function using the parameters 3 and 5 to replace a and b in your function. The result is returned in the variable x .

Now click the worksheet and choose Insert Worksheet from the menu. A message box will appear with the answer of 15.