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

Get FREE Advanced Excel Exercises with Solutions!

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.


VBA OR Function Overview

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.


1. Applying Condition to Test Result Using VBA OR Function

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.

Apply Condition to Test Result Using VBA Or

Step 1:

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

Apply Condition to Test Result Using VBA Or

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 cells 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.

Apply Condition to Test Result Using VBA Or

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 within the Range value, so you can buy the product.

Apply Condition to Test Result Using VBA Or

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

Apply Condition to Test Result Using VBA Or


2. Performing Conditional Loop to Test Result Using VBA OR Function

In the previous example, for a single cell, we used a VBA Or in the If statement. To test the conditions 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.

Perform Conditional Loop to Test Result Using VBA Or

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.

Perform Conditional Loop to Test Result Using VBA Or

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.

Perform Conditional Loop


3. Combining OR Function 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.

And Function in VBA

Step 1:

  • Firstly, to activate 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.

And Function

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.

And Function


Download Practice Workbook

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


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.

What is ExcelDemy?

ExcelDemy - Learn Excel & Get Excel Solutions Center provides online Excel training , Excel consultancy services , free Excel tutorials, free support , and free Excel Templates for Excel professionals and businesses. Feel free to contact us with your Excel problems.
Bhubon Costa
Bhubon Costa

Bhubon Costa, B.Sc. in Naval Architecture & Marine Engineering from Bangladesh University of Engineering & Technology, has worked with the ExcelDemy since 2021. Currently, he has been working as a reviewer. Notably, he has written over 90 articles and led several VBA content development teams. He has a great passion for the fields of data analytics and data science. His areas of expertise include Excel VBA, Power Query, Pivot Table, Power BI, MySQL, PostgreSQL, machine learning, and Python... Read Full Bio

We will be happy to hear your thoughts

Leave a reply

Advanced Excel Exercises with Solutions PDF

 

 

ExcelDemy
Logo