VBA OR Function

What Is OR Function In VBA?

The Excel VBA OR function is a logical function that returns TRUE if any of the specified conditions are TRUE. It returns FALSE only if none of the conditions are TRUE. To access the VBA OR function, list the conditions to be checked, and separate each with the Excel VBA OR condition.

For example, consider a condition where we check whether a person above 18 or below 21 is is considered a teenager. In such a case, access VBA OR function checks whether either of these conditions is true, as shown below:

When this code is executed, it returns a popup message “Maybe a teenager or an adult,” since only the condition “a<20” is true for the above example.

Syntax

The syntax for using the OR function is as shown below:

Condition 1 Or condition 2 Or…… [condition n]

Here, ‘condition 1’ and ‘condition 2’ are the conditions to be checked by the VBA OR function.

How To Use OR Function in VBA?

To access the VBA OR function, write the relevant code using the Excel VBA editor. The following steps shown below explain how the VBA OR function is used.

Step 1: Go to the “Developer” tab in the toolbar and click the “Visual Basic” option. Now, the VBA Editor opens, where you can add functions and Sub procedures.

Click the “Insert” button and click the “Module” button to create a new module or blank page.

Step 2: Consider an example where the VBA OR function, similar to the Excel OR function, is used to check if either of the cells in the “A” column contains a value greater than 20.

It can be done by using the OR function in VBA to check if either of these cells contains a value greater than 20.

Step 3: Create a function called CheckNumbers() to check the above condition.

Step 4: Enter variables ‘Value1’ and ‘Value2’ and declare their range as shown below.

Step 5: Enter the conditions to check if either of the cells is greater than 20 and connect them using the OR function. Using the If-Else conditions, print the output based on condition parameters.

Code:

Sub CheckNumbers()
Value1 = Range(“A1”).Value
Value2 = Range(“A2”).Value
If Value1 > 20 Or Value2 > 20 Then
MsgBox “At least one value is greater than 20.”
Else:
MsgBox “Neither value is greater than 20.”
End If
End Sub

If either of the conditions is true, at least one value is greater than 20. Else, neither value is greater than 20.

Step 5: Run the code by pressing the F5 or Run button. Since cell A2 has a value greater than 20, the following output is observed.

Examples

Here are a few examples to help understand the VBA OR function and its functionalities.

Example #1

Consider a Truth Table for three inputs implementing OR function for columns “A,” “B,” and “C.” First, we must access VBA OR function to check if either cell has a value of 1 and, if so, return the output “TRUE”; else, it returns the result “FALSE” in column “D.”

Step 1: To implement the Boolean OR gate function in Excel VBA, create a sub-procedure starting with Sub <procedure_name>(), as shown below.

Step 2: Declare two variables “lastRow” and “i” and assign them the “Long” datatype. Then declare the range of “lastRow.”

As shown in the above code snippet, the range of the table is found using the “End(xlUp)” function.

Here, column “A” is used to calculate the range along with the count of rows and find the end of the row range with the help of XlUp (XLOOKUP ()). After creating the sub-procedure, it is done for the table to be dynamic and hold multiple row values.

Step 3: Declare a FOR loop that runs through the whole table. Using an If-Else condition nested inside the FOR loop, check whether each row has at least a single ‘1’ as the input.

Here, ‘i’ is iterated from 1 till the table end, as shown above, to check whether the values in columns “A,” “B,” and “C” are greater than 0; if the condition is satisfied, the cell in column “D” is printed as “TRUE.” If not, it is printed as “FALSE.”

Code:

Sub orFunction()
Dim lastRow As Long
Dim i As Long
lastRow = Range(“A” & Rows.Count).End(xlUp).Row
For i = 1 To lastRow
If Range(“A” & i).Value > 0 Or Range(“B” & i).Value > 0 Or Range(“C” & i).Value > 0 Then
Range(“D” & i).Value = “TRUE”
Else
Range(“D” & i).Value = “FALSE”
End If
Next i
End Sub

