Create matrix3 from: constant * matrix1 + (1-constant) * matrix 2

Question

I need help creating a matrix that corresponds to this equation: c * matrix1 + (1-c) * matrix2

Where matrix1 is given by:

Function VarCovarZeros(InputMatix As Range) As Variant
    Dim MatrixColumns  As Long
    MatrixColumns = InputMatix.Columns.Count

    Dim MatrixRows  As Long
    MatrixRows = InputMatix.Rows.Count

    Dim Matrix() As Double
    ReDim Matrix(1 To MatrixColumns, 1 To MatrixColumns)

    Dim i As Long
    For i = 1 To MatrixColumns
        Matrix(i, i) = Application.WorksheetFunction.Covar(InputMatix.Columns(i), InputMatix.Columns(i)) * MatrixRows / (MatrixRows - 1)
    Next i

    VarCovarZeros = Matrix

End Function

And matrix 2 is given by:

Function VarCovar(rng As Range) As Variant
    Dim i As Integer
    Dim j As Integer
    Dim numcols As Integer

    numcols = rng.Columns.Count
    numrows = rng.Rows.Count

    Dim Matrix() As Double
    ReDim Matrix(numcols - 1, numcols - 1)

    For i = 1 To numcols
        For j = 1 To numcols
            Matrix(i - 1, j - 1) = Application.WorksheetFunction.Covar(rng.Columns(i), rng.Columns(j)) * numrows / (numrows - 1)
        Next j
    Next i
    VarCovar = Matrix

End Function

The matrices are based on the same raw data, but needs to be updated dynamically every period, why I not only need it for the two static matrices.

I want to put it in an excel function so that: c is in "M1"

minverse(M1*VarCovarZeros(A3:F27)+(1-c)*(VarCovar(A3:F27))

But it's like it is not reading the "M1*VarCovarZeros(A3:F27)+(1-c)*(VarCovar(A3:F27))" part as one matrix, since it is not giving the results it is supposed to.

That's why I need help with creating the matrix with VBA, so it will be posible to say:

minverse(matrix3(A3:F27))

Anyone that can help?

You cannot multiply a constant `M1` with a Matrix\Array in VBA. `M1*VarCovarZeros(A3:F27)` won't work. Instead you must loop through the matrix/array and multiply each value with your factor `M1`. — Pᴇʜ, Mar 29 '19 at 11:10
I guess you know how to google for *"vba multiply array by constant"*? Also see [Multiplying arrays with scalars and adding in VBA](https://stackoverflow.com/questions/12979034/multiplying-arrays-with-scalars-and-adding-in-vba) approach would be similar. — Pᴇʜ, Mar 29 '19 at 12:00

Create matrix3 from: constant * matrix1 + (1-constant) * matrix 2

0 Answers0