Want to become an expert in VBA? So this is the right place for you. This blog mainly focus on teaching how to apply Visual Basic for Microsoft Excel. So improve the functionality of your excel workbooks with the aid of this blog. Also ask any questions you have regarding MS Excel and applying VBA. We are happy to assist you.

If Then Else Statement (Excel VBA)

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

Contact Form

Name

Email *

Message *

Popular Posts