Matrix Ops Type Mismatch

[Stacy35216]

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

[Tom Ogilvy]

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" <[email protected]> wrote
in message news:[email protected]...
>
> 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
>

[Peter T]

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" <[email protected]> wrote
in message news:[email protected]...
>
> 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
>

[Stacy35216]

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.

[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

"Peter T" <peter_t@discussions> wrote in message
news:#[email protected]...
> 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" <[email protected]>
wrote
> in message news:[email protected]...
> >
> > 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
> >
>
>

[Peter T]

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
>

[Peter T]

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
>

[Tom Ogilvy]

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" <[email protected]> wrote
in message news:[email protected]...
>
> 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
>