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)
FunctionName = Whatever you want the function to return
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
2 Replies to “VBA: How to Write Your Own Function”
you can store your functions within an Excel Addins (xlam), so it will be available every time you open Ms Excel l
LikeLiked by 1 person
LikeLiked by 1 person