<

[Product update] Infrastructure Analysis Tool is now available with Business Accounts.Learn More

x

Creating your own Excel Formulas and doing the impossible

Published on
12,611 Points
3,011 Views
21 Endorsements
Last Modified:
Approved
Community Pick
Martin Liss
Over 40 years of programming experience. Expand my "Full Biography" to see links to some articles I've written.
This article describes how to create your own Excel formula when there isn't a built-in formula that meets your needs.
If you are like me then you’ve occasionally found situations where there is no formula (also called a function) in Excel that meets your needs and so you write a macro (or get help writing one) that does the job. That’s good but unless the macro is triggered by some event in Excel like selecting a new cell or selecting a new sheet then you need a manual approach to running it, like a button or a shortcut key. Formulas on the other hand run automatically when the data that they are associated with changes and so it would be nice if you could create your own formula, and as the title of this article says, you can.

The basics


The macros mentioned above are a type of Visual Basic for Applications (VBA) code and to create them Excel provides the Visual Basic Integrated Design Environment (the IDE). Two of the main types of code are Subs which don’t return a value when executed and Functions which do, and we are going to use the latter to do what we want. 

Fortunately, Excel allows us to write functions that can be used in exactly the same way as the built-in functions. These functions are called User Defined Functions or UDFs. In order to write a UDF you'll need a basic understanding of how to write VBA code. This article will provide some of that information and if you need more then there are several good articles here that will help.
 

UDFs are created in the IDE and to get there you press Alt+F11. Once there, right-click on your workbook name in the "Project-VBAProject" pane (at the top left corner of the editor window). If you don’t see an existing module, or if you want to add a new one, then select Insert -> Module from the context menu.

Creating a UDF


To begin, select the module where you want the new UDF to reside.

UDFs like all functions have a name, an argument list, and a return type. Arguments are values passed to the function and with a UDF they are usually ranges. The generic form of a Function declaration is

[ScopeKeyword] Function FunctionName (Arg As DataType,……….) As ReturnType

Open in new window


ScopeKeyword is an optional value that defines the scope of where the function can be used. The allowed values are Public which sets the functions scope to the entire workbook including all the worksheets, and Private which restricts the scope to the module or sheet where the function resides. If the keyword is omitted the function is treated as Public so it's my habit to not include it. FunctionName is the function/formula name, Arg is the first of what could be several arguments, DataType indicates the type of value that the function expects for the argument, and ReturnType indicates the type of value that the function will return. Note that neither DataType nor ReturnType are required, and if left out then Excel will treat them as the Variant data type which will handle all types of data. The problem with Variants is that they are the largest and slowest of the data types but in most cases you won’t notice. My advice however is to use a data type that specifically describes the data like Range or String whenever possible.

For our first UDF let’s assume that you want to add up the absolute values in a range, so that if there were 4 cells in the range and they contained 1, -5, 6 and 8 the result would be 20. I’m certain that a regular formula could be built to do this, but let’s proceed as if it couldn’t.

We’ll call our UDF SumABS and we’ll build it so that it expects one argument of type Range and returns a value of type Double. I could have used one of several other ReturnTypes like Integer or Currency but our UDF might encounter a situation where the sum exceeds the Integer limit of 32,767 and/or it might encounter values with more than Currency's limit of 4 decimal places to the right. Here’s the UDF which once we get by the little bit of math, is pretty simple.

Function SumABS(r As Range) As Double
Dim cel As Range ' Define a Range variable to be used to
                 ' step through the cells in the input range
Dim dblTotal As Double ' Define a variable to hold the total

' Step through each cell in the range and sum the absolute values
For Each cel In r
    ' Abs is a built-in math function that returns the
    ' absolute value of a number. The absolute value is
    ' how far the number is from 0 and since both 5 and -5 are
    ' 5 away from 0, it gives 5.
    dblTotal = dblTotal + Abs(cel.Value)
Next

