引数が不定でもParamArrayで大丈夫、ParamArrayでユーザーファンクション

今回は不連続なセル範囲の文字列の結合をしていきたいと思います。
連続する文字列、特定のセル範囲が決まっていてそれを結合するという、ユーザ定義関数は以前作ったことがありますが、今回は連続している部分もあり、していない部分もあります。
そして、セルの数も不揃いだというような、そういったケースでの結合を考えていきたいと思います。

今回使う、ParamArrayという名前から、配列の一種だと気づく方も多いかと思います。
配列を本格的に学ぶ準備体操としてparamarrayを学んでみましょう。

マクロ講座動画編 100回

動画でExcel　引数が不定でもParamArrayで大丈夫、ParamArrayでユーザーファンクション

配列の引数が不定でもParamArrayで大丈夫、ParamArrayでユーザーファンクション

この記事で学べる内容

複数のセル範囲（連続・不連続のどちらも）に入力された文字列を、ユーザー定義関数でまとめて結合する考え方を理解する
引数の数が実行のたびに変わる「可変長引数」の場面で、ParamArray を使って柔軟に引数を受け取る方法を学ぶ
ParamArray の基本ルール（
・宣言は ParamArray 引数名() As Variant
・プロシージャ内で 1 つだけ宣言できる
・他の通常引数と組み合わせてよいが、ParamArray は必ず最後の引数に置く）を整理して覚える
「区切り文字（delimiter）」「空白を無視するかどうか（ignore_empty）」「結合対象のセル／範囲（joinText）」といった意味のある引数設計で、使いやすいユーザー定義関数を作るコツを学ぶ
ParamArray で受け取った引数を For Each で走査し、Range なのか単一値なのかを TypeName で判定して処理を分けるテクニックを身につける
二重の For Each ループ（外側：ParamArray で受け取った各 grouped ／内側：grouped 内の各 piece）を使って、不連続なセル範囲の全ての値を取り出す処理を理解する
ignore_empty フラグと IsEmpty 関数を組み合わせて、「空白セルを無視する／しない」を切り替えられるロジックを作る方法を学ぶ
動的配列 Dim newArray() As Variant を ReDim Preserve で都度拡張しながら値を追加していく実践的な配列操作パターンを習得する
最終的に Join(newArray, delimiter) で配列を 1 つの文字列に結合し、ユーザー定義関数の戻り値として返す一連の流れを理解する
不連続なセル範囲を一度に結合できるユーザー定義関数を作成し、手作業でのコピペや & 演算子による結合を大幅に省力化する方法を学ぶ

動画版「マクロ講座」です。
セル範囲の文字列を結合するユーザー定義関数を以前に紹介したことがありました。

Function Ketugou(MyRange As Range) As String
    Dim c As Range
    For Each c In MyRange
        Ketugou = Ketugou & c
    Next
End Function

今回は実行するたびに引数の数が変わってしまうような場合、引数を特定できないときに、どのようにコードを作るかと言うお話をします。
Sampleとしてユーザー定義関数を作成します。
結論から言えばこのような時は引数にParamArrayを指定してやることで解決します。
またParamArrayを使うときの決まりや、コード内で動的配列を使う方法も紹介しています。
（サンプルファイルは、こちらです。引数が不定でもParamArrayで大丈夫、ParamArrayでユーザーファンクション、サンプル100回）

マクロ動画　引数が不定でもParamArrayで大丈夫、ParamArrayでユーザーファンクション

今回の動画で利用している、Excel VBA の ParamArray とは

作成しようとしているマクロでは、引数の数が決まっていなくて、増えることがわかっています。
そのような可変長引数を指定するための特殊な引数です。これは、引数の数が固定ではなく変わる可能性がある場合に使用します。
ParamArray はFunctionまたは、サブルーチンに指定する引数のリストの必ず最後に配置します。
VBA では他の通常引数と一緒に使えます。ただし「ParamArray 引数は 1 つだけ」「必ず最後の引数に置く」という制約があります。
ParamArrayを使用することで、引数の数が固定ではなくても、柔軟に対応できるようになります。

不連続なセル範囲の文字列の結合をする

