如何写一个VBA集合一个Excel工作表 [英] How to write a VBA collection to an Excel sheet

问题描述

我有一些现有的code，我修改。这code创建一个从preexisting工作表中的行的集合。它创建了一个大的2-D采集，并在每列不同的信息。有声明每列的数据类型的单独的类模块。

I have some existing code that I am modifying. This code creates a collection of rows from preexisting worksheet tables. It creates a large 2-D collection, with distinct information in each column. There is a separate class module that declares the data type for each column.

在code通过每个项目依次循环的2-D集合写入新的工作表。我从来没有使用过的集合，并且还想写收集到纸张在一个单一的通行证。目前的code需要当表有很多记录相当长的时间。

The code writes the 2-D collection to a new sheet by looping through each item in turn. I have never used a collection before, and would like to write the collection to the sheet in a single pass. The current code takes quite a long time when the table has lots of records.

有没有办法将整个集合转换为2-D数组，或者让我能然后写在一个单一的2-D阵列去？或者是有向整个集合写入表，就像一个2-D阵列的方法吗？我试图寻找这一点，迄今未果。任何一般点将AP preciated！

Is there a way to convert the entire collection to a 2-D array, or so that I can then write the 2-D array in a single go? Or is there a way to write the entire collection to the sheet, just like with a 2-D array? I have tried to search for this and have so far been unsuccessful. Any general points would be appreciated!

下面是一些例子code，用粗体的意见，来说明是如何被使用的集合。

Here is some example code, with comments in bold, to illustrate how the collection is being used.

定义类模块，命名为TableEntry

Public Item1 As String
Public Item2 As String
Public Item3 As String
Public Item4 As Integer
Public Item5 As Integer

主程序 - 创建集合，填写收集，收集写入到表

Sub MainRoutine()

Dim table As Collection
Set table = New Collection

Call FillCollection(File As String, ByRef table As Collection)

Call WriteCollectionToSheet(ByRef table As Collection)

子程序1 - 填写集合

Dim wb As Workbook
Set wb = Workbooks.Open(File)

Dim ws As Worksheet
For Each ws In ActiveWorkbook.Worksheets

Dim R As Range
Set R = ws.Range("A2")

  Dim e As TableEntry
  For i = 1 To 20

    Set e = New TableEntry

    e.Item1 = R.Offset(i + 1, 0).Offset(0, 0)
    e.Item2 = R.Offset(i + 1, 0).Offset(0, 1)
    e.Item3 = R.Offset(i + 1, 0).Offset(0, 2)
    e.Item4 = R.Offset(i + 1, 0).Offset(0, 3)
    e.Item5 = R.Offset(i + 1, 0).Offset(0, 4)

    table.Add e

  Next i

Next ws

子程序2 - 写收集到表

推荐答案

我觉得最简单的方法来打印的词典的到Excel的小号preadsheet是使用 WorksheetFunction .Transpose（ 变 键入的 阵列 ）

I think the easiest way to print a Dictionary onto Excel spreadsheet is by using WorksheetFunction.Transpose(Variant type Array)

的下面code 的

• 创建具有键和项目的范例字典


• 创建一个数组的（键，物品）的和充满他们的元素的
  从字典中的一气呵成的


• 使用 WorksheetFunction.Transpose（VariantArray）打印阵列的在
  一气呵成的

• Creates a sample Dictionary with keys and items
• Creates two Arrays (keys, items) and fills them with the elements from the Dictionary in one go
• Uses WorksheetFunction.Transpose(VariantArray) to print arrays in one go

Option Explicit

添加引用Microsoft脚本运行时
>>工具>> >>引用Microsoft脚本运行

' Add Reference to Microsoft Scripting Runtime ' >> Tools >> References >> Microsoft Scripting Runtime

Sub CollectionToArrayToSpreadSheet()
    Cells.ClearContents
    ' think of this collection as
    '   key     =   cell.row
    '   item    =   cell.value
    Dim dict As New Dictionary
    dict.Add Key:=1, Item:="value1"
    dict.Add Key:=2, Item:="value2"
    dict.Add Key:=3, Item:="value3"

    ' THIS WAY
    'Range("A1:A" & UBound(dict.Keys) + 1) = WorksheetFunction.Transpose(dict.Keys)
    'Range("B1:B" & UBound(dict.Items) + 1) = WorksheetFunction.Transpose(dict.Items)

    ' OR
    Range("A1").Resize(UBound(dict.Keys) + 1, 1) = WorksheetFunction.Transpose(dict.Keys)
    Range("B1").Resize(UBound(dict.Items) + 1, 1) = WorksheetFunction.Transpose(dict.Items)

End Sub

---

在你的情况...

如果这就是你正在尝试做的（注意的 表 的 是一家集的）

If this is what you are trying to do (note table is a Collection)

Range("A1:A" & table.Count) = WorksheetFunction.Transpose(table)

不幸的是，答案是否定的。

Unfortunately, the answer is NO.

您不能没有遍历集合转集合到A S preadsheet。

You can't transpose a collection over to a spreadsheet without iterating through the collection.

你可以做些什么来加快进程是：

What you can do to speed the process up is:

• 关闭 Application.ScreenUpdating


• 遍历集合及以上的值复制到一个数组，
  然后使用 WorksheetFunction.Transpose（）打印
  一切一气呵成表的 的（使用逻辑从第一
  部分答案的）

• turn off Application.ScreenUpdating
• iterate over the collection and copy the values over to an array, then use the WorksheetFunction.Transpose() to print everything to sheet in one go (use the logic from the first part of the answer)

追问：

在你的情况，你可以重写子WriteCollectionToSheet（为ByRef表为集合）像这样（的的code看起来有点丑，但效率应要确定的）

In your case you can rewrite the Sub WriteCollectionToSheet(ByRef table As Collection) like this (the code looks a bit ugly but the efficiency should be OK)

Sub WriteCollectionToSheet(ByRef table As Collection)

    Dim dict1 As New Dictionary
    Dim dict2 As New Dictionary
    Dim dict3 As New Dictionary
    Dim dict4 As New Dictionary
    Dim dict5 As New Dictionary

    Dim i As Long
    For i = 1 To table.Count
        dict1.Add i, table.Item(i).Item1
        dict2.Add i, table.Item(i).Item2
        dict3.Add i, table.Item(i).Item3
        dict4.Add i, table.Item(i).Item4
        dict5.Add i, table.Item(i).Item5
    Next i

    Range("A1:A" & UBound(dict1.Items) + 1) = WorksheetFunction.Transpose(dict1.Items)
    Range("B1:B" & UBound(dict2.Items) + 1) = WorksheetFunction.Transpose(dict2.Items)
    Range("C1:C" & UBound(dict3.Items) + 1) = WorksheetFunction.Transpose(dict3.Items)
    Range("D1:D" & UBound(dict4.Items) + 1) = WorksheetFunction.Transpose(dict4.Items)
    Range("E1:E" & UBound(dict5.Items) + 1) = WorksheetFunction.Transpose(dict5.Items)

End Sub

在VBA集合迭代更多细节及印刷板@ <一个href=\"http://vba4all.com/how-to-write-a-vba-collection-andor-dictionary-scripting-dictionary-to-a-s$p$padsheet/\"相对=nofollow> vba4all.com

More details on VBA Collections iterations and printing to Sheet @ vba4all.com

这篇关于如何写一个VBA集合一个Excel工作表的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！