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
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.