none
How to create User Defined "AND" function using VBA? Facing Issue in UDF "AND" Function RRS feed

  • Question

  • Hi,

    How to create User Defined "AND" function using VBA?

    I cannot able to do step by step code debug after executing the highlighted code line which mentioned in below mentioned screenshot. And I cannot able to store value in "A1" cell.  For example ArryCondition(1) will have value "C2>10"


    Bala

    Friday, April 29, 2016 3:08 PM

Answers

  • You cannot change the value of another cell in a function used in a formula.

    Another point: the array returned by Split starts at index 0, not at index 1.

    Try this version:

    Function VBAAND(strAllConditions As String) As Boolean
        Dim ArryCondition As Variant
        Dim Cond_Indx As Integer
        VBAAND = True
        ArryCondition = Split(strAllConditions, ",")
        For Cond_Indx = 0 To UBound(ArryCondition)
            If Evaluate(ArryCondition(Cond_Indx)) = False Then
                VBAAND = False
                Exit For
            End If
        Next Cond_Indx
    End Function


    Regards, Hans Vogelaar (http://www.eileenslounge.com)


    Friday, April 29, 2016 3:26 PM
  • >>>I cannot able to do step by step code debug after executing the highlighted code line which mentioned in below mentioned screenshot. And I cannot able to store value in "A1" cell.  For example ArryCondition(1) will have value "C2>10"

    According to your description, I suggest that you could follow Hans's suggestion. In addition, you could set Cell's formula then get Cell's value, refer to below:
    Set tempRng = Range("A1")
    tempRng.Formula = "=" & ArryCondition(Cond_Indx)
    If (tempRng.Value = False) Then
       ......
    End If

    • Proposed as answer by David_JunFeng Wednesday, May 4, 2016 8:36 AM
    • Marked as answer by Balaramji Wednesday, May 4, 2016 5:29 PM
    Monday, May 2, 2016 1:56 AM

All replies

  • You cannot change the value of another cell in a function used in a formula.

    Another point: the array returned by Split starts at index 0, not at index 1.

    Try this version:

    Function VBAAND(strAllConditions As String) As Boolean
        Dim ArryCondition As Variant
        Dim Cond_Indx As Integer
        VBAAND = True
        ArryCondition = Split(strAllConditions, ",")
        For Cond_Indx = 0 To UBound(ArryCondition)
            If Evaluate(ArryCondition(Cond_Indx)) = False Then
                VBAAND = False
                Exit For
            End If
        Next Cond_Indx
    End Function


    Regards, Hans Vogelaar (http://www.eileenslounge.com)


    Friday, April 29, 2016 3:26 PM
  • >>>I cannot able to do step by step code debug after executing the highlighted code line which mentioned in below mentioned screenshot. And I cannot able to store value in "A1" cell.  For example ArryCondition(1) will have value "C2>10"

    According to your description, I suggest that you could follow Hans's suggestion. In addition, you could set Cell's formula then get Cell's value, refer to below:
    Set tempRng = Range("A1")
    tempRng.Formula = "=" & ArryCondition(Cond_Indx)
    If (tempRng.Value = False) Then
       ......
    End If

    • Proposed as answer by David_JunFeng Wednesday, May 4, 2016 8:36 AM
    • Marked as answer by Balaramji Wednesday, May 4, 2016 5:29 PM
    Monday, May 2, 2016 1:56 AM
  • Hi Hans Vogelaar,

    Thank You Very Much !!! 


    Bala

    Wednesday, May 4, 2016 5:32 PM