How to Use Or Function in Excel VBA (3 Examples)

Any criteria-based calculation relies on logical functions. In Excel, one of the most undervalued logical functions is “Or“. When it comes to solving complex computations, ‘Or’ is a must to learn. In this tutorial, we will go over the VBA Or Function in depth. To learn more about the function, read the entire article.

Download Practice Workbook

Download this practice workbook to exercise while you are reading this article.

Introduction to the VBA Or Function

The Or function in VBA is more like a statement than a function. It is used to test multiple conditions. Generally, It is familiar as an If Or statement.

⟴ Syntax

If [First Condition] Or [Second Condition]

⟴ Return Value

Returns the value which meets any of the conditions.

3 Examples to Use the VBA Or in Excel

1. Apply Condition to Test Result Using VBA Or

In Excel, the best combination is a pair of logical functions. When multiple logical formulae are nested inside another logical formula, it implies that the calculation needs a massive number of criteria to be tested.

In VBA Macro, we’ll combine the If conditional function with the Or function. Let’s imagine we have a data set below that shows the price of a product at two different stores. Additionally, we have a range for which we can purchase a product from any of the shops if the range is less than or equal to the prices of any of the stores. So, by following the steps below, we’ll use a VBA Or in If statement to do this.

Step 1:

• First of all, press Alt + F11 to open the VBA Macro-Enabled Worksheet.
• Then, click on The Insert.

Step 2:

• After selecting Module, Paste the following VBA codes.

Sub IF_OR_Example1()
If Range("C5").Value <= Range("E5").Value Or Range("D5").Value <= Range("E5").Value Then
   Range("F5").Value = "Buy"
Else
   Range("F5").Value = "Do Not Buy"
End If
End Sub

Here,

• Range(“C5”).Value <= Range(“E5”).Value is the condition between the C5 cell and E5
• Range(“D5”).Value <= Range(“E5”).Value is the condition between the cell D5 and E5.
• Range(“F5”).Value = “Buy” is the return value and F5 is the return cell if it meets any of the conditions.
• Range(“F5”).Value = “Do Not Buy” is the return value and F5 is the return cell if it doesn’t meet any of the conditions.

Step 3:

• Save and press F5 to run the program.

Therefore, you can see the word ‘Buy’ will appear in cell F5 as it meets the criteria of any of the shop’s prices are within the Range value, so you can buy the product.

Applying the previous steps, you can get the results for the remaining cells.

Read More: How to Use VBA And Function in Excel (4 Examples)

2. Perform Conditional Loop to Test Result Using VBA Or

In the previous example, for a single cell, we used a VBA Or in If statement. To test the condition one by one for the entire column would take a long time. So, in the Or in If statement, we may use a loop to evaluate the results for the entire column (F5:F11). Simply follow the instructions outlined below to accomplish this.

Step 1:

• To open the VBA Macro, press Alt + F11.
• After selecting the new Module from the Insert tab, paste the following VBA codes.

Sub IF_OR_Example1()
Dim k As Integer
For k = 5 To 11
If Range("C" & k).Value <= Range("E" & k).Value Or Range("D" & k).Value <= Range("E" & k).Value Then
Range("F" & k).Value = "Buy"
Else
Range("F" & k).Value = "Do Not Buy"
End If
Next k
End Sub

Here,

• Dim k As Integer is the declaration of k as a variable.
• For k = 5 To 11 is the range of k.
• If Range(“C” & k).Value <= Range(“E” & k).Value is the first condition between column C and E for taking k = 5.
• Range(“D” & k).Value <= Range(“E” & k).Value is the second condition between column D and E for taking k = 5.
• Range(“F” & k).Value = “Buy” is the return value in column F if it meets the entire conditions.
• Range(“F” & k).Value = “Do Not Buy” is the return value in column F if it doesn’t meet the entire conditions.
• End If is If statement close tag.
• Next k is the loop command as it will repeat the process for the value of up to 5 to 11.

Step 2:

• Finally, to run the program, save and press F5.

Consequently, you can experience that the entire column (F5:F11) in the range is tested.

Related Content: VBA If – Then – Else Statement in Excel (4 Examples)

Similar Readings

• How to Use VBA Mod Operator (9 Examples)
• VBA Format Function in Excel (8 Uses with Examples)
• How to Use VBA Right Function in Excel (6 Examples)
• Use Log Function in Excel VBA (5 Suitable Examples)
• How to Use VBA Abs Function in Excel (9 Examples)

3. Combine Or with And Function in VBA

In addition to the previous methods, to apply additional criteria, we can combine the VBA Or statement with the And statement. The figure below shows the previous action; now we wish to apply a new condition. The new range will be priced at 10 dollars. Simply follow the steps outlined below.

Step 1:

• Firstly, for activating the VBA Macro, press Alt + F11.
• Paste the following VBA codes, after selecting the new Module.

Sub IF_OR_Example1()
If Range("C5").Value <= Range("E5").Value Or Range("D5").Value <= Range("E5").Value Then
   Range("F5").Value = "Buy"
Else
   Range("F5").Value = "Do Not Buy"
End If
End Sub

Here,

The program algorithm is the same as described in method 2 except for a change in additional And condition.

Range(“E” & k).Value < 10 determines that the range value in Column E has to be less than 10.

Step 2:

• Finally, save and press F5 to run the program.

As a result, you can visualize that every cell commented with ‘Do Not Buy’ as the new range is less than 10 and no cell meets the criteria.

Read More: How to Execute VBA Function Procedure in Excel (2 Easy Ways)

Conclusion

To conclude, I hope this post has offered clear instructions on how to utilize the VBA Or function in combination with other functions in Excel to test various conditions. These methods should all be acquired and applied to your data. Analyze the practice book and apply what you’ve learned. We’re inspired to keep offering programs like this because of your contributions.

If you have any questions, please do not hesitate to contact us. Please share your thoughts in the comments section below.

Your questions will be answered as soon as possible by the Exceldemy team.

Related Articles

• VBA Date Function (12 Uses of Macros with Examples)
• How to Use VBA Function Procedure with Arguments in Excel
• How to Use VBA InstRev Function (7 Suitable Examples)
• Use the VBA Chr Function (2 Examples)
• How to Use the Left Function in VBA in Excel (2 Examples)
• How to Call a Sub in VBA in Excel (4 Examples)