VBA AND Function

What is Excel VBA AND function?

The Excel VBA AND function checks whether multiple conditions are all TRUE. It returns TRUE when all the conditions are true and FALSE if any condition is false. To use the AND function, list the conditions to be checked, and separate them with the VBA AND condition.

For example, suppose a company wants to check whether its employees are eligible for bonuses based on a 4-month criterion. In that case, the Excel VBA AND function can check the specifics and return TRUE or FALSE based on the criteria entered. Use an “If” statement with the VBA AND function to check for multiple conditions simultaneously, as shown below.

When you execute this code, it will return a popup message as TRUE since all the conditions are met. It uses the VBA AND function to check whether the person has earned more than the target sales for that month to be applicable for the bonus.

Syntax

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

condition 1 And condition 2 And……. [condition n]

Here, ‘condition 1’ and ‘condition 2’ are the conditions to be checked.

How To Use VBA AND Function?

You can use the VBA AND function, similar to the Excel AND, by writing the relevant code using the VBA editor. We use the following steps to explain how the VBA AND function can be used using the VB Editor.

Step 1: To write code using the VBA editor , click the Developer tab in the toolbar and click on the “Visual Basic“ option. Now, the VBA Editor opens to add new functions according to one’s preferences and necessary output.

Step 2: Take an example of adding three numbers and print an output based on certain conditions. For this, insert a new module by clicking Insert tab > select Module.

Step 3: Create a function named add_num to find the total of three numbers.

Step 4: Enter the values of the three numbers and a variable sum_num to find their total, as shown below.

Step 5: Enter the conditions to check the sum and connect them using the AND function. Use the If…Else condition to print the output based on the evaluation.

Code:

Private Sub add_num()
a = 10
b = 15
c = 18
sum_num = a + b + c
If sum_num > 9 And sum_num < 100 Then
MsgBox “Two-digit sum.”
Else
MsgBox “Single-digit sum.”
End If
End Sub

Here, if both conditions are satisfied, the sum is a two-digit result, else it’s a single-digit result.

Step 6: Run the code by pressing F5 or the Run button. Since the digits add up to a two-digit sum, you get the result below.

Examples

Here are a few examples to help us understand the VBA AND function and its uses.

Example #1

Consider a table that determines the gender and age group of an input based on some parameters.

The table above has three age groups, namely, “AgeGroup1“, “AgeGroup2,“ and “AgeGroup3,“ which are defined in the Sub function.

Step 1: We know where the user will give the inputs from the above image. For “Age” is in cell “B2” and “Gender” input is in cell “B1”.

Step 2: Construct an “If“ statement to check whether the given input is “M“ or “F“ and the given age group is “AgeGroup1“.

The above code checks whether the entered Gender is “F,” “M,” or no gender has been entered, and the age group is AgeGroup1. Then, the “MsgBox” function prints the words in a popup box.

Step 3: Based on these factors, a simple VBA code is constructed using the AND function and its usage. In the code in the Sub Declare_Gender() task, to print a Message Box output based on these conditions, the AND function is used in the “If” statement.

Step 4: In the table, the given inputs are “F“ and “AgeGroup1,“ where the ranges of Gender and Age are declared.

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

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

The responses can be customized by changing the text in the “MsgBox“ line.

Step 7: By changing the inputs, you get a different output. For instance, if there’s no input in the Gender as given below, you get the following output.

Example #2

Consider a table where we need to compute the truth table for AND Boolean logic gate, which returns “TRUE“ only if both outputs are 1; else, it returns the value “FALSE“.

A and B are the inputs, and S is the resultant output. Using the AND statement in the Excel VBA editor, it can be done quickly with a nested FOR loop with a nested IF.

Step 1: Go to the “Developer“ section in the toolbar and click the “Visual Basic“ option. Now, the VBA Editor opens to add new functions according to one’s preferences and necessary output.

Step 2: Create a sub-procedure; start with Sub <procedure_name>() as shown below.

Step 3: Create a nested FOR loop for the row size of the table. One FOR loop is used to traverse the range of the table, and the other FOR loop is used to iterate every cell present in the table.

Here, ‘lastRow’ is used to loop till the end of the table, and ‘i’ is used to iterate every cell in the range. Both the variables are assigned the “Long“ datatype since it can store large numbers.

