Tag Archives: custom function

Custom_Function_1

How to Create a Custom Function in Excel

User_Defined_1I’ve always been intrigued by people who are outgoing enough to “flower power” their vehicles. While secretly wishing to have that sort of bravado, I have to admit to a shyer personality that could never handle such an attention-getting customization.

On the other hand, customizing Excel to match my preferences is right up my alley! In two previous posts, I illustrated how personal format preferences could be set as the default for new spreadsheets (here) and how the auto-formatting of hyperlinks could be removed (here). Today’s post will create a user defined or custom function for a calculation that is not available in Excel’s built-in functions.

A Custom Function to Calculate Percentage Change

It’s always been a mystery to me why there is not a built-in function to calculate percentage change between two values. Although it is simple to calculate, I use it often enough that creating a function could save a lot of time. We’ll use the following data to calculate the percentage change in the number of units sold from one month to the next.

Custom_Function_2

To manually calculate the percentage change from January to February, in Cell C3, we’ll provide =(B3-B2)/B2. Essentially the original number divided by the net change in the original and new value yields the percentage change, in this case, 3.6%.

Custom_Function_3

To transform this formula into a custom function, we’ll need to use the Visual Basic Editor.

1. Press Alt + F11. Select Insert on the Menu Bar. Select Module.

Custom_Function_4

2. Enter the following code. Note that the name of the custom function and the two parameters – PerChange, Older, and Newer were chosen by me on the fly. My first choice was Old and New for the parameters but New was a reserved keyword, so I couldn’t use it.

Custom_Function_5

3. Press ALT + F11 to return to the spreadsheet. The function may now be used in Cell C3 to calculateĀ  the percentage change by identifying the Older and Newer Values in Cells B3 and B2, respectively.

Custom_Function_6

Custom_Function_7

Saving the Function to Use in Other Workbooks

To save this custom function (and others!) to be available in other workbooks, they will need to be saved in a file that is stored in the XLSTART folder.

1. Search for the location of the XLSTART folder in Programs file hierarchy. Using Windows Explorer, search for XLSTART kind:=folder. Make note of the location.

2. Click on File, Save As. Provide a name in the File Name. Select Excel Add-in in the Save as Type drop down options. Navigate to the XLSTART folder located in step 1. Click the Save button.

When Excel starts, the Add-in will be resident in memory and accessible to all workbooks.

If you found this post helpful, you may subscribe by email to receive more great tips, tricks, and templates in your inbox.

Cheers,

Annie