VBA AND Function

Publication Date :

Blog Author :

Download FREE VBA AND Function In Excel Template and Follow Along!
VBA AND Excel Template.xlsm

Table Of Contents

arrow

Excel VBA AND Function

AND function is a logical function as well as a logical operator. We can have the "TRUE" result if all the conditions are fulfilled in this function. If any of the conditions fails, the output returns "FALSE." We have a built-in AND command in VBA to use.

We hope you have reviewed our articles on “VBA OR” and “VBA IF OR.” This function is just the opposite of the OR function. In the OR function, to get the result as "TRUE," we need any of the supplied logical conditions to be satisfied. But in the AND function, it is just the reverse. To get the "TRUE" result, all the supplied logical tests in excel must be satisfied.

Look at the syntax of the AND function in excel.

AND AND AND function 1

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

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

For example, look at the below image.

AND function 1-1

When we applied the logical function AND, we got the results. For example, in cells C4 and C5, we got the result as "TRUE" because Test 1 and 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 equals 250 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
VBA AND Example 1-3

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
VBA AND Example 1-2

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

Code:

Sub AND_Example1()

  Dim K As String

  K = 25 >= 20

End Sub
Example 1-4

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

Code:

Sub AND_Example1()

  Dim K As String

  K = 25 >= 20 And 30 <= 29

End Sub
Example 1-5

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

Code:

Sub AND_Example1()

  Dim K As String

  K = 25 >= 20 And 30 <= 29

  MsgBox K

End Sub
Example 1-6

Run the macro to see what the result is.

VBA AND Example 1-7

We got the result as "FALSE" because we applied two conditions. The first condition 25>=20, this condition is satisfied, so the result is "TRUE." However, the second condition, 30<=29, is not satisfied, so the result is "FALSE." To get the result as TRUE, both the conditions should be satisfied.

Example #2

Now, we 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.

VBA AND Example 2

Here, we got "TRUE" as a result because:

1st Logical Test: 100 > 95 = TRUE

2nd Logical Test: 100 < 200 = TRUE

Our final result is "TRUE" since we got the "TRUE" results for both the logical tests.

Example #3

Now, we will see data from the worksheet. First, use the data we have used to show the example of the Excel AND function.

Example 3

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

Since we have more than one data cell, we need to use loops to avoid writing unnecessary and time-consuming lines of code. So, we have written the code below for you. The formula and logic are the same because we have only 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

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

VBA AND Example 3-1

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

It works opposite the OR function, where OR requires any supplied conditions to be "TRUE" to arrive at the results. But, AND function requires 100% result in a logical test to arrive at the results.