MacLochlainns Weblog

While the VLOOKUP and HLOOKUP functions are powerful tools in Excel, they limit their search to the first column or row of a range. This post shows you how to leverage the COUNTIFS function to perform an inclusive between search against the first two columns or rows of a range.

Moreover, the VLOOKUP function searches the first column of a range and looks for an approximate or exact match. The approximate match search throws an exception when the lookup key’s value precedes the first item in the range, and picks up the row immediately less than the lookup key. This type of search requires two condition. The data must be presorted into an ascending order for the sort column, the range must be contiguous, and two matching keys shouldn’t exist. That means that each search column or row cell points to the row of interest for any search key greater than it and less than the next. An exact match search finds the row that matches the lookup key and throws an error when there isn’t an exact match.

Neither of these allow for range searches between non-contiguous sets, like the one below. The date ranges where a value should be found are from the 16th of a month to the end of a month rather than the range between the 16th of one month to the 15th of the next. While this could be done by structuring a row with zeros the gap periods, a more effective solution is possible by using the COUNTIFS. At least, this is true from Excel 2007 forward.

The solution to this problem starts with recognizing how a COUNTIFS works. The COUNTIF provides the opportunity to compare a range of values against a single value, and the COUNTIFS allows multiple comparisons of values against ranges of values. The COUNTIFS function returns the number of matches that meet all conditions. Therefore, when a value is found in only one of the ranges the COUNTIFS function returns a 1, and when a value is found n times it returns n as a number.

The formula in cell F2 checks for the number of times the value in F1 exists:

=COUNTIFS($A$1:$A$13,"<="&F1,$B$1:$B$13,">="&F1)

If you evaluate when the foregoing function returns 1 before performing a VLOOKUP function, you can guarantee a match within a non-contiguous range of values. That formula is:

=IF(COUNTIFS($A$1:$A$13,"<="&F1,$B$1:$B$13,">="&F1)=1,VLOOKUP(F1,A2:C13,3),0)

Hope this helps some folks, as always …

I had a chance (30 minutes) to play with Excel 2011 on the Mac OS X today. I was shocked to discover that Excel 2011 didn’t appear to support User-Defined Functions (UDFs) like Excel 2010 for Windows. My understanding was that this release would be one where it implemented Visual Basic for Applications (VBA) like Windows. Initially I thought it didn’t but I bought my own copy, did a full install with Visual Basic, and it appears that Microsoft has delivered. Oops, my bad for assuming the machine I played on originally had a solid installation. It appears to have had only a standard installation.

Watch out because UDFs fail with a #NAME! error on a standard install of Excel 2011. While they’re found in the Insert Function dialog in both cases, they only appear to work with a full installation. The downside for Insert Function is that, like Excel 2008, it has no quick poplist to narrow the function choices to groups. We have the big list once more.

Here are my test functions:

Function hello()
  hello = "Hello World!"
End Function
 
Function hellowhom(nameIn As Variant)
  hellowhom = "Hello " + CStr(nameIn) + "!"
End Function

I think I found the trick to get Excel 2011 recognize and run User-Defined Functions. Make sure you do a custom installation and check Visual Basic for Application. Anyway, you can test these functions like that shown below. Column A contains the text of the formulas (a preceding single quote does that), and column B contains actual function calls.

Enabling the Developer ribbon took a few moments when I got my own copy. I figured that updating this was better than making a new post on the blog and linking them. It’s a three step process.

1. Navigate to the Excel menu item and select Preferences…, as shown below.

2. In the Excel Preferences shown below, click the Ribbon icon.

3. In the Show or hide tabs, or drag them into the order you prefer: box shown below, enable the Developer checkbox.

It’s awesome, now accountants and economists can switch to Mac OS X without having to host a virtual machine with Microsoft Excel.

While discussing the limitations of wild card comparisons in the Microsoft Excel’s IF function, I resolved to share my ISLIKE function. It acts like the LIKE operator in SQL. You can’t use the word like as the function name because it’s a reserved word in Visual Basic for Applications (VBA).

The function lets you compare any string to see if it is contained in another string. The default mode is case-insensitive, and the override mode case-sensitive. The optional third element lets you override the default comparison method.

You should remember or note that a number 0 is always evaluated as false, and any other number (negative or positive) is always true. You can put the ISLIKE function in a macro sheet or an Add-in library. I recently posted instructions on how to create your own Add-in library.

Here is the VBA code for the user defined 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
25
26
27

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

Line numbers 12 and 19 use the "*", which is equivalent to the regular expression of "\.*". I’ve no idea why they made this choice, and it’s not well explained on Microsoft’s web site covering SmartTags.

