VBA: How to Write Your Own Function

So last week, I learned that VBA buttons are supremely helpful. For moving data between workbooks or breaking down information from a KML file, these are ideal. Especially the KML file importer because it’s basically populating a full on spreadsheet.

But if you want to have a function run very specific math or customized scraping on a string that’s only going to affect one cell, go with the UDF. Sounds like a terrible disease, doesn’t it? In Excel, it stands for User Defined Function.

Step 1: Open the VBA Editor

How do you do that? Go into the Developer tab (I explained how to get that in an old post), and click the little icon with the magnifying glass.

Now you have to get a new module going. Click Insert, then Module.

Step 2: Define the Function

Here’s an example of a function to define. Say you have a column full of number ranges (1-3, 6-10, 12) and so on. Somewhere there needs to be a total number completed from those ranges. This is weirdly difficult with the standard Excel functions because that range is a string, but I want to get a count from it which is normally done with integers ( 1-3, 6-10, 12 = 9). Notice when I say total count, it’s inclusive of all numbers, so I can’t do simple subtraction either. I want 6-10 to count numbers 6, 7, 8, 9, and 10, so the desired result is 5, not 4.

I get that a pre-schooler could do this because it’s literally just counting, but I hate counting. Especially if it’s in a long-ass spreadsheet of doom.

So to the VBA Editor I went. And here’s what I did.

The bare bones of that is just this:

Function FunctionName(Parameter As DataType)
Code…
Code…
Code…
FunctionName = Whatever you want the function to return
End Function

Step 3: Test It Out

This function is called WorkAreaCount, so just type that into the desired cell and plug in the string range (or a cell reference).

Keep in mind, you can only use that function within that particular workbook. Oh, and just in case you ever find yourself in need of a range counter, I’m posting the script below with comments.

Function WorkAreaCount(ran As String)

Dim finalcount As Integer
Dim i As Integer
finalcount = 0
ran = ran & ","
Do While Len(ran) > 0
ran1 = Left(ran, InStr(ran, ","))
If InStr(ran1, "-") > 0 Then
posNum = InStr(ran, "-")
StNum = Val(Left(ran, posNum))
'StNum is the first number in the range
ran = Right(ran, Len(ran) - posNum)
'ran now equals the rest of the string
posNum = InStr(ran, ",")
StENum = Val(Left(ran, posNum))
'StENum now equals the last number of the range
ran = Right(ran, Len(ran) - posNum)
i = StNum
'Set i to Starting number of range
Do While i < StENum + 1
'While i is less than the end number
finalcount = finalcount + 1
'Add 1 to final count
i = i + 1
Loop
Else
finalcount = finalcount + 1
ran = Right(ran, Len(ran) - InStr(ran, ","))
End If
Loop
WorkAreaCount = finalcount

End Function