PDA

View Full Version : [SOLVED] Sumproduct in VBA



Bazman
04-07-2009, 12:11 AM
Hi there,

Got two problems in one little segment of code!

I want to enter the IF statement "If" output is "M1" or "M2" what syntax should I be using.

Also I want to be able to use SUMPRODUCT in VBA but it does not seem to work the program just stops when I try to call it? Again what syntax should I use?


If output = ("M1" Or "M") Then
Dim Prob() As Double
ReDim Prob(N)
For i = 1 To N
Prob(i) = Application.Max(p2 ^ i * (1 - p2) ^ (N - i))
Next i
M1 = Application.SumProduct(Prob, Val)
M2 = Application.SumProduct(Prob, Val ^ 2)
End If

Thanks

Baz

mdmackillop
04-07-2009, 12:54 AM
If output = "M1" Or output = "M" Then


You cannot use SumProduct directly in VBA, you need to EVALUATE it.
Have a look here for more info.
http://www.xldynamic.com/source/xld.SUMPRODUCT.html

Bob Phillips
04-07-2009, 01:04 AM
With OR and AND functions, you need to test both indivifually like so


If output = "M1" Or output = "M" Then

SUMPRODUCT is an array formula, so you cannot use Applictaion, you have to evaluate it


M1 = Application.Evaluate("SumProduct(" & Prob & "," & Val & ")")

Bob Phillips
04-07-2009, 01:05 AM
Oopos, didn't see Malcolm's response.

Bazman
04-07-2009, 01:33 AM
thanks!

To all of you.

Baz