VBA：求和矩阵
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/12380725/
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
VBA: Summing a matrix
提问by
Why doesn't this function work?
为什么这个功能不起作用？
Type =funtest(2.1)in Excel and it'll give me #VALUE!.
键入=funtest(2.1)在Excel中，它会给我#VALUE!。
Public Function funtest(a As Double) As Double
Dim z, j, i As Integer
Dim matrix(3, 3, 3) As Double
For z = 0 To 3 Step 1
For j = 0 To 3 Step 1
For i = 0 To 3 Step 1
matrix(z, j, i) = a
Next i, j, z
funtest = Application.WorksheetFunction.Sum(matrix)
End Function
采纳答案by chris neilsen
WorksheetFunction.Sumwill work with either a range or a 2 dimentional array. It errors because you are passing it a 3 dimensional array.
WorksheetFunction.Sum将适用于范围或二维数组。它出错是因为您传递给它一个 3 维数组。
So, this works
所以，这有效
Public Function funtest(a As Double) As Double
Dim z As Long, j As Long, i As Long
Dim matrix() As Double
ReDim matrix(0 To 3, 0 To 4)
For j = LBound(matrix, 1) To UBound(matrix, 1)
For i = LBound(matrix, 2) To UBound(matrix, 2)
matrix(j, i) = a
Next i, j
funtest = Application.WorksheetFunction.Sum(matrix)
End Function
Note I have modified your declarations slighly, see note at end of answer.
注意我稍微修改了您的声明，请参阅答案末尾的注释。
To sum higher dimensional arrays you will need to do some looping.
要对更高维的数组求和，您需要进行一些循环。
One option (which may or may not suit your overal requirements) is to declare your array slightly differently, as a so called Jagged Array.
一种选择（可能适合也可能不适合您的总体要求）是稍微不同地声明您的数组，作为所谓的Jagged Array.
Public Function funtest2(a As Double) As Double
Dim z As Long, j As Long, i As Long
Dim matrix() As Variant
Dim InnerMatrix(0 To 4, 0 To 4) As Double
' Dimension Jagged Array
ReDim matrix(0 To 4)
For i = LBound(matrix, 1) To UBound(matrix, 1)
matrix(i) = InnerMatrix
Next
'Load Data into matrix
For z = LBound(matrix) To UBound(matrix)
For j = LBound(matrix(z), 1) To UBound(matrix(z), 1)
For i = LBound(matrix(z), 2) To UBound(matrix(z), 2)
matrix(z)(j, i) = a
Next i, j, z
' Sum matrix
For z = LBound(matrix) To UBound(matrix)
funtest2 = funtest2 + Application.WorksheetFunction.Sum(matrix(z))
Next
End Function
This is an array of 2 dimensional arrays. The Sumis then applied to each of the inner arrays in turn. This way, at least you are only looping one dimension and not all three.
这是一个二维数组的数组。将Sum然后被施加到每个反过来内阵列。这样，至少你只循环了一个维度而不是所有三个维度。
Note on Dimand Integer
You must specify all As Type's, otherwise variables default to Variant
In your code zand jwill be Variants
注意Dim和Integer
你必须指定所有As Type的，否则变量默认为Variant
在你的代码中，z并且j将是Variants
Also, using Integerrather than Longis actually counter productive on a 32 bit OS: Long's will be slightly faster.
此外，在 32 位操作系统上使用Integer而不是Long实际上适得其反：Long's 会稍微快一点。
回答by Doug Glancy
I'm going to take you literally when you say "I'm trying to solve the simple case of a (3,3,3) matrix with each element equal to some double, a". This will do that:
当您说“我正在尝试解决 (3,3,3) 矩阵的简单情况，其中每个元素都等于某个双精度值 a”时，我将按字面意思理解您。这将做到这一点：
Public Function funtest(a As Double) As Double
funtest = 4*4*4*a
End Function
回答by Todd Moses
First, when you get #VALUE!this means there is an error, it can mean using a matrix that is not valid.
首先，当您得到#VALUE!这意味着存在错误时，这可能意味着使用了无效的矩阵。
To answer your question, your code does not work because your syntax is not correct. The following function creates a matrix from values.
要回答您的问题，您的代码不起作用，因为您的语法不正确。以下函数根据值创建矩阵。
Function FQ_matrix_create(StartValue As Double, Interval As Double,
nrow As Long, ncol As Long) As Double()
Dim M() As Double
' Creates matrix with sequential element values with given row and
' column sizes. Fills matrix row-wise with numbers.
' - set Interval = 0 for constant element values
' - error input arguments nrow and ncol are not positive integers
To SUM the Values use:
要对值求和，请使用：
Function FQ_matrix_element_sum(M() As Double, SumOption As
MatrixDirection) As Double()
' Returns the sum of elements of matrix M, either row or column wise
' - Rowwise sum returns a horizontal 1xNcol matrix
' - Columnwise sum returns a vertical 1 xNrow matrix
' - Element sum (all elements) returns a 1x1 matrix
' - error if M is not a matrix
' - error if SumOption is not 1 (nRowWiseSum) or 2 (nColWiseSum) or
3 (nElementSum)
To help you understand Matrix in Excel VBA, here is a good resource: http://finaquant.com/download/matrixvectorvba
为了帮助您理解 Excel VBA 中的矩阵，这里有一个很好的资源：http: //finaquant.com/download/matrixvectorvba
Specifically, take a look at the PDF Download on the site.
具体来说，请查看网站上的 PDF 下载。
