Using MMult output in equation

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

Please Login or Register  to view this content.

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

Originally Posted by 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.

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