You can shorten the program and make it slightly less efficient by performing the case sensitive match twice. The following doesn’t rely on nested block IF statements. You also must change the third parameter to a Boolean or the Not operator won’t work correctly.

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

Somebody asked me, “How this can be useful?” That meant from their perspective, how is it useful beyond a comparison of cell values. A quick example of that additional utility can be demonstrated from an early post. In that post on replacing a VLOOKUP function with a UDF, there’s compound logic used to find the occurrence of a substring in a string. The ISLIKE function can simplify that logic.

Here’s that snippet of the code from that referenced post. The snippet evaluates whether the base string is greater than one character and checks the second character of the string to see if it is a - (minus sign).

58

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

You can replace the compound logic with a call to the LIKE function. It would replace line 58 as follows:

58

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

The logic of the function examines to see if the first parameter contains the second parameter. This is what is meant by like. In the following illustration, you examine if a string contains a substring (or is like a substring). The cell A2 is also assigned a Range name. Range names can increase the readability of your models and functions. All but the last one perform case insensitive comparisons that find whether the substring in cell A2 is found with cell range of the B5:B10. The B10 formula performs a case sensitive match.

Another logic comparison uses grades to see if a minus sign is found in them. The example compares the cell values in A1 and A2 against the B1 and B2 values. Returning true for one and false for the other cases in column C of the respective rows.

Here are the calls to the ISLIKE user defined function. The first one is case-insensitive and the second case-sensitive, but there’s no case sensitivity with a minus sign, -.

As always, I hope this helps. If you’ve comments or alternative approaches, please let me know.

Microsoft Excel has many Visual Basic for Application (VBA) features that are seemingly not well understood. One of these features is passing a range value into a VBA procedure (a Sub) or function. The following example demonstrates how to pass a dynamic range to a local multiple dimensional array, and process the uploaded data in a VBA function.

My sample spreadsheet looks like the screen shot below. The formula call is in cell A11 and the text of the formula is in cell B11.

I left a debug MsgBox() call that demonstrates how you size a range. This shows the range above based on zero-based numbering, which means 5 rows are reported as 4 rows because 0 contains a row, and 3 columns are reported as 2 columns for the same reason. If the dialog looks strange to a Windows user, that’s because it’s one generated on a Mac OS X running Excel 2011. 🙂

I kept this as simple as possible to demonstrate the how to do this. Unfortunately, feedback required adding more extensive comments and making it a bit more bulletproof on concepts. The variable names were chosen to help read the syntax. Two loops are used to demonstrate (a) how you assign the range values to a multidimensional array, and (b) how you read the array values in your VBA code.

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62

Function dynamicArray(lookupValue As String, table As Range)
 
  ' Define the row starting cell, Row n maps to the first row where the range is found in the spreadsheet.
  ' Row 1 is the first row and 1,048,576 is the last possible row.
  Dim rowStart As Integer
  Dim rowSize As Integer
 
  ' Define the column starting cell, Column A maps to 1, Column B maps to 2, et cetera.
  ' Column A is the first column and XFD is the last possible column.
  Dim columnStart As Integer
  Dim columnSize As Integer
 
  ' Create a dynamic multiple dimension array without any physical size.
  Dim multidimensionArray As Variant
 
  ' Define and declare a local returnValue variable.
  Dim returnValue As Boolean
  returnValue = False
 
  ' Assign the starting row and column values, and the length of values.
  ' Since you need to add the row and column to the starting grid coordinates, you need to use 0-based numbering,
  ' which means you subtract one from the length.
  rowStart = table.Row
  rowSize = table.Rows.Count - 1
  columnStart = table.Column
  columnSize = table.Columns.Count - 1
 
  ' This demonstrates that the range starts in the row and column, and
  ' the length and width of the multiple dimension array.
  ' ----------------------------------------------------------------------
  ' Insert single quotes for the next two lines to suppress testing the program with variables.
  MsgBox ("(RowStart [" + CStr(rowStart) + "], (ColStart [" + CStr(columnStart) + "]) " + _
    "(RowSize [" + CStr(rowSize) + "] ColSize [" + CStr(columnSize) + "])")
  ' ----------------------------------------------------------------------
 
  ' Redimension the arrays maximum size, rows first, columns second.
  ReDim multidimensionArray(rowSize, columnSize)
 
  ' Read through the range and assign it to a local and dynamically sized array variable.
  ' An important note to those unfamilar with the Cells function, it works on the active worksheet and uses two
  ' parameters, the absolute row and column number or relative row and column numbers. In this sample, the
  ' easiest solution is to use absolute row an dolumn numbers.
  For i = 0 To rowSize
    For j = 0 To columnSize
      multidimensionArray(i, j) = CStr(Cells(rowStart + i, columnStart + j))
    Next j
  Next i
 
  ' Read through the local variable range and view the content set.
  For i = 0 To rowSize
    For j = 0 To columnSize
      ' Check if the lookupValue has been found and return true.
      If lookupValue = CStr(multidimensionArray(i, j)) Then
        returnValue = True
        Exit For
      End If
    Next j
  Next i
 
  ' Return a Boolean value: true when found and false when not found.
  dynamicArray = returnValue
