Writing a Simple Function
The object of this exercise is to create a function to accept two numbers, which then multiplies them together and returns the result. The function will have the name Multiply. The following table cites the four main mathematical operators 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
As with the subroutine, you must have at a bare minimum the function line and the end function line (header and footer). Note that it must be entered in a module you have inserted, not a module belonging to a form or report.
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 to the routine that called the function. Note that the name of the function now appears in the drop-down at the top right of the code window. This is because it is now an official function within both your VBA code and Access.
You can now use this function in two ways: by calling it directly from within a SQL query as a function, or by using it within your VBA code. To call it directly from within a SQL query, use "select Multiply(3.4) from MyTable;". Having your own custom functions opens up interesting possibilities as to what you can now build into your queries.
However, a word of warning when running custom function queries over large amounts of data. Close down the VBE window before you run the query, otherwise this window will keep being updated as the query is run, meaning the query will take considerably longer to run than normal.
Now, for the second way to use the function: calling it from within your VBA code. For the sake of simplicity, you will next call your new function using the same event from which you called the Hello World example.
Return to the initial Hello World . Turn the "Hello World" statement into a comment by putting a single quote (') character before it and enter the following code so it looks like this:
Sub MyCode() '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.
Click the cursor anywhere on the MyCode procedure and run the code by clicking the Run icon in the VBE toolbar or pressing F5. You will see a message box displaying 15.