MS Office Forum

Ask Question   UnAnswered
Home » Forum » MS Office       RSS Feeds

AND - Only two expressions

  Asked By: Navin    Date: Jan 02    Category: MS Office    Views: 1376

I am new to VBA coming from Excel. In Excel AND can handle up to 30
items. After checking VBA help I see that AND can handle only two
items. Is there a way to write this to arrive at the same results?

Dim TopB As Single
Dim MaxQ As Single
MaxQ = 1
If ltpB <> "" And ltqB <> "" _
And ltpB <> TopB And ltqB > MaxQ Then



4 Answers Found

Answer #1    Answered By: Ellen Simpson     Answered On: Jan 02

Did you try it?????????????????????

Answer #2    Answered By: Patricia Johnson     Answered On: Jan 02

I have found that it helps me to write  my functions in real words --
test that both ltpB and ltqB are empty strings, if so then check to make
sure that ltpB is not equal to TopB AND ltqB is not greater than MaxQ.
You may want to nest the two functions much like you would do in an
Excel Function, first testing IF both ltpB and ltqB are not empty
strings then test for IF ltpB is not equal to TopB and ltqB is not
greater than Max Q do something.

Dim TopB As Single
Dim MaxQ As Single
MaxQ = 1

If ltpB <> "" And ltqB <> "" Then
If ltpB <> TopB And ltqB > MaxQ Then
End If

Answer #3    Answered By: Calandre Bernard     Answered On: Jan 02

Although Help says: "Used to perform a logical conjunction on two
expressions." there's nothing to stop you stringing several ANDs together.

To test, I tried the following: Enter 1,2,3,4,5 in cells A1:E1
1. This formula in another cell:
returns TRUE
2. In vba:
If Range("A1") = 1 And Range("B1") = 2 And Range("C1") = 3 And
Range("D1") = 4 And Range("E1") = 5 Then MsgBox "True!"
(That's all one line) pops up the message box.
3. If you want to stick with the way the worksheet AND works then also
in vba:
If Application.WorksheetFunction.And(Range("A1") = 1, Range("B1") = 2,
Range("C1") = 3, Range("D1") = 4, Range("E1") = 5) Then MsgBox "True too!"
(Likewise one line) also pops up the message box.

If I changed any value in any of the cells A1:E1,
Item 1 returned FALSE, items 2 and 3 did not pop up a message box.

Answer #4    Answered By: Alyssa Campbell     Answered On: Jan 02

All of your suggestions and
guidance have been very helpful. Rhonda's suggestion helped me
understand the difference between a Block If and a single-line form
If and find mistakes I was making there. David and Pascal, your
suggestions helped me understand that my mistake was not with AND but
with the scope of my variable. Everytime I was cycling through my
function my variable was losing its value so of course the AND was
not doing what I wanted it to.

Didn't find what you were looking for? Find more on AND - Only two expressions Or get search suggestion and latest updates.