VBA:求矩阵 [英] VBA: Summing a matrix
问题描述
在Excel中键入 = funtest(2.1)
我会给我 #VALUE!
。
公共功能funtest(a As Double)As Double
Dim z,j,i As Integer
Dim matrix(3,3,3)As Double
对于z = 0至3步骤1
对于j = 0至3步骤1
对于i = 0到3步骤1
矩阵(z,j,i)= a
下一个i,j,z
funtest = Application.WorksheetFunction .Sum(matrix)
结束函数
WorksheetFunction.Sum
将适用于范围或2维数组。它错误,因为你传递一个三维数组。
所以,这个工作
code>公共函数funtest(a As Double)As Double
Dim z As Long,j As Long,i As Long
Dim matrix()As Double
ReDim矩阵(0到3,0到4)
对于j = LBound(矩阵,1)到UBound(matrix,1)
对于i = LBound(matrix,2)To UBound(matrix,2)
矩阵(j,i)= a
下一个i,j
funtest = Application.WorksheetFunction.Sum(matrix)
结束函数
注意我修改了你的声明,见答案末尾的注释。
一个选项(可能或可能不适合您的主要要求)是稍微声明您的阵列不同的是,作为所谓的 Jagged Array
。
公共功能funtest2(a As Double)As Double
Dim z As Long,j As Long,as As Long
Dim matrix()As Variant
Dim InnerMatrix(0 To 4,0 To 4)As Double
'Dimension Jagged Array
ReDim matrix(0 To 4)
对于i = LBound(matrix,1)To UBound(matrix,1)
matrix(i)= InnerMatrix
下一个
'将数据加载到矩阵
对于z = LBound(矩阵)到UBound(matrix)
对于j = LBound(matrix(z),1)到UBound(matrix(z),1)
对于i = LBound (z),2)到UBound(矩阵(z),2)
矩阵(z)(j,i)= a
下一个i,j,z
'总和矩阵
对于z = LBound(矩阵)到UBound(矩阵)
funtest2 = funtest2 + Application.WorksheetFunction.Sum(matrix(z))
下一个
结束函数
这是一个二维数组的数组。然后将 Sum
依次应用于每个内部数组。这样,至少你只是循环一个维度,而不是所有三个。
注意 Dim
和整数
您必须指定所有 As Type
,否则变量默认为变体
在您的代码 z
和 j
将变体
另外,使用整数
而不是 Long
实际上在32位操作系统上是有效的: Long
将会稍快一些。
Why doesn't this function work?
Type =funtest(2.1)
in Excel and it'll give me #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
WorksheetFunction.Sum
will work with either a range or a 2 dimentional array. It errors because you are passing it a 3 dimensional array.
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
.
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 Sum
is then applied to each of the inner arrays in turn. This way, at least you are only looping one dimension and not all three.
Note on Dim
and Integer
You must specify all As Type
's, otherwise variables default to Variant
In your code z
and j
will be Variants
Also, using Integer
rather than Long
is actually counter productive on a 32 bit OS: Long
's will be slightly faster.
这篇关于VBA:求矩阵的文章就介绍到这了,希望我们推荐的答案对大家有所帮助,也希望大家多多支持IT屋!