MacLochlainns Weblog

Michael McLaughlin's Technical Blog

Site Admin

Excel UDF Tutorial

with 26 comments

Microsoft Excel supports macros but it also supports library functions, known as User Defined Functions (UDF). Library functions are less risky than macros because they must return a value to a cell. This is a quick tutorial, mostly for my students, but as usual for anybody who’s interested.

Microsoft Excel User Defined Functions (UDFs) are different than standard VBA macros. They’re behavior is restricted. You can’t access other cells in a workbook, and may only return a value (also known as an expression) to the cell that uses the formula. That having been said, they can dramatically hide the complexity of mega-formulas and remove them from the editing control of users.

Here are the steps to configure your Excel 2007 environment to work with Visual Basic for Applications (VBA) and UDFs. They show you how to display the developer ribbon, open a module for editing, save VBA code into a library (*.xlam file), and add the library file as an “Add-in” library to your Excel 2007 installation. The name of the library will be the same as the Workbook where you created it.

  1. Displaying Excel’s Developer Tab

There are four steps to make this visible. They are:

  1. Choose the Office Button. You’ll see the following:

  1. Click the Excel Options button.

  1. Click the Popular tab if not highlighted (it’s the default). Inside the right side pane, click the Show Developer tab in the Ribbon check box to enable it, and click the OK button to set it.

  1. Click the Developer ribbon tab. Click the left most icon to launch the Visual Basic Editor screen.

  1. Open a Module in the Visual Basic Editor by clicking the Insert menu item and choosing the Module element.

  1. Copy the following function definition into the open Module. The name of the UDF will be the case-sensistive name of the function in the VBA module. That means you’ll now have a newSerialDate function in your list of functions when you click the insert function button.

I struggled to come up with a simple function to illustrate this and how you debug UDFs. A word to the wise, you can’t use a numeric variable inside a MsgBox by itself. If you attempt it, Excel will return a #Value! error in the cell where you call the UDF. You must include a numeric variable as an argument (also known as a call parameter) to the CStr() function. An example is: Cstr(myVariable). You call this sample function by entering:

=monthEnd(cell_reference)

When you pass a date (actually an integer in the spreadsheet), the function handles it as a string. While no data type is assigned the dateIn variable in the example, variables without an explicit data type are always of the Variant data type. The Variant data type is a master data type and can hold any other type, which makes it like the Object data type in Java.

This program parses the string, then uses the DateSerial() function to return it as a number. It’s critical to note that the last line returns the value in the newSerialDate variable, and that variable must always be the function name. Place a single quote mark before all MsgBox() function calls after verifying that the function works.

Public Function newSerialDate(dateIn)
 
  ' Define local variables.
  Dim day As String
  Dim month As String
  Dim year As String
  Dim startPosition As Integer
  Dim endPosition As Integer
  Dim length As Integer
 
  ' Initialize local variables and parse the month from the left.
  startPosition = 1
  endPosition = InStr(startPosition, dateIn, "/")
  month = Left(dateIn, endPosition - 1)
 
  ' This is a debugging tool to track variables during execution.
  MsgBox ("Month [" + month + "]")
 
  ' Shift the start position and parse the day from the middle.
  startPosition = endPosition + 1
  endPosition = InStr(startPosition, dateIn, "/")
  day = Mid(dateIn, startPosition, endPosition - startPosition)
 
  ' This is a debugging tool to track variables during execution.
  MsgBox ("Day [" + day + "]")
 
  ' Get the remainder of the string.
  startPosition = endPosition
  length = Len(dateIn) - startPosition
  year = Right(dateIn, length)
 
  ' This is a debugging tool to track variables during execution.
  MsgBox ("Year [" + year + "]")
 
  ' Return a value from a User Defined Function (UDF) by using
  ' the function name as the return variable.
  newSerialDate = DateSerial(year, month, day)
 
End Function

Aside from the fact that all this parsing isn’t really necessary because the problem is much simpler and cleaner. At least, it becomes so when you understand the breadth of built-in functions in VBA. You can solve the problem by designating the formal parameter as a Double like the example below.

