MMULT in VBA - 1004 error

[James_Latimer]

Excel 2016

I was hoping someone could give me some pointers please, i'm having some issues with the following and can't quite understand why.

I have the following code

Dim var1 as Variant, var2 as Variant

var1 = Sheets("Sheet1").Range("R16:AC16")
var2 = Sheets("Sheet2").Range("P4:P15")

MsgBox (Application.WorksheetFunction.MMult(var1, var2))

I then get the following error 'Run-time error 1004: Unable to get the MMult property of the WorksheetFunction Class'

If i do the same thing on the front end of Excel (using the same ranges) then it works fine. Can someone throw me a fricking bone here please?

Thank you kind Excelers.

 

[RoryA]

Works fine for me (up until the type mismatch when you try and Msgbox an array). I assume you meant to use two different sheets in the code?

 

[sheetspread]

Returning the 2D array via msgbox may be tricky, but notice that:

Excel 2010
	P	Q	R	S	T	U	V	W	X	Y	Z	AA	AB	AC
4	9		54	9	72	63	63	45	9	90	90	18	54	90
5	4		24	4	32	28	28	20	4	40	40	8	24	40
6	2		12	2	16	14	14	10	2	20	20	4	12	20
7	3		18	3	24	21	21	15	3	30	30	6	18	30
8	4		24	4	32	28	28	20	4	40	40	8	24	40
9	7		42	7	56	49	49	35	7	70	70	14	42	70
10	8		48	8	64	56	56	40	8	80	80	16	48	80
11	3		18	3	24	21	21	15	3	30	30	6	18	30
12	2		12	2	16	14	14	10	2	20	20	4	12	20
13	3		18	3	24	21	21	15	3	30	30	6	18	30
14	4		24	4	32	28	28	20	4	40	40	8	24	40
15	10		60	10	80	70	70	50	10	100	100	20	60	100
16			6	1	8	7	7	5	1	10	10	2	6	10
Sheet1

Cell Formulas
Range	Formula
R4:AC15	{=MMULT(P4:P15,R16:AC16)}
Press CTRL+SHIFT+ENTER to enter array formulas.

while:

Sub mtxmlt()
Dim var1 As Variant, var2 As Variant
var1 = Range("R16:AC16")
var2 = Range("P4:P15")
Range("r4:ac15") = Application.MMult(var2, var1)
End Sub

gives you:

Excel 2010
	P	Q	R	S	T	U	V	W	X	Y	Z	AA	AB	AC
4	9		54	9	72	63	63	45	9	90	90	18	54	90
5	4		24	4	32	28	28	20	4	40	40	8	24	40
6	2		12	2	16	14	14	10	2	20	20	4	12	20
7	3		18	3	24	21	21	15	3	30	30	6	18	30
8	4		24	4	32	28	28	20	4	40	40	8	24	40
9	7		42	7	56	49	49	35	7	70	70	14	42	70
10	8		48	8	64	56	56	40	8	80	80	16	48	80
11	3		18	3	24	21	21	15	3	30	30	6	18	30
12	2		12	2	16	14	14	10	2	20	20	4	12	20
13	3		18	3	24	21	21	15	3	30	30	6	18	30
14	4		24	4	32	28	28	20	4	40	40	8	24	40
15	10		60	10	80	70	70	50	10	100	100	20	60	100
16			6	1	8	7	7	5	1	10	10	2	6	10
Sheet1

 

[shg]

MsgBox isn't happy with an array, even a single-element array like yours.

MsgBox (Application.WorksheetFunction.MMult(var1, var2)[COLOR="#FF0000"](1)[/COLOR])

 

[James_Latimer]

Thanks for the quick replies peeps. Genuinely appreciated.... clearly i dont know what i am doing!
Out of curiosity, I have the following formula within a table which returns the value 0.5166

=((EXP(MMULT($R$16:$AC$16,'Poutput'!$P$4:$P$15))^R15)*EXP(-(EXP(MMULT($R$16:$AC$16,'Poutput'!$P$4:$P$15))))/FACT(R15))

I was wanting to create some code that would generate this table via VBA and just put the values in rather than having all the formulae. I was wanting to create an array to save the results to and then dump the values into a table on a worksheet right at the end...

outputFlows(countCycle, countAttacks) = ((Exp(Application.MMult(fixedP, baselineP) ^ countAttacks) * Exp(-(Exp(Application.MMult(fixedP, baselineP)))) / Application.Fact(countAttacks)))

Where fixedP is a fixed range (row), baselineP is a fixed range (column), countAttacks is an integer between 0 and 11. The idea being that i loop through what would be the columns and rows of the table and generate it in VBA using the above.

Is it even possible to do what i'm after? The formulae don't appear to be entered as array formula.

I'm confused and ultimately need an adult. I hope i'm making sense!!!!!

 

[James_Latimer]

Sorry - should have added. I am now getting System 13 Type Mismatch errors.

 

[James_Latimer]

OK... i've been doing more investigation on this... i did have my output array set as Double. I changed this to Variant and now the MMULT bit works (in isolation). I started breaking the formula down into chunks to test each bit.

I've had success with
outputFlows(countCycle, countAttacks) = Application.MMult(baselinePoisson, fixedBaseline)

But when i try to add the EXP bit in i get a System13 error, i used...
outputFlows(countCycle, countAttacks) = Application.Exp(Application.MMult(baselinePoisson, fixedBaseline) ^ countAttacks)

 

[James_Latimer]

Sorted it.... shg gave me the pointer that i needed.

I sorted it by adding the (1) after the MMULT. So now it does this...

outputFlows(countCycle, countAttacks) = Exp(Application.MMult(baselinePoisson, fixedBaseline)(1)) ^ countAttacks

Clearly i have managed to bundle my way through this. Apologies for me spewing my thought process all over this thread. Thanks to all who have taken the time to offer assistance. You guys are the best!