HOME > 即効テクニック > Excel VBA > 関数関連のテクニック > 配列をセルに一括で代入する

配列をセルに一括で代入する|Excel VBA

関数関連のテクニック

配列をセルに一括で代入する

(Excel 97/2000/2002/2003/2007/2010/2013/2016)

Excelは、配列を一括でセルに代入できます。
たとえば、次のような方法です。
このとき、配列の大きさと、代入するセル範囲の大きさを一致させるのがポイントです。

Sub Sample1()
    Dim tmp(2) As String
    tmp(0) = "Excel"
    tmp(1) = "Word"
    tmp(2) = "Access"
    Range("A1:C1") = tmp
End Sub

ただし、このような一次元配列を一括代入できるセル範囲は、

セルA1・セルB1・セルC1

のように「横方向のセル範囲」です。こうした配列を、

セルA1
セルA2
セルA3

といった「縦方向のセル範囲」に一括代入することはできません。

Sample1の

    Range("A1:C1") = tmp

の部分を、次のように変更すればうまく行きそうなものですが、

    Range("A1:A3") = tmp

これを実行すると、次のように代入されてしまいます。

■配列の値を縦方向のセルに代入する

もちろん、次のように配列の要素を個々に操作すれば、横でも縦でも好きな方向に代入することは可能です。

Sub Sample2()
    Dim tmp(2) As String, i As Long
    tmp(0) = "Excel"
    tmp(1) = "Word"
    tmp(2) = "Access"
    For i = 0 To 2
        Cells(i + 1, 1).Value = tmp(i)
    Next i
End Sub

でも、ちょっとめんどうですね。
何とか一次元配列を「縦方向のセル範囲」に一括代入できないでしょうか。

一次元配列である限り、そのまま一括代入はできません。
縦方向のセル範囲に一括代入するには、元の配列が二次元配列でなければなりません。
たとえば次のような配列です。

Sub Sample3()
    Dim tmp(2, 0) As String
    tmp(0, 0) = "Excel"
    tmp(1, 0) = "Word"
    tmp(2, 0) = "Access"
    Range("A1:A3") = tmp
End Sub

したがって、

    tmp(0) = "Excel"
    tmp(1) = "Word"
    tmp(2) = "Access"

という一次元配列を、

    tmp(0, 0) = "Excel"
    tmp(1, 0) = "Word"
    tmp(2, 0) = "Access"

という二次元配列に変換できれば、縦方向のセル範囲に一括代入できるわけです。

■一次元配列を二次元配列に変換するTranspose関数

一元配列を二次元配列に変換するには、とても高度なプログラミングテクニックが必要だと思われるかもしれませんが、実は簡単なんです。

Excelには、横方向の表を縦方向に変換して貼り付ける機能があります。
そう「形式を選択して貼り付け」の「行列の入れ替え」です。
さらに、このような行列の変換を行うワークシート関数もあります。
それがTRANSPOSE関数です。
TRANSPOSE関数を使えば、一次元配列を二次元配列に一発で変換して、縦方向のセル範囲に代入できます。

Sub Sample4()
    Dim tmp(2) As String
    tmp(0) = "Excel"
    tmp(1) = "Word"
    tmp(2) = "Access"
    Range("A1:A3") = WorksheetFunction.Transpose(tmp)
End Sub