If you’re looking to make the jump from being a regular old Excel user to being a poweruser, one of your first steps should be learning how to write a custom Excel function. It will expand what you’re able to accomplish with Excel, and genuinely add to your productivity.
Why Use a Custom Excel Function
Here are several reasons off the top of my head:
- You need to perform a calculation that relies on complex logic (AND, IF, OR operators). Excel’s syntax isn’t great for this sort of thing.
- There’s a calculation from your industry that you repeatedly find yourself using that isn’t one of Excel’s built-in functions.
- You’re writing a larger piece of VBA code, and need to segment out calculations.
The example I’m going to demonstrate doesn’t fall under any of those 3 categories, meaning there are many more uses than the ones I just outlined!
Here’s what I’m trying to accomplish: I need to count the number of significant digits for a given floating point (decimal) number. Significant digits can play a critical role in data analysis, and Excel doesn’t have a built-in function that I can use.
Enable the Developer Tab
1. The first step, if you’ve never written a custom function before (sometimes referred to as a “macro”, within the community), is to enable Excel’s DEVELOPER tab. You don’t technically need this tab to write VBA code, but you’ll eventually find it useful.
Right click anywhere on the top ribbon and select Customize the Ribbon.
Next, enable the Developer tab.
Open Visual Basic
2. Open the Visual Basic for Applications (that’s VBA for short) dialog by hitting Alt+F11
3. There’s a lot to explain about exactly how to write a function at this point, and I’ll definitely be getting into some of that on future posts, but what you want to do is to create a new Module for your function to reside within. To do that, go to Insert > Module.
You’ll see a folder title “Modules” pop up under your project navigation toolbar, and it will have a “Module 1” selected underneath it.
4. Now for the shortcut where I don’t explain exactly how to write the code, I just throw it up there and show you it works.
Function COUNTSIG(num As String) 'Take out the period num = Replace(num, ".", "") 'Remove leading zeros (excel will remove the trailing) Do While StrComp(Left(num, 1), CStr(0)) = 0 num = Right(num, Len(num) - 1) Loop COUNTSIG = Len(num) End Function
5. Most importantly, now you can use the function as if it came with Excel all along!
One thing that should be made clear here is that this module, and the function within it, are children of the specified Excel file only!
If you were to close that file without saving it, your custom Excel function would be lost. Likewise, if you try to open a new Excel file, this function wouldn’t be available to that file. If you take your saved file and send it to a colleague, they’ll open the spreadsheet and have the ability to use your function. This VBA code travels with the Excel file.