The Excel VBA If Statement is one of 2 basic Conditional Statements in VBA (including the VBA Select Case statement. It allows to conditionally execute sections of code based on whether a certain condition is met or not.
We will start with a simple example of
Dim sales as Long sales = 100 'The example If...Then...Else Statment If sales > 50 Then Debug.Print "Your sales exceeded 50$" Else Debug.Print "Your sales did not exceed" End if
VBA If Then Else
The syntax of the VBA If Then Else Statement is as follows:
If Condition1 Then 'Runs if Condition1 is True ElseIf Condition2 Then 'Runs if Condition2 is True '..... Else 'Runs if neither Conditions above are True End if
VBA Boolean operators
Boolean value are either TRUE or FALSE. Similarly a condition can either evaluate to being TRUE (as in met) or FALSE (as in the condition is not met). VBA as other languages has a boolean variable type which you can equally use if your IF-THEN-ELSE-STATEMENT.
Boolean operators
| Operator | Example | Description |
|---|---|---|
| AND | [CONDITION_1] AND [CONDITION_2] | Return TRUE if both CONDITION_1 and CONDITION_2 are met (if they both evaluate to TRUE) |
| OR | [CONDITION_1] OR [CONDITION_2] | Return TRUE if EITHER CONDITION_1 or CONDITION_2 are met (if either one evaluates to TRUE) |
| NOT | NOT([CONDITION]) | Return TRUE if CONDITION is NOT met (if it evaluates to false) |
Now let’s focus on the conditions.
| Operator | Description |
|---|---|
| = | is equal to (val1 = val2) |
| <> | is not equal to (val1 <> val2) |
| < | is less than (val1 < val2) |
| > | is greater than (val1 < val2) |
| <= | is less than or equal (val1 <= val2) |
| >= | is greater than or equal (val1 >= val2) |
VBA Iif function
Something that very often comes in handy, especially when wanting to reduce the amount of code is the IIF function. If works similarly as the IF function in Excel formulas. Based on the condition it will return either one of 2 values. See the structure of the IIF statement below:
Iif([CONDITION] , [VALUE1], [VALUE2]) 'Return [VALUE1] if [CONDITION] is met 'Return [VALUE2] if [CONDITION] is NOT met
This is an example usage:
Dim val1 as Integer, val2 as Integer, maxOfTwo as Integer val1 = 1 val2 = 10 maxOfTwo = Iif(val1 > val2, val1, val2)
The IIF statement is often forgotten although it is very powerful and will make your code so much more clear.
