Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Application.WorksheetFunction.MMult
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 |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Application.WorksheetFunction.MMult
"Gabriel" wrote...
.... 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. |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
Application.WorksheetFunction.MMult
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 -- Regards, Tom Ogilvy Gabriel wrote in message m... 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 |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
Application.WorksheetFunction.MMult
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 Using Windows XP & Office XP = = = = = = = = = = = = = = = = = "Gabriel" wrote in message m... 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 |
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
Application.WorksheetFunction.MMult
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 -- Dana DeLouis = = = = = = = = = = = = = = = = = <snip |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Reuse Application.WorksheetFunction | Excel Discussion (Misc queries) | |||
Application.WorksheetFunction error | Excel Discussion (Misc queries) | |||
Need help with Application.WorksheetFunction | Excel Discussion (Misc queries) | |||
application.worksheetfunction.mmult help | Excel Programming | |||
Using Application.WorksheetFunction.Ln(...) in VBA | Excel Programming |