如何在 VBA 中引用变量矩阵中的整列或整行?

Question

我花了很多时间寻找一种方法，如何在我在 VBA 中创建的矩阵中引用范围(列或行(。

一个简单的示例是循环遍历 4 x x 矩阵的第 4 行，以对列 x 的第 1 到 3 列求和。

下面的代码是很长的路要走：

x = 10
Dim Matrix() As Variant
ReDim Matrix(1 to 4, 1 to x)
For c = 1 to x
Matrix(4, c) = Application.WorksheetFunction.Sum(Matrix(1, c), Matrix(2, c), Matrix(3, c))
Next

我正在寻找一种没有错误的方法：

For c = 1 to x
Matrix(4, c) = Application.WorksheetFunction.Sum(Range(Matrix(1, c), Matrix(3, c)))
Next

当我尝试运行它时弹出以下错误：

*Run-time error '1004': Method 'range' of object '_Global' failed*

您能否帮助解决此问题，因为其他解决方法似乎相当耗时。

谢谢！

安 迪

Answer 1

正如 James Poag 所说，范围不是一个全局函数，必须与工作表相关联调用。 如果要使用该WorksheetFunction.Sum则必须将矩阵复制到工作表中。 在下面的示例中：

1. 创建一个 4 x 10 的随机数矩阵，
2. 创建一个新的工作表，
3. 将矩阵复制到工作表，
4. 使用WorksheetFunction.Sum添加区域的行，以及

5. 删除VBA宏添加的工作表

   Option Explicit
   Public Sub Matrixer()
   Dim x As Long
   x = 10
   Dim matrix() As Double
   ReDim matrix(1 To 4, 1 To x)
   'Generate the matrix
   Dim rowNDX As Long
   Dim colNDX As Long
   For rowNDX = 1 To UBound(matrix, 1)
   For colNDX = 1 To UBound(matrix, 2)
   Randomize
   matrix(rowNDX, colNDX) = Rnd
   Next colNDX
   Next rowNDX
   'Write the maxtrix to a sheet
   'First add a worksheet to do the calculation
   Dim wb As Workbook: Set wb = ActiveWorkbook
   Dim strName As String: strName = "MATRIXCALC"
   Dim ws As Worksheet
   Set ws = wb.Worksheets.Add(Type:=xlWorksheet)
   With ws
   .Name = strName
   End With
   'Write the maxtrix to the sheet
   'This code was provide/adapted from Chip Pearson's blog at
   'http://www.cpearson.com/excel/ArraysAndRanges.aspx
   Dim Destination As Range
   Set Destination = ws.Range("A1")
   Destination.Resize(UBound(matrix, 1), UBound(matrix, 2)).Value = matrix
   'Use the worksheet function to Sum the range
   Dim RowSum(4) As Double
   Dim rngSum As Range
   For rowNDX = 1 To 4
   Set rngSum = ws.Range("A" & Trim(CStr(rowNDX)) & ":A" & Trim(CStr(UBound(matrix, 2))))
   RowSum(rowNDX) = WorksheetFunction.Sum(rngSum)
   Next rowNDX
   'Delete the worksheet added by the macro
   'Prevent asking user if it's ok to delete worksheet
   Application.DisplayAlerts = False
   ws.Delete
   'Turn application display alerts back on.
   Application.DisplayAlerts = True
   End Sub