Creating Your Own VBA Functions

The basic syntax for creating a function procedure in VBA is as follows:

Private/Public Function FunctionName(paramter list) as type 'Function procedure code is listed here FunctionName = Return value End Function

This is similar to the syntax for any procedure with the procedure name, parameter list, and an End statement. You can, and should include a Private or Public keyword to define the scope of the function. One obvious difference is the Function keyword replaces Sub. Also, you should define a return type to the function. The return data type is used for the value that the function sends back to the calling procedure. If you do not specify the data type, then the function's return value will be of type variant. The function returns a value by assigning the desired value to the name of the function, although the return value is usually stored in a variable.

Use Exit Sub or Exit Function ¡f you need to return program execution to the calling procedure before the rest of the code in the procedure executes.

Functions are called from expressions where you would normally insert a variable or literal. For example, instead of assigning a literal to a variable, a function call can be used to assign the function's return value to the variable.

myVar = MyFunction(paraml)

Here, the variable myVar is assigned the return value of the function named MyFunction() that is passed one parameter in the form of a variable named paraml.

Now let's consider an example of a function that mimics one of Excel's built-in functions. The following function calculates the result of raising a number to a specified power. I named the function PowerDB() and set its return value as type double. The PowerDB() function accepts two numerical values for input, the number to which the exponent will be applied (number), and the value of the exponent (n). The function has been given public scope.

The code is really very simple. The value of the variable number is raised to the power of the value of the variable n, and then the result is restored in the variable number. The value of the variable number is assigned to the function so that it may be returned to the calling procedure.

Public Function PowerDB(ByVal number As Double, n As Single) As Double number = number A n PowerDB = number End Function

A procedure that utilizes the PowerDB() function can be written as follows:

Private Sub TestPower() Dim number As Double Dim n As Single Dim result As Double number = Val(InputBox("Enter a number.", "Number")) n = Val(InputBox("Enter the value of the exponent.", "Exponent")) result = PowerDB(number, n) MsgBox (number & "A" & n & " = " & result) End Sub

The only new idea here is the line that calls the PowerDB() function, result = PowerDB(num-ber, n). The variable result is assigned the return value of the function and output in a message box. Note that the data types for the PowerDB() function and variable result match (double). The variable number was passed to the PowerDB() function by value because if I passed it by reference its value would be changed by the function. Since I want to use the original value of number in the final output, I must pass it by value. The variable n was passed by reference because I did not change its value in the function procedure and VBA is more efficient when passing values by reference.

A public scope for the function PowerDB() makes it visible to all procedures in the project and the Excel application provided the function is contained in a standard module. Thus, this function can now be used like any other function in Excel. Returning to the Excel application and entering the formula =PowerDB(2,8) into any worksheet cell will return the value 256 to that cell. The PowerDB() function is even listed in Excel's insert function tool as shown in Figure 3.7 and 3.8.

Step 1 of the Insert Function tool in the Excel application.

Step 1 of the Insert Function tool in the Excel application.

You now see that I named the function PowerDB() in order to avoid a conflict with Excel's POWER() function. You can create your own library of VBA functions to use in your spreadsheet applications. Keeping a library of VBA functions saves you valuable time as you do not have to re-write these functions to use them in another project.

Step 2 of the Insert Function tool in the Excel application.

Step 2 of the Insert Function tool in the Excel application.

Biorhythm Awareness

Biorhythm Awareness

Who else wants to take advantage of biorhythm awareness to avoid premature death, escape life threatening diseases, eliminate most of your life altering mistakes and banish catastrophic events from your life.

Get My Free Ebook


Post a comment