Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Matrix Ops Type Mismatch
Windows XP, Excel 2003 So here's the problem: I have a VBA add-in that performs multiple regression and I'm trying to add t-statistic calculations to it. Therefore, I need to perform some matrix operations along the way. Problem is, I keep getting Type Mismatch errors. The independent variables are stored in an array (each to its own column) that is declared as Double and therefore I would assume that manipulations of this array would also be Double and could be declared as either Variant or Double. There's some code below that defines a macro that duplicates my problem. If you declare XMatrix as variant, you throw the error at the ** line. If you declare XMatrix and XTransposeMatrix as double, you throw the error at the ** line. And if you declare XMatrix as double and everything else as variant, you throw the error at the *** line. Go ahead, play with it a little. It's fascinating and frustrating. I can't seem to find the combination that will complete the calculations. I've just been playing around with the variable types looking for a solution because I can't reason what they should be. There must be something I don't know. I also spent some time looking for a command that would return the type of a variable, but couldn't find it, if there is one. If anyone has any ideas, please help! Thanks! Sub Test() Dim IndVariableValues(4, 3) As Double Dim XMatrix() As Double Dim XTransposeMatrix() As Variant Dim XTransposeXMatrix() As Variant Dim HatMatrix() As Variant IndVariableValues(1, 1) = 1 IndVariableValues(1, 2) = 2 IndVariableValues(1, 3) = 3 IndVariableValues(2, 1) = 3 IndVariableValues(2, 2) = 2 IndVariableValues(2, 3) = 4 IndVariableValues(3, 1) = 2 IndVariableValues(3, 2) = 1 IndVariableValues(3, 3) = 5 IndVariableValues(4, 1) = 6 IndVariableValues(4, 2) = 3 IndVariableValues(4, 3) = 4 ReDim XMatrix(UBound(IndVariableValues, 1), UBound(IndVariableValues, 2) + 1) ReDim XTransposeMatrix(UBound(IndVariableValues, 2) + 1, UBound(IndVariableValues, 1)) ReDim XTransposeXMatrix(UBound(IndVariableValues, 2) + 1, UBound(IndVariableValues, 2) + 1) For Index = 1 To UBound(IndVariableValues, 2) XMatrix(Index, 1) = 1 Next Index For Index = 1 To UBound(IndVariableValues, 1) For Index2 = 2 To UBound(IndVariableValues, 2) + 1 XMatrix(Index, Index2) = IndVariableValues(Index, Index2 - 1) Next Index2 Next Index XTransposeMatrix = Application.WorksheetFunction.Transpose(XMatrix) '* XTransposeXMatrix = Application.MMult(XTransposeMatrix, XMatrix) '** HatMatrix = Application.MInverse(XTransposeXMatrix) '*** End Sub -- Stacy35216 ------------------------------------------------------------------------ Stacy35216's Profile: http://www.excelforum.com/member.php...o&userid=15952 View this thread: http://www.excelforum.com/showthread...hreadid=398268 |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Matrix Ops Type Mismatch
Try this:
Sub Test() Dim IndVariableValues(4, 3) As Double Dim XMatrix() As Double Dim XTransposeMatrix() As Variant Dim XTransposeXMatrix() As Variant Dim HatMatrix As Variant IndVariableValues(1, 1) = 1 IndVariableValues(1, 2) = 2 IndVariableValues(1, 3) = 3 IndVariableValues(2, 1) = 3 IndVariableValues(2, 2) = 2 IndVariableValues(2, 3) = 4 IndVariableValues(3, 1) = 2 IndVariableValues(3, 2) = 1 IndVariableValues(3, 3) = 5 IndVariableValues(4, 1) = 6 IndVariableValues(4, 2) = 3 IndVariableValues(4, 3) = 4 ReDim XMatrix(UBound(IndVariableValues, 1), _ UBound(IndVariableValues, 2) + 1) 'ReDim XTransposeMatrix(UBound(IndVariableValues, 2) + 1, _ UBound(IndVariableValues, 1)) 'ReDim XTransposeXMatrix(UBound(IndVariableValues, 2) + 1, _ UBound(IndVariableValues, 2) + 1) For Index = 1 To UBound(IndVariableValues, 2) XMatrix(Index, 1) = 1 Next Index For Index = 1 To UBound(IndVariableValues, 1) For Index2 = 2 To UBound(IndVariableValues, 2) + 1 XMatrix(Index, Index2) = IndVariableValues(Index, Index2 - 1) Next Index2 Next Index XTransposeMatrix = _ Application.WorksheetFunction.Transpose(XMatrix) '* XTransposeXMatrix = Application.MMult(XTransposeMatrix, XMatrix) '** PrintMatrix XMatrix, Range("A1") PrintMatrix XTransposeMatrix, Range("A11") PrintMatrix XTransposeXMatrix, Range("A21") Debug.Print TypeName(Application.MInverse(XTransposeXMatrix)) HatMatrix = Application.MInverse(XTransposeXMatrix) If IsArray(HatMatrix) Then For i = LBound(HatMatrix, 1) To UBound(HatMatrix, 1) For j = LBound(HatMatrix, 2) To UBound(HatMatrix, 2) Debug.Print "HatMatrix(" & i & "," & j & ")=" _ & HatMatrix(i, j) Next Next End If '*** End Sub Sub PrintMatrix(v, rng As Range) rws = UBound(v, 1) - LBound(v, 1) + 1 cols = UBound(v, 2) - LBound(v, 2) + 1 rng.Resize(rws, cols).Value = v End Sub Minverse is returning an error for me. -- Regards, Tom Ogilvy "Stacy35216" wrote in message ... Windows XP, Excel 2003 So here's the problem: I have a VBA add-in that performs multiple regression and I'm trying to add t-statistic calculations to it. Therefore, I need to perform some matrix operations along the way. Problem is, I keep getting Type Mismatch errors. The independent variables are stored in an array (each to its own column) that is declared as Double and therefore I would assume that manipulations of this array would also be Double and could be declared as either Variant or Double. There's some code below that defines a macro that duplicates my problem. If you declare XMatrix as variant, you throw the error at the ** line. If you declare XMatrix and XTransposeMatrix as double, you throw the error at the ** line. And if you declare XMatrix as double and everything else as variant, you throw the error at the *** line. Go ahead, play with it a little. It's fascinating and frustrating. I can't seem to find the combination that will complete the calculations. I've just been playing around with the variable types looking for a solution because I can't reason what they should be. There must be something I don't know. I also spent some time looking for a command that would return the type of a variable, but couldn't find it, if there is one. If anyone has any ideas, please help! Thanks! Sub Test() Dim IndVariableValues(4, 3) As Double Dim XMatrix() As Double Dim XTransposeMatrix() As Variant Dim XTransposeXMatrix() As Variant Dim HatMatrix() As Variant IndVariableValues(1, 1) = 1 IndVariableValues(1, 2) = 2 IndVariableValues(1, 3) = 3 IndVariableValues(2, 1) = 3 IndVariableValues(2, 2) = 2 IndVariableValues(2, 3) = 4 IndVariableValues(3, 1) = 2 IndVariableValues(3, 2) = 1 IndVariableValues(3, 3) = 5 IndVariableValues(4, 1) = 6 IndVariableValues(4, 2) = 3 IndVariableValues(4, 3) = 4 ReDim XMatrix(UBound(IndVariableValues, 1), UBound(IndVariableValues, 2) + 1) ReDim XTransposeMatrix(UBound(IndVariableValues, 2) + 1, UBound(IndVariableValues, 1)) ReDim XTransposeXMatrix(UBound(IndVariableValues, 2) + 1, UBound(IndVariableValues, 2) + 1) For Index = 1 To UBound(IndVariableValues, 2) XMatrix(Index, 1) = 1 Next Index For Index = 1 To UBound(IndVariableValues, 1) For Index2 = 2 To UBound(IndVariableValues, 2) + 1 XMatrix(Index, Index2) = IndVariableValues(Index, Index2 - 1) Next Index2 Next Index XTransposeMatrix = Application.WorksheetFunction.Transpose(XMatrix) '* XTransposeXMatrix = Application.MMult(XTransposeMatrix, XMatrix) '** HatMatrix = Application.MInverse(XTransposeXMatrix) '*** End Sub -- Stacy35216 ------------------------------------------------------------------------ Stacy35216's Profile: http://www.excelforum.com/member.php...o&userid=15952 View this thread: http://www.excelforum.com/showthread...hreadid=398268 |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
Matrix Ops Type Mismatch
I don't think you have a vba array problem but a problem with MInverse. This
function will error if any entire row or column contains all zeros, which XTransposeXMatrix has when when it comes applying to this function. Step through and look in the Locals window (Alt-v, s). Test the function on the worksheet with a similar values a cell array. Regards, Peter T "Stacy35216" wrote in message ... Windows XP, Excel 2003 So here's the problem: I have a VBA add-in that performs multiple regression and I'm trying to add t-statistic calculations to it. Therefore, I need to perform some matrix operations along the way. Problem is, I keep getting Type Mismatch errors. The independent variables are stored in an array (each to its own column) that is declared as Double and therefore I would assume that manipulations of this array would also be Double and could be declared as either Variant or Double. There's some code below that defines a macro that duplicates my problem. If you declare XMatrix as variant, you throw the error at the ** line. If you declare XMatrix and XTransposeMatrix as double, you throw the error at the ** line. And if you declare XMatrix as double and everything else as variant, you throw the error at the *** line. Go ahead, play with it a little. It's fascinating and frustrating. I can't seem to find the combination that will complete the calculations. I've just been playing around with the variable types looking for a solution because I can't reason what they should be. There must be something I don't know. I also spent some time looking for a command that would return the type of a variable, but couldn't find it, if there is one. If anyone has any ideas, please help! Thanks! Sub Test() Dim IndVariableValues(4, 3) As Double Dim XMatrix() As Double Dim XTransposeMatrix() As Variant Dim XTransposeXMatrix() As Variant Dim HatMatrix() As Variant IndVariableValues(1, 1) = 1 IndVariableValues(1, 2) = 2 IndVariableValues(1, 3) = 3 IndVariableValues(2, 1) = 3 IndVariableValues(2, 2) = 2 IndVariableValues(2, 3) = 4 IndVariableValues(3, 1) = 2 IndVariableValues(3, 2) = 1 IndVariableValues(3, 3) = 5 IndVariableValues(4, 1) = 6 IndVariableValues(4, 2) = 3 IndVariableValues(4, 3) = 4 ReDim XMatrix(UBound(IndVariableValues, 1), UBound(IndVariableValues, 2) + 1) ReDim XTransposeMatrix(UBound(IndVariableValues, 2) + 1, UBound(IndVariableValues, 1)) ReDim XTransposeXMatrix(UBound(IndVariableValues, 2) + 1, UBound(IndVariableValues, 2) + 1) For Index = 1 To UBound(IndVariableValues, 2) XMatrix(Index, 1) = 1 Next Index For Index = 1 To UBound(IndVariableValues, 1) For Index2 = 2 To UBound(IndVariableValues, 2) + 1 XMatrix(Index, Index2) = IndVariableValues(Index, Index2 - 1) Next Index2 Next Index XTransposeMatrix = Application.WorksheetFunction.Transpose(XMatrix) '* XTransposeXMatrix = Application.MMult(XTransposeMatrix, XMatrix) '** HatMatrix = Application.MInverse(XTransposeXMatrix) '*** End Sub -- Stacy35216 ------------------------------------------------------------------------ Stacy35216's Profile: http://www.excelforum.com/member.php...o&userid=15952 View this thread: http://www.excelforum.com/showthread...hreadid=398268 |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
Matrix Ops Type Mismatch
Try putting, at the top of your module -
Option Base 1 or declare your arrays like this Dim myArray(1 to x, 1 to x) or if you prefer the fefault 0 base Dim myArray(x - 1, 1 to x - 1) Regards, Peter T "Peter T" <peter_t@discussions wrote in message ... I don't think you have a vba array problem but a problem with MInverse. This function will error if any entire row or column contains all zeros, which XTransposeXMatrix has when when it comes applying to this function. Step through and look in the Locals window (Alt-v, s). Test the function on the worksheet with a similar values a cell array. Regards, Peter T "Stacy35216" wrote in message ... Windows XP, Excel 2003 So here's the problem: I have a VBA add-in that performs multiple regression and I'm trying to add t-statistic calculations to it. Therefore, I need to perform some matrix operations along the way. Problem is, I keep getting Type Mismatch errors. The independent variables are stored in an array (each to its own column) that is declared as Double and therefore I would assume that manipulations of this array would also be Double and could be declared as either Variant or Double. There's some code below that defines a macro that duplicates my problem. If you declare XMatrix as variant, you throw the error at the ** line. If you declare XMatrix and XTransposeMatrix as double, you throw the error at the ** line. And if you declare XMatrix as double and everything else as variant, you throw the error at the *** line. Go ahead, play with it a little. It's fascinating and frustrating. I can't seem to find the combination that will complete the calculations. I've just been playing around with the variable types looking for a solution because I can't reason what they should be. There must be something I don't know. I also spent some time looking for a command that would return the type of a variable, but couldn't find it, if there is one. If anyone has any ideas, please help! Thanks! Sub Test() Dim IndVariableValues(4, 3) As Double Dim XMatrix() As Double Dim XTransposeMatrix() As Variant Dim XTransposeXMatrix() As Variant Dim HatMatrix() As Variant IndVariableValues(1, 1) = 1 IndVariableValues(1, 2) = 2 IndVariableValues(1, 3) = 3 IndVariableValues(2, 1) = 3 IndVariableValues(2, 2) = 2 IndVariableValues(2, 3) = 4 IndVariableValues(3, 1) = 2 IndVariableValues(3, 2) = 1 IndVariableValues(3, 3) = 5 IndVariableValues(4, 1) = 6 IndVariableValues(4, 2) = 3 IndVariableValues(4, 3) = 4 ReDim XMatrix(UBound(IndVariableValues, 1), UBound(IndVariableValues, 2) + 1) ReDim XTransposeMatrix(UBound(IndVariableValues, 2) + 1, UBound(IndVariableValues, 1)) ReDim XTransposeXMatrix(UBound(IndVariableValues, 2) + 1, UBound(IndVariableValues, 2) + 1) For Index = 1 To UBound(IndVariableValues, 2) XMatrix(Index, 1) = 1 Next Index For Index = 1 To UBound(IndVariableValues, 1) For Index2 = 2 To UBound(IndVariableValues, 2) + 1 XMatrix(Index, Index2) = IndVariableValues(Index, Index2 - 1) Next Index2 Next Index XTransposeMatrix = Application.WorksheetFunction.Transpose(XMatrix) '* XTransposeXMatrix = Application.MMult(XTransposeMatrix, XMatrix) '** HatMatrix = Application.MInverse(XTransposeXMatrix) '*** End Sub -- Stacy35216 ------------------------------------------------------------------------ Stacy35216's Profile: http://www.excelforum.com/member.php...o&userid=15952 View this thread: http://www.excelforum.com/showthread...hreadid=398268 |
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
Matrix Ops Type Mismatch
Typo
or if you prefer the fefault 0 base Dim myArray(x - 1, 1 to x - 1) or if you prefer the default 0 base Dim myArray(x - 1, x - 1) where x is the count of required elements, which must be the same for both rows & cols with MINVERSE Peter T Try putting, at the top of your module - Option Base 1 or declare your arrays like this Dim myArray(1 to x, 1 to x) or if you prefer the fefault 0 base Dim myArray(x - 1, 1 to x - 1) Regards, Peter T |
#6
Posted to microsoft.public.excel.programming
|
|||
|
|||
Matrix Ops Type Mismatch
Yeah, well, I guess I'm pretty bad at asking for help. I should have included the line "Option Base 1" above the macro posted above, because that's in my original project, and that difference accounts fora ll the zeroes that shouldn't be there. Unfortunately (for me), adding that line makes the macro run, but I'm still throwing the same error in my original program. I'll try to figure out why copying and pasting to another sub solves the problem (!). In the meantime, thanks for your help anyway. -- Stacy35216 ------------------------------------------------------------------------ Stacy35216's Profile: http://www.excelforum.com/member.php...o&userid=15952 View this thread: http://www.excelforum.com/showthread...hreadid=398268 |
#7
Posted to microsoft.public.excel.programming
|
|||
|
|||
Matrix Ops Type Mismatch
What's the problem, your original routine as-is with Option Base 1 appears
to work fine, giving same results as doing similar in worksheet cells. Regards, Peter T Yeah, well, I guess I'm pretty bad at asking for help. I should have included the line "Option Base 1" above the macro posted above, because that's in my original project, and that difference accounts fora ll the zeroes that shouldn't be there. Unfortunately (for me), adding that line makes the macro run, but I'm still throwing the same error in my original program. I'll try to figure out why copying and pasting to another sub solves the problem (!). In the meantime, thanks for your help anyway. -- Stacy35216 ------------------------------------------------------------------------ Stacy35216's Profile: http://www.excelforum.com/member.php...o&userid=15952 View this thread: http://www.excelforum.com/showthread...hreadid=398268 |
#8
Posted to microsoft.public.excel.programming
|
|||
|
|||
Matrix Ops Type Mismatch
With Option Base1, this ran fine for me:
xl2003 Option Base 1 Sub Test() Dim IndVariableValues(4, 3) As Double Dim XMatrix() As Double Dim XTransposeMatrix() As Variant Dim XTransposeXMatrix() As Variant Dim HatMatrix As Variant IndVariableValues(1, 1) = 1 IndVariableValues(1, 2) = 2 IndVariableValues(1, 3) = 3 IndVariableValues(2, 1) = 3 IndVariableValues(2, 2) = 2 IndVariableValues(2, 3) = 4 IndVariableValues(3, 1) = 2 IndVariableValues(3, 2) = 1 IndVariableValues(3, 3) = 5 IndVariableValues(4, 1) = 6 IndVariableValues(4, 2) = 3 IndVariableValues(4, 3) = 4 ReDim XMatrix(UBound(IndVariableValues, 1), _ UBound(IndVariableValues, 2) + 1) 'ReDim XTransposeMatrix(UBound(IndVariableValues, 2) + 1, _ UBound(IndVariableValues, 1)) 'ReDim XTransposeXMatrix(UBound(IndVariableValues, 2) + 1, _ UBound(IndVariableValues, 2) + 1) For Index = 1 To UBound(IndVariableValues, 2) XMatrix(Index, 1) = 1 Next Index For Index = 1 To UBound(IndVariableValues, 1) For Index2 = 2 To UBound(IndVariableValues, 2) + 1 XMatrix(Index, Index2) = IndVariableValues(Index, Index2 - 1) Next Index2 Next Index XTransposeMatrix = _ Application.WorksheetFunction.Transpose(XMatrix) '* XTransposeXMatrix = Application.MMult(XTransposeMatrix, XMatrix) '** PrintMatrix XMatrix, Range("A1") PrintMatrix XTransposeMatrix, Range("A11") PrintMatrix XTransposeXMatrix, Range("A21") Debug.Print TypeName(Application.MInverse(XTransposeXMatrix)) HatMatrix = Application.MInverse(XTransposeXMatrix) If IsArray(HatMatrix) Then PrintMatrix HatMatrix, Range("A30") End If '*** End Sub Sub PrintMatrix(v, rng As Range) rws = UBound(v, 1) - LBound(v, 1) + 1 cols = UBound(v, 2) - LBound(v, 2) + 1 rng.Resize(rws, cols).Value = v End Sub -- Regards, Tom Ogilvy "Stacy35216" wrote in message ... Yeah, well, I guess I'm pretty bad at asking for help. I should have included the line "Option Base 1" above the macro posted above, because that's in my original project, and that difference accounts fora ll the zeroes that shouldn't be there. Unfortunately (for me), adding that line makes the macro run, but I'm still throwing the same error in my original program. I'll try to figure out why copying and pasting to another sub solves the problem (!). In the meantime, thanks for your help anyway. -- Stacy35216 ------------------------------------------------------------------------ Stacy35216's Profile: http://www.excelforum.com/member.php...o&userid=15952 View this thread: http://www.excelforum.com/showthread...hreadid=398268 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
type mismatch? | Excel Programming | |||
type mismatch here! | Excel Programming | |||
Type mismatch | Excel Programming | |||
Type mismatch | Excel Programming | |||
Type Mismatch | Excel Programming |