#VALUE, Variant to double type conversion problem in VBA programming

Question

Option Explicit

Function MatrixMulta(aMatrix As Range, bMatrix As Range, cl As Double, price As Double)

Dim cMatrix As Variant

Dim final As Double

cMatrix = Application.MMult(aMatrix, bMatrix)

cMatrix = Application.MMult(cMatrix, Application.Transpose(aMatrix))

cl = Application.WorksheetFunction.Norm_S_Dist(cl, True)

final = Sqr(cMatrix) * cl * price

MatrixMulta = final

End Function

aMatrix = 1x3 matrix

bMatrix = 3x3 matrix

cl = 0.95

price = 26023

Excel is showing #VALUE error.

Problem: The resultant of matrix multiplication is in variant form but input value for the below formula must be in double.

    final = sqr(cMatrix) * cl * price

Answer 1

cMatrix is not a 1x1 matrix, your conclusion is wrong, see screenshot below.

E2:F3: {=MMULT(B2:C3,B6:C7)}

E6:F7: {=MMULT(E2:F3,TRANSPOSE(B2:C3))}

Please read the description section in the article I mentioned:

"The result is an array with the same number of rows as array1 and the same number of columns as array2."

If you want the top left item of cMatrix and always ignore the others, try this code:

Function MatrixMulta(ByVal aMatrix As Range, ByVal bMatrix As Range, ByVal cl As Double, ByVal price As Double)
  Dim cMatrix As Variant
  With WorksheetFunction
    cMatrix = .MMult(.MMult(aMatrix, bMatrix), Application.Transpose(aMatrix))
    cl = .Norm_S_Dist(cl, True)
  End With
  MatrixMulta = Sqr(cMatrix(1, 1)) * cl * price
End Function

Andreas.

Answer 2

last cMatrix calculation resulted in a 1x1 matrix. So I thought that it might work as a number.

But I also tried this:

Option Explicit

Function MatrixMulta(aMatrix As Range, bMatrix As Range, cl As Double, price As Double)

Dim cMatrix As Variant

Dim final As Double

Dim sd As Double

cMatrix = Application.MMult(aMatrix, bMatrix)

cMatrix = Application.MMult(cMatrix, Application.Transpose(aMatrix))

sd = CDbl(cMatrix)    <-------

cl = Application.WorksheetFunction.Norm_S_Dist(cl, True)

final = Sqr(sd) * cl * price

MatrixMulta = final

End Function

Answer 3

It doesn't matter if the argument is a double or variant. The problem is that MMLUT returns an array, see

https://support.office.com/en-us/article/mmult-...

But VBA.SQR requires a number!

How do you want to calculate the square root from an array?

Andreas.