Sumproduct in UDF

[Guest]

Dear All,

Excel 2000.

I cannot get the following UDF to work - it comes out as #VALUE!.

Code is entered as numbers, ex: 230
Job is entered as text, ex: Manager
Country is entered as text, ex: England
Rng1, Rng2, Rng3 and Rng4 are named ranges

Function Function_Name(Code As Integer, Job As Boolean, Country As Boolean)

Function_Name = SumProduct(--(Rng1 = Code), --(Rng2 = Job), --(Rng3 =
Country), (Rng4))

End Function

Help much appreciated.

 

[Bob Phillips]

Why are you using Booleans for text strings.

This works

Function Function_Name(Code As Integer, Job As String, Country As String)
Dim sFormula As String

sFormula = "SumProduct(--(Rng1=" & Code & ")," & _
"--(Rng2=""" & Job & """)," & _
"--(Rng3=""" & Country & """), (Rng4))"
Function_Name = Evaluate(sFormula)
End Function

But why are you creating a UDF that just does what SUMPRODUCT does anyway?
Not efficient.

 

[Guest]

Hi Bob,

Thank you very much. And if I add "Application.Volatile" the UDF takes care
of any changes as well.

Using Booleans for text strings was just a silly mistake.

I am simply using UDF in this case to make an Excel application more user
friendly. There will be several different sumproduct formulas prepared. The
user will then copy these UDF formulas into other sheets within the the same
workbook. Then it's easier for the user to change cell references without
"destroying" the formula.
--
Regards,

Martin

Why are you using Booleans for text strings.

This works

Function Function_Name(Code As Integer, Job As String, Country As String)
Dim sFormula As String

sFormula = "SumProduct(--(Rng1=" & Code & ")," & _
"--(Rng2=""" & Job & """)," & _
"--(Rng3=""" & Country & """), (Rng4))"
Function_Name = Evaluate(sFormula)
End Function

But why are you creating a UDF that just does what SUMPRODUCT does anyway?
Not efficient.