Mastering the IF Statement in VBA for Excel

In this video, you'll learn how to implement the IF statement in Visual Basic for Applications (VBA) to automate tasks in Excel. The video walks you through writing a simple VBA subprocedure that evaluates conditions and displays a message box based on the evaluated result. This foundational skill is essential for any Excel user looking to leverage VBA for more advanced functionality.

What will you learn

• How to declare and assign a variable in VBA.
• How to write an IF condition in VBA.
• The correct syntax for writing IF statements, including the use of "Then" and "End If".
• How to display results using a message box in VBA.
• Basic troubleshooting and ensuring your code runs without errors.

Takeaway notes

• The IF statement in VBA is similar in logic to the IF function in Excel but requires specific syntax:
• The "Then" keyword must follow the condition.
• Always close an IF statement with "End If".
• A message box can be used to display the results of the condition check.
• Changing the value of the variable and re-running the procedure helps verify if the condition and code are functioning correctly.

Practice questions

1. Write a VBA macro that assigns the value 10 to a variable y and checks if y is less than 15. If true, display a message box stating "y is less than 15."
2. Modify the example from the video: instead of greater than 2, check if x is equal to 5. Display the message "x is 5" if the condition is true.
3. Create a VBA script that assigns a variable z the value of 0 and checks if z is not equal to 0. If true, present a message "z is not zero."
4. Write a subprocedure that assigns the current year to a variable and checks if it is a leap year. If true, display "This is a leap year" in a message box.
5. Implement nested IF statements in VBA. For example, assign a value to a, check if a is greater than 10, and if that is true, then check if a is also less than 20.
6. Practice handling errors by purposefully writing an incorrect IF statement without "End If" and observe the type of error message generated.
7. Write a VBA procedure that prompts the user to input a number and then checks if the number is positive, negative, or zero. Display a suitable message for each case.
8. Develop a VBA code that assigns a student's score to a variable and checks if it is greater than 50. If true, display "Pass", otherwise, display "Fail".
9. Combine multiple conditions using IF statements. For instance, check if a variable b is between 1 and 100.
10. Write a subprocedure to check if a date stored in a variable myDate falls on a weekend. Display a message accordingly.
11. Create a VBA macro that changes the value of a cell (e.g., A1) based on a condition check on another cell's value (e.g., B1).
12. Experiment with Boolean variables in an IF condition. Assign True or False to a variable, and display appropriate messages in a message box.
13. Write a VBA code snippet that evaluates the length of a string and if the length is greater than 10, displays a message "String is too long".
14. Modify the provided code to include an ELSE statement, displaying a message box "Condition not met" when the condition is false.
15. Create a VBA code to check multiple conditions using ElseIf. For instance, check if a number is greater than 0, equal to 0, or less than 0, and display messages accordingly.