An Introduction to Excel VBA Functions

What is an Excel VBA function?

In Excel VBA, a function is a calculation that is performed that returns a value. Sometimes functions need an input value, where a calculation is performed and returns a value. For example, to use the SUM function, it needs input values to be able to add them and output the total value.

However, there are some functions that don’t require input values. For example, the NOW function returns the current system-formatted date and time without any input values needed for the function to work.

In Excel, there are a lot of built in functions that already exist. There are basic functions like SUM and AVERAGE or more complex functions like VLOOKUP and SUMIF.

What they all have in common and what makes them functions is that they all return a value. 

SUM adds up all the values and returns the total value, AVERAGE adds up all the input values, divides by the number of input values and returns that value. VLOOKUP finds a specific value from an array based on a lookup value and returns that value, and finally, SUMIF adds up all the values, based on specified criteria and returns the total value.

There’s only a limited number of functions built in Excel. Often, there will be times when Excel hasn’t got the function required to do the job. In Excel VBA, there’s the ability to write custom functions. These are known as User Defined Functions (UDFs).

There are endless Excel user-defined function examples. One would be to calculate the commission that a salesperson is due, based on the sales they’ve made in a given period. There isn’t a built-in function in Excel that is able to do that, but a user-defined function could be written and used in Excel.

How to write an Excel VBA function

Let’s go through an Excel VBA function example of how to write a function that would calculate the sales commission for a salesperson based on the number of sales made. 

In this example, if the salesperson makes sales less than $50,000, the sales commission is calculated at 5%. If the sales made are $50,000 or above, the sales commission is calculated at 10%.

1. First, access the Developer tab, by clicking File.

2. Click Options.

3. Click Customize Ribbon.

4. Tick the Developer box.

5. Once the Developer Tab appears at the top of the ribbon, click the Visual Basic button or press and hold Alt and F11 together on the keyboard to go to the Visual Basic Editor (VBE).

6. Once you’re in the Visual Basic Editor, click Insert and Module.

7. Click on the newly-created module.

8. To write a function, start with the keyword function, the name of the function with no illegal characters, and the Excel VBA function return type. This is what value you would like the function to return or output. For example, a string would be text, long would be a long integer or a boolean which would be a TRUE or FALSE value.

The Excel VBA function return types are the same as variable types.

---

For more information on VBA variable types, check out GoSkills VBA Variable Types in Excel.

---

9. Between the brackets are the inputs or arguments required for the function to be able to do its job properly. For example, to calculate the sales commission amount, it needs to know the sales amount. Add in the arguments by giving a sensible name, the keyword As and its Excel VBA function return type.

10. From here, the code will be similar to writing a subroutine. The key difference is to ensure there’s a line of code with the function name and what it's returning. That tells the function what to expect to output as the value.

In this example, the lines

Calculate_Commission = Salary * 0.05

and

Calculate_Commission = Salary * 0.05

tell the function the calculation to perform and the value to be returned. The calculation performed is

Salary * 0.05

and

Salary * 0.1.

It assigns the value to the Calculate_Commission function via an equals sign.

It's a basic function but demonstrates the fundamental principles of arguments and Excel VBA function return types. Functions can have many more lines of code and be a lot more complex, but the principles remain the same.

Using Excel functions in VBA

Once the function has been written, it can be used in the main Excel interface, like all the other built-in Excel functions. Simply type = and then the start of the function name as usual, and it will now appear in the drop-down list to use, along with all the other built-in functions.

An example of how to use the function can be seen below:

VBA function vs. Sub

The key difference between a VBA function and a Sub is that a function returns a value, but a Sub doesn’t.

Going back to the example of calculating sales commission based on the number of sales made. An example of a Sub would be to highlight the cells with sales less than $50,000 in red and highlight the cells with sales more than $50,000 in green. No values are returned; the Sub is highlighting the cells only. The Sub can’t calculate the commission value based on the number of sales and commission percentage.

Conversely, a function can’t only highlight the cells because simply highlighting cells doesn’t return a value. The function can calculate the commission amount because it returns a value. In this case, a commission value is based on the amount of sales multiplied by the commission percentage.

Built-in Excel VBA functions

There are built-in functions that can be used in subroutines or custom functions that appear in the Visual Basic Editor but don’t appear in the main Excel interface. For example, VBA.msgbox displays a message box on the screen. It’s not necessary to include VBA as a prefix: "Msgbox" will suffice, but it’s good practice to add the library name before the function to show which library the function belongs to. 

For a full Excel VBA functions list, click View and click Object Browser or use the keyboard shortcut F2.

From there, select VBA from the dropdown to see a full list of functions and properties available to the VBA library.

To summarize

Excel VBA functions are a powerful tool that can help automate repetitive tasks, streamline workflows, and enhance Excel's built-in functions. With VBA, users can create their own custom functions to extend Excel's functionality beyond its built-in capabilities. Writing VBA functions can seem intimidating at first, but with a little practice, anyone can master it. GoSkills offers a range of Excel courses, including one specifically for Macros and VBA, to help users learn how to create custom functions and automate their Excel spreadsheets, and master data analysis.

Ready to become a certified Excel ninja?

Take GoSkills' Macros & VBA course today!

Start free trial