将VBA集合转换为数组

Question

我正在尝试创建一个数组，其中包含工作簿中所有带有“Template”一词的工作表名称。我认为最简单的方法是首先创建一个集合，然后将其转换为一个数组，但我遇到了麻烦。目前我收到的错误信息是在

上。

collectionToArray (col)

问题描述：我在使用代码时遇到了“参数不是可选的”错误。

目前进展比较困难，需要帮助。谢谢！

Public col As New Collection
Public Sub Test()
For Each ws In ThisWorkbook.Worksheets
    If InStr(ws.Name, "Template") <> 0 Then
        col.Add ws.Name
    End If
Next ws

collectionToArray (col)
End Sub

Function collectionToArray(c As Collection) As Variant()
    Dim a() As Variant: ReDim a(0 To c.Count - 1)
    Dim i As Integer
    For i = 1 To c.Count
        a(i - 1) = c.Item(i)
    Next
    collectionToArray = a
End Function

- SanomaJean

---

尝试移除括号。我不确定为什么你需要同时使用两者，为什么不只用一个集合或一个数组呢？ - SJR

有道理...是否有类似于IsInArray的集合等效物？@SJR - SanomaJean

我的意思是，在测试结束时，您会拥有一组感兴趣的表格，因此您可以在其上进行工作。您还需要什么？ - SJR

@SJR 对，你说得对。有没有一种简单的方法来测试一个字符串是否在我的集合中？我只知道如何测试一个东西是否在数组中。 - SanomaJean

好的，现在我明白你在问什么了。实际上使用一个字典会更好，它有一个Exists方法（或者只需坚持使用数组）。 - SJR

@SJR 嗯，我可以做到，让我试试一些东西，看看哪个有效。 - SanomaJean

Answer 1

collectionToArray (col)

注意函数名称和其参数列表之间的空格吗？那是VBE告诉您的：

我将接受该参数并将其作为值进行评估，然后将其ByVal传递给您调用的该过程，即使该过程的签名为ByRef，无论明确还是不明确。

这种“多余的括号”习惯不可避免地会让您在某个时候遇到奇怪的“对象所需”的运行时错误：摆脱它。

我认为Function做得过多：Variant可以完全包装一个数组，因此我会更改其签名以返回Variant而不是Variant()。

Integer是一种16位带符号整数类型（即其他某些语言中的short），最好使用Long（32位带符号整数，即其他一些语言中的int） - 这样，当您需要处理超过32,767个值时（尤其是涉及工作表时），就可以避免遇到“溢出”问题。

Public col As New Collection

这使得col成为一个自动实例化的对象变量，并且可能会产生令人惊讶的副作用。考虑以下代码：

[此处省略]

Dim c As New Collection
c.Add 42
Set c = Nothing
c.Add 42
Debug.Print c.Count

您希望这段代码做什么？如果您认为“错误91，因为对象引用是Nothing”，那么您被自动实例化所困扰了。最好避免此类问题，将声明和分配作为分开的指令。

除此之外，CLR的答案提供了解决方案：一个Function应该返回一个值，调用代码应该使用这个值。

result = MyFunction(args)

你会注意到 VBE 清除了你可能会在 MyFunction 和 (args) 之间添加的任何空格：这就是 VBE 告诉你的：

我将使用该参数将其传递给 MyFunction，并将函数的返回值分配给 result。

- Mathieu Guindon

---

这实际上是一个相当不错的答案，我不得不读两遍并且玩了一下代码才能确切地理解你在第一部分的意思。也许你可以考虑添加一个像这个例子 - https://github.com/Vitosh/VBA_personal/blob/master/ByReferenceByValue.vb 这样的例子来使第一部分更容易理解。 - Vityata

Answer 2

所有东西都在那里，只是你没有将该函数作为函数使用。你需要将结果存储在某个变量中，比如 'NewArray'..?

Public col As New Collection
Public Sub Test()
For Each ws In ThisWorkbook.Worksheets
    If InStr(ws.Name, "Template") <> 0 Then
        col.Add ws.Name
    End If
Next ws


' Tweaked as per Vityata's comment

If col.Count > 0 Then 
    newarray = collectionToArray(col)
Else
    ' Do something else
End If

End Sub

Function collectionToArray(c As Collection) As Variant()
    Dim a() As Variant: ReDim a(0 To c.Count - 1)
    Dim i As Integer
    For i = 1 To c.Count
        a(i - 1) = c.Item(i)
    Next
    collectionToArray = a
End Function

- CLR

Answer 3

This is my collectionToArray function:

Public Function CollectionToArray(myCol As Collection) As Variant

    Dim result  As Variant
    Dim cnt     As Long
    
    If myCol.Count = 0 Then
        CollectionToArray = Array()
        Exit Function
    End If
    
    ReDim result(myCol.Count - 1)
    For cnt = 0 To myCol.Count - 1
        result(cnt) = myCol(cnt + 1)
    Next cnt
    CollectionToArray = result

End Function

这个方法比你现在使用的更好，因为它不会在集合为空时报错。如果想避免集合为空时出错，你可以考虑添加如下检查：

If col.Count > 0 Then k = CollectionToArray(col)

- Vityata

---

谢谢。我刚用了这段代码，但是Redim应该是myCol.count-1。否则你会在数组中得到一个空成员，可能会引起问题（在我这种情况下是在RemoveDuplicates中）。 - Fredrik

我不明白为什么要使用 myCol.Count-1 这个语句。正常的代码应该是没有 -1 的，就像这张图片所示：https://ibb.co/iQfJ38 没有空成员... - Vityata

redim 中的参数是上边界。 - Fredrik

代码中这一行应该写成：ReDim result(myCol.Count - 1)。否则，在数组最后会有一个空元素。 - Grim

@Grim - 其实有一个奇怪的情况，警告我不要那样做，但我现在无法确切地记得它是什么。现在检查我之前评论的截图，看到它当时是正常工作的。只是想知道它何时会出错...无论如何，我已经编辑过了。 - Vityata

Answer 4

不必使用标准集合，您可以使用字典，因为它具有可以将所有信息传输到数组的 .keys 方法。编码更少，更简单 :)

Dim ws As Worksheet Dim arr

Dim Dic As Scripting.Dictionary Set Dic = New Dictionary

For Each ws In ActiveWorkbook.Worksheets

If ws.Name Like "Template" Then

Dic.Add ws.Name, "Awesome"

End If

Next ws

arr = Dic.Keys

- Matt