Excel宏加载数组 [英] Excel Macro loading Arrays

问题描述

我不确定如何表达这个问题，但是遇到一个问题，我需要将一组工作表中的名称列表加载到单独的数组中.

I am not sure how to phrase the question, but I have come up against an issue where I need to load a list of names from a group of sheets into seperate arrays.

例如，Sheet 1，Column A有10个名称要加载到array1，Sheet 2，Column A有14个名称要加载到array2.

For example, Sheet 1, Column A has 10 names to be loaded into array1, Sheet 2, Column A has 14 names to be loaded into array2.

我知道可以将其硬编码到宏中以全部加载它们，但是名称列表不是固定长度，并且避开了很多代码，我想知道是否可以将其缩短.

I know this could be hard coded into a macro to load them all but the list of names is not a fixed length and shy of a lot of code I was wondering if this could be shortened.

我的思想告诉我这样做的方式如下.

The way that my mind would tell me to do this is below.

Dim tarray1 As Variant
Dim tarray2 As Variant
Dim tarray3 As Variant

For f = 1 To 3

    Sheets("Region " & f).Select
    With ActiveSheet
        lastrow = .Cells(.Rows.Count, "A").End(xlUp).Row
        tarray& f = ActiveSheet.Range("A2:A" & lastrow)
    End With


Next

有问题的实际工作表有10个单独的工作表，具有从区域1到10读取的名称.因此，将有10个单独的tarray.这样做是出于报告目的，并将在工作表中来回传递.

The actual sheet in question has 10 seperate sheets to have the names read in going from Region 1 to 10. So there will be 10 seperate tarrays. This is done for reporting purposes and will be passed back and forth around the sheets.

但是tarray & f显然无效，并且会引发编译错误.

However tarray & f is obviously not valid and will throw up compile errors.

有人能想到解决这个问题的方法吗?

Can anyone think of a way to get around this?

希望有人可以提供帮助.

Hope someone can help.

推荐答案

以下例程向您展示了如何使用交错的锯齿形变量，我认为这是您需要的技术.

The following routine shows you how to use a jagged array of variants which I believe is the technique you need.

从工作表的前十行到TArray，我已加载了不同数量的单元格.我使用Debug.Print展示了如何访问元素.

I have loaded different numbers of cells from the the first ten rows of a worksheet to TArray. I use Debug.Print to show how to access the elements.

Sub Test()

  Dim InxCol As Integer
  Dim InxRow As Integer
  Dim InxTA As Integer
  Dim TArray() As Variant

  ReDim TArray(1 To 10)

  With Sheets("xxxxx")

  For InxTA = 1 To 10
    ' There must be at least two cells in each range if the result is to be an
    ' array.  The access code relies on TArray being a true array of arrays.
    ' If this is not possible, you can test for TArray(N) being an array
    ' or a variable using VarType
    TArray(InxTA) = .Range(.Cells(InxTA, 1), .Cells(InxTA, 12 - InxTA)).Value
  Next

  End With

  For InxTA = 1 To 10
    For InxRow = LBound(TArray(InxTA), 1) To UBound(TArray(InxTA), 1)
      For InxCol = LBound(TArray(InxTA), 2) To UBound(TArray(InxTA), 2)
        Debug.Print TArray(InxTA)(InxRow, InxCol) & " ";
      Next
    Next
    Debug.Print
  Next

End Sub

新材料

我已决定添加一个解释，以便回答完整.

I have decided to add an explanation so my answer is complete.

您可以将变体设置为任何内容，然后再将其设置为其他内容.以下内容可能很愚蠢，但可以正常工作:

You can set a variant to anything and then set it to something else. The following may be stupid but it works:

Dim V As Variant

V = 5
Debug.Print V
V = "Today"
Debug.Print V
V = Array(1, 2, 3)
Debug.Print V(0) & " " & V(1) & " " & V(2)

考虑:Range( xxx ).Value

如果xxx是单个单元格，则返回单个变量.在所有其他情况下，它将返回一个二维数组.第一个维度用于行，第二个维度用于列.这与通常的做法相反，但与Cells(R, C).Value匹配.

If xxx is a single cell, it returns a single variable. In all other cases it returns a two dimensional array. The first dimension is for the rows and the second for the columns. This is the opposite of normal practice but it matches Cells(R, C).Value.

我已经声明:

Dim TArray() As Variant
Redim TArray(1 to 10)

所以TArray是一个变体数组

So TArray is an array of variants

然后我设置TArray(N) = Range( xxx ).Value

如果xxx是单个单元格，则TArray(N)将是单个值.

If xxx is a single cell, TArray(N) will be a single value.

但是，如果xxx是两个或更多单元，则TArray(N)是一个二维数组.要访问TArray中的单个单元格，我必须指定TArray的元素，然后再指定范围的行和列.因此:

But if xxx is two or more cells, TArray(N) is a two dimensional array. To access a single cell within TArray, I must specify a element of TArray and then a row and column of the range. Thus:

TArray(Element)(Row, Column)

TArray的每个元素可以具有与其他每个元素不同的大小.一个可以是单个变量，另一个可以是单个行范围，另一个可以是单个列范围，而另一个可以是矩形.

Each element of TArray can have a different size from every other element. One can be a single variable, another a single row range, another a single column range and yet another a rectangle.

这很难一开始就引起您的注意.否-删除一开始".玩这个功能.做我所做的事情，然后从您的一个工作表中加载行，列和矩形.

This is difficult to get your head around at first. No - delete "at first". Play with this functionality. Do what I did and load rows, columns and rectangles from one of your worksheets.

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