VBAの部品: シートに配列を一括入力する

2次元配列の値を一括入力する関数です。For文などで行やセル単位に入力を繰り返すことなく処理できるので、格段に高速です。

シート上の範囲と配列との入力の仕組み

VBAでは、2次元配列と範囲を “=” でつなぐだけで値の取得や入力ができます。

シートに配列を一発入力する関数

入力先の始点のセル（CL）と2次元配列（ARY）を指定して呼び出す関数です。

'シートに2次元配列を入力する
'引数: CL 入力開始セル / ARY 2次元配列
Function f_ArrayToSheet(CL As Range, ARY As Variant) 
    Dim r As Long, c As Long

    ' 要素数（行・列数）を取得
    r = UBound(ARY, 1) - LBound(ARY, 1) + 1
    c = UBound(ARY, 2) - LBound(ARY, 2) + 1
    '開始セルから範囲を拡張して貼り付け
    CL.Resize(r, c).Value = ARY
End Function

配列の要素数に応じて入力範囲をリサイズ

配列を一括して入力する場合、入力先の範囲を配列の要素数に応じたサイズにする必要があります。その行数と列数を UBound と LBound の差分から求めています（UBoundは要素数ではなく最大数なので）。
配列の求め方によってLBoundは0だったり1だったりしますが、どちらでも対応できます。このあたりを毎回気にしないで済むようにしたかったのが汎用部品化している理由でもあります。

ListObject（テーブル）に配列を追記する関数

入力先がテーブルの場合は、追記した行までテーブル領域が拡張されなければなりません。この拡張はExcelによって自動的に行われますが、そのためにはいくつかの注意点があります。

Sub f_ArrayToTable(LO As ListObject, ARY As Variant)
    
    Dim rng As Range
    Dim r As Long, c As Long
    
    With LO
        .ShowTotals = False
        Set rng = .ListRows.Add.Range(1)
    End With
    
    '範囲をリサイズして配列を入力
    r = UBound(ARY, 1) - LBound(ARY, 1) + 1
    c = UBound(ARY, 2) - LBound(ARY, 2) + 1
    rng.Resize(r, c).Value = ARY

End Sub

集計行の表示に注意

テーブルの最終行の直下にデータを入力すると自動的に領域が拡張されるという便利仕様があります。しかし、集計行が表示されている場合、その下にデータを入力してもテーブル領域は拡張されません。このため、上記マクロでは、ShowTotals プロパティで集計行を非表示にしています。

入力起点となるセルの特定

ListRows.Add メソッドでテーブルの末尾にテーブル行を追加し、その先頭セルを入力起点とします。データなしの状態（ListRows.Count = 0）のテーブルにも対応できます。この起点から配列を入力すると、テーブルが自動的に拡張されます。