Using MMult output in equation

[RME26]

Hi

I have a user function which defines two vectors (as arrays) and multiplies them together to get a single value.
This works fine and if I stop there the function will display this value in the cell, no problem.

However, if I want to do anything else with the value within the function e.g. add or subtract another number, then I get a #VALUE! error in the cell

I assume this is because of some incompatibility with variable types, but can't seem to find a way to make it work.

Any help would be much appreciated

 Function abc(x)

Dim theta As Variant, Xvec As Variant
theta = Array(1, 2, 3, 4, 5, 6)
Xvec = Array(1, 2, 3, 4, 5, 6)
rownum = Application.Caller.Row

Xvec(0) = 1
Xvec(1) = Cells(rownum, 2)
Xvec(2) = Cells(rownum, 3)
Xvec(3) = Cells(rownum, 4)
Xvec(4) = Cells(rownum, 5)
Xvec(5) = Cells(rownum, 6)

theta = Application.Transpose(theta)

Dim Z As Variant
Z = Application.MMult(Xvec, theta)

abc = Z + 1

End Function

[MrShorty]

I set up a quick spreadsheet and added a stop statement to the top of your procedure. Then, stepping through the procedure, I see that z is a variant containing a one element array after being assigned the result of the matrix multiplication (MMULT() returns an array even when it is a single element). The abc= assignment statement is treating z as a scalar variable, and you get an error. By modifying the assignment statement to treat z as an array (abc=z(1)+1), the procedure ran without error.

[RME26]

Thanks MrShorty, that works great.

One quick follow up if I may - I'd tried this already using Z(0) on the basis that Arrays in VBA start at zero (I thought Z(0) would give the first element in an array). Obviously this didn't work, but I don't understand why

[MrShorty]

VBA arrays default to 0, but Excel arrays default to 1. I believe this behavior is because the MMULT() function is an Excel function, so it sends a base-1 array back to VBA.

[RME26]

VBA arrays default to 0, but Excel arrays default to 1. I believe this behavior is because the MMULT() function is an Excel function, so it sends a base-1 array back to VBA.

Ha, nice one Microsoft. Thanks for your help, much appreciated