AND function in VBA

OB1 · #1 (**permalink**) 01-29-12, 05:59

Hi,

This is probably a very simple one for you all, but as a newbie I'm struggling to find a solution!

Is there an AND function in VBA? I've tried using a syntax like you would in a cell formula but it doesnt seem to work in VBA?

Thanks,

Owain

Colin Legg · #2 (**permalink**) 01-29-12, 17:27

Hi,

The syntax in a formula and VBA is different.

A formula in C1 might be:

=IF(AND(A1="OB1",B1="Question"),"And Function","")

The VBA equivalent would be

Code:

If Range("A1").Value = "OB1" And Range("B1").Value = "Question" Then
    Range("C1").Value = "And Function"
Else
    Range("C1").Value = ""
End If

Does that help?

OB1 · #3 (**permalink**) 01-30-12, 05:06

Thank you Colin, thats great!

Is there a limit to how many AND's you can put in the code? I think the function in excel is limited to 70 odd?

OB1

Colin Legg · #4 (**permalink**) 01-30-12, 05:30

In formulas:
If you are using Excel 2003 or earlier then you are limited to 7 nested functions. If you are using Excel 2007 or later then I think the limit is 64 nested functions.

In VBA, there's no particular limit that I'm aware of, but a lot of And's will be hard to read. Also, if you have a lot of conditions which are time consuming to calculate, it's worth bearing in mind that And statements in VBA (and the AND() function in Excel formulas) do not short-circuit. Let me explain....

Suppose you have a VBA statement like this:

Code:

If <condition1> And <condition2> And <condition3> Then
    'Do Something
End If

Now, for "Do Something" to happen, all 3 conditions must be True. When each condition is assessed, as soon as one of them returns False then it is pointless to check the others. For example, if <Condition1> is False then there's no need to check <Condition2> or <Condition3>. Unfortunately, VBA And statements are not optimised in this way, meaning that every condition will always be assessed, regardless of what previous conditions have returned. The closest we can get to short-circuiting in VBA is to write separate If statements:

Code:

If <Condition1> Then
    If <Condition2> Then
        If <Condition3> Then
            'do something
        End If
    End If
End If

But, depending on the code logic (And/Or etc), this isn't always an option. There are no hard and fast rules here - a lot of stylistic choice.

OB1 · #5 (**permalink**) 01-30-12, 11:58

Hi Colin,

Very helpful, that has answered my question completely!

Thank you very much,

Owain

Internet Services

Web Development

Digital Marketing

Consumer Tech