Today let’s learn how to use If Then Else statement in vba. If Then Else is a very basic control flow statement. It tells the program to evaluate conditions in the order listed and then execute a certain section of the code if a particular condition is true.
So let’s learn how to use If Then Else statement in a vba macro. I will use this simple application to explain it. This is a part of a custom calculator.
So let’s calculate the Mid estimation value for the roof. Here is how we need to calculate the value
If C2 value is 800 or less, then Mid Est is 5000
If C2 value is greater than 800 and less than or equal 1000, then Mid Est is 6000
If C2 value is greater than 1000 and less than or equal 1200, then Mid Est is 7000
If C2 value is greater than 1200 and less than or equal 1400, then Mid Est is 8000
If C2 value is greater than 1400 anand less than or equal d 1600, then Mid Est is 9000
If C2 value is greater than 1600 and less than or equal 1900, then Mid Est is 10000
If C2 above 1900, then MANUAL
We can develop the code in two different methods. Without else clause and with else clause. Below is how we can develop the code without else clause.
|
Dim Gross_SF As Double Dim MidEst As Variant Gross_SF = Range("C2").Value If Gross_SF <= 800 Then MidEst = 5000 ElseIf Gross_SF <= 1000 Then MidEst = 6000 ElseIf Gross_SF <= 1200 Then MidEst = 7000 ElseIf Gross_SF <= 1400 Then MidEst = 8000 ElseIf Gross_SF <= 1600 Then MidEst = 9000 ElseIf Gross_SF <= 1900 Then MidEst = 10000 ElseIf Gross_SF > 1900 Then MidEst = "MANUAL" End If Range("D5").Value = MidEst |
This is how we can develop the code with else clause.
|
Dim Gross_SF As Double Dim MidEst As Variant Gross_SF = Range("C2").Value If Gross_SF <= 800 Then MidEst = 5000 ElseIf Gross_SF <= 1000 Then MidEst = 6000 ElseIf Gross_SF <= 1200 Then MidEst = 7000 ElseIf Gross_SF <= 1400 Then MidEst = 8000 ElseIf Gross_SF <= 1600 Then MidEst = 9000 ElseIf Gross_SF <= 1900 Then MidEst = 10000 Else MidEst = "MANUAL" End If Range("D5").Value = MidEst |
So the program first checks whether Gross_SF is less than or equal 800. If it is true then assign value 5000 to MidEst variable and go to the end of the if then else statement. If the first condition is false then program evaluate next condition. So it checks whether Gross_SF less than equal 1000. (This is same as checking C2 value is greater than 800 and less than or equal 1000) Because in first condition we checked whether it is less than or equal 800. Program will executed to second condition only if C2 is greater than 800. If second condition is true then MidEst will become 6000. Otherwise program will evaluate next condition and so and so. If no condition is true then program will execute the section in the else part. No section will be executed if “Else” clause is not available.
According to the above requirements we need to output either a value or a string depending on the user input. Due to that reason we need to define MidEst variable as variant. If we define it as type double then program will gives an type mismatch error when the C2 value is greater than 1900.
Below are few sample outputs for different C2 values.
When C2 is 650
When C2 is 1700
When C2 is 2200
