MacLochlainns Weblog

Michael McLaughlin's Technical Blog

Site Admin

UDF replaces VLOOKUP

without comments

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.

Written by maclochlainn

May 28th, 2010 at 2:25 am

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

«
»