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.

 

[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

 

[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

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


in message news:[email protected]...

 

[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

 

[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]

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

 

[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