Function newSerialDate(dateIn As Double)
  newSerialDate = dateIn
End Function

Alternatively, you can accept a Variant, which maps to a String. Then, you convert it to a Date like this:

Function newSerialDate(dateIn)
  newSerialDate = DateValue(dateIn)
End Function
  1. Create the following spreadsheet, the formula values are noted below the screen shot. You should be able to copy and past them into the spreadsheet. After you’ve created the spreadsheet, entering a new date in cell A1 causes the UDF to run. When the UDF runs “as-is”, you’ll see message dialog boxes that show values set during runtime.

Spreadsheet Functions
Cell Formula
A2 =newSerialDate(A1)
C1 =VLOOKUP(TYPE(A1),$E$3:$F$7,MATCH("Meaning",$E$2:$F$2,FALSE))
C2 =VLOOKUP(TYPE(A2),$E$3:$F$7,MATCH("Meaning",$E$2:$F$2,FALSE))

  1. You can now save this as an Add In library but comment out those debug MsgBox() function calls. Click the Office Button and click SaveAs in the menu, then accept Excel Workbook initially. When you get to the SaveAs dialog, choose Excel Add-In as the file type. Below is a screen capture of the drop down selection box.

  1. Open a new Excel Workbook. Click the Excel Options button. Click the Add-Ins tab. Inside the right side pane, make sure the Manage drop down says Excel Add-ins before you click the Go button.

  1. Check the Samplefunction check box as shown below. Samplefunction is the name of the Workbook that contains the module, and it is saved as an *.xlam file. Click the OK button to add the library. You’ve now created, and added an Add-In library to your new spreadsheet. It will create a SampleFunction.xlam file in the users directory.

The file is found by default in:

C:\Users\UserName\AppData\Roaming\Microsoft\AddIns

Ranjit asked how you could call a UDF from inside a module. In the answer noted below, I show how to do it with the Unary function:

Function Unary(number As Integer)
  Unary = number + 1
End Function

Then, enter the following ordinary Increment macro:

Sub Increment()
  ActiveCell.FormulaR1C1 = Unary(ActiveCell.Value())
End Sub

Enter a number in a cell, then navigate to Tools -> Macro -> Macros… and choose the increment macro, which increments the value previously in the cell by calling the Unary function.

As always, I hope this is helpful to a few folks.

Written by maclochlainn

May 26th, 2010 at 1:03 am

Posted in Excel 2007,Microsoft Office,Microsoft Windows 7,VBA,VBA Tutorial

Tagged with , , , , , ,

«
»

26 Responses to 'Excel UDF Tutorial'

