This how to guide will show you how to create your own Function with Excel Visual Basic, a powerful concept that unlocks the potential of Excel and importantly this is something everyone is capable of so read on.
There are over 400 Functions in Excel but you probably use a small percentage of them, in fact I expect the average Exceller is using less than 10%.
Some of the Excel Functions, =SUM, =AVERAGE and =VLOOKUP will be familiar to almost all Excel analysts but very few are likely to work with Functions like =PDURATION or =ATANH very often if every. The latter actually calculates the inverse hyperbolic tangent of a number for those of you that are interested!
With so many Functions available in Excel you might think that Microsoft have you covered but eventually we all end up asking those same questions of why can’t Excel do this? or, why doesn’t Excel have a function for that?
The good news is in a way they do! Excel has a function for everything you can think of, the only caveat is that you have to create it yourself but that is a plus point because it leaves you with endless possibilities.
Create your own Custom Excel Function
By using Excel Visual Basic it is possible, in fact it is very easy, to create your very own Excel functions. These can be Functions that perform exactly what YOU want and not something generic.
They can be basic like a Function that adds numbers together or applies a tax rate or they can be sophisticated with Functions that perform Fuzzy Lookups or use algorithms to generate results.
To understand how you can create your very own functions in Excel it is best to try a simple example first to grasp the key concepts.
A basic example of a custom Excel Function is one that converts Miles into Kilometers(KM).
Define the Function
The first step is to define the function, this will make writing the Visual Basic code easier and ensure you get the desired result.
A good practice is to work backwards from the end result so in this example the desired end result is a function that can convert a mileage amount into kilometers. The reason for doing this is that it will save us having to remember the conversion factor in the future or looking it up every time.
The next step is to define how to calculate the end result. A quick Google search shows that there are 1.60934 kilometers to 1 mile so to convert miles into kilometers the function will need to multiple a mileage value by 1.60934:
=Mileage Value * 1.60934
With the function clearly defined it is time to build.
First off open up a new Excel workbook and go to the Visual Basic Editor using either ALT+F11 or click on the Developer Tab in the Ribbon and select Visual Basic (if you don’t have the Developer Tab showing in your Excel Ribbon click here to find out how to unlock it).
In the Visual Basic Editor click on Insert > Module from the drop down menu at the top of the screen, then select the code window so you can begin constructing your Excel function.
To tell Excel you are creating a Function the Visual Basic code always starts with the keyword Function and finish with the keyword End Function.
1 2 3 4 5
Function 'The calculation will go here End Function
All Functions need a label so in this example we will call it KMs to make it simple to remember.
All Functions have variables or inputs, these are the values that go into the function and have the calculation performed on them. These variables must be defined (given a name) and declared (given a data type).
In this example the variable is Miles so we will call it Miles and the data type is always numerical so that can be declared as an Integer.
1 2 3
Function KMs(Miles As Integer) End Function
Now the function is properly defined in Visual Basic the next step is to write the actual calculation. In this example the aim is that KMs will equal Miles * 1.60934.
1 2 3
Function KMs(Miles As Integer) KMs = (Miles * 1.60934) End Function
Believe it or not the function is now complete, yes it really is that simple!
The function has the name KMs so that is how it will be called on the worksheet, the variables have been named and their data type declared and finally the calculation has been defined.
Testing the new Miles to KMs Function
Close the Visual Basic Editor down to take you back to the Excel Worksheet, its time to test.
On the Excel worksheet create a small table with mileage values that need to be converted, you don’t have to format the table and make it look pretty but you will need some mileage values to convert:
In this example the Function will be used in column C to show the Kilometer value for each of the Mileage values in column B. To enter the new function type =KMs(B2) into cell C2:
When you press the enter key to apply the Function the mileage amount will be converted into kilometers. Note that in the below screenshot I have changed the number of decimal places on show. This, along with setting the first mileage value to 1, helps to confirm the KMs Function is working as intended:
Looks correct to me!
1 Mile is converted into 1.60934 Kilometers, exactly the desired result.
The formula can be dragged down the rest of the table and will perform the same calculation each time going forward, although you may want to lower the amount of decimal places on show!
Note: The Excel workbook will have to be saved as a Macro-enabled version to retain the new KMs function.
Key Tips for Creating your own Excel Functions
- Define the requirements of your custom Excel function first. Define what the end result should look like and work backwards to define what variables/inputs are needed, their data types and the calculation you need to perform to get the desired result.
- Add notes in your VBA code. When you get used to writing Custom Excel functions you will likely end up writing lots of them so it helps to keep each one well documented.
- Consider saving all your Functions to the Personal Workbook rather than in specific Excel files. In the long run this will make sure your Custom Functions are available every time you use Excel rather than only when you use a specific Excel file.
- Be creative! Creating your own Excel functions can save you a lot of time down the road. Think big and act now!
I will post again with some examples of more advanced custom functions, who knows they may be of use to some of you so be sure to check back with the site. As usual my Twitter feed updates when I post so be sure to follow to get the latest Excel tips.
My closing advice is to get started today and write your very own Excel function, why not share it with us when you are finished and maybe it will help another Excel user out.