' Set the function equal to dblTotal so that it will 
' be returned to the sheet.
SumABS = dblTotal
End Function.

Open in new window


If the values were in cells A1 to A4 then to use the UDF you would enter = SumABS (A1:A4) in a cell or the formula bar.

Note that not only does the ReturnType defines the type of data that the function will return, but it also defines what data type the function is, and since in our case it’s type Double, we don’t need dblTotal and this works just fine.

Function SumABS(r As Range) As Double
Dim cel As Range

For Each cel In r
    SumABS = SumABS + Abs(cel.Value)
Next

End Function

Open in new window


A caution about the function arguments


If you had a situation where you wanted to create a UDF that involved two non-adjacent cells and you knew that, say, the second cell was always 4 cells to the right of the first like with B1 and F1, then you might be tempted to only include a single argument for B1 in the function description and to use .Offset(0, 4) to refer to F1, but that would be a mistake. Writing it that way would work as long as you didn’t just change the value in F1. UDFs only “know” about their arguments so since no argument was provided for F1 the UDF wouldn’t know that F1 had changed, and so the result of the UDF wouldn’t change.

A more complex example


Let’s assume that you need a formula that will return the last row or column of a merged range. Here’s a UDF that I wrote that does just that. Don’t let the ‹ › around the end_col_or_row freak you out – I’ll get to that in a minute.

Function MergedRange(merged_range As Range, Optional ‹end_col_or_row› As String = "") As Long
Select Case UCase(‹end_col_or_row›)
    Case "ENDROW"
        MergedRange = merged_range.MergeArea.Row + merged_range.MergeArea.Rows.Count - 1
    Case "ENDCOL"
        MergedRange = merged_range.MergeArea.Column + merged_range.MergeArea.Columns.Count - 1
    Case "COL"
        MergedRange = merged_range.MergeArea.Column
    Case "", "ROW"
        MergedRange = merged_range.MergeArea.Row
    Case Else
        ' Return a #Value! error
        MergedRange = CVErr(xlErrValue)
End Select
End Function

Open in new window


To use the above as a function, all you need to do is to enter =MergedRange(B7) in the Formula Bar, where B7 is any cell in the merged area. By default the UDF will return the number of the first row, but you could also enter =MergedRange(B7, “endcol”) to get the last column number or =MergedRange(B7, “endrow”) to get the last row number. In a similar fashion the option “col” returns the first column number and “row” returns the first row. Any other optional value returns a #Value! error.

One problem with UDFs is that while when you enter or choose a built-in formula like VLOOKUP and you press the Tab key, IntelliSense shows you this

Article_Intellisense.jpg
which shows the parameters that are expected or optional, but that doesn’t happen with a UDF. The good news is that pressing Ctrl+Shift+A will show you the expected and optional values. There’s still a problem however and that is that if the function header for my UDF was written this way

Function MergedRange(merged_range As Range, Optional end_col_or_row As String = "") As Double

Open in new window


Then after Ctrl+Shift+A you’d see

Article_2.jpg
and you wouldn’t know that “end_col_or_row” was optional. You could rename it something like “optional_end_col_or_row” or “opt_end_col_or_row” but it would be nice if we could put square brackets around it. Unfortunately that’s not allowed but you can come close, and that’s because Excel allows you to use any ASCII character between 128 and 255 in a variable name, so in my UDF I added bracket-ish ASCII characters 139 and 155 to the variable name . Note that they are not the same as “<” and “>” which are ASCII 60 and 62. An easy way to generate those "bracket" characters is to type the two lines beginning with "?" into the Immediate Window, press Return, and copy/paste the results into the UDF. 
chr.jpgNow Ctrl+Shift+A shows you this

Article_3.jpg
and it’s pretty obvious that it’s optional.
 

Doing the impossible


