Create custom Excel User-Defined Functions using Visual Basic (VBA). UDF's act like new Excel keywords and they are essentially mini-programs that can live within a cell. Instead of writing a horrendously-long formula, replace it with a keyword.
This article demonstrates these functions:
=FindLastSpacePos (find last space position)
=ReturnLastWord
=SuperTrim
=CountWords
This article was originally written for OfficeXP and has been re-written for Excel 365.
Excel User-Defined Formulas: Tutorial
Excel makes it famously easy to build formulas that are so horrendously long they become unmanageable. This article describes a way to tame complicated formulas by writing your own Excel User-Defined Functions.
In other words, Excel has built-in functions, such as "=Sum", "Find", "Mid", "Left." Now you can write your own equal-sign-keywords. This moves the complexity out of the spreadsheet and places it within a mini Visual-Basic program. The VBA program is easier to maintain and easier to read.
Benefits:
- The function is written once but can be called many times
- Allows for if-statements, nested-ifs, switch statements, and other logic
- Logic is formatted with indentations, making it easier to read
- Transportable to other sheets
- Works for any version of Office
- Will blow people's minds when they see how smart you are
Standard Excel Formulas:
As an introduction, spend a moment reviewing Excel's "Find" function.
"Find" locates any character (or group of characters) and returns the first position of the found-character. For example, if cell A3 contained the text
"Dogs, Cats, Mice, and Mooses"
This formula:
=find(" ",A3,1)
searches for and finds the first-found space, starting at position 1. In this example it returns a numeric 6. Try this yourself by typing the formula in cell B3.
But what if last space position is needed?
Excel does not have a native way to do this.
This is where you can write your own function.
Simple User-Defined Function:
In this section, write a user-defined Excel function that locates the "last space" in a string, returning a numeric value - the horizontal position of the space.
Consider the phrase "Dogs, cats, Mice, and Mooses", typed in Cell A3.
In cells B3, C3, and D3, could be these formulas:
=LastSpacePos(A3) = 22
=CountWords(A3), returning "5" or
=ReturnLastWord(A3) returns "Mooses".
 |
| Click for larger view |
"=CountWords", "=FindLastSpacePos", and "=ReturnLastWord" are not normal Excel functions.
Write these new functions using Excel's Visual Basic Editor. Yes, this is an actual programming language. Do not panic.
To build the function, follow these steps, starting with a new, blank sheet:
A. Since Macros can be a virus vector, security has to enable the feature.
In Excel, select File, Options
On the Left-Nav, choose "Trust Center", then "Trust Center Settings"
Choose "Disable VBA Macros with notification"
(in other words, you are prompted before they are allowed).
B. For test data, type in Cell A3, no quotes:
"Dogs, cats, Mice, and Mooses"
Press Enter.
Start the VBA Editor
1. From the new sheet, start the Visual Basic Editor:
Press Alt-F11
(or on the Ribbon, View, "Macros")
2. In the Visual Basic (VBA) editor, left-Nav, highlight your workbook at the top of the tree (e.g. "VBAProject Book1")
Select menu "Insert, Module"
The new Module name, probably called "Module1", is added to the tree-diagram. The module can hold one or more functions. It is typical to have a stack of them within Module1. The name, Module1, is not particularly important.
3. Code in the blank editing Window.
In the blank editing panel (Book1 - Module1 (Code)), type this statement, which tells VBA to require declared variable names – this statement is recommended for all VBA Macros:
"Option Explicit" (no quotes, press enter when done)
4. On the next line down, invent a new function called "FindLastSpacePos" routine by literally typing this statement on the next blank line (type on one line, no word-wrapping):
Public Function FindLastSpacePos
(ByVal passedCell as String) As Integer
Complete the remainder of the function by typing or copying the rest of the routine. Type the text carefully, including upper and lower-case. The words "End Function" mark the end of the routine.
Edit, Copy (or type Control-C)
Option Explicit
Public Function FindLastSpacePos (ByVal passedCell As String) As Integer
'This function returns a numeric value showing the last space position of a cell
'Use: =FindLastSpacePos(Celladdress)
Dim ihpos1 as integer 'Horizontal Position
ihpos1 = InstrRev(passedCell, " ")
'Return the results to the calling function:
FindLastSpacePos = ihpos1
End Function3. Close the Visual Basic editor by clicking the Big-X, in the upper Right corner (no need to "Save;" it saves automatically). This returns you to the spreadsheet. See Office 2007/2010/2016 Save Warning, later in this article.
where:
- "Public Function" means the function is visible to the Excel Spreadsheet. (As an aside, "Private Functions" would only be visible to the VBA routines).
- "ReturnLastSpacePos" is an arbitrary name invented to describe the function and this is the name that will be typed in the Excel sheet (e.g. =ReturnLastSpacePos...).
"passedCell" is an invented name, which represents the first value passed into the function. In the example, =ReturnLastSpacePos(A1) – cell A1 is the first passed parameter and it gets temporarily re-assigned the name "passedCell" once it arrives in the new function.
- "passedCell" must be declared as a String, Integer, "Float", etc. This tells the function what 'type' of value is expected. "Integer" means a non-decimal-number, "Float" means decimals are allowed, and "boolean" means True/False.
- In Excel, the keyword "ByVal" is required for all passed parameters.
- Ultimately, the function "returns" an integer (the answer) to the calling-spreadsheet; note the "As Integer" after the closing parenthesis. In other words, the function itself has a 'type'.
Testing the New Function:
Return to the spreadsheet. Assuming a test-string is in cell A3 ("Dogs, cats, mice, and mooses"), type this formula in cell B3:
=FindLastSpacePos(A3)
Press Enter.
Results: B3 should report "23": The 23rd position in the string is the last-space in the string.
Write a Return Last Word:
Continue the example by writing a second module that parses the last word from the phrase.
The new function is called "=ReturnLastWord" and it uses the previous function as a subroutine, further demonstrating the power of UDFs.
1. Press Alt-F11; returning to the Visual Basic Editor.
2. Confirm "Module1" is highlighted on the tree-side and locate your previously-written code (you should arrive there, by default)
3. Place the editing cursor after the last End Function statement.
Press return a few times, inserting blank lines for cosmetics.
4. Create a new function called "ReturnLastWord".
Type this code, including the "Public Function" statement:
 |
