Excel VBA IIF
If you are the regular user of VBA macros then you must have come across the function called “IIF” or you might have seen this function on the internet. At first sight, for you must have thought it is an IF condition like our regular IF statement in Excel. But this isn’t the same IF statement we use to evaluate logical tests and arrive results based on the criteria’s we give. In this article, we will take you through the “VBA IIF” condition in VBA.
What Does IIF Condition Do in VBA?
This is very similar to our IF condition but slightly different in nature. “VBA IIF” condition tests the supplied the expression or logical test and returns either TRUE or FALSE as the result.
VBA IIF Syntax
Take look at the syntax of the IIF function.
- Expression: This is nothing but the logical test we would like to conduct.
- Ture Part: If the logical test is TRUE then what should be the result of the TRUE part.
- False Part: If the logical test is FALSE then what should be the result of the FALSE part.
We can enter our own results with TRUE & FALSE parts. Though arguments look similar to that of IF condition this will be slightly different. We will see that in the examples of Excel VBA IIF function.
One of the key difference between regular “IF” and this “IIF” is we can reduce the code to single line with IIF where with IF condition it takes a minimum of 5 lines to arrive the same result.
Example of VBA IIF Function
Below are the examples of VBA IIF Function in excel.
Example #1 – VBA IIF
Ok, we will see one simple example of IIF function. Now we will test whether one number is greater than or less than to another number. Follow the below steps to write the VBA code.
Step 1: Start the macro.
Step 2: Define the variable as String in VBA.
Sub IIF_Example() Dim FinalResult As String End Sub
Step 3: Define two more variables as Long in VBA.
Sub IIF_Example() Dim FinalResult As String Dim Number1 As Long Dim Number2 As Long End Sub
Step 4: Now for the variable “Number1” assign the value of 105 and for variable “Number2” assign the value of 100.
Sub IIF_Example() Dim FinalResult As String Dim Number1 As Long Dim Number2 As Long Number1 = 105 Number2 = 100 End Sub
Step 5: Now for the first defined variable “FinalResult” we will assign the result of IIF function. So open the IIF for the variable.
Step 6: Supply the expression as Number1 > Number2.
Step 7: Now if the expression is TRUE what should be the result. I will assign the result as “Number 1 is greater than Number 2”.
Step 8: Now if the expression is FALSE what should be the result. I will assign the result as “Number 1 is less than Number 2”.
Now the variable value will be either one of the below.
If True: “Number 1 is greater than Number 2”
If False: “Number 1 is less than Number 2”
Step 9: Let’s show the result in a message box in VBA.
Sub IIF_Example() Dim FinalResult As String Dim Number1 As Long Dim Number2 As Long Number1 = 105 Number2 = 100 FinalResult = IIf(Number1 > Number2, "Number 1 is Greater than Number 2", "Number 1 is Less than Number 2") MsgBox FinalResult End Sub
Now let’s run the code and see the result.
Since Number 1 value is 105 which is greater than the Number 2 value of 100 we got the result as “Number 1 is Greater than Number 2”. Since the Expression is TRUE, the IIF condition returned this result.
Example #2 – IF vs IIF
You must be wondering what the difference between IF & IIF is. Yes, there is a difference in coding. For an example look at the IF condition code.
Sub IIF_Example() Dim FinalResult As String Dim Number1 As Long Dim Number2 As Long Number1 = 105 Number2 = 100 If Number1 > Number2 Then MsgBox "Number 1 is Greater than Number 2" Else MsgBox "Number 1 is Less than Number 2" End If End Sub
Using IF first we have applied a logical test.
If Number1 > Number2 Then
Then if the logical test is true we have applied the result.
MsgBox "Number 1 is Greater than Number 2"
Then if the logical test is false we have applied the different result.
MsgBox "Number 1 is Less than Number 2"
Both the functions return the same result but with IIF we can code in single line only, where IF statement requires multiple lines.
Example #3 – VBA Nested IIF Condition
Like how we use nested IF to test multiple conditions similarly we can use multiple IIF as well. Take a look at the below code.
Sub IIF_Example2() Dim FinalResult As String Dim Marks As Long Marks = 98 FinalResult = IIf(Marks > 90, "Dist", IIf(Marks > 80, "First", IIf(Marks > 70, "Second", IIf(Marks > 60, "Third", "Fail")))) MsgBox FinalResult End Sub
The above IIF condition tests five logical tests and returns the result accordingly.
This has been a guide to VBA IIF. Here we discuss how to use Excel VBA IIF Function with the help of practical examples and downloadable excel template. Below are some useful articles related to VBA –
- Find Excel Files using VBA FileDialog
- Examples of VBA Join Function
- ByRef Function in Excel VBA
- Delete Files in VBA | Kill Method
- Excel VBA Charts
- VBA IF OR Examples
- VBA IFERROR Examples
- Excel VBA Course
- VBA Beginners Tutorial
- 35+ Courses
- 120+ Hours of Videos
- Full Lifetime Access
- Certificate of Completion
- Basic Excel Training
- Advanced Excel Training
- Basic & Advanced VBA Course
- Excel Dashboard Course
- Data Analysis in Excel
- Create VBA Applications