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屋！