Application.WorksheetFunction.MMult

[Gabriel]

Hi,

I just want to write a quick function in VBA which performs the the
matrix multiplication of a vector with itself transposed.

In a worksheet the calcul would look like this
{=MMULT(myVector,TRANSPOSE(myVector))}. If myVector is for instance
7;8;9 then the result would simply be 49. However, when I call the
Test vba Function it just doesn't work:


Function Test(myVector) As Double

Test = Application.WorksheetFunction.MMult(MyVector, _
Application.WorksheetFunction.Transpose(MyVector))

End Function

Am I missing somethin here?
Thank you in advance
Gabriel

 

[Harlan Grove]

In a worksheet the calcul would look like this
{=MMULT(myVector,TRANSPOSE(myVector))}. If myVector is for instance
7;8;9 then the result would simply be 49. However, when I call the
Test vba Function it just doesn't work:

Function Test(myVector) As Double

Test = Application.WorksheetFunction.MMult(MyVector, _
Application.WorksheetFunction.Transpose(MyVector))

End Function

Am I missing somethin here?

Yes - an explanation of what you mean by 'it just doesn't work'. Does it
generate syntax errors, runtime errors, or return the wrong result?

In any case, it's a mystery why you're not using

Test = Application.WorksheetFunction.SumProduct(myVector, myVector)

Note: if your vector is a vertical vector, then

MMULT({a;b;c},TRANSPOSE({a;b;c})) = {a*a,a*b,a*c;b*a,b*b,b*c;c*a,c*b,c*c}

Meaning that if you want a scalar result (the dot product of a vector with
itself), then for horizontal vectors your function is OK, but for vertical
vectors you must use

Test = Application.WorksheetFunction.MMult( _
Application.WorksheetFunction.Transpose(MyVector), _
MyVector)

If vectors could be both vertical and horizontal, SUMPRODUCT makes much more
sense than MMULT.

 

[Tom Ogilvy]

It returns an array, not a double: in the case of {7,8,9}, an array with
one element.
(this is a US interpretaton of {7,8,9} which is a row array/vector)

Function Test(myVector) As Variant
Test = Application.MMult(myVector, _
Application.Transpose(myVector))

End Function
Sub Tester5()
Dim varr(1 To 1, 1 To 3) As Variant, varr1 As Variant, i As Long
varr(1, 1) = 7
varr(1, 2) = 8
varr(1, 3) = 9
Debug.Print TypeName(Test(varr)), LBound(varr), UBound(varr)
varr1 = Test(varr)
For i = LBound(varr1) To UBound(varr1)
Debug.Print i, varr1(i)
Next
End Sub

Results would be 194

if your vector were 7;8;9, then it would return a 3 x 3 array
(this is a US interpretation of 7;8;9 which is a column array/vector)

49, 56, 63
56, 64, 72
63, 72, 81

Sub Tester6()
Dim varr(1 To 3, 1 To 1) As Variant, varr1 As Variant
Dim j As Long, i As Long, sStr As String
varr(1, 1) = 7
varr(2, 1) = 8
varr(3, 1) = 9
Debug.Print TypeName(Test(varr)), LBound(varr), UBound(varr)
varr1 = Test(varr)
For i = LBound(varr1, 1) To UBound(varr1, 1)
For j = LBound(varr1, 2) To UBound(varr1, 2)
If j < UBound(varr1, 2) Then
sStr = sStr & varr1(i, j) & ", "
Else
sStr = sStr & varr1(i, j)
End If
Next
Debug.Print sStr
sStr = ""
Next
End Sub

 

[Dana DeLouis]

Just to add. If you got a single answer of 49, you were most likely
selecting 1 cell and Array entering your equation. You need to select a 3*3
area and then Array enter your equation. I believe your vba function would
work if you did not make the function return a double. (it's an array).
Here is your equation that returns a 3*3 array. As the others mentioned,
it depends on if you are passing a horizontal or vertical array to the
function. If you want, you can test for that in your function.

Function Test(myVector) As Variant
With Application.WorksheetFunction

' If you want the Dot Product, include the next line
' myVector = .Transpose(myVector)

Test = .MMult(myVector, .Transpose(myVector))
End With
End Function

Sub TestIt()
Dim Answer
[A1] = 7
[A2] = 8
[A3] = 9
Answer = Test([A1:A3].Value)
End Sub

 

[Dana DeLouis]

This might be 3 different ways for the Dot product if one is passing a
vertical array.


Function Test(myVector) As Variant
With Application.WorksheetFunction

' If you want the Dot Product, one can use
Test = .Transpose(myVector)
Test = .MMult(Test, .Transpose(Test))(1)

' or... (Reverse the order)
Test = .MMult(.Transpose(myVector), myVector)(1)

' or...
Test = .SumProduct(myVector, myVector)

' 3*3 Array
Test = .MMult(myVector, .Transpose(myVector))
End With
End Function

Sub TestIt()
Dim Answer
[A1] = 7
[A2] = 8
[A3] = 9
Answer = Test([A1:A3].Value)
End Sub