If you’ve seen questions about using a UDF to update two cells you have probably seen the answer as some form of “You can’t do that”. While that’s strictly true, it doesn’t mean it can’t be accomplished with a little bit of code outside the UDF. Consider this problem: You need a formula that will return twice the value of another cell and you also want to place the date of the update in a second cell 6 cells to the right of the first The sticking point is that you don’t know where in the sheet the formula will be used. If you knew the latter piece of information it would be easy to add code in the Worksheet_Change event and use Intersect to determine if the Target cell fell in some range and to update the second cell with today’s date, but since we don’t know the range, what do we do? The following is a solution. (Some clever person may be able to do it some other way, but let’s ignore that.)

In a module add this code:

Option Explicit
Public gdteUpdateDate As Date
Public gstrAddressChanged As String

Function DoImpossible(c As Range) As Double
DoImpossible = c.Value * 2
gdteUpdateDate = Now()
gstrAddressChanged = c.Address
End Function

Open in new window


And in the worksheet where the DoImpossible UDF is going to be used, add this code:

Private Sub Worksheet_Calculate()
    If gstrAddressChanged <> "" Then
        Range(gstrAddressChanged).Offset(0, 6).Value = gdteUpdateDate
    End If
    gstrAddressChanged = ""
End Sub

Open in new window


How does it work?
 


Assume there’s a value of 3 in B10 and we place =DoImpossible(B10) in D10. When you do that the UDF will set D10 to 6 but it will also set gdteUpdateDate to today’s date and set gstrAddressChanged to the address of the cell that was changed. The execution of the UDF causes the worksheet to recalculate and so as long as gstrAddressChanged isn’t blank the date get’s put in the cell six to the right. Tah-Da!

While as with most programming topics there's more to learn about UDFs, I hope that reading this article has given you the information that you need to get started writing your own. If you have any questions that you think are of public interest please post them here. I'd also be happy to answer specific questions sent to me via EE messages.

If you find that this article has been helpful, please click the “thumb’s up” button below. Doing so lets me know what is valuable for EE members and provides direction for future articles. It also provides me with positive feedback in the form of a few points. Thanks!
21
Comment
Author:Martin Liss
  • 3
  • 3
  • 2
  • +5
13 Comments
LVL 67

Expert Comment

by:Jim Horn
Good tutorial that reads well.  Voted Yes.
0
LVL 1

Expert Comment

by:LD16
Excellent article!
0
LVL 9

Expert Comment

by:Christopher Jay Wolff
Nice touch on the ASCII brackets.
0
CompTIA Security+

Learn the essential functions of CompTIA Security+, which establishes the core knowledge required of any cybersecurity role and leads professionals into intermediate-level cybersecurity jobs.

LVL 7

Expert Comment

by:Doug
I just recently wrote a boatload of UDF's for a project where the ASCII and CTRL+SHIFT+A tips would have been extremely useful.  I'll keep this in mind for the next time. I also saw a several posts on-line that requested a way to display arguments but responses typically stated that you can't really do this for UDF's. I'm sure this post would have been a welcome response to those folks. Thanks very much for posting this article.
0

Expert Comment

by:J. Andrew Smith
The brackets can be entered more easily and directly by holding down Alt, and on the numeric keypad typing "139" or "155", then releasing the Alt key.  Another good pair of alternatives are 171 and 187, the double-angle-brackets -- for more check out the Character Map and choose the Windows: Western character set.

What other keystrokes like Ctrl-Shift-A are there in formula-entry mode?
0

Expert Comment

by:J. Andrew Smith
Martin, isn't there a special way to write UDFs such that they're as fully-compiled (and thus fast) as built-in functions, rather than partially compiled?
0
LVL 55

Author Comment

by:Martin Liss
Not to my knowledge but let me know if I'm wrong.
0
LVL 26

Expert Comment

by:Ejgil Hedegaard
Martin.
The description for "A caution about the function arguments" is not entirely true.
A UDF function can update on every worksheet calculation if it is made volatile.
Use "Application.Volatile" in the function code.
So if F1 in the example change by input, or as result of a formula, the UDF result will update.
But only use it when absolutely needed, since the function run on every calculation, also when there are no changes to the input cells.
I agree that it is better to include the cells in the function arguments, but if that is not possible, making the function volatile could be an option.
0
LVL 55

