locked
Evaluate returns #Value RRS feed

  • Question

  • hi guys,

    i need some help with Evaluate.

    i'm tryin to create a function which will work as RANKIF. The code return #Value.

    any idea why?

    Function rankif(ParamArray kriterler())
    On Error GoTo hata
        Dim i As Integer
        Dim formül As String
        Dim str As String
        
        For i = LBound(kriterler) To UBound(kriterler)
            If i = UBound(kriterler) Then
                formül = formül & """>""" & "&" & kriterler(i)
            Else
                If i Mod 2 = 0 Then
                    formül = formül & kriterler(i).Address & ";"
                Else
                    formül = formül & """" & kriterler(i) & """" & ";"
                End If            
            End If
        Next i
        
        str = "=COUNTIFS(" & formül & ")"
        rankif = Evaluate(str) ' + 1
        'rankif = str 'if i uncomment this, it shows the correct formula
        Exit Function
        
    hata:
    rankif = Err.Description
    End Function


    Dig your well before you are thirsty

    Wednesday, January 18, 2017 2:15 PM

Answers

  • i'm tryin to create a function which will work as RANKIF. The code return #Value.

    any idea why?

    Several issues:
    VBA speaks english, so the separator must be a comma, not a semicolon.
    COUNTIFS doesn't support a compare operator like ">".

    Try the code below.

    Andreas.

    Function rankif(ParamArray kriterler()) As Variant
      Dim i As Integer
      Dim Args As Variant, This As Variant
    
      On Error GoTo hata
      'Invalid arguments?
      If UBound(kriterler) Mod 2 = 0 Then
        Err.Raise 5, "RankIf", "Arguments must be passed as pairs"
      End If
      'Make an empty array
      Args = Array()
      For i = LBound(kriterler) To UBound(kriterler)
        'Add the given argument
        If IsObject(kriterler(i)) Then
          'Use the Address
          This = kriterler(i).Address
        Else
          'Use the value
          This = kriterler(i)
          'Surround by "" is not numeric
          If Not IsNumeric(This) Then This = """" & This & """"
        End If
        'Increase the array
        ReDim Preserve Args(0 To UBound(Args) + 1)
        'Store This into the last slot
        Args(UBound(Args)) = This
      Next
      
      'Debug.Print "=COUNTIFS(" & Join(Args, ",") & ")"
      rankif = Evaluate("=COUNTIFS(" & Join(Args, ",") & ")")
      Exit Function
    
    hata:
      rankif = Err.Description
    End Function

    • Proposed as answer by Chenchen Li Thursday, January 19, 2017 2:46 AM
    • Marked as answer by ExcelinEfendisi Thursday, January 19, 2017 6:05 AM
    Wednesday, January 18, 2017 4:30 PM

All replies

  • i'm tryin to create a function which will work as RANKIF. The code return #Value.

    any idea why?

    Several issues:
    VBA speaks english, so the separator must be a comma, not a semicolon.
    COUNTIFS doesn't support a compare operator like ">".

    Try the code below.

    Andreas.

    Function rankif(ParamArray kriterler()) As Variant
      Dim i As Integer
      Dim Args As Variant, This As Variant
    
      On Error GoTo hata
      'Invalid arguments?
      If UBound(kriterler) Mod 2 = 0 Then
        Err.Raise 5, "RankIf", "Arguments must be passed as pairs"
      End If
      'Make an empty array
      Args = Array()
      For i = LBound(kriterler) To UBound(kriterler)
        'Add the given argument
        If IsObject(kriterler(i)) Then
          'Use the Address
          This = kriterler(i).Address
        Else
          'Use the value
          This = kriterler(i)
          'Surround by "" is not numeric
          If Not IsNumeric(This) Then This = """" & This & """"
        End If
        'Increase the array
        ReDim Preserve Args(0 To UBound(Args) + 1)
        'Store This into the last slot
        Args(UBound(Args)) = This
      Next
      
      'Debug.Print "=COUNTIFS(" & Join(Args, ",") & ")"
      rankif = Evaluate("=COUNTIFS(" & Join(Args, ",") & ")")
      Exit Function
    
    hata:
      rankif = Err.Description
    End Function

    • Proposed as answer by Chenchen Li Thursday, January 19, 2017 2:46 AM
    • Marked as answer by ExcelinEfendisi Thursday, January 19, 2017 6:05 AM
    Wednesday, January 18, 2017 4:30 PM
  • thanks Andreas,

    it was just stuıpid of me. i just replaced ";" with "," in my own code, and then worked.

    by the way, countifs does support the ">".

    thanks


    Dig your well before you are thirsty

    Thursday, January 19, 2017 6:07 AM