Microsoft Excel VBA Function Procedures

/ Uncategorized / By

User-Defined Functions

Microsoft Excel VBA Function Procedures can be used to standardise and simplify any type of calculation in Excel spreadsheets. As a user of Excel you will be aware that the application already comes with a vast library of functions. In Excel, whether you use a simple SUM() function or a more advanced one, a formula ‘palette’ is available to assist in creating the data instructions for the function.

If you have a situation where you or another Excel user has to make the same kind of calculation on a regular basis you may be able to create a custom (or User-defined) function. When the function is used the palette will recognise it as if it were one of Excel’s own. This is an incredibly powerful element of application customising in Excel. To create a user-defined function you have to write code in the VB Editor.

Furthermore, although Excel includes many built‑in functions, you may 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 Microsoft Excel VBA 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 below. 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.

In this example of a function procedure, the If…Then…Else statement is used. 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, 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.
  3. If necessary, place the insertion point after the last line of code in the module, and then press Enter
  4. Type Function [Function Name] (Argument 1, Argument 2…Argument n)
  5. Press Enter
  6. Type the desired function procedure, indenting text as desired.

Using a User‑Defined Function

You can use user‑defined Microsoft Excel VBA 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 Microsoft Excel VBA 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 Formula toolbar, click the Insert Function button (shown below).

The Insert Function dialogue box becomes available.,or  you could just click the Insert Function button on the Function Ribbon

  1. In the Function Category list, scroll down and click on User Defined. In the Function Name list box, select the desired function.
  2. Choose OK.
  3. In the Formula palette, in the argument boxes, type or select the desired arguments.
  4. Choose OK.

Globally Available User-Defined Microsoft Excel VBA Functions

In the same way that macros are normally stored attached to their workbooks, functions written in the VB Editor will only be available when the host workbook is open.

Referencing a User-defined Function from Another

If you type (or reference) a user-defined function in an open 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.xls’!Bonus(C2,D2,E2) when typing the Bonus function in a workbook other than Personnel File.xls.

Alternatively, if you use the Paste Function dialog box, functions in all open workbooks are shown and available. The functions listed under Function Name… are shown with their preceding 3–D references.

Referencing a User-defined Function in an unopened workbook

If you need a User-Defined Microsoft Excel VBA Function to be available more globally it can be stored in the Personal Macro Workbook in the same way as macros.

Find out more on our VBA section here or view our events pages for upcoming courses in Glasgow and Edinburgh.