Author Comment

by:Martin Liss
Thanks. I was aware of Application.Volatile but I chose not to mention it because the article was not meant to be comprehensive and also because when a UDF contains Application.Volatile it recalculates every time Excel calculates and that can lead to sluggish performance.
0
LVL 1

Expert Comment

by:Sach44
Hi Martin, what are your thoughts on setting calculation to xlManual and controling the calculation in the spreadsheet(s) in every possible way using Application.Calcutefull ?  Speedwise i am having a lot of success with this method and do not experience any sluggish performance. What can be the downside to this
0
LVL 55

Author Comment

by:Martin Liss
Since I use Excel 2010 and that method is for Excel 2013 and later I can't really comment. Please send me an EE message if you want to discuss this further.
0

Expert Comment

by:J. Andrew Smith
This is for Sach44: a co-worker of mine came up with a very nice way to suppress automatic recalculation and then restore it to its previous state -- after all you might have two procedures, A calling B, and if you blindly set it to manual at the top of each, and automatic at the bottom of each, then it will be automatic right after A has called B.  But, if you set up a local variable in each function to hold its state, you have to remember to include a statement at the bottom to restore it, so now you can't use Exit Sub/Function, and you even have to write up an error-handler to resume at the statement that restores it -- what a pain!

Here's the nice alternative: a class that suppresses auto-calculation when instantiated and restores the state when destroyed:

Class clsCalcSuppressor
Option Explicit

Private FormerStatus As XlCalculation

Private Sub Class_Initialize()

    FormerStatus = xlCalculationAutomatic       ' Establish default

    If Workbooks.Count > 0 Then                 ' With no workbooks open, just mentioning "Application.Calculation" causes type mismatch!
        FormerStatus = Application.Calculation
        Application.Calculation = xlCalculationManual
    End If

End Sub

Private Sub Class_Terminate()
    If Workbooks.Count > 0 Then Application.Calculation = FormerStatus
End Sub

Open in new window

Now all you have to do is include two statements just at the top of any procedure:
Dim CS As clsCalculationSuppressor
Set CS = New clsCalculationSuppressor

Open in new window

...and when the procedure ends by any means, the state is restored, because CS is local and therefore gets destroyed.
(But what I wonder is: why doesn't the one statement Dim CS As New clsCalculationSuppressor work?)

Of course this technique can be used for many other application properties, e.g., the status bar, displaying alerts, enabling events, the mouse cursor, screen updating...
0
LVL 1

Expert Comment

by:Sach44
Hi Andrew.

On my part, I have carefully stored the many original Excel user settings using registry entries upon activation (commandar("ply"), decimal seperator, thousand seperator, xlcalculation, ...), modified them for my needs, and then set them back to the original state on the workbook deactivate_event (that is launched on close and opening a workbook in the same excel instance). Curiously, I have noticed that the orginal usersettings remain if if a new Excel instance is created with my workbook previously openned in its own instance. This appears to work alright for my needs at the moment. Tested using Excel 2013 64 bit and Excel 2010 32bit.

As I am a beginner, I suppose the solution you suggested is quicker; I will give it a try once I get a chance. Thanks!
0

Featured Post

Fundamentals of JavaScript

Learn the fundamentals of the popular programming language JavaScript so that you can explore the realm of web development.

Join & Write a Comment

This Micro Tutorial will demonstrate in Microsoft Excel how to add style and sexy appeal to horizontal bar charts.
How can you see what you are working on when you want to see it while you to save a copy? Add a "Save As" icon to the Quick Access Toolbar, or QAT. That way, when you save a copy of a query, form, report, or other object you are modifying, you…

Keep in touch with Experts Exchange

Tech news and trends delivered to your inbox every month