Yesterday I showed you how to create your own functions using Excel VBA, or Visual Basic for Applications. Today’s post will take this to the next level.

If you’ve played with creating and using your own functions, you may have noticed that once you close that workbook, you lose the ability to use that function. This is because Excel uses its built-in functions first, then looks to what is saved in the workbooks that are open. Excel doesn’t by nature save VBA code into itself; it only reads what’s been saved into the individual files.

Here’s an example. Say you’re working on a physics project for school. You’re working out of your spiral notebook from which you’ve been working all year. When you need to remember the formula for momentum, you can just flip through your notebook, find where you wrote it down, and use it to calculate the problem you’re currently working on. If you were working in a different book, you’d have to go back to this book to flip through and find the formula. If you didn’t have the book with the formula written on it (assuming you lack the cognitive capacity to memorize it), you wouldn’t be able to solve the problem.

So the excel file in which we saved our CAGR formula yesterday is the spiral notebook you’ve been taking to class every day, and in order to remember how to solve that formula, Excel needs to keep the spiral notebook open.

But, there is a way to make Excel memorize the formula. It is done with add-ins.

Generally speaking, when a program accepts add-ins, they have to be programmed using a higher-level programming code than VBA, and they have to be compiled in a very specific manor such that the host program is able to read and employ them. In the case of Excel, you are able to create your own add-ins just by saving a certain Excel file as a “Microsoft Excel Add-In (*.xla)”, which is the last type in the “Save As Type” drop-down when saving a file.

The methodology here is that any VBA code you write – be it custom functions or macros – will be saved in this .xla file, and when you include it as an add-in for Excel…wait, I’m getting ahead of myself.

Let’s start from the beginning. Go ahead an open up a new spreadsheet in Excel. Don’t worry about the cells within this worksheet. You can type whatever you want there, or you can type nothing at all. What you want to focus on is the VBA window.

On the menu, select Tools -> Macro -> Visual Basic Editor. You want a spot to type stuff, right? So click Insert -> Module. Here is our blank canvas where we enter in our custom formulas and whatnot. We’ll use a simple formula for today’s example. Type this into your module:

Function stupid_formula (myinput as integer)

stupid_formula = myinput ^ 3

End Function

If you can’t tell, all this function will do is cube what you pass to it. If you were to close this window out, go back into your spreadsheet and type “=stupid_formula(2)” into a cell and hit enter, the cell’s value would be 8. However, if you were to close this workbook, open a new workbook, and type that formula in, the cell’s value would be “#NAME?” because Excel already forgot what you wrote in it’s spiral notebook.

Ok, so you’ve written “stupid_formula” in your module, and you haven’t closed your Excel file. Here’s where the fun starts. Close your module window to get back to the spreadsheet with which you began this journey. Save it (via File -> Save) as a “Microsoft Excel Add-In”. Once you select that as the file type, Excel will default to the “Add-Ins” directory, which is probably a pretty good place to save these. For this post, I’m going to save mine as “guj_formulas.xla”.

Now we just need to register our file as an add-in. This will tell Excel to open this file each time Excel is started. So to follow through with the previous metaphor, every time Excel gets ready to work, it grabs its trusty spiral notebook with all of your formulas in it.

To do this, go to Tools -> Add-Ins…

Click “Browse”, and double click your file. It adds it to the list of Add-Ins, and goes ahead and checks it for you.

And that’s it! Every time you open excel you can use the “stupid_forumla” formula. Alternatively, you can enter the VBA window at any time to view the formulas you have saved as an add-in, and alter or add to them. You will need to save your work using the save function *on the VBA window* in order to realize these changes in future instances of Excel.

Have fun with it, and remember, Excel works for you, not the other way around.

A couple of warnings, or at least, considerations:

In my office, spreadsheets are usually meant to be passed around – and that means that formulas must be reproduceable on any user’s computer. If a user-defined formula (a custom formula or UDF) is held in an add-in (or in personal.xls) then it is only available to a s/sheet when the sheet is loaded on a computer that has that add-in installed, or has had that formula added to personal.xls. Other than big (usually commercially available) add-ins we keep UDFs in a module attached to the s/sheet that needs them.

I prefer to put

operationslike creating / formatting charts, deleting blank rows, applying finicky formats, or sorting on > 3 sort fields either in personal.xls or in an add-in (if it is to be shared with a group). All of these actions can be performed and then will ‘stand alone’ – it doesn’t matter if the next user has the same facility or not; the s/sheet has been chnaged in the way youi want and will stay that way. If the next user can’t make the same change (really, they just can’t do so as easily) then that’s their problem, not yours.I have had some trouble with this, everything works smoothly with the CAGR forumla, I went through all the steps and got to the very last part where you click it to be an add-in. After doing so I go back to my workbook and it locks the pane. The add-in will work but the rest of the excel file is froze. Or if I get the file to unfreeze then the formula won’t work. I have searched to see if there is a security setting not allowing my add-in to work but it seems that all requirements are accounted for.

Please help.