Need help using array formula in VBA

[dryper]

I have the following array formula which I want to convert into a UDF:

={SUM(IF(ISNUMBER(AB10:AF10),AB10:AF10/AB$5:AF$5*AB$6:AF$6))/SUMPRODUCT(AB$6:AF$6,(AB10:AF10<>"MC")*1,(AB10:AF10<>"VR")*1)}

Read that ActiveSheet.Evaluate can be used to evaluate array formula, so I created this function in VBA:

Function SUPER1(score As Range, total As Range, weight As Range)
    
    SUPER1 = ActiveSheet.Evaluate("=SUM(IF(ISNUMBER(score),score/total*weight))/SUMPRODUCT(weight,(score<>""MC"")*1,(score<>""VR"")*1)")

End Function

But I keep getting #NAME? error in cells with the following formula:

=SUPER1(AB10:AF10,$AB$5:$AF$5,$AB$6:$AF$6)

Please help. Thanks.

 

[AlphaFrog]

Try something like this (not tested)...

Function SUPER1(score As Range, total As Range, weight As Range)
    
    SUPER1 = ActiveSheet.Evaluate("SUM(IF(ISNUMBER(" & score.Address & ")," & score.Address & "/" & total.Address & "*" & weight.Address & "))/SUMPRODUCT(" & weight.Address & ",(" & score.Address & "<>""MC"")*1,(" & score.Address & "<>""VR"")*1)")

End Function

 

[dryper]

Try something like this (not tested)...

Function SUPER1(score As Range, total As Range, weight As Range)
    
    SUPER1 = ActiveSheet.Evaluate("SUM(IF(ISNUMBER(" & score.Address & ")," & score.Address & "/" & total.Address & "*" & weight.Address & "))/SUMPRODUCT(" & weight.Address & ",(" & score.Address & "<>""MC"")*1,(" & score.Address & "<>""VR"")*1)")

End Function

Great. Thanks!