VBA AND Function

Excel VBA AND Function

AND is a logical function as well as a logical operator which means that if all the conditions provided in this function are fulfilled then only we will have the true result whereas if any one of the condition fails the output is returned to be false, we have inbuilt AND command in VBA to use.

I hope you have gone through our article on “VBA OR” and “VBA IF OR.” This function is just the opposite of the OR function. In the OR function, we needed anyone of the supplied logical conditions to be satisfied to get the result as TRUE. But in the AND function, it is just the reverse. In order to get the result of TRUE, all the supplied logical tests in excel need to be satisfied.

Ok, look at the syntax of the AND function in excel.

[Logical Test] AND [Logical Test] AND [Logical Test]

In the above, I have two test scores out of 600.

In the result column, I need to get the result as TRUE if the score of both the tests is greater than equal to 250.

Look at the below image.

When we apply the logical function AND we got the results. In cell C4 & C5, we got the result as TRUE because Test 1 & Test 2 scores are greater than or equal to 250.

Look at the C6 cell here. We have got FALSE even though the score of Test 2 is equal to 250. This is because, in Test 1, the score is only 179.

Examples to Use VBA And Function

Example #1

For example, we will test the numbers here, whether 25>=20 and 30<=31.

Step 1: Declare the variable as String.

Code:

Sub AND_Example1()

  Dim K As String

End Sub

Step 2: For the variable “k,” we will assign the value by applying the AND function.

Code:

Sub AND_Example1()

  Dim K As String

  K =

End Sub

Step 3: Supply the first condition as 25>=20.

Code:

Sub AND_Example1()

  Dim K As String

  K = 25 >= 20

End Sub

Step 4: Now open AND function and supply the second logical test i.e., 30<=29.

Code:

Sub AND_Example1()

  Dim K As String

  K = 25 >= 20 And 30 <= 29

End Sub

Step 5: Now show the result of the variable “k” in the message box in VBA.

Code:

Sub AND_Example1()

  Dim K As String

  K = 25 >= 20 And 30 <= 29

  MsgBox K

End Sub

Run the macro to see what the result is.

We got the result as FALSE because we out of applied two conditions, first condition 25>=20, this condition is satisfied, so the result is TRUE, and the second condition 30<=29 this is not satisfied result is FALSE. In order to get the result as TRUE, both the conditions should be satisfied.

Example #2

Now I will change the logical test to “100>95 AND 100<200.”

Code:

Sub AND_Example2()

  Dim k As String

  k = 100 > 95 And 100 < 200

  MsgBox k

End Sub

Run the code to see the result.

Here we got TRUE as a result because

1^st Logical Test: 100 > 95 = TRUE

2^nd Logical Test: 100 < 200 = TRUE

Since we got the TRUE results for both the logical tests, our final result as TRUE.

Example #3

Now we will see data from the worksheet. Use the data that we have used to show the example of excel AND function.

Here the condition is Test 1 Score >= 250 AND Test 2 Score >= 250.

Since we have more than one cell of data, we need to use loops to avoid writing unnecessary and time-consuming lines of codes. I have written the below code for you; formula and logic are the same only thing is I have used “VBA For Next Loop.”

Code:

Sub AND_Example3()

  Dim k As Integer

  For k = 2 To 6
   Cells(k, 3).Value = Cells(k, 1) >= 250 And Cells(k, 2) >= 250
  Next k

End Sub

This will give the result exactly the same as our worksheet function, but we will not get any formulas. We get only results.

Like this, we can apply the AND logical function to test multiple conditions, which are all should be TRUE to arrive at the desired results.

This works completely opposite of OR function, where OR requires any one of the supplied condition to be TRUE to arrive the results but AND requires 100% result in a logical test to arrive the results.

Recommended Articles

This has been a guide to VBA AND Function. Here we learn how to use AND logical operator with practical examples and download an excel template. You may also have a look at other articles related to Excel VBA –

• VBA Wait
• VBA DatePart
• Data Types in VBA