从创建2D数组的Excel单元格读取数组 [英] Reading array from Excel cells creating 2d array

问题描述

我正在尝试将单元格值列表读入Excel VBA中的数组。我这样做是通过

I'm attempting to read a list of cell values in to an Array in Excel VBA. I am doing this through:

Dim varHrArray As Variant
varHrArray = Range(Cells(TITLE + 1, HR_LOCATION), Cells(TITLE + intHrLength, HR_LOCATION)).Value

在此方法有效的同时，它会创建一个二维数组我不确定为什么。这已经通过消息框得到确认，其中 MsgBox（varHrArray（1，1））可以正常工作，但 MsgBox（varHrArray（1））给出下标超出范围错误并通过直接检查监视窗口中的变量- varHrArray 显示为 Variant / Variant（1到7，1到1）（顺便说一句，列表的长度正确是7）。

While this works, it's creating a 2d array and I am uncertain exactly why. This has been confirmed by by message boxes, with MsgBox (varHrArray(1, 1)) working as expected but MsgBox (varHrArray(1)) giving a 'Subscript Out Of Range' error and through directly examining the variable in the watch window - varHrArray is showing as Variant/Variant(1 to 7, 1 to 1) (7 is correct for the length of the list, by the way).

I不清楚为什么会发生或如何阻止它。

I'm not clear why this is happening or how it can be stopped.

推荐答案

每当使用单元格范围创建数组时，创建的数组将始终是二维数组。这是因为电子表格在二维中存储值，即行和列。因此，第一个维度是行，第二个维度是列。

Whenever an array is created using range of cells then array created will always be a two dimensional array. This is because a spreadsheet stores values in two dimensions i.e. rows and columns. So the first dimension is the rows and the second dimension is the columns.

此外，数组的下限始终为1，即LBound（varHrArray）= 1。

Moreover, lower bound of array will always be 1 i.e. LBound(varHrArray)=1.

您可以遍历数组

For i = 1 To UBound(arr)
    some_var = arr(i, 1) 'or arr(i, 2),arr(i, 3) as per column
Next

尽管有一些方法可以将二维数组转换为一维数组。其中之一是

Though there are methods to convert 2-d array into 1-d array. One of them being

For i = 1 To UBound(varHrArray, 1)
    tempArr(i) = varHrArray(i, 1)
Next

将工作表范围读取到VBA数组

Reading A Worksheet Range To A VBA Array

读取工作表上的范围并将其放入数组中非常简单VBA。例如，

It is very simple to read a range on a worksheet and put it into an array in VBA. For example,

Dim Arr（）As Variant'声明未分配的数组。

Arr =范围（ A1：C5）'Arr现在是分配的数组

当您将数据从工作表导入VBA数组时，该数组始终2维。第一维是行，第二维是列。因此，在上面的示例中，Arr的大小隐式设置为 Arr（1到5，1到3），其中5是行数，3是列数。即使工作表数据位于单行或单列（例如Arr（1至10，1至1））中，也会创建二维数组。无论您的模块中可能具有哪个Option Base指令，用于将工作表数据加载到的数组的下限（LBound）始终等于1。您无法更改此行为。

When you bring in data from a worksheet to a VBA array, the array is always 2 dimensional. The first dimension is the rows and the second dimension is the columns. So, in the example above, Arr is implicitly sized as Arr(1 To 5, 1 To 3) where 5 is the number of rows and 3 is the number of columns. A 2 dimensional array is created even if the worksheet data is in a single row or a single column (e.g, Arr(1 To 10, 1 To 1)). The array into which the worksheet data is loaded always has an lower bound (LBound) equal to 1, regardless of what Option Base directive you may have in your module. You cannot change this behavior.

以上是这篇文章。

这篇关于从创建2D数组的Excel单元格读取数组的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！