Excel allows you to create your own “User Defined Functions” (UDF) that can be used the same way as any other built-in function in Excel (i.e. IF, SUM, VLOOKUP, etc.). With a little knowledge of VBA code, you can create your own function to do pretty much whatever you want.
To illustrate how to create a UDF, we’ll create a function that calculates fuel consumption in miles per gallon.
- First, open a new Excel workbook.
- Open VBA (Alt+F11)
- Right click the workbook’s name (i.e. VBAProject (Book1))and select Insert | Module
In order to calculate your vehicle’s fuel consumption in miles per gallon, you’ll need to know a couple things:
- How many miles driven (ending miles less beginning miles)
- Number of gallons used
- Miles per Gallon = (Ending Miles – Beginning Miles) / Gallons Used
Now, from the new module you opened in VBA, begin typing your function as follows.
After entering the UDF in your module, return back to the Excel workbook and test it out.
Take a look at the example below to see how this function works the same way any built in function would work.
You can create your own functions to do just about any calculation you can think of, as well as manipulating text.
These functions can be saved in the workbook you’re working in, or can be saved as an add-in if you wish to use it in more than one workbook.
To save the workbook as an add-in, simply click File | Save As enter a name for the file, then change the file type to Microsoft Excel Add-In (*.xla). In Excel 2007, you can then import the add-in by clicking Excel Options after clicking the Office Button, then clicking Add-Ins. Towards the bottom of the screen, select Manage: Excel Add-Ins and click Go. In earlier versions of Excel, click Tools | Add-Ins. On the next screen, click Browse and find the location of your Add-In. Make sure it is checked in the list, and hit OK. You should now be able to access your Add-In in any file you’re using.