Step 4: Next, the range of the ‘lastRow’ variable is defined. Since the input columns are “A“ and “B,“ and there are multiple columns to be checked, the value of the last range can be defined using the VBA Xlookup function (XlUp).

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

 Step 5: Now use a for loop to iterate through each cell in the range of ‘lastRow.’

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

The complete code is as follows.

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

Step 6: To run the VBA sub-procedure, click the green arrow on the top of the VBA editor in the toolbar.

The resulting output is:

Example #3

In this case, consider a table where one needs to color the Loan and Dues cell in green to show that a loan is approved for those who satisfy some conditions based on the parameters given in the same sheet.

The parameters given are as follows:

Here, it is observed that the loan amount should be greater than $5500 and the previous dues greater than $2500 for the loan to be rejected.

Step 1: To make this work, first, we need to convert it into a table, which can be done by clicking any cell in the table and pressing “Ctrl + T” to convert it to a table. It will result in a popup asking for the size of the table.

Step 2: As you can see, Excel takes the table’s exact size automatically, after which you can click the tick box for “My table has headers.”

After converting it into a table, it will look like this:

Step 3: Go to the “Developer“ section in the toolbar and click the “Visual Basic“ option. Now, the VBA Editor opens to add new functions according to one’s preferences and necessary output.

Step 4: First, declare a new Sub procedure highlighting the approved cells in green and the others in red.To do that, create a sub procedure, start with Sub <procedure_name>() as shown below.

Step 5: Construct an “If“ statement that checks whether the value is greater than 5500 and less than 9000. But since it should work dynamically within the table, we use a FOR loop.

To find the length of the table, click any random cell and type “=,” and click on the marked area as shown in the image (between the column cell name and the column title) to find the value range of the column.

If Enter is pressed, it will show a #value error.

One cell cannot store all values, but we only need its range, which is shown in cell D14 in the above image.

Step 6: Similarly, find the range for the “Loan“ column as shown above using the:

Now that the necessary ranges are found, let us continue constructing the IF statement.

Step 7: Start with a “For“ loop in the Excel VBA editor.

We use the Range function to execute the loop for the range of the table acquired from the previous steps.

Step 8: Write an “If“ condition checking whether the value is between 5500 and 9000. If the condition is true, color the cell red in color. If not, color the cell green.

Adding the “Next” word after the condition is essential to execute the loop. For example, in the above code, “cell” is the variable that loops through all the cells and checks the Excel AND function condition.

Also, Excel VBA has a color library with various colors which can be used to set the background color as follows:

Step 9: Similarly, write a for loop for “Existing Dues“ and color the cells appropriately. If the dues are greater than 2500, we color the cell in red, else in green.

Here, “c” is the variable that loops through all cells in the “Existing Dues” column range and checks the condition.

The complete code is as follows:

Sub declare_approval()
Dim cell As Range
For Each cell In Range(“Table1[Loan (in USD)]”)
If (cell.Value > 5500 And cell.Value <= 9000) Then cell.Interior.Color = VBA.ColorConstants.vbRed Else: cell.Interior.Color = VBA.ColorConstants.vbGreen End If Next For Each c In Range(“Table1[Existing Dues]”) If (c.Value > 2500) Then
c.Interior.Color = VBA.ColorConstants.vbRed
Else: c.Interior.Color = VBA.ColorConstants.vbGreen
End If
Next
End Sub

Step 10: To run the code, click the green arrow on the VBA editor toolbar.

The resultant output is:

Here, those whose dues are greater than $2500 and whose loan amount is between $5500 and $9000 have their loans rejected and color-coded in red, while the remaining have their loans approved and color-coded in green.

Important Things to Note

1. Order of Arguments: The order of the arguments in the AND function does not matter, as the function will return the same result regardless of the order.
2. Short-circuit evaluation: The AND function in Excel VBA uses short-circuit evaluation. If the first argument is false, it does not bother evaluating the rest because the result will always be false.
3. It helps you compare numerous expressions which can be connected by the logical AND.

Frequently Asked Questions (FAQs)

Download Template

This article must be helpful to understand the VBA AND Function, with its formula and examples. You can download the template here to use it instantly.

Recommended Articles

Guide to VBA AND Function. Here we explain how to use it to check whether the given conditions are TRUE or FALSE with examples. You may learn more from the following articles –

• VBA Format Number
• VBA UBOUND
• VBA Subscript Out of Range