How I created my own functions in Excel using VBA

Although Excel has over 450 functions, sometimes they are just not enough. These built-in functions are not enough if you want to automate certain tasks or perform unique calculations in your databases. This is where the magic of VBA (Virtual Basic for Applications) comes in. It is an undiscovered Excel feature for creating custom functions that has completely changed the way I handle my spreadsheets.




In this guide, I'll start with the VBA basics and give you practical examples of using custom functions to take your Excel skills to a whole new level.

User-defined functions (UDF) in Excel

Before I go through the step-by-step guide to create custom functions in Excel, let's talk about user-defined functions. As the name suggests, it is a custom function that you create using VBA to extend Excel's capabilities beyond its built-in options. Suppose you want to extract numerical information from specific cells in your Excel sheet. Instead of performing the task manually for hundreds of cells, you can simply create your own function and run it with a single click.


The possibilities are endless here. It all depends on your use case, your work style and your ability to develop custom functions in Excel. Whether you are looking for customized solutions or optimized efficiency, or want to create your own functions to expand your Excel skills, VBA can help you tackle complex challenges with ease.

Creating and using a custom function in Excel

VBA is built right into Excel and already exists right under our noses. However, before you start creating your first custom function, there are a few rules you should follow to avoid confusion and annoying errors.

Rules for creating custom functions in Excel

  • A user-defined function begins with function and ends with End function. You need to write logic between the Function and End Function statements.
  • The function statement is followed by the name of the function. You must give it a unique name so that you can easily identify and use it at any time.
  • The name must not match Excel's standard function names, because in this case the standard function is always executed.
  • The name of the user-defined function cannot be the same as a cell address in the worksheet. For example, using the name CDE567 is not allowed.
  • You cannot use a space in your Excel functions. However, you can use an underscore like average_number in your function.


Using custom functions in Excel

Now that you know the basics about custom Excel functions, let's create one. In the following example, I will create an Excel function to calculate all odd numbers from a given range of cells.

  1. Open an Excel workbook on your PC.
  2. Press the Alt + F11 (or Option + F11 for Mac) keys to open VBA. Alternatively, you can go to the developer and select Visual Basic.
    Open Visual Basic in Excel

  3. Choose Insert and click on module.
    Open the module in Visual Basic

  4. Write down the VBA code. As you can see from the following example, I have AddOdd Names for better familiarity. Press the Ctrl + S (or Command + S on Mac) to save it.
    Writing a Visual Basic function


You can now close the VBA window and return to your Excel worksheet. Select a cell, enter =Add Odd(B4:B14) and press Enter to calculate the total number of odd numbers in a given range.

Execute Excel function


Now let's create another function where you need to calculate the age of your customer using the date of birth present in the database. This function can be useful for targeted campaigns, analyzing customer demographics and various other purposes.

  1. Open the VBA Editor in Microsoft Excel (see steps above).
  2. Create a new module. Enter the following function.
    A user-defined function in Excel


Close the window and go to your Excel workbook. Go to any cell and enter =CalculateAge(A22) to calculate the final result. If you make changes to the cell range, the function automatically updates the final value.

Running a custom function in Excel

You can always calculate the same thing using the built-in functions, but in this case a custom function will speed up the process.


Consider another scenario: you work in a sales department and often need to set commission for sales reps based on their total sales numbers. Currently, the commission is 5% for sales up to $5,000, 7% for sales between $5,001 and $10,000, and 10% if sales exceed $10,000. You can create a custom function with relevant conditions and get the desired results in no time.

  1. Open a VBA editor in Microsoft Excel (check the steps above).
  2. Create a new module from the Insert menu. Write a custom function below.
    Write a custom function in Excel


Go back to your Excel worksheet and enter =Calculate commission(G11) and calculate the commission in no time.

Calculate commission based on Excel function

As you can see, VBA offers numerous ways to customize Excel functions to suit your needs. Once you master it, you will find it extremely powerful and productive.


Use Excel on Steroids

VBA has completely changed the way I work with Excel. However, it does take some learning curve and sometimes you might even encounter errors when running your custom functions. As a beginner, you can start with the basics, experiment, and gradually work your way up to more complex functions. With a little practice, you'll be amazed at what you can achieve. This flexibility is one of the reasons Excel is better than modern database solutions like Notion.

Leave a Comment