在 Excel VBA 中构建数组

Build Array in Excel VBA

提问by augusthippo

Desired result: The result would be a function that builds an array of values from column B. The values are restricted by having the same value in column A. 'E.g. Column A value = 1 myArray = (0,1,2)' 'Column B Value = 2 myArray = (4,5,6,7,8)'

所需结果：结果将是一个函数，该函数从 B 列构建一个值数组。这些值受 A 列中具有相同值的限制。'例如，A 列值 = 1 myArray = (0,1,2)'' B 列值 = 2 myArray = (4,5,6,7,8)'

First time trying to use arrays in VBA and need help. I have the following data in columns A and B in Excel:

第一次尝试在 VBA 中使用数组并需要帮助。我在 Excel 的 A 列和 B 列中有以下数据：

A   B
1   0
1   1
1   2
2   4
2   5
2   6
2   7
2   8
3   9
3   10
3   11
4   12
4   15
4   18

I have the following VBA Code:

我有以下 VBA 代码：

Function buildMyArray()

    Dim ARange as Range
    Dim B as Integer
    Dim myArrary as Variant

    For Each ARange In Sheets("SheetName").Range("B:B")
        If ARange.Value = 1 Then
            B = Application.WorksheetFunction.VLookup(ARange.Value, Sheets("SheetName").Range("A:B"), 2, False)
            myArray = Array(B)
        End If
    Next ARange

End Function

I am trying to build a function that will search column A for each instance of an integer (which I will eventually dynamically pass). Then for each instance of that integer do a vlookup on the adjacent column (B). I then want to create an array of the values from the lookup (in the above case (0,1,2)).

我正在尝试构建一个函数，该函数将为每个整数实例（我最终将动态传递）搜索列 A。然后对于该整数的每个实例在相邻列 (B) 上进行一次 vlookup。然后我想从查找中创建一个值数组（在上述情况下为 (0,1,2)）。

The above code is the closest I have gotten. I get returned the first value in B (0) as the only value in the array. I am reasonably confident this is because the array logic is in the If statement. However, if I move it out of there I get compile errors.

上面的代码是我得到的最接近的代码。我返回 B (0) 中的第一个值作为数组中的唯一值。我有理由相信这是因为数组逻辑在 If 语句中。但是，如果我将其移出那里，则会出现编译错误。

Please help a novice learn. Thanks.

请帮助新手学习。谢谢。

回答by user1759942

think of an array as one row of an excel sheet, with the columns being the indexes. What you want to do you said was store values in said array. So you get your value, b, and you want to put that in the array.

将数组视为 Excel 工作表的一行，其中的列是索引。你想要做的是将值存储在所述数组中。所以你得到了你的值 b，你想把它放在数组中。

what you want to do:

你想做什么：