以下の図のように、セル範囲の文字列を結合したいケースは、わりとよくあります。
セル範囲が連続している場合は、引数にするセル範囲は１つです。
通常単独で実行されるサブプロシージャーの場合は引数を付けませんけれども、他のプロシージャーから呼び出す形で使うマクロは、Callステートメントで呼び出すことができます。
そういったプロシージャーには引数を指定することができます。ファンクションプロシージャーというのもあります。それはもう関数と同じことですね。関数はサブプロシージャーからも呼ぶことできますしシート上でユーザ定義関数として使うこともできます。
結合したいセル範囲がいくつもあって、引数が複数になるケースでは、どのようにコードを書けばいいのかというと、 Paramarrayというのを使います。Paramarrayを使うと、プロシージャーの呼び出しの時にその位置の後ろから指定される引数を配列として受け取ります。
そのParamarrayと付けた引数に関してはその都度、数が変わっても大丈夫なんです。
必ずこのParamarrayというのは、引数複数ある場合は一番最後に指定するという約束があります。データ型もバリアントということで、これも決まりです。
引数は図のように３つ取ることに決めました。空白がある場合、空白の扱いをどうするかということで、それを無視するかどうかが、
ignore empty、空白無視をtrueにすれば無視するし、falseにすれば無視しない。
JoinTextは、複数ある可能性のあるセル範囲ですからParamarrayをつけて最後に記述します。

 
Function ketugou(delimiter As String, ignore_empty As Boolean, _
   ParamArray joinText() As Variant)
End Function

不連続なセル範囲の文字列の結合をする

Functionの名前と引数の部分ができました。
ではコードの部分を書いていきましょう。
結合対象は、 Paramarray　で joinTextという配列に受け取ったものですね。
それはrangeだったりcellだったり rangeだったりcellだったりっていうように、数が不特定です。
3行目を結合していくケースを考えることにしましょう。
離れているかたまりは、セルが複数であろうとも、１つであろうともGroupとします。
またgroupedの中の１つ１つはpieceとよびましょう。
さらにカウント変数のiと動的配列として、Dim newArray() As Variantを宣言します。
for eachで受け取った範囲のgroupedを回します。
For Each piece In grouped
Next piece
でもgroupedはjoinText で複数受け取っていますから、2重For文にしてやる必要があります。
For Each grouped In joinText
　　For Each piece In grouped
　　Next piece
Next grouped

受け取った範囲が、複数セルなのか、単独セルなのか

外側のFor文では、受け取ったjoinTextの一つのgrouped が範囲なのか単独セルなのかを見ます。
もし範囲ならば、ひとつひとつを見ていくので、
If TypeName(grouped) = "Range" Then
となります。
ここで、空白を無視する場合とそうでない場合に分かれます。

   For Each grouped In joinText
        If TypeName(grouped) = "Range" Then
            For Each piece In grouped
                If Not ignore_empty Or Not IsEmpty(piece) Then
                    ReDim Preserve newArray(i)
                    newArray(i) = piece
                    i = i + 1
                End If
            Next piece
         Else
                If Not ignore_empty Or Not IsEmpty(grouped) Then
                    ReDim Preserve newArray(i)
                    newArray(i) = grouped
                    i = i + 1
                End If
         End If
    Next grouped

If Not ignore_empty Or Not IsEmpty(piece) Then
空白を無視するか、またはセルが空白で無かったら、その時は
条件にあえば、
ReDim Preserve newArray(i) というように、ReDimで配列の要素数を変更します。

次にElseのケースでは、groupedが空白じゃなかったら、要素を増やして、
newArray(i) = grouped　
と入れるわけです。コードの全体は次のようになります。

 Function ketugou(delimiter As String, ignore_empty As Boolean, _
   ParamArray joinText() As Variant)
    Dim grouped As Variant, piece As Variant, i As Long
    Dim newArray() As Variant
    i = 0
    For Each grouped In joinText
        If TypeName(grouped) = "Range" Then
            For Each piece In grouped
                If Not ignore_empty Or Not IsEmpty(piece) Then
                    ReDim Preserve newArray(i)
                    newArray(i) = piece
                    i = i + 1
                End If
            Next piece
         Else
                If Not ignore_empty Or Not IsEmpty(grouped) Then
                    ReDim Preserve newArray(i)
                    newArray(i) = grouped
                    i = i + 1
                End If
         End If
    Next grouped
   
    ketugou = Join(newArray, delimiter)
End Function