Excel Custom Functions – Creating & Sharing Functions The Easy Way!

Files referred to in this post: Book1.xls EdsAddIns.xlam

The correct way to create, use & share your custom functions is with an Add-In. Luckily, this is easy. All you need to do is save the attachment (Eds Add Ins.xlam) to this email somewhere you can get to from your Excel workstation.

For this example, I used my Desktop:

We’ll take a look inside in a minute, but for now…

1. Save the attached .XLAM file on your Desktop.
2. Open up Excel with a blank workbook.
3. Go to File -> Options:

4. Then Select Add-Ins:

5. In the Add-Ins window, select Browse:

6. Navigate to your Eds Add Ins.xlam file (the one you saved to the Desktop):

…and click OK

7. You now see the file in your list of Add-Ins, and it is checked:

…just click OK, because that is it!

8. Now let’s do a simple test of the function I wrote, and included in the above add-ins file. Create a column for the number grade, and auto-fill it from 100 to 0:

9. Now, put the new formula to work in the cell next to the 100 grade:

…using the formula like this: =LetterGradeFromNumber(A2)

You should see an A+:

…which is correct.

10. Finally, drag/fill from B2 all the way down to the % Grade of 0:

[…]

Pretty cool, eh? I also attached the workbook I used for these screenshots. Now there is something subtle here to note. If you were to use Macros for all this, you would need to save your file as a .xlsm file, and confuse your users with screens coming up warning about macro security, etc. Here, we can put the Add-In file on a server for everyone to use, and as you can see, I used a simple .xlsx file.

You can have as many custom functions as you like in that one .xlam file. Let’s now take a look at how I did this:

1. Open the Add-In file you saved on your Desktop. When you do, Excel will challenge you to accept that there are Macros in it. If you have your security set to always accept Macros, you won’t see this warning. If you do, just click Enable Macros:


2. Once you do, you’ll think Excel is broken, because no worksheet comes up! This is fine, as when programming Excel, we don’t need a worksheet, we use the back-end/guts of Excel for our magic. Click Alt-F11 to bring up the VBA interface:

Hey! That’s one handsome function!

What has happened is really simple. All I did was:

1. I added a Module by right-clicking on VBAProject (Add Ins.xlam) and selecting Insert -> Module.
2. I named that new module (a place for code that all worksheets can access) “Formulas”. I could have named it anything, really.
3. I wrote my code into that module that you see. I will explain the code:

This is the name of the function as you call it from a worksheet. It takes the variable numberGrade, which you supply, and returns a String object (alphabetic, not a number). Which is fine, as we want things like A+, C-, B returned.

I created a variable called “n” to work with, as it was faster to type than numberGrade. I just copied the input value to n.

Then just a little sanity check. We don’t want to deal with mistakes. By the way, by assigning the name of the function a value, I am exiting the function, as in: LetterGradeFromNumber = “Invalid value!”

The rest is really easy. I used the chart/scale below to create a set of circumstances for each letter grade, and the first one that matches is the one that the function is assigned, which like we said then exits the function at that point, returning to the calling cell the String object (the grade).

Now tell me that isn’t cool!!!

For reference, I used this scale:

Your feedback welcome and anticipated.

pat
:)

Files referred to in this post: Book1.xls EdsAddIns.xlam

Leave a Reply

Your email address will not be published. Required fields are marked *