Adding Custom Functions to Microsoft Excel

Posted on April 6, 2009 by Yilmaz Rona — No Comments ↓

Anybody who works extensively with Excel has found themselves in the hell of nested ‘IF‘ functions. This week I will show you how to write a function that you can embed into your formula field for a particular cell. The example I will use is that of a teacher wanting to assign letter grades to a group of students. However, this example can easily be adapted for all sort of applications, including the calculation of bonus codes,

The Problem
Microsoft Office Add Ons and Customization
Database Programming
Data Analysis

The Problem

A teacher wants to assign letter grades to his students, but does not want to do it by hand. Nor does he want to use a difficult to decipher, complex formula built out of the formulas that are built into Excel.

The Data

The roster of students is:

Student	Grade
Smith, Adam	77.5
Ludwig von Mises	96.3
Friedrich Hayek	89.0
John Maynard Keynes	62.3
Milton Friedman	84.0
Frederic Bastiat	79.3
Karl Popper	74.2
John Galbraith	57.25

The school has a policy regarding letter grades:

Letter Grade	Lower Numerical Bound	Upper Numerical Bound
A	92.5	N/A
A-	90.0	92.4
B+	87.5	89.9
B	82.5	87.4
B-	80.0	82.4
C+	77.5	79.9
C	72.5	77.4
C-	70.0	72.4
D+	67.5	69.9
D	60.0	67.4
F	N/A	59.9

Writing a Custom Function Using Visual Basic for Applications (VBA)

There are several tricky ways to use the built in excel formulas to generate a letter grade when input a numerical grade, but they are all complicated and easily broken. The most elegant way to handle this is to write a custom function.

We open the Visual Basic editor,

Tools -> Macros -> Visual Basic Editor

We select

Insert -> Module

In the module we type the following:

Function LetterGrade(NumGrade As Variant)

'Function to Convert a Numerical Grade to a Letter Grade
On Error Resume Next 'This command prevents the error handler from stopping execution if someone asks for a bad number to be converted into a letter grade
Dim NumericalGrade As Double 'Define a variable to store a decimal number
NumericalGrade = CDbl(NumGrade) 'Attempt to convert the input value into a decimal number
If Err.Number <> 0 Then 'True only if something went wrong in the conversion
LetterGrade = CVErr(xlErrNA) 'If that happens we return a NA value
Exit Function 'and stop executing the function.
End If
Select Case NumericalGrade 'Checks to see which of the first of the CASE conditions below are satisfied by NumericalGrade
Case Is < 60 'True if Numerical Grade < 60
LetterGrade = "F"
Case Is < 67.5 'True if Numerical Grade < 67.5. This is only checked, though, if NumericalGrade >= 60
LetterGrade = "D"
Case Is < 70
LetterGrade = "D+"
Case Is < 72.5
LetterGrade = "C-"
Case Is < 77.5
LetterGrade = "C"
Case Is < 80
LetterGrade = "C+"
Case Is < 82.5
LetterGrade = "B-"
Case Is < 87.5
LetterGrade = "B"
Case Is < 90
LetterGrade = "B+"
Case Is < 92.5
LetterGrade = "A-"
Case Else
LetterGrade = "A"
End Select
End Function

Using the Function

At this point, we can type in the name of the function, LetterGrade in the formula of any cell. For example, if we wanted cell C2 to contain a letter grade based on a numerical grade stored in cell B2, we would set C2‘s formula equal to ‘=LetterGrade(B2)‘.