vba - 无论如何将集合直接写入工作表？

Question

无论如何使用VBA将集合直接写入工作表？就像是

Worksheets("Sheet3").Range("C2") = MyCollection

找不到任何有效的东西

Answer 1

已编辑 为“类”方法添加代码，而不是已经发布的“子”方法

与 Jake 的答案最接近的真正解决方案是 Range 的扩展方法(可能命名为“WriteCollection”)。类(class)。

不幸的是，VBA 不允许这样(以及许多其他)功能，但它可以在一定程度上通过类模块方法来模仿

“上课”方式

插入类模块

通过单击主菜单中的“插入->类模块”将类模块插入到您的项目中

您将看到“项目管理器”窗口中的“类模块”节点下出现“类 1”子节点

重命名它

将其起始名称更改为有意义的名称

您可以通过单击“项目管理器”窗口中的类子节点并在“属性”窗口中编辑“(名称)”属性值来执行此操作(如果您的 VBE 中还没有此窗口，请按 F4)

让我们将其重命名为“MyRange”，但您可以在后续步骤中选择与它一致的任何名称

输入类(class)代码

进入类代码 Pane (在“项目管理器”窗口中双击子节点类)并添加以下代码:

`

Option Explicit

'---------------------------------------------------------------------
'Properties
'---------------------------------------------------------------------
' all 'Public' properties will be shown by Intellisense for every variable declared of this class

Public Range As Range '<~~ declare a property of the "Range" class. it therefore will have all methods and property of a "Range" object
'---------------------------------------------------------------------


'---------------------------------------------------------------------
'Methods
'---------------------------------------------------------------------
' all 'Public' methods will be shown by Intellisense for every variable declared of this class

Public Sub WriteCollection(myColl As Collection, Optional direction As Variant) '<~~ this will be the method to call and have a Collection written down into a range
    If IsMissing(direction) Then direction = "V"

    If direction = "V" Then
        Range.Resize(myColl.Count).Value = CollToRangeVertical(myColl)
    Else
        Range.Resize(, myColl.Count).Value = CollToRangeHorizontal(myColl)
    End If
End Sub
'---------------------------------------------------------------------


'---------------------------------------------------------------------
'helpers
'---------------------------------------------------------------------
' these are subs/function needed for the inner functioning of the class, so they are declared as 'Private' to prevent their use from outside it

Private Function CollToRangeVertical(myColl As Collection) As Variant
    CollToRangeVertical = Application.Transpose(CollToStrng(myColl))
End Function

Private Function CollToRangeHorizontal(myColl As Collection) As Variant
    CollToRangeHorizontal = CollToStrng(myColl)
End Function

Private Function CollToStrng(myColl As Collection) As Variant
    Dim i As Long
    For i = 1 To myColl.Count
        CollToStrng = CollToStrng & CStr(myColl(i)) & "|"
    Next i
    If CollToStrng <> "" Then CollToStrng = Split(Left(CollToStrng, Len(CollToStrng) - 1), "|")
End Function
'---------------------------------------------------------------------
'---------------------------------------------------------------------

剥削你的类(Class)

将任何模块添加到您的类所在的同一项目中，然后键入以下示例

`

Option Explicit
Sub main()
    Dim MyCollection As New Collection
    Dim mRange As New MyRange '<~~ declare a variable of the MyRange class and set a new instance of it

' fill Collection
MyCollection.Add "C"
MyCollection.Add "B"
MyCollection.Add "A"

'set the "Range" property of your "mRange" object of the "MyRange" class
Set mRange.Range = Worksheets("Sheet3").Range("C2") '<~~ it can be set to any range in any open workbook and worksheet

'exploit "MyRange" class method(s)
mRange.WriteCollection MyCollection '<~~ write down a collection "vertically"
mRange.WriteCollection MyCollection, "H" '<~~ write down a collection "horizontally"

'exploit Excel "Range" class methods through the "Range" property of "MyRange" class object
With mRange.Range
    .Offset(10, 1).Value = .Value & "- offsetted" '<~~ copy/paste values
    .Resize(.Rows.Count, 1).Sort Key1:=.Range("A1") '<~~ sort the collection you just wrote down
End With

End Sub

“子”方法

你是否需要继续收集

不， Range 没有这种直接的“方法”。目的

无论如何，其调用应该比您希望的更清晰，因为您至少必须指定从初始单元格扩展集合项的“方向”

但它也应该处理每个集合项的实际类型

只是为了一个练习，我放下了一个带有以下“签名”的“WriteColl”子:

WriteColl, Range, Collection [, direction]

其中方向是一个可选参数，用于指定从给定范围扩展粘贴集合项的方向

因此可以像下面这样使用:

WriteColl Worksheets("Sheet3").Range("C2"), MyCollection

为此，您必须在任何模块中附上以下代码:

Option Explicit

Sub main()
    Dim MyCollection As New Collection
    MyCollection.Add "A"
    MyCollection.Add "B"
    MyCollection.Add "C"

    WriteColl Worksheets("Sheet3").Range("C2"), MyCollection
    WriteColl Worksheets("Sheet3").Range("D2"), MyCollection, "H"
End Sub

'method
Sub WriteColl(myRng As Range, myColl As Collection, Optional direction As Variant)
    If IsMissing(direction) Then direction = "V"

    If direction = "V" Then
        myRng.Resize(myColl.Count).Value = CollToRangeVertical(myColl)
    Else
        myRng.Resize(, myColl.Count).Value = CollToRangeHorizontal(myColl)
    End If
End Sub

'helpers
Function CollToRangeVertical(myColl As Collection) As Variant
    CollToRangeVertical = Application.Transpose(CollToStrng(myColl))
End Function

Function CollToRangeHorizontal(myColl As Collection) As Variant
    CollToRangeHorizontal = CollToStrng(myColl)
End Function

Function CollToStrng(myColl As Collection) As Variant
    Dim i As Long
    For i = 1 To myColl.Count
        CollToStrng = CollToStrng & CStr(myColl(i)) & "|"
    Next i
    If CollToStrng <> "" Then CollToStrng = Split(Left(CollToStrng, Len(CollToStrng) - 1), "|")
End Function