vba 在excel vba中构建矩阵
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/11481892/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
constructing a matrix in excel vba
提问by user1525225
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.
我需要帮助构建 anxn 矩阵，其中 n 等于时间 t 的剩余股票数量，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
回答by chris neilsen
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 MMULTmay be usefull, eg
部分，我不确定你到底在追求什么，但MMULT可能有用，例如
ws.Range("L1:U10") = Application.WorksheetFunction.MMult(matrixelement, matrixelement)
回答by whytheq
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:
蚕食 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 ?!
- 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 ?!
- 如果 b12/b13/b14 都是常数，那么矩阵中的每个数字肯定都是相同的？！
- 如果每个数字都相同，那么乘以转置的矩阵将只是矩阵每个单元格中每个数字的平方？！
