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.

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

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 are 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

Read More: How to Use VBA Case Statement (13 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.

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

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


Similar Readings:


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.

And Function in VBA

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.

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

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


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

Bhubon Costa

Bhubon Costa

Myself, Bhubon Costa, an engineer, is currently employed with Exceldemy as an Excel & VBA Content Developer. I appreciate solving analytical difficulties, finding practical answers, and, for the most part, I enjoy taking on new tasks. I write articles on Exceldemy about various ways to get out of Microsoft Excel's stuck conditions. My passion is to leave my mark on the world through my work and to have an impact on the community who benefit from it.

We will be happy to hear your thoughts

Leave a reply

ExcelDemy
Logo