End Function

You would call it with syntax like that below. The range can be any valid range value in Excel, which is any two cell references separated by a colon.

=dynamicArray("LookupString",A1:G5)

As always, I hope this helps others looking for a way to perform this task natively in Microsoft Excel. As an addendum, I thought the absence of a code example for VB on the MSDN web page was quite interesting.

A colleague asked how a User Defined Function (UDF) could simplify their grading. They were using a VLOOKUP() function and range component that they’d copy into spreadsheets generated from Blackboard.

You typically want a letter grade before you transcribe it into the student record system. What you usually start with is a percentage and a defined range of percentages that map to letter grades. While you can solve that probelm with a VLOOKUP() function, is it the best way. I’d suggest this type of activity is ideally suited to a UDF solution.

The VLOOKUP() function call in cell C2 is:

=VLOOKUP(B2,$E$2:$G$13,MATCH("Letter Grade",$E$1:$G$1,FALSE),TRUE)

A User Defined Function (UDF) can replace the VLOOKUP() function, the nested MATCH() function, and lookup range. You create a record type, like VirtualLookup, which must be defined outside of a subroutine or function.

The letterGrade() UDF call in cell C2 is much simpler:

=letterGrade(B2)

You can refer to the following blog post for clarification on how to create a UDF. UDFs can simplify our lives for routine operations. If you’re not interested in the programming, just copy it into your Visual Basic for Application library and it should work.

These letterGrade examples require a custom data type. It must be defined before any function or subroutine in a module. It’s generally a good idea to make these available in other modules by making them Public.

' Define a record type (must happen outside of a function or sub block.
Type VirtualLookupRecord
  lowerLimit As Double
  upperLimit As Double
  letterGrade As String * 2    ' A variable length string up to 2 characters.
End Type

For those interested in understanding how to program this UDF. You leverage that User Defined Type (UDT) when you define the function. In this example, you create a fifteen element array of the record structure. The example defines an array with 1-based numbering index. The default for Microsoft Excel is 0-based numbering. You would define a 0-based numbered array of fifteen elements like the one in the example.

Here’s the complete function that you can copy into your spreadsheet.

Dim VirtualLookup(15) As VirtualLookup

Since the formal parameter of the function is optional, you need an if-block to replace a null value with a zero. Then, you need a loop to read through the array and find where the call parameter value is within the lower and upper grade range values. When it finds a match, it assigns the letter grade to a variable and then breaks out (exits) the loop. If you implement this, don’t forget to comment out the debugging MsgBox() function call.

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73

