Excel XP VBA Array formula

[SMS - John Howard]

The first two sub procedures below work OK

The third one however, produces a Type Mismatch error.

Do VBA Array formulas not accept variable arguments or am I miss-keying
something?

All help gratefully accepted.

Dim rep As String
Dim pco As String

Sub addpco()

Range("K1") = "005522"
Range("L1") = "PRI"

rep = "005522"
pco = "PRI"

TotalPRI = [Sum((A2:A14=K1)*(D214=L1)*(G2:G14))]
MsgBox TotalPRI

TotalPRI = [Sum((A2:A14="005522")*(D214="PRI")*(G2:G14))]
MsgBox TotalPRI

TotalPRI = [Sum((A2:A14=rep)*(D214=pco)*(G2:G14))]
MsgBox TotalPRI

End Sub


Thanks in Anticipation

John Howard
Sydney, Australia

 

[Charles Williams]

[ ] notation for the Evaluate method requires a string between the [] rather
than a mixture of string and VBA variables.
If you want to construct the string using VBA you should use
Application.Evaluate or worksheet.Evaluate rather than []

Also you need to be careful when using Application.Evaluate with unqualified
range references like D214 because they refer to whatever happens to be
the active worksheet at evaluation time: safer to use worksheet.evaluate or
qualified range references.

Charles
______________________
Decision Models
FastExcel 2.1 now available
www.DecisionModels.com

 

[SMS - John Howard]

Thanks Charles.
I will experiment around your suggestions.

[ ] notation for the Evaluate method requires a string between the [] rather
than a mixture of string and VBA variables.
If you want to construct the string using VBA you should use
Application.Evaluate or worksheet.Evaluate rather than []

Also you need to be careful when using Application.Evaluate with unqualified
range references like D214 because they refer to whatever happens to be
the active worksheet at evaluation time: safer to use worksheet.evaluate or
qualified range references.

Charles
______________________
Decision Models
FastExcel 2.1 now available
www.DecisionModels.com

The first two sub procedures below work OK

The third one however, produces a Type Mismatch error.

Do VBA Array formulas not accept variable arguments or am I miss-keying
something?

All help gratefully accepted.

Dim rep As String
Dim pco As String

Sub addpco()

Range("K1") = "005522"
Range("L1") = "PRI"

rep = "005522"
pco = "PRI"

TotalPRI = [Sum((A2:A14=K1)*(D214=L1)*(G2:G14))]
MsgBox TotalPRI

TotalPRI = [Sum((A2:A14="005522")*(D214="PRI")*(G2:G14))]
MsgBox TotalPRI

TotalPRI = [Sum((A2:A14=rep)*(D214=pco)*(G2:G14))]
MsgBox TotalPRI

End Sub


Thanks in Anticipation

John Howard
Sydney, Australia