UDF with Sumproduct
 

I have the following code that I'm trying to get working:

Function Approved(ProgramName As String, PA As String, _
AffirmationType As String) As Variant
Dim num As Variant
Dim denom As Variant
Dim PAStatusAddress As String
Dim PATypeAddress As String
Dim PAProgramAddress As String

Debug.Print ProgramName
Debug.Print PA
Debug.Print AffirmationType

If LCase(AffirmationType) = "direct" Then
AffirmationType = "d"
ElseIf LCase(AffirmationType) = "indirect" Then
AffirmationType = "i"
End If

PAStatusAddress = PA & "!" & Range(PA & "_Status").Address
PATypeAddress = PA & "!" & Range(PA & "_Type").Address
PAProgramAddress = PA & "!" & Range(PA & "_Program").Address

Debug.Print PAStatusAddress, PATypeAddress, PAProgramAddress
'Addresses are correct at this point.
'The following line doesn't evaluate Gives an ERROR 2029. What should I do?

Debug.Print Evaluate("SumProduct(--(PAStatusAddress = ""Approved""))")

num = Evaluate("SumProduct(--(PAStatusAddress =
""Approved""),--(lcase(PATypeAddress) = AffirmationType),
--(PAProgramAddress = ProgramName))")
Debug.Print num

'denom = SumProduct(--(Range(PA & "_Type") = AffirmationType), _
--(Range(PA & "_Program") = ProgramName))
Debug.Print denom

If denom 0 Then
Approved = num / denom
Else
Approved = "N/A"
'How do I get it to enter NA in the cell in a function. something like this:
' activecell.formular1c1 = "=NA()" ???
End If


End Function

UDF with Sumproduct
 

Demo'd from the immediate window:

PAStatusAddress = "Sheet2!C2:C200"
? Evaluate("SumProduct(--(" & PAStatusAddress & " = ""Approved""))")
3

--
Regards,
Tom Ogilvy


"Barb Reinhardt" wrote:

I have the following code that I'm trying to get working:

Function Approved(ProgramName As String, PA As String, _
AffirmationType As String) As Variant
Dim num As Variant
Dim denom As Variant
Dim PAStatusAddress As String
Dim PATypeAddress As String
Dim PAProgramAddress As String

Debug.Print ProgramName
Debug.Print PA
Debug.Print AffirmationType

If LCase(AffirmationType) = "direct" Then
AffirmationType = "d"
ElseIf LCase(AffirmationType) = "indirect" Then
AffirmationType = "i"
End If

PAStatusAddress = PA & "!" & Range(PA & "_Status").Address
PATypeAddress = PA & "!" & Range(PA & "_Type").Address
PAProgramAddress = PA & "!" & Range(PA & "_Program").Address

Debug.Print PAStatusAddress, PATypeAddress, PAProgramAddress
'Addresses are correct at this point.
'The following line doesn't evaluate Gives an ERROR 2029. What should I do?

Debug.Print Evaluate("SumProduct(--(PAStatusAddress = ""Approved""))")

num = Evaluate("SumProduct(--(PAStatusAddress =
""Approved""),--(lcase(PATypeAddress) = AffirmationType),
--(PAProgramAddress = ProgramName))")
Debug.Print num

'denom = SumProduct(--(Range(PA & "_Type") = AffirmationType), _
--(Range(PA & "_Program") = ProgramName))
Debug.Print denom

If denom 0 Then
Approved = num / denom
Else
Approved = "N/A"
'How do I get it to enter NA in the cell in a function. something like this:
' activecell.formular1c1 = "=NA()" ???
End If


End Function

UDF with Sumproduct
 

Hi Barb,

Error 2029 is not a VBA error, it is the result of your formula and is
equal to the #NAME error.
Three reasons for that.

1) lcase(PATypeAddress)
lcase is a VBA function, the equivalent worksheet function is
LOWER

2) lower(PATypeAddress) = AffirmationType
In this case Excel looks for two named ranges PATypeAddress and
AffirmationType
To insert the value of AffirmationType in your formula you should
write
lower(PATypeAddress) = """ & AffirmationType & """
Note that the quotes appear three times each occasion. Putting a
double quote twice in a string makes VBA put it once in the output.
The third one is simply to end the string so you can insert your
parameter. So VBA passes the string
lower(PATypeAddress) = "d"
to the evaluate function.
3) The same goed for PAProgramAddress = ProgramName
So your line should read in full
num = Evaluate("SumProduct(--(PAStatusAddress =""Approved""),--
(lower(PATypeAddress) = """ & _
AffirmationType & """),--(PAProgramAddress = """ & ProgramName &
"""))")

(and ofcourse all named ranges should exist and be of the same size)

I didn't check the calculation for 'denom' but it will be similar.

Finally, to get the #N/A error in your cell
Approved = CVErr(xlErrNA)

In general you can use CVErr to return all of the Excel errors #NAME,
#DIV/0, ... in a cell. Look for XLCVError in the object browser for a
list of all the constants.

DQ

UDF with Sumproduct
 

Thanks all. I didn't notice that I missed a bunch of "" and & for my
formula. I still had problems but opted to use an alternate method.

"dq" wrote:

Hi Barb,

Error 2029 is not a VBA error, it is the result of your formula and is
equal to the #NAME error.
Three reasons for that.

1) lcase(PATypeAddress)
lcase is a VBA function, the equivalent worksheet function is
LOWER

2) lower(PATypeAddress) = AffirmationType
In this case Excel looks for two named ranges PATypeAddress and
AffirmationType
To insert the value of AffirmationType in your formula you should
write
lower(PATypeAddress) = """ & AffirmationType & """
Note that the quotes appear three times each occasion. Putting a
double quote twice in a string makes VBA put it once in the output.
The third one is simply to end the string so you can insert your
parameter. So VBA passes the string
lower(PATypeAddress) = "d"
to the evaluate function.
3) The same goed for PAProgramAddress = ProgramName
So your line should read in full
num = Evaluate("SumProduct(--(PAStatusAddress =""Approved""),--
(lower(PATypeAddress) = """ & _
AffirmationType & """),--(PAProgramAddress = """ & ProgramName &
"""))")

(and ofcourse all named ranges should exist and be of the same size)

I didn't check the calculation for 'denom' but it will be similar.

Finally, to get the #N/A error in your cell
Approved = CVErr(xlErrNA)

In general you can use CVErr to return all of the Excel errors #NAME,
#DIV/0, ... in a cell. Look for XLCVError in the object browser for a
list of all the constants.

DQ