Tuesday, May 5, 2009

Excel User-Defined Formulas

How to create custom Excel User-Defined Functions using Visual Basic (VBA). Demonstrated will be =FindLastSpacePos and =ReturnLastWord, which act like new Excel Keywords. This is a relatively advanced topic but even if you do not know Visual Basic, this article will guide you through the steps. Do not be afraid to try this; you can't hurt anything and it will be fun.

Additional Related Articles:
Using Excel Functions to Parse City,StateZip fields
SuperTrim <-Go here if you just want the code for SuperTrim, Find First, Find Last

FindLastSpacePos (Returns the numeric position of last space in a phrase or sentence)
ReturnLastWord  (Returns the last word in a phrase or sentence / Find Last Word)


Excel User-Defined Formulas: Tutorial

In Excel it is easy to build horrendous formulas that are so long they become unmanageable and Excel "if-statements" are a famous example. 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 keywords. This moves the complexity out of the spreadsheet and places it within a mini Visual-Basic program.

  • The function is written once but can be called many times
  • Allows for if-statements, nested-ifs and other logic
  • Logic is formatted with indentation; easier to interpret
  • Transportable to other sheets

The techniques illustrated work with either Office XP and newer versions. Illustrations are from Office 2007 and Office XP.  These techniques work in all versions of Office.

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 position of the found-character. For example, if cell A3 contained the text

"Dog and Cat and Moose"

This formula:

=find(" ",A3,1)

The Formula searches for the first-found space in cell A3, starting at position 1 and, in this example, returns a numeric 4. You can try this yourself by typing the formula in cell B3.

What if you needed to find the position of the last space? Excel does not have an obvious 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.

When done, you could type this cell formula in cell (B3): =FindLastSpacePos(A3) and it would return a number (in the example, '16'). The end-result will look like this:

"FindLastSpacePos" ("Find Last Space Position") is not a normal Excel function-name. To build the function, follow these steps, starting with a new, blank sheet:

1. In cell A3, type "Dog and Cat and Moose"
Press Enter.

2. Start the Visual Basic Editor:

In Excel 2010/2013 and newer:
Click File, Options, "Truct Center"
In "Trust Center Settings", "Macro Settings", select "Enable all Macros"
Click OK to return to your worksheet.
Press Alt-F11

In Excel 2007, click top-menu "Developer", then click "Visual Basic" (See illustration, below).

In Excel 2002, click "Tools, Macro, Visual Basic Editor".
(or for either version, press Alt-F11)

3. In the Visual Basic (VBA) editor, highlight your workbook (e.g. "Book1")
Select menu "Insert, Module".

Note a new Module name, probably called "Module1", is added on the tree-diagram on the left
. The module can hold multiple new function; you do not need a new module for each function in the spreadsheet. The name, Module1, is not particularly important. Alternately, you can build the new module in this fashion:

3. Begin coding in the blank editing Window.

In the blank editing screen (Book1 - Module1 (Code)), type this statement, which tells VBA to require declared variable names – this is recommended for all VBA Macros:

"Option Explicit" (no quotes, press enter when done)

4. Create the new "FindLastSpacePos" routine by literally typing this statement on the next blank line (this is typed on one line, no word-wrapping):

Public Function FindLastSpacePos (ByVal passedCell as String) As Integer

Complete the remainder of the function by typing the rest of the routine. Type the text carefully, including upper and lower-case. (Click the illustration for a larger view; click right-x to return or copy from the code-window, below):

Optionally, copy this code, follow these steps:

1. Highlight all of the code below.
IE users may need to scroll to the bottom, then back to the top in order to highlight properly.

2. 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 in the passed-string.
'Use: =FindLastSpacePos(Celladdress c3)

Dim ihpos1 as integer 'Horizontal Position
ihpos1 = InstrRev(passedCell, " ")

'Return the results to the calling function:
FindLastSpacePos = ihpos1
End Function