Subscribe to comments with RSS or TrackBack to 'Excel UDF Tutorial'.

  1. [...] an example LPAD() User Defined Function [...]

    Learn how to LPAD a string in Excel | MacLochlainns Weblog

    31 May 10 at 11:34 am

  2. [...] You can replace the compound logic checking for the value of the second element of a string with the ISLIKE function. [...]

    LIKE operation in Excel

    4 Jun 10 at 11:28 am

  3. Quite informative for a newbie. I am actually looking for help to create a UDF for myself with the purpose of comparing a column containing dates on one sheet with a reference date on another sheet and correspondingly pick up values from a second column on the same sheet as column of dates. Can you help me ??

    Rituraj

    7 Jun 10 at 2:33 am

  4. You need to pass the cell references as call parameters to your UDF. You reference call parameters as an absolute, mixed, or relative reference. You can use a cell from another worksheet with syntax like this for a relative cell reference:

    =SomeUDFName(Sheet2!A1)

    … another workbook with syntax that includes a mixed cell reference, like:

    =SomeUDFName('C:\My Documents\[WorkbookName.xlsx]Sheet1!A$1)

    … or a network qualified path with absolute notation, like:

    =SomeUDFName('\\SomeServer\SharedFiles\[WorkbookName.xlsx]Sheet1!$A$1)

    A UDF can only return a value to a cell, or a value as a call argument to another function call. Only macros have the ability of accessing interactively cells in the workbook during processing. More or less, any UDF is an isolated function that takes inputs, consumes them, and produces an output.

    maclochlainn

    7 Jun 10 at 9:20 pm

  5. will this function displayed in auto formula list

    sundar

    2 Aug 10 at 1:21 am

  6. It appears if you start to type it but not in the generic poplist.

    maclochlainn

    9 Aug 10 at 8:11 am

  7. [...] UDFs can simplify our lives for routine [...]

    UDF for a VLOOKUP

    6 Sep 10 at 3:17 pm

  8. [...] some interesting parameter validation rules for Excel User-Defined Functions (UDFs). I found that optional values are suppressed when you pass a cell reference that points to an empty [...]

    UDF Parameters

    31 Oct 10 at 12:07 am

  9. If I write a really simple function such as:

    ‘get the first position of a single character in a string
    Public Function GetCharPos(ByVal c As String, ByRef s As String) As Integer
    GetCharPos = InStr(1, s, c)
    End Function

    why can’t I call it in the Immediate window with:
    ? GetCharPos(” “, “Mr John Doe”)

    More generally, I can’t see any of my procs and funcs. I’m using Excel 2007.

    Thanks
    Charlie

    Charlie Howard

    5 Jul 11 at 5:20 am

  10. I freaking love u!!! thank u

    betty

    13 Oct 11 at 3:20 pm

  11. Informative description provided
    Thanks

    kj

    17 Nov 11 at 2:19 am

  12. Thanks the best discussion I’ve found anywhere. One question: If I want to modify a UDF, can I simply open it, modify it in the VBA editor, and then save?

    Michael Trombetta

    10 Feb 12 at 3:22 pm

  13. Yes, you can in a workbook. You should always keep a separate copy of the macro libraries, that’s what you can dynamically edit. If you’ve added a copy as a library, you must first remove it (by unchecking the box in the Add-Ins dialog) and then delete it from the physical location on the file system; and then you can add it back with modifications.

    You really shouldn’t have to delete it from my opinion but some folks report that changes don’t seem to take effect. My guess is that they’ve got two files or a cache read problem but I’ve never had the problem. If you follow the steps above you shouldn’t either. A little extra work may save time debugging it.

    maclochlainn

    10 Feb 12 at 8:05 pm

  14. Hi,

    Is it possible to call one userdefined functions inside another user defined function? I have a UDF written by a dfferent user which i want to call in a UDF i am writting.

    I have tried
    call.UDFtoBeCalled(parameter1, parameter 2)
    Buth this did not appear to work

    Thanks in advance,
    DK

    DK

    12 Feb 12 at 5:21 pm

  15. If the function is in the same module, you only need the module name. Just remember, that unlike a subroutine a function returns a value. The means if the UDF returns a string, you’d need to assign its return value to a local variable. I borrowed the following from another post of mine. The first example declares an ISLIKE function.

    1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24

    Function ISLIKE( lookupValue As Variant _
               , containsValue As Variant _
               , Optional caseSensitive As Boolean = 0)
 
  ' Define a logical return variable and assign a default value.
  Dim logicReturn As Boolean
  logicReturn = False
 
  ' Finds a case-insensitive value as a component in a larger value.
  If caseSensitive And _
     lookupValue Like "*" + containsValue + "*" Then
      logicReturn = True
 
  ' Finds a case-sensitive value as a component in a larger value.
  ElseIf Not caseSensitive And _
    UCase(lookupValue) Like "*" + UCase(containsValue) + "*" Then
      logicReturn = True
 
  End If
 
  ' Return the logical value.
  ISLIKE = logicReturn
 
End Function

    A quick example of how to use the ISLIKE function replaces the logic in the following:

    58

    ElseIf Len(LetterGrades(i)) > 1 And Mid(LetterGrades(i), 2, 1) = "-" Then

    With this, where the call to the function simplifies the logic of the ElseIF comparison:

    58

    ElseIf ISLIKE(LetterGrades(i),"-") Then

    In your case, I’ve a hunch it’ll be something like this:

    Dim someVariable AS String * 10
someVariable = SomeUDF(callParameter1, callParameter2)

    Hope this helps.

    maclochlainn

    12 Feb 12 at 10:08 pm

  16. What I’m finding is that Excel (2007) intermittently adds the full pathname of the add-in to the function name when it’s used in a cell formula. Then if the spreadsheet is opened on another computer, even if the other computer has the add-in installed, the function is not recognized. Any idea how to solve this?

    Thanks!

    WesD

    15 Jun 12 at 2:19 pm

  17. thanks ,this is very useful me

    manita sharma

    25 Jun 12 at 2:08 am

  18. I write functions all the time and understand them well. However, when you select User defined to use them I don’t want the bookname!function to display, rather I would like to see the name of the function only. How is that accomplished? Example PERSONAL1.XLSB!getLastPOPrice is what shows but I would like it to show getLastPOPrice, I have seen this from others but the code is protected so I can’t see what they did.

    Jeff

    10 Oct 12 at 1:15 pm

  19. Hi,

    Is it possible to call a UDF into a macro in excel? If yes, please explain how.

    Cheers

    Ranjit

    11 Oct 12 at 8:15 pm

  20. Sure, it’s easy. Create a module and inside the module put this Unary function:

    Function Unary(number As Integer)
  Unary = number + 1
End Function

    Then, enter the following ordinary Increment macro:

    Sub Increment()
  ActiveCell.FormulaR1C1 = Unary(ActiveCell.Value())
End Sub

    Enter a number in a cell, then navigate to Tools -> Macro -> Macros… and choose the increment macro, which increments the value previously in the cell by calling the Unary function.

    Hope that’s what you wanted. :-) By the way, I added it in the base blog post too.

    maclochlainn

    11 Oct 12 at 11:27 pm

  21. Jeff, You need to put it into a Module, and add the module to the environment. That’s how folks distribute UDF in organizations to make them transparent. That way they’re not embedded inside a specific Excel Workbook.

    maclochlainn

    11 Oct 12 at 11:37 pm

  22. Michael, Yes you can simply open it, modify it in the VBA editor, and save it when a standalone in the Workbook. If you’ve added it in as a library, you should remove the module from the library, open a non-library copy and put the changes in that non-library copy, and then put a copy of the module back in the library.

    maclochlainn

    11 Oct 12 at 11:44 pm

  23. Hello,

    These discussions look like they might be close to the problem I am having!

    I have a subprocedure and a function in the same module. The function accepts arument values from the subprocedure, but when the function finishes running, the value calculated by the function is not passed back into the subroutine:

    Here is the code with the sub and functions. Your help is appreciated!

    1
2
3
4
5
6
7
8
9
10
11
12
13
14

    Sub ABC()
  Dim column As Integer
 
  column = Finder("Buyer", 1)
 
  'the function finds the column I am looking for but
  'how do i call the value of column from the function
  'back to the sub?

  MsgBox (column)
 
'the value of column is always 0 for some reason.

End Sub
    1
2
3
4
5
6
7
8
9
10

    Function Finder(word As String, column As Integer) As Integer
  MsgBox (column)
  Cells(2, column).Select
  Do Until Cells(2, column) = word
    column = column + 1
  Loop
  MsgBox (column)
 
'how do I send it back to the subroutine?
End Function

    david

    27 Dec 12 at 9:23 am

  24. David,

    Sorry for the delay in replying but you caught me in a very busy week. Basically, you can’t return a value from a SUB but you can from a FUNCTION. The key is to assign the value that you want to return to the name of the function.

    You would assign a return value like the following at the end of the function:

      Finder = column
End Function

    Hope that helps …

    maclochlainn

    15 Jan 13 at 8:30 pm

  25. [...] posted a comment on my Excel UDF Tutorial [...]

    Excel VBA Do Until Loop

    15 Jan 13 at 9:55 pm

  26. On further thought, I think you want a solution to navigating a range. If that’s the case here’s how you do it.

    maclochlainn

    15 Jan 13 at 9:55 pm

Leave a Reply