' Define a function to convert a percentage into a letter grade.
Function letterGrade(Optional percentage As Double = 0)
 
  ' Define a return variable
  Dim grade As String * 2
 
  ' Define a single dimension array of a UDT (record)
  Dim VirtualLookup(1 To 12) As VirtualLookupRecord
 
  ' Record initialization
  VirtualLookup(1).lowerLimit = 0.93
  VirtualLookup(1).upperLimit = 1#
  VirtualLookup(1).letterGrade = "A"
  VirtualLookup(2).lowerLimit = 0.9
  VirtualLookup(2).upperLimit = 0.93
  VirtualLookup(2).letterGrade = "A-"
  VirtualLookup(3).lowerLimit = 0.87
  VirtualLookup(3).upperLimit = 0.9
  VirtualLookup(3).letterGrade = "B+"
  VirtualLookup(4).lowerLimit = 0.83
  VirtualLookup(4).upperLimit = 0.87
  VirtualLookup(4).letterGrade = "B"
  VirtualLookup(5).lowerLimit = 0.8
  VirtualLookup(5).upperLimit = 0.83
  VirtualLookup(5).letterGrade = "B-"
  VirtualLookup(6).lowerLimit = 0.77
  VirtualLookup(6).upperLimit = 0.8
  VirtualLookup(6).letterGrade = "C+"
  VirtualLookup(7).lowerLimit = 0.73
  VirtualLookup(7).upperLimit = 0.77
  VirtualLookup(7).letterGrade = "C"
  VirtualLookup(8).lowerLimit = 0.7
  VirtualLookup(8).upperLimit = 0.73
  VirtualLookup(8).letterGrade = "C-"
  VirtualLookup(9).lowerLimit = 0.67
  VirtualLookup(9).upperLimit = 0.7
  VirtualLookup(9).letterGrade = "D+"
  VirtualLookup(10).lowerLimit = 0.63
  VirtualLookup(10).upperLimit = 0.67
  VirtualLookup(10).letterGrade = "D"
  VirtualLookup(11).lowerLimit = 0.6
  VirtualLookup(11).upperLimit = 0.63
  VirtualLookup(11).letterGrade = "D-"
  VirtualLookup(12).lowerLimit = 0#
  VirtualLookup(12).upperLimit = 0.6
  VirtualLookup(12).letterGrade = "F"
 
  ' Read through the possible lookup array values.
  For i = 1 To (UBound(VirtualLookup) + 1)
 
    ' Assign a grade if the percentage criterion or criteria match.
    If percentage > VirtualLookup(1).lowerLimit Then
      grade = VirtualLookup(1).letterGrade
      ' Exit the loop.
      Exit For
    ElseIf percentage > 0 And _
           percentage >= VirtualLookup(i).lowerLimit And _
           percentage < VirtualLookup(i).upperLimit Then
      grade = VirtualLookup(i).letterGrade
      Exit For
    Else
      grade = "UW"
    End If
 
  Next i
 
  ' A debug message (remark out for deployment).
  MsgBox ("Completed [" + grade + "]")
 
  ' Return the letter grade.
  letterGrade = grade
 
End Function

Somebody asked for a variable array set by the input parameters. That’s a bit more programming and requires understanding the two types of array initialization and the difference between 0-based and 1-based numbering systems. Since this program assigns a constructed Array type to a Variant data type variable, 0-based arrays are consistently used in the example. That’s a change from the foregoing example.

Here is a function that lets you set the hundredth limits to something other than the traditional .x3 and .x7. It substitutes default values if you attempt to enter something greater than a tenth for either limit.

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
89
90
91
92
93
94
95
96
97
98
99
100
101
102
103
104
105
106
107
108
109
110
111
112
113
114
115
116
117
118
119
120
121
122
123
124
125
126
127
128
129
130
131
132
133
134
135
136
137
138
139
140
141
142
143
144
145
146
147
148
149
150
151
152
153
154
155
156
157
158
159
160
161
162
163
164
165
166

