配列をセル範囲(Range)に一括出力する

配列をセル範囲(Rangeオブジェクト)に一括で出力する方法を紹介します。

配列を使ったマクロ高速化の肝となる部分ですので、
しっかり習得しておきましょう。

一次元配列をセルに一括出力する

一次元配列をセルに一括出力する場合は、以下のコードを実行します。

' セルに出力する配列
Dim 一次元配列 As Variant
一次元配列 = Array(1, 2, 3, 4)

' 横方向→ に出力する場合
Range("A1:D1").Value = 一次元配列

' 縦方向↓ に出力する場合
Range("A1:A4").Value = WorksheetFunction.Transpose(一次元配列)

　
一次元配列をセルに出力する場合は、上記コードのように

[配列と大きさをそろえたセル範囲].Value = 一次元配列

この形でRangeオブジェクトのValueプロパティに配列を代入できます。

VBAの一次元配列はセル範囲的には横方向(1行n列)の配列なので、
横方向→に出力するには単純に代入するだけでよいですが、
縦方向↓に出力する場合はn行1列の配列に直してから代入する必要があります。

まさにその処理を行う関数「TRANSPOSE関数」がワークシート関数にありますので、
縦方向に出力する場合はWorksheetFunction.Transposeを使用してから代入します。

※ Transpose関数は「要素数65,536まで」しか使用できない制限があり、
　これを超えるデータを一括出力する場合は一旦二次元配列にする必要があります。
　その方法については後述の「二次元配列をセルに出力する」方法を参照ください。

このように一括出力を行うことでセルへのアクセス回数を1回にすることができ、
以下のように1つずつ出力するのに比べて圧倒的に早い速度で出力できます。

' ↓ これだと i 回セルにアクセスするため数百～数万倍時間がかかることもある
Dim i As Long
For i = LBound(一次元配列) To UBound(一次元配列)
    Cells(R + i, C) = 一次元配列(i)
Next

　
配列を使っても最後の出力が一括でないと高速化にはなりませんので、
配列を使った目的が高速化であるならば、必ずこの方法で出力してください。

さて基本構文としては

[配列と大きさをそろえたセル範囲].Value = 一次元配列

こちらでいいのですが、実際にマクロを組むとなると、
セル範囲と配列の大きさをそろえるのにひと手間かかります。

セル範囲と配列の大きさをそろえるコードを簡潔に書くには、
RangeオブジェクトのResizeプロパティに配列の大きさを渡すのがおすすめです。

' セルに出力する配列
Dim 配列 As Variant
配列 = Array(1, 2, 3, 4)

' 出力するセル範囲の始点となるセル
Dim 始点セル As Range
Set 始点セル = Worksheets("○○").Range("A1")

' 出力用に配列の大きさを取得
Dim 要素数 As Long
要素数 = UBound(配列) - LBound(配列) + 1
    
' 横方向→ に出力する場合
始点セル.Resize(1, 要素数).Value = 配列

' 縦方向↓ に出力する場合
始点セル.Resize(要素数, 1).Value = WorksheetFunction.Transpose(配列)

　
このように「始点セル.Resize(配列の要素数)」というコードを用いれば、
セル範囲をアドレスや行列番号で取得する必要がなくなります。

書くのも簡単ですし、なにより読みやすくなりますね。

Resizeプロパティは配列以外にも便利に使える場面が多いので覚えておきましょう。

要素数を求めるUbound/Lbound関数についてはこちらの記事をどうぞ
www.limecode.jp

二次元配列をセルに一括出力する

二次元配列をセルに一括出力する場合は、以下のコードを実行します。

' セルに出力する配列（適当にどこかのセル範囲から取得）
Dim 二次元配列 As Variant
二次元配列 = Range("E10:H13").Value

' 二次元配列をセルに一括出力
Range("A1:D4").Value = 二次元配列

　
上記コードの通り、一次元配列の時と同様

[配列と大きさをそろえたセル範囲].Value = 二次元配列

というコードで一括出力することができます。

ちなみに今回は代入する二次元配列も「セル範囲.Value」からもらってきました。

そこでこのコードの変数をなくして代入してみると、

Range("A1:D4").Value = Range("E10:H13").Value

と、よく知る「値のみコピー」のコードになります。

