How to use the IF THEN ELSE ELSEIF END IF statement [VBA]
This article demonstrates how to use the If ... Then statement in Visual Basic for Applications (VBA). You can combine the If ... Then statement with Else and ElseIf to make it more versatile and create more advanced conditions.
Table of Contents
1. How to use the If ... then statement
The picture above demonstrates an If ... Then statement using a condition, you can use logical operators like the:
- < less than sign
- > greater than sign
- = equal sign
- <> not equal signs meaning a smaller than and a larger than sign combined.
This particular example checks if the value in B3 is smaller than the value in cell D3. If true the If statement runs the remaining code after the Then statement, in this case, it shows a message box with text Value1 is smaller than Value2. See the image above.
VBA code
'Name macro Sub Macro1() 'If ... Then statement If Range("B3") < Range("D3") Then MsgBox "Value1 is smaller than Value2" 'Stop macro End Sub
1.1 If ... Then condition: larger than
VBA code
'Name macro Sub Macro1() 'If ... Then statement If Range("B3") > Range("D3") Then MsgBox "Value1 is larger than Value2" 'Stop macro End Sub
1.2 If ... Then condition: equal to
VBA code
'Name macro Sub Macro1() 'If ... Then statement If Range("B3") = Range("D3") Then MsgBox "Value1 is equal to Value2" 'Stop macro End Sub
1.3 If ... Then condition: not equal to
VBA code
'Name macro Sub Macro1() 'If ... Then statement If Range("B3") <> Range("D3") Then MsgBox "Value1 is not equal to Value2" 'Stop macro End Sub
2. How to use the If ... Then ... End If statement
The If ... Then ... End If statement allows you to run multiple lines of code, the End if statement tells the subroutine when the lines have been run and the If ... Then ... End if statement is completed.
2.1 VBA code
'Name macro Sub Macro2() 'If ... Then ... Endif statement If Range("B3") < Range("D3") Then 'Save number 45 to cell E3 in current worksheet Range("E3") = 45 'Show message box MsgBox "Value1 is smaller than Value2" End if 'Stop macro End Sub
The subroutine above saves the number 45 to cell E3 if the value in cell B3 is smaller than the value in D3.
The msgbox function then displays a dialog box containing the message Value1 is smaller than Value2.
3. How to use the If ... Then ... Else ... End if statement
The ELSE statement allows you to run code if the logical expression is not met.
3.1 VBA code
'Name macro Sub Macro3() 'If ... Then ... Else ... Endif statement If Range("B3") < Range("D3") Then 'Display message box MsgBox "Value1 is smaller than Value2" Else 'Display message box MsgBox "Value1 is not smaller than Value2" End If 'Stop macro End Sub
4. How to use the If ... Then ... Elseif ... Else ... Endif statement
The ELSEIF statement lets you create another logical expression, you may have multiple ELSEIFs in the statement.
4.1 VBA code
'Name macro Sub Macro4() 'If ... Then ... ElseIf ... Else ... Endif statement If Range("B3") < Range("D3") Then 'Display message box MsgBox "Value1 is smaller than Value2" ElseIf Range("B3") = Range("D3") Then 'Display message box MsgBox "Value1 is equal to Value2" Else 'Display message box MsgBox "Value1 is larger than Value2" End If 'Stop macro End Sub
5. Where to put the VBA code?
- Copy the VBA macro code.
- Press Alt and F11 to open the Visual Basic Editor (VBE).
- Press with left mouse button on "Insert" on the top menu.
- Press with left mouse button on "Module", see the image above.
- A new module is inserted, see the Project Explorer above.
- Paste the VBA macro code to the code module.
- Exit VBE and return to Excel.
6. How to run a VBA macro
- Press Alt and F8, a dialog box appears.
- Select the macro you want to run.
- Press with left mouse button on "Run" button.
Get Excel *.xlsm macro-enabled file
If then else statement category
This workbook contains two worksheets, one worksheet shows a calendar and the other worksheet is used to store events. The […]
This post Find the longest/smallest consecutive sequence of a value has a few really big array formulas. Today I would like to […]
Did you know that you can select all cells containing comments in the current sheet? Press F5, press with left […]
Excel categories
One Response to “How to use the IF THEN ELSE ELSEIF END IF statement [VBA]”
Leave a Reply
How to comment
How to add a formula to your comment
<code>Insert your formula here.</code>
Convert less than and larger than signs
Use html character entities instead of less than and larger than signs.
< becomes < and > becomes >
How to add VBA code to your comment
[vb 1="vbnet" language=","]
Put your VBA code here.
[/vb]
How to add a picture to your comment:
Upload picture to postimage.org or imgur
Paste image link to your comment.
I am in need of an if/then statement that will run a macro. I created a drop down list with values. If a value is selected, I want the macro to check a checkbox on the next worksheet - which is where the drop down list values came from. I have tried a few things and none of them have worked. Any assistance would be greatly appreciated.