Excel: Use VBA to Get Around Nested IF Statement Limitation

If you’ve ever used nested IF statements in Microsoft Excel, you might be aware of the fact that it is impossible to nest more the 7 functions in 2007 (or 64 in 2010 - thank you to 'danonuke' for enlightening me on this). What if you really need more than 7, though? Using VBA to create your own function to get around this limitation is actually fairly simple, even if you aren’t very familiar with programming in Excel.

If you are comfortable with VBA, then you’re going to want to scroll down and take a look at the code that I’ve posted. For those of you that are newer to the world of VBA, I’m going to explain how to do this in a series of steps.

For this example, pretend that you are a teacher who has to assign letter grades to a worksheet that contains all of the final marks of your students. Marks can be one of 11 different possibilities: A+, A, A-, B+, B, B-, C+, C, C-, D, or F.

With 11 different grades, using a nested IF statement might not work. In Excel 2007, you’d either have to simplify your marking scheme or completely leave out some ranges of marks. What we’re going to do is create what is called a user-defined function.

Not entirely sure what that means? Well, assuming you’ve used Excel to some extent, you probably already use some functions that Excel has already made for you. When you add up values using SUM, that’s a function. When you calculate an average using AVERAGE, that’s also a function. Using VBA, you can create your own functions which allow you to get the results you need.

First, you will need to save your workbook as a macro-enabled workbook. This is a special file format that specifically lets you use the functionality that we’re about to add using VBA. On the ribbon, click on ‘File’ and then choose ‘Save As’. In the ‘Save as type’ drop-down, choose ‘Excel Macro-Enabled Workbook (*.xlsm)’, and then click the ‘Save’ button.

Now, you need to open up the VBA editor window. On your keyboard, press Alt+F11. Go to ‘Insert’, and then click on ‘Module’. It is in this module that you are going to place your code.

Copy and paste the following code into the module (or type it out yourself, if you really feel like it):

Function GradePoint(Grade As Double)
Select Case Grade
Case 95 To 100
GradePoint = "A+"
Case 90 To 95
GradePoint = "A"
Case 85 To 90
GradePoint = "A-"
Case 80 To 85
GradePoint = "B+"
Case 75 To 80
GradePoint = "B"
Case 70 To 75
GradePoint = "B-"
Case 65 To 70
GradePoint = "C+"
Case 60 To 65
GradePoint = "C"
Case 55 To 60
GradePoint = "C-"
Case 50 To 55
GradePoint = "D"
Case 0 To 50
GradePoint = "F"
Case Else
GradePoint = "N/A"
End Select
End Function

When you’re done, close the VBA editor window (just click the x in the upper right-hand corner like any other window). Now you’re back on the spreadsheet.

At this point, you’re ready to use the function that you just created. Suppose that cell A3 has a student's final mark, which is 92.3. In cell A4, type in the following:

=GradePoint(A3)

You will now see the result “A” in cell A3.

Now, if this happens to be the exact scenario that you find yourself in, then you’re all set. However, it is likely that you are going to want to tweak this to suit your own needs. I’m going to explain a few things about the code that will hopefully make this task easier for you.

First of all, you can change the name of the function in the very first line. I have called it ‘GradePoint’. Go ahead and change this to something else if you like. This is the text that you are going to enter when using the function, so if you would rather enter something like ‘=EmployeeRating(A3)’, then change ‘GradePoint’ to ‘EmployeeRating’. Don’t just make this change in the first line, make sure you change it for every other instance of the term ‘GradePoint’ in the entire function.

You can go ahead and add or remove cases as you need to, and make changes to the numbers used in the different ranges. Think of the cases as different possible scenarios, and the item in quotation marks are the results that you would like to see in each scenario. In plain English, the first case is like saying “If the grade point is between 95 and 100, then the result is A+”.

The very last case you see, ‘Case Else’, is the result that is returned in the event that none of the other cases apply. It’s similar to the FALSE part of an IF statement. You’re going to want to leave this case to deal with anything unexpected. In my example, I’m showing ‘N/A’ for anything that isn’t between 0 and 100. That way, if I accidentally enter a mark such as 540 instead of 54, I will see ‘N/A’.

I hope that this post helps you out as you build your own function to get around the limitations of nested IF statements!

Categories: Excel, Excel VBA, functions, Microsoft Office 2007, Microsoft Office 2010, VBA