Unleash the power of Excel to simplify your work and save your time. Does that sound like something you want to do? What is one way of doing this? By creating your very own Excel function.
Yes, you can do it, and it’s really quite easy to do! Just follow the simple steps here and be on your way to becoming a VBA master.
What is a Function?
Did you know that you probably use at least one function every time you create a formula in Excel? A basic formula will have math operators such as a plus sign (+) for addition or a minus sign (-) for subtraction.
Once you do more than that in a formula, you will use one of Excel’s many built-in functions, such as SUM, IF, and VLOOKUP.
A function is really just a complex formula pre-built for you. You just need to tell it the values that it needs to do its work, and it will give back to you the answer.
If you click on the Insert Function button in Excel, as shown below, it shows the functions and how to create them.
Why Create Your Own Excel Function?
Excel has many built-in functions, but at times you may need one that Excel doesn’t have. For example, you may want to calculate the varying commission on sales of items, based on the sale price.
Now, you could use a formula to calculate that, but that could get complicated, messy and increase the chance of errors. As well, you would need to remember that formula each time you wanted to use it in other spreadsheets.
Here is where creating your own function shines!
Create the function once, and use it repeatedly in one or 1,000 workbooks, knowing that it will consistently give you the correct answer each time.
An Excel function created by you, the user, is also known as a User Defined Function or UDF. Using VBA to write code and make your own Excel functions is really quite easy.
Creating Your Own Excel Function
Here are the steps to create your very own Excel Function:
Step 1 – Open the Visual Basic Editor
On the developer tab in Excel, click Visual Basic. If you can’t see the developer tab, click here to see how to enable it.
Step 2 – Insert a module
A module allows you to write the code that will become your function. In the Visual Basic editor click Insert, then click Module.
Step 3 – Enter the Function
You can start typing the function directly in the module.
Here is a sample function that performs a commission calculation that varies depending on the amount of sales.
Function SalesCommission(SalePrice As Currency)
Dim CommissionCalc As Currency
Select Case SalePrice
Case 100 To 199.99
CommissionCalc = SalePrice * 0.05
Case 200 To 499.99
CommissionCalc = SalePrice * 0.75
Case 500 To 999.99
CommissionCalc = SalePrice * 0.1
Case Is >= 1000
CommissionCalc = SalePrice * 0.12
CommissionCalc = 0
SalesCommission = CommissionCalc
It should be pretty self-explanatory what the function does. You pass a value to the function. It takes that value and calculates the percentage based on the criteria of the Select Case. It then returns the value it has calculated back to you.
Here is how to use the newly created function in your workbook. Just type “=” in the cell or the formula bar plus the name of the function that you just created. Excel brings up the name and you just need to hit TAB and then either the value that you want to pass to the function or the cell with the value that you want to pass to the function.
Below is an image of a sample spreadsheet with several formulas using the newly created Excel function.
And now here is an image of the same spreadsheet showing the results.
There you have it. Creating a custom function in Excel can be done in three simple steps.
The calculation performed by the function shown here was a simple one. The process, however, is the same even for a more complicated function.
Hopefully, this post has shown how you can unleash the power in Excel to save you time and make your work easier.
Let me know what you think.