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

Get FREE Advanced Excel Exercises with Solutions!

The VBA And function in Excel is a logical function that allows us to perform a conditional check between two or more conditions. It returns true if all the statements defined inside the function are correct. On the other hand, if one or more conditions are incorrect, it returns false. This VBA And function helps us to compare different conditions against each other to reach a decision.


Introduction to the VBA And Function

The And function is a built-in function in Excel.  

Objective: To perform a logical test between two or more conditions and return true only if all the conditions are true.

Syntax: [Condition 1] And [Condition 2] And [Condition 3] And ……[Condition n]

Arguments: Conditions that can be expressed as a string or a number along with conditional operators.


Using VBA And Function in Excel: 4 Useful Examples

To illustrate the use of the VBA And function, we’ll use the following dataset. The dataset contains test marks in Mathematics and Physics for 5 different students. A student gets passed if he/she has at least 40 or more numbers in both of the subjects. On the other hand, if the exam marks are less than 40 in one or both of the subjects, he/she will not pass. We are going to use the And function to assign the conditions to see whether a student has passed or failed.

Here cells C6:C10 and D6:D10 contain marks for Mathematics and Physics respectively. And we stored our output in the column named ‘Result’ in cells E6:E10.

VBA And Function in Excel


1. Use of the VBA And Function in Excel to Test a Logic

In this example, we used only the VBA And function with two conditions that return either TRUE or FALSE as output after checking the conditions for each of the students.

Follow the steps to execute our first code with the VBA And function.

Steps:

  • Go to the Developer Tab in the Excel Ribbon and click the Visual Basic Tab.

VBA And Function in Excel

  • Now from the Visual Basic Editor choose the Module option from the Insert tab to add a new module.

VBA And Function in Excel

  • Finally, add the following code in the visual code editor and press F5 to run.
Sub Result()
Range("E6") = Range("C6") >= 40 And Range("D6") >= 40
End Sub

Explanation

We can see the output is TRUE. The above code checks the two marks 80 and 75 from cells C6 and D6 against the conditions of whether they are greater than or equal to 40 or not. As it appears that John has got more than 40 in both subjects, the conditions are true.

Let’s run the following code to check the conditions for the other students.

Sub Result()
Range("E6") = Range("C6") >= 40 And Range("D6") >= 40
Range("E7") = Range("C7") >= 40 And Range("D7") >= 40
Range("E8") = Range("C8") >= 40 And Range("D8") >= 40
Range("E9") = Range("C9") >= 40 And Range("D9") >= 40
Range("E10") = Range("C10") >= 40 And Range("D10") >= 40
End Sub

The result is shown in the screenshot below.

VBA And Function in Excel


2. Try the VBA And Function With the IF Condition in Excel

The use of the VBA And function with the If…Then…Else statement executes a group of statements depending on the conditions defined in the And function. In this example, we set the output as “Passed” for the students who passed both the conditions of getting at least 40 or more in both of the subjects and “Failed” otherwise.

 Put the following code in the visual code editor to make this happen.

Sub Result()
If Range("C6") >= 40 And Range("D6") >= 40 Then
Range("E6").Value = "Passed"
Else
Range("E6").Value = "Failed"
End If
End Sub

Explanation

In this code, the If statement checks the value of cells C6 and D6 and compares them with the conditions. The Then statement outputs “Passed” in cell E6 in case both conditions are met. After that, we have the Else statement that prints the value “Failed” in cell E6 in case the And function returns false.

Running code for other students, we’ve got the final output.

VBA And Function in Excel


3. Run a For Loop With the VBA And Function

We can also use the VBA And function within loops to make decisions over a dataset by defining different conditions inside the And function. The use of For…Next statements allow us to loop through a range of data over some predefined conditions using the And function. As a result, we don’t need to apply code over each row of data to test logic. Rather, we just need to define the conditions and loop number based on the dataset.

Apply the following code.

Sub Result()
Dim n As Integer
For n = 6 To 10
Cells(n, 5).Value = Cells(n, 3) >= 40 And Cells(n, 4) >= 40
Next n
End Sub

Explanation

In the dataset, rows 6 to 10 contain the exam results data for five students. This is the reason; we assign the variable n to loop through data from rows 6 to 10. For example, cells (n, 5).Value stores the output in 5 different cells i.e. Cells with (row, column)=(6,5), (7,5), (8,5), (9,5) and (10,5). Again, the Cells(n,3) part gives the value of cells C6:C10 which have row numbers 6 to 10, and column number 3.

VBA And Function in Excel


4. Example of Using the VBA And Function in Excel With the If Condition inside a For Loop

In this example, we used both the If…Then…Else and For..Next statements along with the VBA And function. As a result, we can now set a group of statements (using If…Then…Else) depending on the conditions defined by the And function and execute that on a range of data by defining the loop number (using For…Next). Here is the code below.

Sub Result()
Dim n As Integer
For n = 6 To 10
If Cells(n, 3) >= 40 And Cells(n, 4) >= 40 Then
Cells(n, 5).Value = "Passed"
Else
Cells(n, 5).Value = "Failed"
End If
Next n
End Sub

The following screenshot shows the output.


Notes

  • In this article, we showed the use of the And function for two conditions but it can be used for n number of conditions to filter data more and more precisely.
  • We can also use the MsgBox function to show the VBA code result in a MsgBox if it isn’t necessary to store or use it in a cell.

For example, in the first method, we can use the following code to show the result in the MsgBox.

Sub Result()
MsgBox Range("C6") >= 40 And Range("D6") >= 40
End Sub

Download Practice Workbook

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


Conclusion

Now, we know how to use the VBA And function in Excel. Hopefully, it will encourage you to use this function more confidently. Any questions or suggestions don’t forget to put them in the comment box below.


Related Articles

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.
Al Arafat Siddique
Al Arafat Siddique

Al Arafat Siddique, BSc, Naval Architecture and Marine Engineering, Bangladesh University of Engineering and Technology, has worked on the ExcelDemy project for two years. He has written over 85+ articles for ExcelDemy. Currently, he is working as a software developer. He is leading a team of six members to develop Microsoft Office Add-ins, extending Office applications to interact with office documents. Other assigned projects to his team include creating AI-based products and online conversion tools using the latest... Read Full Bio

We will be happy to hear your thoughts

Leave a reply

Advanced Excel Exercises with Solutions PDF

 

 

ExcelDemy
Logo