How to Use VBA IF And (Test Multiple Conditions) in Excel

Key Takeaways:

• Use the IF statement to test conditions in VBA.
• Use the And operator to check multiple conditions at once.
• All conditions must be TRUE for the code to run.
• If any condition is FALSE, the Else block executes.
• IF And helps automate decisions in Excel VBA macros.

 

Introduction to VBA IF And

In Visual Basic for Applications (VBA), the IF statement can be used to make decisions in your code based on a specified condition. The basic syntax of an IF statement in VBA is as follows:

If condition Then
' Code to be executed if the condition is true
Else
' Code to be executed if the condition is false
End If

The AND function in Excel is an in-built tool that evaluates two or more conditions. It yields a TRUE result only if all the conditions are true; otherwise, it returns FALSE when at least one condition is false.

When dealing with multiple conditions in an IF statement, you can use logical operators such as And to combine those conditions. The syntax for VBA IF And will be –

If condition1 And condition2 Then
' Code to be executed if both condition1 and condition2 are true
Else
' Code to be executed if at least one of the conditions is false
End If

 

Example of VBA IF And

Suppose you need to check if the project manager is eligible for a commission or not based on 2 conditions –

• Project Status is Completed
• Project Income is greater than $30,000

If both conditions are true, the manager is rewarded with a commission, and if even 1 condition is not met, the commission will not be rewarded.

STEP 1: Go to the Developer tab > VBA.

STEP 2: In the Microsoft Visual Basic for Applications window, go to Insert > Module.

STEP 3: Start the sub procedure.

STEP 4: To start the logical test, insert the IF statement with the 1st condition i.e., B1 = Completed.

STEP 5: Insert the AND operator.

STEP 6: Enter the 2nd condition i.e., B2 > 30000.

STEP 7: Enter the action that needs to be performed if both statements are correct. Here, you want to display the message – “Commission earned”.

STEP 8: Enter the action that needs to be performed if one of the statements is incorrect. Here, you want to display the message – “No Commission”.

STEP 9: Enter END IF.

STEP 10: Press Ctrl + S to save the module.

STEP 11: In the dialog box, select No to save the file as a macro-enabled workbook.

STEP 12: In the Save As dialog box, select Excel Macro-Enabled Workbook. Press Save.

STEP 13: Go to the Developer Tab > Macro.

STEP 14: In the Macro dialog box, select the Macro Name – Commission, and press Run.

The message “Commission Earned!” will be displayed as both conditions are met i.e. project status is completed and income is greater than 30,000.

If you change the project status to “In Progess” and income to 75,000 run the macro again, the message “No Commission” will be displayed. This is because one of the conditions i.e. project status is not met.

In this way, you can easily combine two or more conditions in an IF statement by using the AND operator in VBA.

 

FAQs

What is the VBA IF statement?

It is used to test a condition and run different code based on the result.

What does the And operator do in VBA?

It combines multiple conditions and returns TRUE only if all conditions are TRUE.

Can I use more than two conditions with And?

Yes, you can combine multiple conditions in a single IF statement.

What happens if one condition is FALSE?

The IF condition becomes FALSE, and the Else block runs, if included.

Can I use And with other VBA operators?

Yes, you can combine it with operators like Or, Not, >, <, =, and <> to create more advanced conditions.