Step 4: To execute this code, press the Run button on the top of the VBA editor, which looks like a green triangle.

Step 5: We get the following output after executing this code.

Example #2

Consider a sales table containing Names, Products sold, Sales, and the Commissions received as columns. There is a need to create a function that calculates the commission for each salesperson based on the following criteria:

• If the salesperson is “John” or “Sally,” their commission rate is 10%.
• If the product sold is a “Widget” or “Gizmo,” the commission rate is 5%.
• If the sales amount is more significant than $1,000, the commission rate is 15%.

Step 1: Create a sub-procedure “CalculateCommission” to calculate the commission based on the conditions specified above.

Step 2: Declare four variables, ws, lastRow, I, and commissionRate, as shown below:

As shown below, ws is declared as a datatype of type Worksheet; that is, it can be selectively set to working in a specific sheet in an Excel Workbook as shown below.

Meanwhile, lastRow and ‘i’ are declared as Long datatypes, while commissionRate is declared a Double datatype.

Step 3: Define the range of lastRow as shown below with the help of the xlUp VBA function.

As seen above, the “ A “ range” of a given specific worksheet is found by counting the number of filled rows and returning the value using the xlUp function.

Step 4: Initialize a FOR loop in which If-Else statements are used to check for the conditions for the table range in the loop.

As shown, the commissionRate is found and calculated based on the criteria specified, after which the D column is filled by multiplying the initial value with its corresponding commissionRate.

Code:

Sub CalculateCommission()
Dim ws As Worksheet
Dim lastRow As Long
Dim i As Long
Dim commissionRate As Double
Set ws = ThisWorkbook.Sheets(“Sheet4”) ‘can be set to any worksheet as preferred
lastRow = ws.Cells(ws.Rows.Count, “A”).End(xlUp).Row
For i = 2 To lastRow
If ws.Cells(i, “A”).Value = “John” Or ws.Cells(i, “A”).Value = “Sally” Then
commissionRate = 0.1
ElseIf ws.Cells(i, “B”).Value = “Widget” Or ws.Cells(i, “B”).Value = “Gizmo” Then
commissionRate = 0.05
Else
commissionRate = 0
End If
If ws.Cells(i, “C”).Value > 1000 Then
commissionRate = 0.15
End If
ws.Cells(i, “D”).Value = ws.Cells(i, “C”).Value * commissionRate
Next i
End Sub

Step 5: Click the green triangle on the toolbar to execute the VBA code.

Step 6: After running the code, the following output is as observed.

Example #3

Consider a table with a list of tasks along with their status of completion and due dates. The list of tasks is in column A; the status is either “Complete” or “Incomplete” in column B, and the Due dates (if any) in column C. Here, we should write a procedure that loops through the list of tasks and highlights tasks that are either marked as complete or have a past due date, in a different color.

It can be accomplished using the VBA OR function.

Step 1: Declare a sub-procedure called “HighlightTasks” in the VBA Editor, as shown below.

Step 2: Declare a variable “task” of Range datatype, which means that the task is a cell or a group of cells to be checked.

Step 3: Initialize a FOR loop that runs between the range of rows A2-A10 (assuming the table values are between A2 and A10).

For each cell, it uses the Offset excel function to check the value of the status in column B (one column to the right of the current cell) and the due date in column C (two columns to the right of the current cell).

The Offset function can be used to move across columns or rows. The code “task.Offset(0,1).Value” checks different values in the same row but different columns.

For Each task In Range(“A2:A10”)
If task.Offset(0, 1).Value = “Complete” Or (task.Offset(0, 2).Value < 9 – 3 – 2023 And task.Offset(0, 1).Value <> “Complete”) Then
task.Interior.Color = RGB(255, 255, 0)
End If

