Custom Functions in Excel VBA

/ Uncategorized / By

You can write Visual Basic For Applications (VBA) code to create your own custom functions in Excel VBA, known as Used Defined Functions.

Working With User‑Defined Functions

Although Excel includes many built‑in functions, you will often need to create your own user‑defined functions to perform calculations for which no Excel function exists. For example, you might need to calculate the total return for an investment account based on the change in the value of the assets in the account, the income generated by the assets, and the monetary additions and withdrawals made to and from the account. Since there is no built‑in Excel function for this problem, you will need to create a user‑defined function.

Creating a User‑Defined Function

You create a user‑defined function in the Code window of the Visual Basic Editor. All user‑defined functions start with the Function statement and end with the End Function statement, as shown in Figure 1. The name(s) in the parentheses within the Function statement are the arguments of the user‑defined function. Arguments refer to the values used in the calculation. Using arguments that closely resemble the data, such as the column headings, lets you better understand the function structure in the cell.

The Visual Basic code between and including the Function statement and the End Function statement is the Function procedure. The purpose of the Function procedure is to return a value. To return a value, the Function procedure must include a function assignment statement. An assignment statement assigns a value to the variable or constant to the left of the equal sign of the assignment statement based on the calculations to the right of the equal sign. A function assignment statement is an assignment statement that assigns a value to the function name.


Figure 1: A Sample User‑Defined Function

In Figure 1, an If…Then…Else statement is used in the Function procedure. The text ExceededSales >= 0.5 in the If statement is referred to as a condition. If the condition is true, then the assignment statement following it is executed. If the condition is false, the ElseIf statement is read. The condition ExceededSales >= 0.25 is treated in the same manner. Lastly, if neither of the preceding conditions is true, then the assignment statement Bonus = 0 is executed.

It is easier to read and comprehend the code if you indent certain parts of the Function procedure. In the code shown in Figure 1, each assignment statement within the If…Then…Else statement is indented, which lets you easily see which parts of the code are assignment statements and which parts are lines containing the conditions.

When entering Visual Basic code, you must press Spacebar after procedure and statement names (not assignment statements), such as Function, If, ElseIf, and Else, before entering code following these names. You do not need to press Spacebar after other procedure and statement names, such as End If and End Function, because you will not enter code after these names. All procedure and statement names appear in blue.

To create a user‑defined function:

  1. Open the Visual Basic Editor.
  2. In the Project Explorer, double‑click the desired module.

or

  1. In the Project Explorer, select the desired folder, and then from the Insert menu, choose Module.
  2. If necessary, place the insertion point after the last line of code in the module, and then press Enter
  3. Type Function [Function Name] (Argument 1, Argument 2…Argument n)
  4. Press Enter
  5. Type the desired function procedure, indenting text as desired.

Using a User‑Defined Function

You can use user‑defined functions in any workbook as long as the workbook containing the module in which the function is located is open. If you want a user‑defined function to be available to all workbooks without having to open the workbook containing the function, type (or copy) the user‑defined function code in a module of the Personal Macro Workbook.

In a worksheet, you enter a user‑defined function the same way you enter a built‑in function. You can type the function or you can use the Paste Function dialog box. The advantage of using the Paste Function dialog box is that you are assured of entering the correct function, and the Formula palette guides you through the process of entering the arguments.

To use a user‑defined function:

  1. On the Standard toolbar, click the Paste Function button.
  2. In the Paste Function dialog box, in the Function category list box, select User Defined.
  3. In the Function name list box, select the desired function.
  4. Choose OK.
  5. In the Formula palette, in the argument boxes, type or select the desired arguments.
  6. Choose OK.

Note: If you type the function in a workbook other than the workbook containing the function, the 3‑D reference to the workbook containing the function must precede the function. For example, you enter =’Personnel File 2.xls’!Bonus(C2,D2,E2) when typing the Bonus function in a workbook other than Personnel File 2. You can avoid this by using the Paste Function dialog box. When you select User Defined in the Category list box, all user‑defined functions in open workbooks appear, with 3–D references preceding the user‑defined functions in other open workbooks.

Find out more about Custom Functions in Excel VBA at visualbasictraining.co.uk or browse our training course options at http://www.eident.co.uk/training-courses/microsoft-excel-vba-two-day-introduction/