Excel allows you to create your own “User-Defined Functions” 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 Visual Basic Applications (VBA) code, you can create your own function to meet your needs.

As an example of how to create a User-Defined Function, let’s create a function that calculates fuel consumption in miles per gallon.

  • 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 minus 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 User-Defined Function in your module, go back to the Excel workbook and test your new function. This function works the same way any built in function would work.