VBA Excel二维数组 [英] VBA Excel 2-Dimensional Arrays

问题描述

我试图找出如何声明一个二维数组，但是到目前为止我发现的所有例子都是用set整数声明的。我正在尝试创建一个程序，它将使用两个二维数组，然后在这些数组上执行简单的操作（如查找差异或百分比）。数组由Excel表格中的数字填充（一组数字在Sheet1上，另一组在Sheet2上，两个集合具有相同数量的行和列）。

I was trying to find out how to declare a 2-Dimensional array but all of the examples I have found so far are declared with set integers. I'm trying to create a program that will utilize two 2-Dimensional arrays and then perform simple operations on those arrays (such as finding difference or percent). The arrays are populated by numbers in Excel sheets (one set of numbers is on Sheet1 and another set is on Sheet2, both sets have the same number of rows and columns).

因为我不知道有多少行或列我将要使用变量。

Since I don't know how many rows or columns there are I was going to use variables.

Dim s1excel As Worksheet
Dim s2excel As Worksheet
Dim s3excel As Worksheet
Dim firstSheetName As String
Dim secondSheetName As String
Dim totalRow As Integer
Dim totalCol As Integer
Dim iRow As Integer
Dim iCol As Integer

Set s1excel = ThisWorkbook.ActiveSheet

' Open the "Raw_Data" workbook
Set wbs = Workbooks.Open(file_path & data_title)
wbs.Activate
ActiveWorkbook.Sheets(firstSheetName).Select
Set s2excel = wbs.ActiveSheet

' Find totalRow, totalColumn (assumes there's values in Column A and Row 1 with no blanks)
totalRow = ActiveSheet.Range("A1").End(xlDown).Row
totalCol = ActiveSheet.Range("A1").End(xlToRight).Column

Dim s2Array(totalRow, totalCol)
Dim s3Array(totalRow, totalCol)

For iRow = 1 To totalRow
    For iCol = 1 To totalCol
        s2Array(iRow, iCol) = Cells(iRow, iCol)
    Next iCol
Next iRow

ActiveWorkbook.Sheets(secondSheetName).Select
Set s3excel = wbs.ActiveSheet

For iRow = 1 To totalRow
    For iCol = 1 To totalCol
        s3Array(iRow, iCol) = Cells(iRow, iCol)
    Next iCol
Next iRow

当我尝试运行这个我在Dim s2Array（totalRow，totalCol）中得到一个编译时错误，表示需要一个常量表达式。如果我将其更改为Dim s2Array（1 To totalRow，1 To totalCol），则会发生相同的错误。因为我不知道什么维度是从去除我不能声明它像Dim s2Array（1，1），因为那我将得到一个超出范围的异常。

When I attempt to run this I get a compile-time error at the "Dim s2Array(totalRow, totalCol)" saying that a constant expression is required. The same error occurs if I change it to "Dim s2Array(1 To totalRow, 1 To totalCol)". Since I don't know what the dimensions are from the get go I can't declare it like "Dim s2Array(1, 1)" because then I'll get an out-of-bounds exception.

谢谢，

Jesse Smothermon

Jesse Smothermon

推荐答案

其实我不会使用任何REDIM，也不会使用循环来将数据从工作表转移到数组：

In fact I would not use any REDIM, nor a loop for transferring data from sheet to array:

dim arOne()
arOne = range("A2:F1000")

甚至

arOne = range("A2").CurrentRegion

就是这样，你的数组的填充速度比循环快，没有重做。

and that's it, your array is filled much faster then with a loop, no redim.

这篇关于VBA Excel二维数组的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！