VBA TRANSPOSE関数で一次元データのみの二次元配列を一次元配列に変換する。

VBAでは、セル範囲と配列の相互変換ができるが、たとえ1列分しか変換しなくても二次元配列となってしまう。

VBAのJoin関数でひとつの文字列に加工しようと思ったら色々と工夫が必要なのだが、実はワークシート関数のTRANSPOSEを使うと簡単にできるという裏技がある。

たとえば次のようなデータを用意する。
f:id:t-hom:20180802072110p:plain

これを二次元配列arrとして読み込み、Transpose関数を使って一次元配列arr2を作る。あとはJoinするだけ。

Sub 一次元データのみの二次元配列のJoin()
    Dim arr: arr = Range("A1:A3")
    Dim arr2: arr2 = WorksheetFunction.Transpose(arr)
    MsgBox Join(arr2, vbNewLine)
End Sub

次に行データだったらどうするか。
f:id:t-hom:20180802072255p:plain

そのままではうまくいかないので、Transposeを2回かませて列データに変更する。

Sub 一次元データのみの二次元配列のJoin_行バージョン()
    Dim arr: arr = Range("A1:C1")
    Dim arr2: arr2 = WorksheetFunction.Transpose(arr)
    Dim arr3: arr3 = WorksheetFunction.Transpose(arr2)
    MsgBox Join(arr3, vbNewLine)
End Sub

参考

この事実を教えてくれたのはこちらのサイト。感謝。
配列研究室 STEP 3

上記サイトではEvaluate関数の短縮表記である[]を使って更に短く書いている。
今回のケースに適用すると、以下のとおり。

Sub 一次元データのみの二次元配列のJoin_行バージョン()
    MsgBox Join([TRANSPOSE(TRANSPOSE(A1:C1))], vbNewLine)
End Sub

ただこれだとセル範囲がベタ書きになってしまうので、ThisWorkbookからRangeまで確実に指定したい時に応用が利かない。
※↓こう書きたい場合のこと

Sub 一次元データのみの二次元配列のJoin_行バージョン()
    Dim arr: arr = ThisWorkbook.Sheets(1).Range("A1:C1")
    Dim arr2: arr2 = WorksheetFunction.Transpose(arr)
    Dim arr3: arr3 = WorksheetFunction.Transpose(arr2)
    MsgBox Join(arr3, vbNewLine)
End Sub

[]を使わずに少し短くしようと思ったら、こうかな？

Sub 一次元データのみの二次元配列のJoin_行バージョン()
    Dim arr: arr = ThisWorkbook.Sheets(1).Range("A1:C1")
    With WorksheetFunction
        MsgBox Join(.Transpose(.Transpose(arr)))
    End With
End Sub