在excel vba中构造矩阵

I need help in constructing a n x n matrix, where n is equal to the number of remaining stocks at time t for t = 0, ..., 10. Initially, I have in total 10 stocks, which I will discard one by one as time goes by.

我需要帮助构建焦虑矩阵,其中n等于t = 0时的剩余股票数量,...,10。最初,我总共有10个股票,我将逐个丢弃它们作为时间过去了。

Each element of the matrix will be equal to sigma(i) x sigma (j) x rho , where sigma(i)=sigma(j) = 0.25 and rho=0.2

矩阵的每个元素将等于sigma(i)x sigma(j)x rho,其中sigma(i)= sigma(j)= 0.25且rho = 0.2

And the last thing I want to do is to multiply that matrix by the transpose of that matrix

我要做的最后一件事是将该矩阵乘以该矩阵的转置

I am really confused where and how to start, and really thankful for your help

我真的很困惑在哪里以及如何开始,真的很感谢你的帮助

Sub Matrix() 
'Sigmai 'Sigmaj 'Rho 
    Dim Sigmai, Sigmaj, Rho As Single 
    Sigmai = Range("b12").Value 
    Sigmaj = Range("b13").Value 
    Rho = Range("b14").Value 
    Dim matrixelement(10, 10) As Single 
    For n = 1 To 10 
        For m = 1 To 10 
            matrixelement(n, m) = Sigmai * Sigmaj * Rho 
            Cells(n, m) = matrixelement(n, m) 
        Next m 
    Next n 
End Sub

2 个解决方案

#1

There are a number of basic problems with your code, which you should clear up before progressing. See comments in code below:

您的代码存在许多基本问题,您应该在进行之前清除这些问题。请参阅以下代码中的评论:

Sub Matrix()
    ' declare ALL your variables
    Dim n As Long, m As Long
    Dim ws As Worksheet

    ' specify type for all variables, otherwise they will be Variant
    Dim Sigmai As Single, Sigmaj As Single, Rho As Single

    ' Explicitly reference the required sheet
    Set ws = Sheet1 ' or ActiveSheet or whatever

    ' qualify range references with worksheet
    Sigmai = ws.Range("b12").Value
    Sigmaj = ws.Range("b13").Value
    Rho = ws.Range("b14").Value

    ' Specify required lower bound. Default base is 0
    Dim matrixelement(1 To 10, 1 To 10) As Single
    For n = 1 To 10
        For m = 1 To 10
            matrixelement(n, m) = Sigmai * Sigmaj * Rho
        Next m
    Next n

    ' return result to sheet in one go
    ws.Range("A1:J10") = matrixelement

End Sub

For the

And the last thing I want to do is to multiply that matrix by the transpose of that matrix

我要做的最后一件事是将该矩阵乘以该矩阵的转置

part, I not sure what you are after exactly, but MMULT may be usefull, eg

部分,我不确定你到底是什么,但MMULT可能是有用的,例如

ws.Range("L1:U10") = Application.WorksheetFunction.MMult(matrixelement, matrixelement)

#2

You could just pass go on the array and put the result straight to the worksheet. A worksheet if effectively just one big 2 dimensional array.

您可以直接传递数组并将结果直接放到工作表中。一个工作表,如果有效只是一个大的二维数组。

Cannibalising Chris Neilsen's code:

Cannibalizing Chris Neilsen的代码:

Sub Matrix()
' declare ALL your variables
Dim n As Long, m As Long
Dim ws As Excel.Worksheet

' specify type for all variables, otherwise they will be Variant
Dim Sigmai As Single, Sigmaj As Single, Rho As Single

' Explicitly reference the required sheet
Set ws = Sheet1 ' or ActiveSheet or whatever

' qualify range references with worksheet
Sigmai = ws.Range("b12").Value
Sigmaj = ws.Range("b13").Value
Rho = ws.Range("b14").Value

For n = 1 To 10
    For m = 1 To 10
        ws.cells(n,m).value = Sigmai * Sigmaj * Rho
    Next m
Next n

End Sub

a couple of things I don't understand:

我不明白的一些事情:

if b12/b13/b14 are all constants then surely every number in the matrix will be the same ?!

如果b12 / b13 / b14都是常数,那么矩阵中的每个数字肯定都是一样的吗?!

if every number is the same then multiplying by the matrix transposed will just have the square of every number in each cell of the matrix ?!

如果每个数字都相同,那么乘以转置的矩阵就会得到矩阵每个单元格中每个数字的平方?!

#1