Results 1 to 5 of 5
  1. #1
    Join Date
    Dec 2007
    Posts
    70

    Unanswered: AND function in VBA

    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

  2. #2
    Join Date
    Sep 2008
    Location
    London, UK
    Posts
    511
    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?

  3. #3
    Join Date
    Dec 2007
    Posts
    70

    AND function

    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

  4. #4
    Join Date
    Sep 2008
    Location
    London, UK
    Posts
    511
    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.

  5. #5
    Join Date
    Dec 2007
    Posts
    70

    AND function

    Hi Colin,

    Very helpful, that has answered my question completely!

    Thank you very much,

    Owain

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •