Collectionをセルに出力/一括出力する

Collectionの中身をセルに出力する方法を解説します。

For Each文を使ってCollectionをセルに出力

まずは基本となるFor Each文を使ったコードです。

Sub Collectionをセルに出力する()

    Dim コレクション As New Collection
    コレクション.Add "みかん"
    コレクション.Add "りんご"
    コレクション.Add "いちご"
    コレクション.Add "さくらんぼ"
    
    Dim R As Long: R = 2
    Dim 要素
    For Each 要素 In コレクション
    
        Cells(R, 1) = 要素
        R = R + 1
        
    Next

End Sub

このコードでCollectionの中身をセルに出力することができます。

これ以外にもFor文を使用する方法はありますが、

• CollectionはFor文を使うと処理速度がかなり遅くなる
• 行番号と要素番号の計算をCells内で行うのが読みづらい

というデメリットがあるため、基本はFor Each文を使用しましょう。

Sub Collectionをセルに出力する_For文版()

    Dim コレクション As New Collection
    コレクション.Add "みかん"
    コレクション.Add "りんご"
    コレクション.Add "いちご"
    コレクション.Add "さくらんぼ"
    
    Dim i As Long: i = 1
    For i = 1 To コレクション.Count
    
        Cells(i - 1, 1) = 要素

    Next

End Sub

配列を使ってCollectionをセルに一括出力

続いてCollectionを一括でセルに出力する方法です。

Collectionのメソッドにこの機能は存在しないため、
一旦配列に変換し、その配列をセルに出力することになります。

また、セルに出力するには「二次元配列」である必要があるため、
一次元になっているCollectionを1×nの二次元配列に変換することになります。

Sub Collectionを縦方向のセル範囲に一括出力する()

    Dim コレクション As New Collection
    コレクション.Add "みかん"
    コレクション.Add "りんご"
    コレクション.Add "いちご"
    コレクション.Add "さくらんぼ"
    
    ' 一旦Collectionを二次元配列に変換
    Dim 二次元配列()
    ReDim 二次元配列(1 To コレクション.Count, 1 To 1)
        
    Dim i As Long: i = 1
    Dim 要素
    For Each 要素 In コレクション
        二次元配列(i, 1) = 要素
        i = i + 1
    Next
    
    ' 二次元配列をセルに出力
    Range("A2").Resize(コレクション.Count).Value = 二次元配列

End Sub

このコードでCollectionを高速でセルに一括出力することができます。

出力セル範囲はRange(Cells,Cells)方式でやらなくても、

起点セル.Resize(コレクションの要素数)

このコードで簡単に取得できることを覚えておきましょう。

横方向に出力する場合は、一次元配列でも二次元配列でもOKです。

コードの流用がしやすいよう、ここでは二次元配列のコードを掲載しておきます。

Sub Collectionを横方向のセル範囲に一括出力する()

    Dim コレクション As New Collection
    コレクション.Add "みかん"
    コレクション.Add "りんご"
    コレクション.Add "いちご"
    コレクション.Add "さくらんぼ"
    
    ' 一旦Collectionを二次元配列に変換
    Dim 二次元配列()
    ReDim 二次元配列(1 To 1, 1 To コレクション.Count)
        
    Dim i As Long: i = 1
    Dim 要素
    For Each 要素 In コレクション
        二次元配列(1, i) = 要素
        i = i + 1
    Next
    
    ' 二次元配列をセルに出力
    Range("B1").Resize(1, コレクション.Count).Value = 二次元配列

End Sub

「二次元配列の行と列」「Resizeの拡張方向」ともに変更する必要があるため、
変更のし忘れにご注意ください。

Collection→セル範囲を汎用関数化する

Collectionをよく使用する方は、
今回のCollection→セル範囲の一括出力を汎用関数にしておきましょう。

' Collection → セル範囲
Sub Collectionをセルに一括出力する(出力コレクション As Collection _
    , 出力起点セル As Range, Optional is行方向に出力 As Boolean = True)

    ' 一旦Collectionを二次元配列に変換
    Dim 二次元配列()
    If is行方向に出力 Then
        ReDim 二次元配列(1 To コレクション.Count, 1 To 1)
    Else
        ReDim 二次元配列(1 To 1, 1 To コレクション.Count)
    End If
    
    Dim i As Long: i = 1
    Dim 要素
    For Each 要素 In コレクション
        If is行方向に出力 Then
            二次元配列(i, 1) = 要素
        Else
            二次元配列(1, i) = 要素
        End If
        i = i + 1
    Next
    
    ' 二次元配列をセルに出力
    If is行方向に出力 Then
        出力起点セル.Resize(コレクション.Count, 1).Value = 二次元配列
    Else
        出力起点セル.Resize(1, コレクション.Count).Value = 二次元配列
    End If
    
End Sub

' 使用例
Sub Collectionを汎用関数でセルに一括出力する()

    Dim コレクション As New Collection
    コレクション.Add "みかん"
    コレクション.Add "りんご"
    コレクション.Add "いちご"
    コレクション.Add "さくらんぼ"
    
    ' 縦方向へ出力
    Call Collectionをセルに一括出力する(コレクション, Range("A2"))

    ' 横方向へ出力
    Call Collectionをセルに一括出力する(コレクション, Range("B1"), False)

End Sub

メインコードが1行になって非常に書きやすく&読みやすくなっていますね。

同じ関数で縦横どちらにも対応できるよう、
第3引数のTrue/Falseで出力方向を指定できるようにしました。

このように、「簡単だけど書くのが面倒なコード」は、
関数化が簡単な割にメリットが大きいです。

汎用関数集を作っている方は、ぜひそのメンバーに加えてあげてください。

汎用関数集の作り方・使い方についてはこちらをどうぞ。

www.limecode.jp