' Define a function to convert a percentage into a letter grade.
Function letterGrade(Optional percentage As Double = 0, _
                     Optional minusTenth As Double = 0.03, _
                     Optional plusTenth As Double = 0.07)
 
  ' Define a variable length string variable.
  Dim grade As String * 2
 
  ' Define local variables.
  Dim minusInverse As Double
  Dim plusInverse As Double
  Dim tenth As Double
 
  ' Assign value to counter.
  Dim counter As Double
 
  ' Define a single dimension array of a UDT (record).
  Dim LetterGrades As Variant
  Dim VirtualLookup(0 To 11) As VirtualLookupRecord
 
  ' Fix incorrect numeric data entry of lower bound.
  If minusTenth >= 0.1 Then
    minusTenth = 0.03
  End If
 
  ' Fix incorrect numeric data entry of upper bound.
  If plusTenth >= 0.1 Then
    plusTenth = 0.07
  End If
 
  ' Assign values to local variables.
  counter = 1#
  minusInverse = 0.1 - minusTenth
  plusInverse = 0.1 - plusTenth
  tenth = 0.1
 
  ' Initialize letter array.
  LetterGrades = Array("A", "A-", "B+", "B", "B-", "C+", "C", "C-", "D+", "D", "D-", "F")
 
  ' Use the array of letter grades to dynamically assign lower and upper bounds.
  For i = 0 To UBound(LetterGrades)
 
    ' There are three models for grades.
    ' -------------------------------------------------------------------
    '  [If]    =[A]     Has two possible values, a + or unadorned.
    '  [ElseIf]=[B,C,D] Have three possible values, a +, -, or unadorned.
    '  [ElseIf]=[F]     Has only an unadorned.
    ' -------------------------------------------------------------------
    If Left(LetterGrades(i), 1) = "A" Then
 
      ' The grade is one character for an unadorned grade.
      If Len(LetterGrades(i)) = 1 Then
        VirtualLookup(i).lowerLimit = counter - minusInverse
        VirtualLookup(i).upperLimit = counter
        VirtualLookup(i).letterGrade = LetterGrades(i)
 
      ' The grade is more than one character and second character a minus.
      ElseIf Len(LetterGrades(i)) > 1 And Mid(LetterGrades(i), 2, 1) = "-" Then
        VirtualLookup(i).lowerLimit = counter - tenth
        VirtualLookup(i).upperLimit = counter - minusInverse
        VirtualLookup(i).letterGrade = LetterGrades(i)
      End If
 
    ElseIf Left(LetterGrades(i), 1) = "B" Or _
           Left(LetterGrades(i), 1) = "C" Or _
           Left(LetterGrades(i), 1) = "D" Then
 
      ' The grade is one character for an unadorned grade.
      If Len(LetterGrades(i)) = 1 Then
        VirtualLookup(i).lowerLimit = counter - minusInverse
        VirtualLookup(i).upperLimit = counter - plusInverse
        VirtualLookup(i).letterGrade = LetterGrades(i)
 
      ' The grade is more than one character.
      ElseIf Len(LetterGrades(i)) > 1 Then
 
        ' The second character is a plus.
        If Mid(LetterGrades(i), 2, 1) = "+" Then
          VirtualLookup(i).lowerLimit = counter - plusInverse
          VirtualLookup(i).upperLimit = counter
          VirtualLookup(i).letterGrade = LetterGrades(i)
 
        ' The second character is a minus.
        ElseIf Mid(LetterGrades(i), 2, 1) = "-" Then
          VirtualLookup(i).lowerLimit = counter - tenth
          VirtualLookup(i).upperLimit = counter - minusInverse
          VirtualLookup(i).letterGrade = LetterGrades(i)
        End If
 
      End If
 
    ElseIf Left(LetterGrades(i), 1) = "F" Then
 
      VirtualLookup(i).lowerLimit = 0#
      VirtualLookup(i).upperLimit = counter
      VirtualLookup(i).letterGrade = LetterGrades(i)
 
    End If
 
    ' Debug message demonstrating the changed values of the dynamically
    ' constructed array (remark out for deployment).
    ' MsgBox (" Counter [" + CStr(counter) + _
    '         "] Index [" + CStr(i) + _
    '         "] Grade [" + CStr(LetterGrades(i)) + _
    '         "] LLimit [" + CStr(VirtualLookup(i).lowerLimit) + _
    '         "] ULimit [" + CStr(VirtualLookup(i).upperLimit) + "]")
 
    ' Increment the tenth value when letter grades change, like A to B, et cetera.
    If LetterGrades(i) = LetterGrades(UBound(LetterGrades)) Then
 
      ' Force a loop exit to avoid reading past the last index value.
      Exit For
 
    ElseIf Not Left(LetterGrades(i), 1) = Left(LetterGrades(i + 1), 1) Then
 
      ' Decrement the tenth placeholder.
      counter = counter - tenth
 
    End If
 
  Next i
 
  ' Read through the possible lookup array values.
  For i = 0 To UBound(VirtualLookup)
 
    ' Assign a grade if the percentage criterion or criteria match, and
    ' force loop exits until the last element of the array is read.
    ' -------------------------------------------------------------------
    '  [If]     Assumes an A is always the first letter.
    '  [ElseIf] Handles all numbers greater than zero.
    '  [Else]   Assumes a zero or null indicate an unofficial withdrawal.
    ' -------------------------------------------------------------------
    If percentage > VirtualLookup(0).lowerLimit Then
 
      ' Assign grade, alsways the first element of the array.
      grade = VirtualLookup(0).letterGrade
 
      ' Force a loop exit when match found.
      Exit For
 
    ElseIf percentage > 0 And _
           percentage >= VirtualLookup(i).lowerLimit And _
           percentage < VirtualLookup(i).upperLimit Then
 
      ' Assign grade.
      grade = VirtualLookup(i).letterGrade
 
      ' Force a loop exit.
      Exit For
 
    Else
 
      ' Assign grade.
      grade = "UW"
 
    End If
 
  Next i
 
  ' A debug message (remark out for deployment).
  ' MsgBox ("Completed [" + grade + "]")
 
  ' Return the letter grade.
  letterGrade = grade
 
End Function

I hope this helps folks that have to perform grading activities in Excel.

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

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

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

4. 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.

=monthEnd(cell_reference)

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

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

Function newSerialDate(dateIn)
  newSerialDate = DateValue(dateIn)
End Function

5. 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.

6. 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.

7. 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.

8. 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.