二次元配列でJOIN関数が使えない - Infomentのブログ～Excel VBA奮闘記～

かつて、範囲から値だけを抜き出して、配列に格納できることを知った私は
思いました。

「これは良いことを学んだ。指定列の値を結合する際に使えそうだ」

具体的には、こんな感じです。

Sub JoinTest()
    Dim seq As Variant
        seq = Selection
        MsgBox Join(seq, vbLf)
End Sub

f:id:Infoment:20180801230437p:plain

イメージとして、こんな結果を期待しました。

f:id:Infoment:20180801230721p:plain

しかし、結果はエラー終了。

f:id:Infoment:20180802000012p:plain

長らく理由がわかりませんでしたが、偶然気が付きました。例え１列しかなくても、この配列は二次元配列であり、恐らくJoin関数は、一次元配列にしか使用できないのだと。

f:id:Infoment:20180801231040p:plain

原因がわかれば、あとは対策するだけです。そこで、二つの方法を考えてみました。

ユーザー定義関数で結合

Joinとは名ばかりで、愚直に処理（一つずつ結合）する関数です。

Public Function Joins(seq As Variant, _
                      Optional delimiter As String = "", _
                      Optional second_dimension_index As Long = 1) _
                      As String
    Dim i As Long
        Joins = seq(1, second_dimension_index)
        For i = 2 To UBound(seq, 1)
            Joins = Joins & delimiter & _
                    seq(i, second_dimension_index)
        Next
End Function

愚直ではありますが、一度に求める結果が得られるため、手軽に使える利点があります。

Join関数を使用できる形に変換

二次元配列だからJoin関数が使用できない。ならば、一次元配列にしてしまえばいい。
ということで、二段階で処理してみました。

指定範囲の値でコレクションを作成
コレクションを配列に変換して結合

Sub JoinTest()
    Dim col As Collection
        Set col = New Collection        
    Dim r As Range
        For Each r In Selection
            col.Add r.Value
        Next        
        MsgBox Join(ToArray(col), vbLf)
End Sub

↓ 他言語には「ToArray メソッド」が有るらしく、そこから名付けたようです（※）。
※この関数については、直属の部下から知恵を拝借しました。

Public Function ToArray(col As Collection) As Variant
    Dim seq As Variant
        ReDim seq(col.Count - 1)        
    Dim i As Long
        For i = 0 To col.Count - 1
            seq(i) = col.Item(i + 1)
        Next
        ToArray = seq
End Function