配列をセルにぶち込む
前回次の記事で、セル範囲を配列にぶち込んで操作する方法を紹介しました。
この時は、Variant型の変数で受ける形でそれをそのままシートに戻しましたが、それ以外の型の配列もシートに一括で代入することは可能です。
今回は例として、String型の配列を作成してそれを一括でシートに代入する方法を紹介したいと思います！
配列をEXCELシートに一括で代入する方法
次のような配列を準備します。
Sub sample()
'配列の準備
Dim tmp(4) As String
tmp(0) = "ひらちんの部屋0"
tmp(1) = "ひらちんの部屋1"
tmp(2) = "ひらちんの部屋2"
tmp(3) = "ひらちんの部屋3"
tmp(4) = "ひらちんの部屋4"
'イミディエイトウィンドウに書き出し
Dim t As Variant
For Each t In tmp
Debug.Print t
Next
End Sub要素が５つのString型の配列変数を用意します。
中身は、「ひらちんの部屋0」から「ひらちんの部屋4」までを順番に代入しておきましょう。
※配列の要素番号は０からですので５つの要素の配列を用意する場合は、要素番号４までとします。
後半は、配列の中身を順番にイミディエイトウィンドウに出力しているコードです。
動画で動きを確認してみましょう！
そんなに難しくないですね(^o^)
横方向に出力
それでは、ここで作った配列をEXCELシートに一括で出力したいと思います。
ここで注意するポイントは、”一括で出力する時は配列の要素数とEXCELシートの出力範囲のセル数をあわせる”必要があることです。
例えば次のようなコードで、A1セルに出力しようとすると、最初の要素番号（ここではtmp(0)）しか出力されません。
Sub sample()
'配列を準備する
Dim tmp(4) As String
tmp(0) = "ひらちんの部屋0"
tmp(1) = "ひらちんの部屋1"
tmp(2) = "ひらちんの部屋2"
tmp(3) = "ひらちんの部屋3"
tmp(4) = "ひらちんの部屋4"
'ここでシートに出力したい
ActiveSheet.Range("A1") = tmp
End Subやってみましょう。
結果は次の図のように、A1セルに最初の要素が出力されます。
一括で出力するには、出力したいセル範囲の数を配列の要素数と同じにする必要があります。
Sub sample()
'配列を準備する
Dim tmp(4) As String
tmp(0) = "ひらちんの部屋0"
tmp(1) = "ひらちんの部屋1"
tmp(2) = "ひらちんの部屋2"
tmp(3) = "ひらちんの部屋3"
tmp(4) = "ひらちんの部屋4"
'ここでシートに出力
With ActiveSheet
.Range(.Cells(1, 1), .Cells(1, 5)) = tmp
End With
End Sub”ここでシートに出力”の部分にあるように、セル範囲を要素数と合わせて指定します。
ここでは、配列の要素数は5つなので、A1セルからE1セルまで（つまりCells(1,1)からCells(1,5)）を指定しています。
では実行してみます。
上手く行きましたね(^o^)
縦方向に出力
ここでは1次元の配列の場合を紹介していますが、先程のように1次元の配列をセルに出力する場合は、何もしないと上の図のように横に向かっての範囲を指定しないとおかしなことになります。
例えば、出力セル範囲を縦方向にした次のようなコードではおかしなことが発生します。
Sub sample()
'配列を準備する
Dim tmp(4) As String
tmp(0) = "ひらちんの部屋0"
tmp(1) = "ひらちんの部屋1"
tmp(2) = "ひらちんの部屋2"
tmp(3) = "ひらちんの部屋3"
tmp(4) = "ひらちんの部屋4"
'ここでシートに出力
With ActiveSheet
.Range(.Cells(1, 1), .Cells(5, 1)) = tmp ’縦に範囲を指定
End With
End Sub指定したすべてのセルに、要素０番の配列の中身が出力されてしまいました(;_;)
こうならないようにするには、WorksheetFunction.Transposeを使います。
WorksheetFunctionプロパティは、ワークシート上の関数を使う命令です。
これを使うと、全てでは無いですが大抵のワークシート上で使っていた関数をVBAで利用することが出来ます。
Transpose関数はおなじみだと思いますが、行と列を入れ替えて表示するワークシート上での関数ですね(^o^)
１次元配列はTranspose関数を通すことで、２次元配列になります。
２次元配列にすることによって、縦方向のセル範囲と合致した並びになると言うわけです。
コードは次のようになります。
Sub sample()
'配列を準備する
Dim tmp(4) As String
tmp(0) = "ひらちんの部屋0"
tmp(1) = "ひらちんの部屋1"
tmp(2) = "ひらちんの部屋2"
tmp(3) = "ひらちんの部屋3"
tmp(4) = "ひらちんの部屋4"
'ここでシートに出力
With ActiveSheet
.Range(.Cells(1, 1), .Cells(5, 1)) = WorksheetFunction.Transpose(tmp)
End With
End Sub上手く出力することが出来ましたね(^o^)
要素数を動的に捉える
サンプルでは、配列の要素数を指定して行っていますので直接数字で指定しましたが、通常データを扱っていると配列の要素数が事前に分からないことも多くあると思います。
配列の要素が事前に分からない場合は、Ubound関数やLbound関数を使うことで配列の要素数を数えることが出来ます。
Ubound関数・・・引数に指定した配列の最大要素”番号”を取得する
Lbound関数・・・ 引数に指定した配列の最小要素”番号”を取得する
取得出来るのは、要素”数”ではなく要素”番号”であることは中止して覚えておく必要があります。
先程の縦に指定したサンプルコードを、Ubound関数を使って書き換えます。
Sub sample()
'配列を準備する
Dim tmp(4) As String
tmp(0) = "ひらちんの部屋0"
tmp(1) = "ひらちんの部屋1"
tmp(2) = "ひらちんの部屋2"
tmp(3) = "ひらちんの部屋3"
tmp(4) = "ひらちんの部屋4"
'ここでシートに出力
With ActiveSheet
.Range(.Cells(1, 1), .Cells(UBound(tmp) + 1, 1)) = WorksheetFunction.Transpose(tmp)
End With
End Sub最大行番号を指定するところで、Ubound関数を利用しています。
先程の繰り返しになりますが、Ubound関数は要素番号を返すので、今回のように０番から始まる配列に対して利用する場合は、要素数が５つの場合「４」が返ってきます。
セルの範囲指定に合わせるように、「＋１」するのを忘れないようにしましょう。
※配列の要素の指定の仕方によって変わるので、その時その時で適切に設定しましょう。
では実行してみます(^o^)
バッチリですね(^o^)
コメント