セル範囲を値のみコピーするこのコードの処理を細かく見ると、
「右辺で生成した二次元配列を左辺のセル範囲に代入」
しているコードということになります。

これを知っておくと、配列のコードも覚えやすくなりますね。

さて、基本構文はこれでよいのですが、
実務で使うとなると配列とセル範囲の大きさをそろえるコードも必要になります。

一次元配列の時と同様、配列の大きさを求めてResizeしたコードがこちらです。

' セルに出力する配列（適当にどこかのセル範囲から取得）
Dim 配列 As Variant
配列 = Range("E10:H13").Value

' 出力するセル範囲の始点となるセル
Dim 始点セル As Range
Set 始点セル = Worksheets("○○").Range("A1")

' 出力用に配列の大きさを取得
Dim 行数 As Long: 行数 = UBound(配列, 1) - LBound(配列, 1) + 1
Dim 列数 As Long: 列数 = UBound(配列, 2) - LBound(配列, 2) + 1
    
' 横方向→ に出力する場合
始点セル.Resize(行数, 列数).Value = 配列

　
配列の開始番号と最終番号を求めるUbound/Lbound関数は、
第2引数に「次元」を渡すと対象次元の大きさを返してくれます。

それによって求めた行(第1次元)、列(第2次元)の値を用いて、
「始点セル.Resize(行数, 列数).Value = 配列」
という記述で二次元配列をセル範囲に一括出力することができます。

こちらも非常に読みやすい記述になりますね。

このコードによる一括出力も一次元配列の時と同様、
セルへのアクセスが1回だけとなるので超高速です。

配列を使ってマクロを高速化する場合は、このコードを利用してください。

配列→セルへの出力を汎用関数化する

今回のコードは構造は単純なのですが、いちいち書くとなると面倒です。

特に要素数を求める「Ubound - Lbound + 1」はたくさん書くとミスの元ですし、
これのために逐一変数を用意するのも大変です。

こういった「簡単だけど書くのが手間な処理」は汎用関数にして持っておきましょう。

' 一次元配列 → セル
Sub 一次元配列をセルに出力する(出力始点セル As Range, Arr出力配列 As Variant _
    , Optional is縦方向へ出力 As Boolean = True)

    Dim 要素数 As Long: 要素数 = Count配列の要素数(Arr出力配列)
    If is縦方向へ出力 Then
        出力始点セル.Resize(要素数, 1).Value _
            = GetArray一次元配列→n行1列の二次元配列(Arr出力配列)
    Else
        出力始点セル.Resize(1, 要素数).Value = Arr出力配列
    End If
    
End Sub

' 二次元配列 → セル
Sub 二次元配列をセルに出力する(出力始点セル As Range, Arr出力配列 As Variant)

    出力始点セル.Resize(Count配列の要素数(Arr出力配列, 1) _
                              , Count配列の要素数(Arr出力配列, 2)).Value = Arr出力配列

End Sub

' 配列要素数の取得
Function Count配列の要素数(Arr, Optional 次元 = 1) As Long
    Count配列の要素数 = UBound(Arr, 次元) - LBound(Arr, 次元) + 1
End Function

' Transpose上限対応用関数
Function GetArray一次元配列→n行1列の二次元配列(Arr As Variant) As Variant
    
    Dim 生成配列()
    ReDim 生成配列(LBound(Arr) To UBound(Arr), 1 To 1)
    
    Dim i As Long
    For i = LBound(Arr) To UBound(Arr)
        生成配列(i, 1) = Arr(i)
    Next
    
    GetArray一次元配列→n行1列の二次元配列 = 生成配列
    
End Function

' 標準モジュールではこう書けるようになる
Call 一次元配列をセルに出力する(Range("A1"), 一次元配列)
Call 一次元配列をセルに出力する(Range("A1"), 一次元配列, False) ' ← 横方向
Call 二次元配列をセルに出力する(Range("A1"), 二次元配列)

汎用関数によって標準モジュールが1行コードになりましたね。

特に「出力先は始点となるセルを指定するだけでよい」ため、
書きやすく、そして読みやすいコードになっています。

関数の中身はほぼ今回の基本コードですが、いくつか解説しますと、

まず「要素数の計算」部分は今回の処理以外にもよく使うコードですので、
単独の汎用関数として作成し、汎用関数の中で汎用関数を呼びました。