| Click for larger view |
Code: Or cut and paste:
Public Function ReturnLastWord (ByVal passedCell As String) As String
Dim ihpos1 as Integer
Dim strlastWord as String
ihpos1 = FindLastSpacePos(passedCell)
strlastWord = Mid(passedCell, ihpos1 + 1, 9999).Trim()
ReturnLastWord = strlastWord
End Function
where:
* Calculate where the last word is by calling the previously-written "FindLastSpacePos" routine (which returns the numeric position of the last word.
* The "Mid-string" command substrings from the last-space-position plus 1 (which skips the space), parsing until the end of the string (e.g. 9999 characters). I added a .Trim() to remove leading and trailing spaces that might be typed by the end-user.
* On the last statement, return the answer with ReturnLastWord = strlastWord.
5. Close the Editor and return to the sheet.
Testing ReturnLastWord:
In the Excel sheet, move to cell C3 and type this formula, which uses the new function name:
=ReturnLastWord(A3)
Results in Cell C3: "Mooses".
In cell A3, type other test-text, including single-word sentences and sentences that end with differing text. The should always find the last word.
Errors:
If errors are found while testing, the function's name will be highlighted in yellow and an obscure and unhelpful error message displays. Click the square "Stop" icon in the VBA editor to stop the program. Review the code. Re-type the formula in the sheet to test again.
Count Words and SuperTrim:
A routine to count the words in a cell will work most of the time by counting the spaces between words, but it will make a mistake if double-spaces were used. Normally, this could be fixed with a .TRIM(), which removes leading and trailing spaces -- but .Trim() does not work with interior spaces.
Resolve this with a helper routine I like to call "SuperTrim" -- this trims leading and trailing spaces, as well as duplicate interior spaces. Write this function first. I admit this is a sneaky block of code I've written and it is beyond the scope of this article to explain how it works:
As before, below the last "End Function", create a new routine with this code:Public Function SuperTrim(strtextIn, Optional trimChar = " ") As String
'Remove multiple duplicate spaces from the interior of a string (cell)
'Note: this module appends to its own return value, making it quasi-recursive
On Error GoTo LocalError
trimChar = CStr(trimChar)
SuperTrim = CStr(textIn)
SuperTrim = Replace(SuperTrim, trimChar, vbNullChar)
While InStr(SuperTrim, String(2, vbNullChar)) > 0
SuperTrim = Replace(SuperTrim, String(2, vbNullChar), vbNullChar)
Wend
' Delete Leading and Trailing
If Left(SuperTrim, 1) = vbNullChar Then
SuperTrim = Right(SuperTrim, Len(SuperTrim) - 1)
End If
If Right(SuperTrim, 1) = vbNullChar Then
SuperTrim = Left(SuperTrim, Len(SuperTrim) - 1)
End If
LocalError:
'You can also fall into this routine legitimately; not just as an error
SuperTrim = Replace(SuperTrim, vbNullChar, trimChar, , , vbTextCompare)
End Function
Count Words:
The final "Count Words" function calls the SuperTrim function, and then counts the interior spaces:Public Function CountWords(textIn) As Integer
'Counts the number of space-delimited words in a string, including
'leading and trailing words.
'Words are super-trimmed
Dim icount As Integer
Dim i As Integer
textIn = SuperTrim(textIn)
icount = 0
If Len(textIn) > 0 Then
For i = 1 To Len(textIn) Step 1
If Mid(textIn, i, 1) = " " Then
icount = icount + 1
End If
Next i
'Account for single-word strings because Len > 0
icount = icount + 1
Else
'icount = 0 because of zero len
End If
CountWords = icount
End Function
Testing:
Back to the sheet, in an empty cell, use these formulas:
=CountWords(A3)
Add redundant interior spaces in the text text
=SuperTrim(A3)
Note the extraneous spaces are collapsed.
Saving:
When the sheet is saved with a special extension, the macros are packaged with the sheet. Optionally, the macros can be saved separately, so they can be used in any sheet. Follow these steps carefully.
By default, Excel saves sheets as ".XLSX" -- but this will strip-out the macros as it saves!
When prompted: 'The following features cannot be saved in macro-free workbooks." Click "No" (a counter-intuitive menu). On the next dialog, change the "File Type" from XLSX to XLSM (or get the the same menu using "File, Save-As", choosing "Excel Macro xlsm").
Be aware that most email systems block xlsm sheets as attachments because macros can do nefarious things; the cyber-security folks will be nervous.
If by accident the macros are stripped, they are not recoverable and will have to be re-written. Missing macros appear in a newly-opened sheet as "#Name?" -- indicating the underlying macro/UDF is missing.
Permanently SAVING MODULE 1
The macros can be saved externally from Sheet1 and with this, the can be imported and used in any worksheet. This is what I do.
1. Open the Macro VBA Editor with "Alt-F11". Locate Module1
2. Highlight Module1's Name
3. In the Properties Window (below the tree diagram), change the (Name) from 'Module1' to a more meaningful name. I recommend "M800_Util", or any name of your choosing.
4. In the tree-diagram, "other-mouse-click" the newly-renamed M800_Util,
Choose "Export"
5. Browse to a good directory (for example, C:\Data\Source\CommonVB)
6. Accept the filename "M800_Util.bas" (note the .bas - basic - extension)
Importing in a New Sheet:
1. From any blank, new sheet: Alt-F11 to open the Visual Basic Editor
2. On the tree-side, highlight the spreadsheet name (e.g. Book1)
3. Other-mouse-click the sheet's name, choose "Import File"
4. Browse to (C:\Data\Source\CommonVB);
Select M800_Util.bas.
5. Close the VB Editor (The big "X") and return to the sheet.
The new functions are now available for use. In any cell, type "=ReturnLastWord()" to test.
Advanced Calls:
Functions can use multiple input parms. Using commas, pass each value separately, through the parenthesis.
For example, =MyFunction(A1, A2, A3), where cell 3 is a number and the others are strings. In the module, it would be represented as:
Public Function MyFunction _
(ByVal passedCell1 as String, _
ByVal, passedCell2 as String, _
ByVal, passedCell3 as Integer) as String
underscores are a line-continuation flag, making the function's parameters easier to read.
Naturally, better names than "passedCell" should be coined: passedFirstName, passedLastName, passedEmployeeNumber. This example passes three cells. Two are concatenated and the the third (EmployeeNumber) is ignored.
 |
| Click for larger view |
In an Excel formula, the results of a vLookup could be sent into the downsteam function. For example:
=ReturnLastWord(vlookup(...stuff...))
or pass multiple values this way:
=MyFunction(A1, vlookup(....), B3&B5)
Summary:
This is a taste of how to write functions in Excel. Admittedly, it is helpful to know Visual Basic, but this article should get you started. Writing this same logic in a native Excel formula would be nearly impossible.
Tucking the logic within a procedural language, the spreadsheet becomes almost readable because the keywords can be self-explanatory (ReturnLastWord, SuperTrim, etc.). But more importantly, the cells can now contain if-then statements, loops, case-selects, subroutines, and any other complicated logic you can imagine. A long Excel if-statement could be reduced to a simple keyword, with some passed parameters.
Followup:
I found this gem on the net the other day: This standard Excel formula also parses the last word in a string and is very creative in how it works:
=RIGHT(A1,LEN(A1)-FIND("*",SUBSTITUTE(A1," ","*",LEN(A1)-LEN(SUBSTITUTE(A1," ","")))))
There is a tradeoff here: the =FindLastWord function is esoteric and most Excel users do not understand how it works, but I would argue most Excel users do not understand this formula either. If the formula needed to be debugged, which is easier?
Related Keyliner articles:
Using Excel for Raffle-Ticket Drawing: Prizeorama
Excel VLookup - a complete tutorial
Excel Coloring Alternate Rows
Excel Parsing City-State-Zip
Excel Importing Text with Leading Zeroes
VB - Return First Word, Last Word, Supertrim
Using VBA to Send Email
Using Excel to select Raffle Tickets - Prize-orama