3. 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 Save Warning, later in this article.

  • "Public Function" means the function is visible to the Excel Spreadsheet. (As an aside, "Private Functions" would only be visible to VBA routines).
  • "ReturnLastSpacePos" is an arbitrary name invented to describe the function and this is the name you will type within 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:
Assuming a test-string is in cell A3 ("Dog and cat and moose"), type this formula in cell B3:


Press Enter.
Results: B3 should report "16": The 16th position in the string is the last-space in the string.

Write a Return Last Word:

Continue the example by writing a second routine that parses the last word from the phrase. The new function, which will be called "=ReturnLastWord", uses the previous function as a subroutine, further demonstrating the power of UDFs.

Admittedly, this is non-sense, but have ReturnLastWord check to see if the last-word is "Moose" and if so, change the spelling slightly. Doing this demonstrates several other concepts. Follow these steps:

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 statement (End Function) and press return a few times, inserting a few blank lines for cosmetic effect.

4. Create a new function called "ReturnLastWord".

Type this logic, including the "Public Function" statement (click the illustration for a larger view; click Back to return):

Code: Or cut and paste the code from here:

Public Function ReturnLastWord (ByVal passedCell As String) As String
Dim ihpos1 as Integer
Dim lastWord as String

ihpos1 = FindLastSpacePos(passedCell)
lastWord = Mid(passedCell, ihpos1 + 1, 9999)

'This if-statement demonstrates more complicated logic
'If LCase(lastWord) = "moose" Then
' lastWord = "Mooses!"
' lastWord = Ucase(lastWord)

ReturnLastWord = lastWord

End Function


* 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, until the end of the string (e.g. 9999 characters).

* For the fun of it, the optional if-statement can look at the substringed word and if it is "moose", make adjustments.

* Return the results with ReturnLastWord = lastWord.

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!". (if you used the optional if-statement)
In cell A3, type other test-text, including single-word sentences and sentences that end with differing text. The routine should always find the last word.

Excel 2007 Save Warning:

When Excel 2007 saves the sheet, it will save it as an ".XLSX" sheet and it will strip the macros out as it saves! When prompted: 'Do you want to save as a macro-safe) workbook', click "No" (counter-intuitive menu-choice). On the next dialog, change the File type from XLSX to XLSM. Or get the the same menu using "File, Save-As". Once set, save the file.
If the macros are stripped, when you next open the sheet, the new functions will display as "#Name?" -- indicating the underlying macro/UDF is missing. Press Alt-F11 and rebuild/re-link the code.

Permanently SAVING MODULE 1

Follow these steps to save the code separately from the sheet; this way you can re-use the same code in any spreadsheet.
1. Alt-F11, locate Modules, 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. For this, I recommend "M800_Util", or any other 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)

To Re-Use the Code in a New Sheet:

1. Alt-F11 for 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.
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 "=FindLastWord()" to test.

Advanced Calls:

If you are writing a function that needs multiple input parameters, pass each value separately, through the parenthesis. Use commas to separate the values.

For example, =MyFunction(A1, A2, A3) would be represented as:

Public Function MyFunction _
(ByVal passedCell1 as String,
_ByVal, passedCell2 as Integer,
_ByVal, passedCell3 as String) as String

Naturally, better names than "passedCell" should be coined: Name, EmployeeNumber, Address.

From the Excel side, you can also pass more complicated values into the downstream function:
=MyFunction(A1, Vlookup(....), B3&B5)


This is a taste of how to write your own functions in Excel. It is helpful to know Visual Basic, but this article should get you started. Writing this same logic in native Excel would be difficult and would probably require multiple columns and long, complicated formulas.

By tucking the logic within a procedural language, the spreadsheet becomes almost readable because it can have if-then-else statements, indentation, and can call other modules and subroutines. Excel's long and horrible formulas can be reduced to a single keyword with some passed parameters.

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

No comments:

Post a Comment

Comments are moderated and published upon review.