続いて「出力方向(縦横)の指定」については関数を2つに分けてもいいのですが、
せっかくなのでTrue/Falseで指定できるようにしました。

私はほぼ「縦」で使うときが多いので、省略時は縦という仕様にしています。

あとは「Transpose上限対応」も行っていますので後述の解説をご覧ください。

この関数を用意しておけば、配列⇒セルの処理を行う際に、
「要素数の計算」「出力先セル範囲の取得」をいちいちやる必要がなくなります。

さらにマクロの本筋とは直接関係のない処理を関数内に隠したことで、
メインマクロ上では1行になって非常に読みやすいコードになります。

こういった簡単だけどひと手間かかる処理は、
簡単に関数化できる上にその効果がとても大きいです。

是非とも関数にして持っておきましょう。

汎用関数の作り方についてはこちらの記事をどうぞ
www.limecode.jp

Transposeの要素数上限(65,536)の対応

一次元配列を縦に出力する場合に必要になるTranspose関数ですが、
要素数65,536を超える配列を渡すと上手く動かなくなります。

これを解決する方法は、上記汎用関数に用意していたように、
一旦「n行×1列の二次元配列」に入れなおしてからセルに出力します。

' Transpose上限対応用関数
Function GetArray一次元配列→n行1列の二次元配列(Arr As Variant) As Variant
    
    Dim 生成配列()
    ReDim 生成配列(LBound(Arr) To UBound(Arr), 1 To 1)
    
    Dim i As Long
    For i = LBound(Arr) To UBound(Arr)
        生成配列(i, 1) = Arr(i)
    Next
    
    GetArray一次元配列→n行1列の二次元配列 = 生成配列
    
End Function

' セルに出力する際
出力始点セル.Resize(要素数, 1).Value _
            = GetArray一次元配列→n行1列の二次元配列(一次元配列)

愚直な「For文で全要素を1つずつ代入する」コードですね。

こういうコードこそ都度書くのは面倒でやっていられませんので、
しっかり汎用関数にしておきましょう。

おまけ：汎用関数をさらに統合する

今回紹介した「一次元配列→セル」「二次元配列→セル」の汎用関数ですが、
これを統合して一本の関数にすることもできます。

一次元・二次元どちらを渡しても動く関数がこちらになります。

' 配列 → セル
Sub 配列をセルに出力する(出力始点セル As Range, Arr出力配列 As Variant _
    , Optional is縦方向へ出力 As Boolean = True)

    ' 配列の次元数で処理を分岐
    Select Case Get配列の次元数(Arr出力配列)
    Case 1
        Dim 要素数 As Long: 要素数 = Count配列の要素数(Arr出力配列)
        If is縦方向へ出力 Then
            出力始点セル.Resize(要素数, 1).Value = _
                GetArray一次元配列→n行1列の二次元配列(Arr出力配列)
        Else
            出力始点セル.Resize(1, 要素数).Value = Arr出力配列
        End If
        
    Case 2
        出力始点セル.Resize(Count配列の要素数(Arr出力配列, 1) _
                          , Count配列の要素数(Arr出力配列, 2)).Value = Arr出力配列
    Case Else
        Err.Raise 1000, , "セル出力に1,2次元以外の配列が指定されました。"
    End Select
    
End Sub

' 配列次元数の取得
Function Get配列の次元数(Arr As Variant) As Long

    ' 渡された変数が配列ではない場合は0を返すこととする
    If IsArray(Arr) = False Then Get配列の次元数 = 0: Exit Function

    ' エラーが出るまでUBoundを取得
    Dim tmp
    Dim 次元数 As Long: 次元数 = 0
    On Error Resume Next
    Do While Err.Number = 0
        次元数 = 次元数 + 1
        tmp = UBound(Arr, 次元数)
    Loop
    On Error GoTo 0

    ' エラーが出たひとつ前の次元が求める次元数
    Get配列の次元数 = 次元数 - 1
    
End Function

なんてことはない、配列の次元数でSelect Case分岐した関数ですね。

配列の次元数を求めるストレートな関数はないため、
「Uboundがエラーになる次元 - 1」をDo文を使って求めています。

実際はこの関数にするかは良し悪しで、
「渡す配列が一次元/二次元かが明確なコードの方が良い」
という考えで行くなら、関数は分けた方が良いとも考えられます。

お好きな方を持って行ってください。