将VBA集合转换为阵列 [英] Converting VBA Collection to Array

问题描述

我正在尝试创建一个包含工作簿中所有带有模板"字样的工作表名称的数组.我认为最简单的方法是先创建一个集合，然后将其转换为数组，但是我遇到了麻烦.现在，我遇到的错误是

I am trying to create an array with all the worksheet names in my workbook that have the word 'Template' in it. I thought the easiest way to do this would be to create a collection first and then convert it to an array but I am having trouble. Right now the error I am getting is on the

collectionToArray (col)

行.我收到

参数非可选错误

Argument Not Optional error

非常困难，对您的帮助非常感谢.谢谢！

Pretty stuck, any help is super appreciated. Thanks!!

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

推荐答案

就这么多了，您只是没有将Function 用作.您需要将结果存储在诸如"NewArray"之类的东西中.

It's all there, you're just not using the Function as a function. You need to store the result in something, like '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

这篇关于将VBA集合转换为阵列的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！