Else-If statements allow the developer to have their function or subroutine consider multiple alternatives. This can allow a piece of code to evaluate multiple criteria quickly. This will explain how to use Else-If statements in Excel VBA.
In this example, we are going to make a function that checks the score that student earned on a English proficiency exam and then assigns a string indicating what level the student is at. Below is a picture of the Excel worksheet before we create the function.
We want to fill the “Level” column automatically. Below is a partial visual of what the code will do.
We want Excel VBA to check the score and assigned a level based on the score. For example, if the student score 9 or higher than Excel VBA will assign a level of “Expert” to the student. If the students score 8 or higher but less than 9 than the student will be assigned the level of “Very good.” This continues until the score of 1 which is not seen in the graphic above.
The code below is the actual code that is placed in the VBA developer.
Function level(score As Double) As String If score >= 9 Then level = "Expert" ElseIf score >= 8 Then level = "Very good" ElseIf score >= 7 Then level = "Good" ElseIf score >= 6 Then level = "Competent" ElseIf score >= 5 Then level = "Modest" ElseIf score >= 4 Then level = "Limited" ElseIf score >= 3 Then level = "Extremely limited" ElseIf score >= 2 Then level = "Intermittent" ElseIf score >= 1 Then level = "Nonuser" End If End Function
At the top of the code, we declare a function and give it the name “level”. This function takes one argument called “score” the input into the function is a numerical value but the output is a string. After this, we have are first ElseIf statement where VBA checks if the score is 9 or higher. If the score is then the level is set to expert. This process continues until the number 1. After this the If statement ends and the function ends.
Below is the output of the code found above
It appears everything is working.
This example shared one way to employ else-if statements in Excel VBA. With a basic example such as this it is possible to employ else-if statements in ways that are much more complex.