when declaring the array, declare it like Dim myArrary() as Variantnotice the brackets (not positive if they are required but its best practise.

在声明数组时，像Dim myArrary() as Variant注意括号一样声明它（如果需要，则不是肯定的，但这是最佳实践。

that declares an empty array. Like an excel sheet with no columns. if you knew for A fact the array would have 5 values, you could declare:

声明一个空数组。就像没有列的 Excel 表一样。如果您知道 A 事实数组将有 5 个值，您可以声明：

Dim myArrary(1 to 5) as Variantthat says, its an array with 5 indexes. (like an excel row with 5 columns)

Dim myArrary(1 to 5) as Variant也就是说，它是一个有 5 个索引的数组。（就像一个有 5 列的 excel 行）

the numbers, 1 to 5, those are the start and end index numbers. so, in that array, when reffering to the first spot, you'd put myArray(1)the brackets like that are how you refer to specific indexes in the array. second spot would be myArray(2) 3rd myArray(3) 4th myArray(4) and 5th (the last spot) would be myArray(5).

数字，1 到 5，那些是开始和结束索引号。所以，在那个数组中，当提到第一个位置时，你会myArray(1)像这样放置括号，这是你引用数组中特定索引的方式。第二个位置是 myArray(2) 第三个 myArray(3) 第四个 myArray(4) 第五个（最后一个位置）是 myArray(5)。

You can declare themn like dim myarray(0 to 5) as variant, that means the first index would be myArray(0) - this array would have 6 spot - 0,1,2,3,4,5

您可以像 dim myarray(0 到 5) 一样声明它们作为变体，这意味着第一个索引将是 myArray(0) - 该数组将有 6 个点 - 0,1,2,3,4,5

So, in your code, you'd need a counter to keep track what iteration of the loop you were on and increment it like counter = cojunter + 1at the bottom of the loop. (it would increase each time the loop ran) then, replace the line that says myArray = Array(B)with myArray(counter) = B

因此，在您的代码中，您需要一个计数器来跟踪您所在循环的迭代次数，并像counter = cojunter + 1在循环底部一样递增它。（每次循环运行时它都会增加）然后，myArray = Array(B)用myArray(counter) = B

so each time the loop runs, Bwill go into a spot in the array.

所以每次循环运行时，B都会进入数组中的一个位置。

1 more thing,

还有1件事，

because we dont know at the begginning how many spots the array will have, and we declare it empty: dim myArray() as variantyou must redefine it after to have spaces before we can add anything. so, at the beginning of the loop, you wanna put Redim Preserve MyArray(1 to counter)this redefines your array as being from 1 to whatever your counter is at. since the counter increases by 1 each time, 1 space will be added to the array each time.

因为我们在开始时不知道数组将有多少个点，因此我们将其声明为空：dim myArray() as variant您必须在有空格之后重新定义它，然后才能添加任何内容。所以，在循环的开始，你想把Redim Preserve MyArray(1 to counter)这个重新定义你的数组从 1 到你的计数器所在的位置。由于计数器每次增加 1，因此每次将向数组添加 1 个空间。

so basically,

所以基本上，

replace Dim myArrary as Variantwith Dim myArrary() as Variant add: dim counter as longup in the variables (name it whatever u want, just be consistant)

替换Dim myArrary as Variant为 Dim myArrary() 作为 Variant add: dim counter as longup 在变量中（随意命名，只要保持一致）

add counter = 1before the loop to initialize the counter

counter = 1在循环之前添加以初始化计数器

add ReDim myArray(1 to counter)in the loop, in the if statment (thus only adding the space if B was found

添加ReDim myArray(1 to counter)在循环中，如果statment（因此仅添加空间，如果乙发现

add counter = counter + 1at the end if the if, but again inside the is, thus only incrementing if a value for B is found

counter = counter + 1如果 if 在最后添加，但在 is 内再次添加，因此只有在找到 B 的值时才会增加

and change myArray = Array(B)to MyArray(counter) = Bin the if statement.

并在 if 语句中更改 myArray = Array(B)为MyArray(counter) = B。

回答by Siddharth Rout

Here is a sample that I created. Try this

这是我创建的示例。尝试这个

Option Explicit

Sub sample()
    Dim ws As Worksheet
    Dim rng As Range, aCell As Range, bCell As Range
    Dim MyAr() As String
    Dim n As Long
    Dim SearchString

    '~~> Set this to the relevant worksheet
    Set ws = ThisWorkbook.Sheets("Sheet1")

    SearchString = 2

    With ws
        '~~> Set this to your range
        Set rng = .Range("A1:A14")

        Set aCell = rng.Find(What:=SearchString, LookIn:=xlValues, _
                    LookAt:=xlWhole, SearchOrder:=xlByRows, SearchDirection:=xlNext, _
                    MatchCase:=False, SearchFormat:=False)

        If Not aCell Is Nothing Then
            Set bCell = aCell

            '~~> Store the value from Col B in the array
            ReDim Preserve MyAr(n)
            MyAr(n) = aCell.Offset(, 1).Value
            n = n + 1

            '~~> Find Next occurance
            Do
                Set aCell = rng.FindNext(After:=aCell)

                If Not aCell Is Nothing Then
                    If aCell.Address = bCell.Address Then Exit Do

                    '~~> Store the value from Col B in the array
                    ReDim Preserve MyAr(n)
                    MyAr(n) = aCell.Offset(, 1).Value
                    n = n + 1
                Else
                    Exit Do
                End If
            Loop
        End If
    End With

    '~~> This will give you the results
    For n = LBound(MyAr) To UBound(MyAr)
        Debug.Print MyAr(n)
    Next n
End Sub

Screenshot:

截图：

Output:

输出：

回答by pnuts

Might well not count as an answer, but without VBA, in C2 and copied down to suit:

可能不算作答案，但没有 VBA，在 C2 中并复制下来以适应：

=IF(A3<>A2,IF(A1=A2,C1&","&B2,"("&B2)&")",IF(A1=A2,C1&","&B2,"("&B2))  

then copy ColumnC and Paste Special Values over the top, filter to select does not contain )and delete visible should show:

然后复制 ColumnC 并在顶部粘贴特殊值，过滤以选择不包含)和删除可见的应显示：

ColumnA start of range
ColumnB end of range
ColumnC array

ColumnA 范围开始
ColumnB范围结束
ColumnC 数组

回答by Tmdean

In VBA, there's no simple concept of "adding an item to an array". You always have to declare beforehand how big you want the array to be, then you can work with the items in the array. While it's possible to write code that increases the size of an array by one element, this usually isn't the correct approach.

在 VBA 中，没有“将项添加到数组”的简单概念。你总是必须事先声明你想要数组有多大，然后你可以处理数组中的项目。虽然可以编写将数组的大小增加一个元素的代码，但这通常不是正确的方法。

In this scenario, building the array will be a two-step process: first you need to count how many items you will be putting in your array, then you need to populate the array. In my example, xis the number you're searching for in column A.

在这种情况下，构建数组将是一个两步过程：首先，您需要计算将要放入数组的项目数，然后需要填充数组。在我的示例中，x是您在 A 列中搜索的数字。

Function MakeArray(x As Integer) As Variant
    Dim result() As Variant
    Dim result_count As Long
    Dim i As Long
    Dim cursor As Range

    result_count = WorksheetFunction.CountIf(Range("A:A"), x)

    ' size the array to how many elements we need, minus one
    ' because the array indexes start at zero
    ReDim result(result_count - 1)

    ' scan through the data and populate our array
    Set cursor = Range("A2:B2")
    i = 0
    Do Until IsEmpty(cursor(1))
        If cursor(1) = x Then
             result(i) = cursor(2)
             i = i + 1
             If i > UBound(result) Then Exit Do
        End If
        Set cursor = cursor.Offset(1)
    Loop

    MakeArray = result
End Function