配列をセルに代入する

---

珍しくmixiのコミュニティで回答したので、こっちにも書いておきます。もともとの質問は「テキストボックスに入力された、改行で区切られたデータを、各セルに一括代入するには？」というものでした。つまり、こんな感じでしょうか。

UserFormの左側はテキストボックスです。MultiLineプロパティをTrueにして、複数行を入力できるようにしています。

一次元配列をセルに代入する

たとえば「田中」「鈴木」「山田」という3要素を持つ配列をセルに代入するには、次のようにします。

Sub Sample1()
    Dim buf As String
    buf = "田中" & vbCrLf & "鈴木" & vbCrLf & "山田"
    Range("A1:C1") = Split(buf, vbCrLf)
End Sub

Split関数は「"田中" & vbCrLf & "鈴木" & vbCrLf & "山田"」という文字列を改行コード(vbCrLf)で区切り

という一次元配列を返します。これは、セル範囲A1:C1と同じ大きさですから「Range("A1:C1") = Split(buf, vbCrLf)」というコードで一括代入が可能です。しかし、この一次元配列を、セル範囲A1:A3といった縦方向のセル範囲に代入することはできません。縦方向のセル範囲に一括代入するには

という二次元配列を作ってやらなくてはなりません。

一次元配列を二次元配列にする

一次元配列を二次元配列に変換するなんて、なんかものすごく高度なプログラミング技術が要求されそうですけど、ちょっと待ってください。Excelの標準機能に似たようなのがありませんか？たとえば、下図のような表があったとします。

これを

のように行列を入れ替えるには、どうしたらいいですか？

そう、「形式を選択して貼り付け」の「行列を入れ替える」ですね。Excelでは、こうした行列の変換を簡単に行えるようになっています。さらに、こうした行列の変換を行うワークシート関数も用意されています。それがTRANSPOSE関数です。

まず、元の表を行列変換した結果のセル範囲を選択しておきます。

今回のケースでは、元の表が「4行×4列」と行列の数が同じなので、変換結果の表も「4行×4列」になります。(失敗した・・・3×4にすればよかった。もう画像撮っちゃったし、このまま進めよう^^;)

左上のセルにTRANSPOSE関数を入力します。

引数に指定するのは、元の表です。ここで大事なポイントがあります。TRANSPOSE関数は配列として入力しなければいけませんので、式を確定するとき、EnterキーではなくShift＋Ctrl＋Enterキーを押します。

実行すると下図のようになります。あらかじめ選択しておいたすべてのセルに同じTRANSPOSE関数が入力されます。数式バーを見ると、配列数式を表す{}が表示されています。

同じように、このTRANSPOSE関数を使うと、次のような変換が可能です。

もう、おわかりですね。今回のように、一次元配列を二次元配列に変換するには、VBAからこのTRANSPOSE関数をコールしてやればいいんです。

Sub Sample2()
    Dim buf As String
    buf = "田中" & vbCrLf & "鈴木" & vbCrLf & "山田"
    Range("A1:A3") = WorksheetFunction.Transpose(Split(buf, vbCrLf))
End Sub

これを応用すると、テキストファイルのデータを複数セルに一括代入することもできますね。ちなみに、こんな感じでしょうか。

Sub Sample3()
    Dim buf As String, tmp As Variant
    With CreateObject("Scripting.FileSystemobject").GetFile("C:\Sample.txt").OpenAsTextStream
        buf = .ReadAll
        .Close
    End With
    tmp = Split(buf, vbCrLf)
    Range(Cells(1, 1), Cells(UBound(tmp), 1)) = WorksheetFunction.Transpose(tmp)
End Sub

いずれにしても、TRANSPOSE関数の存在を知らないと、この発想は思いつきません。Excelでマクロを組むときは、VBAだけじゃなく、機能や関数についても精通していないとダメだよ、という良い例ですね。