# Evaluate returns #Value

• ### 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

• 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)
If IsObject(kriterler(i)) Then
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 Thursday, January 19, 2017 2:46 AM
• Marked as answer by 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)
If IsObject(kriterler(i)) Then
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 Thursday, January 19, 2017 2:46 AM
• Marked as answer by 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