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.

Customize the Ribbon

Next, enable the Developer tab.

Enable Developer

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.

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.

Module 1

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.

Writing the Function

For copy/paste-ability:

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!

Use the Function

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!

Child Of File

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.