Your first user-defined function

Now you can proceed directly to writing a user-defined function.
Let’s start by writing code to calculate a simple function, for example:

F(x)=x3+x2F(x) = x^3 + x^2F(x)=x3+x2

To implement this task, you need to perform the following steps:

1. In the VBA editor window, add a standard module (if you haven’t created one yet) by executing the command Insert | Module.
2. In the window of the created module , type the code from:

Function F(x As Double) As Double
    F = x ^ 3 + x ^ 2
End Function

It should be noted that in VBA there is a universal data type Variant, which is assumed by default if the type of a variable or function has not been explicitly declared. Therefore, the same function could also be coded as follows.

Listing 1.2. User-defined function using the Variant type

Function F(x)
    F = x ^ 3 + x ^ 2
End Function

NOTE:
Once again, note that the user-defined function code is entered in a standard module, which is added to the project using Insert | Module. If there are many modules in the project, do not confuse them. The active module is highlighted in gray in the Project – VBAProject window.

So, the user-defined function has been created. By default, it appears in the User Defined category in the Function Wizard list. Let’s find, for example, the value of this function when x = 4.7. To do this:

1. Go to the Microsoft Office Excel 2010 workbook window.
2. Enter the number 4.7 in cell A1 of the worksheet (for example, Sheet1).
3. Go to cell B1, where we will find the function value.
4. Go to the Formulas tab on the ribbon and in the Function Library group, click Insert Function.
5. In the first Function Wizard window, select the category User Defined from the list and choose the function F. Click OK.
6. In the second Function Wizard window, enter the reference to cell A1 in the X field (or click the corresponding worksheet cell with the mouse) and click OK (Fig. 1.5). The function value is calculated.