Excel has hundreds of powerful functions. Here is how you can create your own!

1. Create

As a simple example, we are going to create a function [1] called VATTED which will take the price of an item and return the prince with 15% VAT added, rounded off to the nearest two decimal places.

To create a custom function in your workbook, follow these steps:

  1. Press Alt+F11 to open the Visual Basic Editor, then click Insert » Module.
  2. A new module window appears on the right-hand side of the Visual Basic Editor.
  3. Type (or copy and paste) the following code into the new module.
Option Explicit
Function VATTED(price)
    VATTED = price * 1.15
    VATTED = Application.Round(VATTED, 2)
End Function

Line 1: read more about Option Explicit statement in the VBA for Excel: a little logic tutorial

Line 2: the beginning of the function includes the name of the function followed by the argument(s) in brackets

Line 3: the value of the price argument is multiplied by 1.15 (effectively adding 15%)

Line 4: Excel’s ROUND() function is accessed using the Round() method of the Application object.

Note that, unlike functions in some other languages (for example PHP and JavaScript), there is no return statement in a VBA function. The name of the function serves as a variable, and it is the value stored in this variable that is returned at the end of the function.

2. Use

You can now use this function in your workbook in the same fashion as any other function.

  • Type and = sign in a cell and start typing the name of your function, VATTED.
  • Once you have typed VAT the full name of your function will appear, double left click on it to add it to the cell.
  • Click on the Inset Function button
  • Select the cell that holds the price
  • Click the OK button

3. Description

You may have noticed when using the Insert Function dialogue that there is no description for your function. Follow these steps to add a description [2]

Using the VBAs Object Browser to locate your custom Excel function.Using VBAs Object Browser to add a Description to your custom Excel function.
  1. Click the Object Browser button to open the Object Browser dialogue
  2. Select VBA Project from the dropdown
  3. Add your descriptive text to the Description input box
  4. Click the OK button

This is Microsoft so you will have to shut Excel down and re-open it for this to take effect.

4. Next steps

Learn to refactor code and use constants in VBA in the Declaring and using Constants in VBA tutorial.


References:

  1. Microsoft Support. (no date) Create custom functions in Excel. Available at: https://support.microsoft.com/en-us/office/create-custom-functions-in-excel-2f06c10b-3622-40d6-a1b2-b6748ae8231f (Accessed: 3 July 2024).
  2. Harvey, G. Excel 2019 All-in-One For Dummies. (2018) How to Add a Description to a User-Defined Function in Excel 2019. Available at: https://www.dummies.com/article/technology/software/microsoft-products/excel/how-to-add-a-description-to-a-user-defined-function-in-excel-2019-255361/ (Accessed: 3 July 2024).

By MisterFoxOnline

Mister Fox AKA @MisterFoxOnline is an ICT, IT and CAT Teacher who has just finished training as a Young Engineers instructor. He has a passion for technology and loves to find solutions to problems using the skills he has learned in the course of his IT career.

Leave a Reply

Your email address will not be published. Required fields are marked *