• The OR function is used to check whether either condition is true.
• If the status is “Complete,” the first condition is true, and the task will be highlighted.
• If the status is not “Complete,” but the due date (9-3-23) has passed, the second condition is true, and the task will be highlighted.
• If neither condition is true, it moves to the next cell in the range.
• Finally, if a task is highlighted, its cell color is set to yellow using the Interior.Color property.

Code:

Sub HighlightTasks()
Dim task As Range
For Each task In Range(“A2:A10”)
If task.Offset(0, 1).Value = “Complete” Or (task.Offset(0, 2).Value < 9 – 3 – 2023 And task.Offset(0, 1).Value <> “Complete”) Then
task.Interior.Color = RGB(255, 255, 0)
End If
Next task
End Sub

Step 4: Click the green triangle on the toolbar to execute the VBA code.

Step 5: The output is as shown below:

Since the date is 09-03-2023, it highlights only the Completed tasks.

Step 6: If it is changed to the current date using the “Date” excel function in Excel VBA, the output changes as such.

As observed, the highlights change dynamically.

Case Study: Employee Bonus Calculation

You work for a company that provides yearly employee bonuses based on their performance. The bonus amount is calculated based on two criteria:

• The employee’s base salary for the year
• The employee’s performance rating is rated on a scale of 1-5, with five being the highest.

The bonus amount is calculated as follows:

• If an employee’s base salary is less than or equal to $50,000 and their performance rating is 4 or 5, they receive a bonus of 5% of their base salary.
• If an employee’s base salary is between $50,001 and $100,000 and their performance rating is 4 or 5, they receive a bonus of 7% of their base salary.
• If an employee’s base salary is greater than $100,000 and their performance rating is 4 or 5, they receive a bonus of 10% of their base salary.

Using Excel VBA’s OR function, write a program that calculates the year-end bonus for each employee based on their base salary and performance rating. The program should display the employee’s name, base salary, performance rating, and bonus amount.

Consider the table shown below:

Hint: Check the above conditions using an If-Else statement nested in the for loop. The results are then filled in column D.

Refer to the code below for understanding if you cannot arrive at the result.

Sub CalculateBonus()
Dim employeeName As String
Dim baseSalary As Double
Dim performanceRating As Integer
Dim bonusAmount As Double
For i = 2 To Range(“A1”).End(xlDown).Row
employeeName = Range(“A” & i).Value
baseSalary = Range(“B” & i).Value
performanceRating = Range(“C” & i).Value
If baseSalary <= 50000 And (performanceRating = 4 Or performanceRating = 5) Then bonusAmount = baseSalary * 0.05 ElseIf baseSalary > 50000 And baseSalary <= 100000 And (performanceRating = 4 Or performanceRating = 5) Then bonusAmount = baseSalary * 0.07 ElseIf baseSalary > 100000 And (performanceRating = 4 Or performanceRating = 5) Then
bonusAmount = baseSalary * 0.1
Else
bonusAmount = 0
End If
Range(“D” & i).Value = bonusAmount
Next i
End Sub

Step 4: To execute the code, click on the green triangle seen in the toolbar.

Step 5: The output for the above code is as shown below.

Important Things To Note

1. Result: The OR function returns “True” if at least one of the logical expressions evaluates to True, and False if all of the expressions evaluate to False.
2. Short-circuiting: The OR function in VBA uses short-circuit evaluation, which means it stops evaluating the logical expressions as soon as it finds one that evaluates to “True.” It can help improve performance in certain situations.
3. Data types: The OR function in VBA can work with any data type that can be evaluated as a logical expression, including Boolean values, numeric values, and strings.
4. Operator precedence: The OR function has lower operator precedence than comparison operators such as “=,” “<>,” “<,” “>,” “<=,” and “>=.” You may need to use parentheses to ensure that the logical expressions are evaluated in the correct order.

Frequently Asked Questions (FAQs)

Recommended Articles

Guide to VBA OR Functions. Here we explain how to use the logical OR function in Excel VBA with examples and case study. You may learn more from the following articles –

• VBA AND Function
• VBA MOD
• VBA Operators