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

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

マクロ講座 動画編 100回

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

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

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

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または、サブルーチンに指定する引数のリストの必ず最後に配置します。
• ParamArray は単独で使用し、他の引数と一緒には使用できません。
• ParamArrayを使用することで、引数の数が固定ではなくても、柔軟に対応できるようになります。

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

1. 以下の図のように、セル範囲の文字列を結合したいケースは、わりとよくあります。
   セル範囲が連続している場合は、引数にするセル範囲は１つです。

   

2. 通常単独で実行されるサブプロシージャーの場合は引数を付けませんけれども、 他のプロシージャーから呼び出す形で使うマクロは、Callステートメントで呼び出すことができます。
   そういったプロシージャーには引数を指定することができます。 ファンクションプロシージャーというのもあります。 それはもう関数と同じことですね。関数はサブプロシージャーからも呼ぶことできますし シート上でユーザ定義関数として使うこともできます。

   

3. 結合したいセル範囲がいくつもあって、引数が複数になるケースでは、どのようにコードを書けばいいのかというと、 Paramarrayというのを使います。Paramarrayを使うと、プロシージャーの呼び出しの時にその位置の後ろから指定される引数を配列として受け取ります。

   

4. そのParamarrayと付けた引数に関しては その都度、数が変わっても大丈夫なんです。
   必ずこのParamarrayというのは、引数複数ある場合は一番最後に指定するという約束があります。 データ型もバリアントということで、これも決まりです。

   

5. 引数は図のように３つ取ることに決めました。 空白がある場合、空白の扱いをどうするかということで、それを無視するかどうかが、
   ignore empty、空白無視をtrueにすれば無視するし、falseにすれば無視しない。
   JoinTextは、複数ある可能性のあるセル範囲ですからParamarrayをつけて最後に記述します。

   

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

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

6. Functionの名前と引数の部分ができました。
   ではコードの部分を書いていきましょう。
   結合対象は、 Paramarray　で joinTextという配列に 受け取ったものですね。
   それはrangeだったりcellだったり rangeだったりcellだったりっていうように、数が不特定です。

   

7. 3行目を結合していくケースを考えることにしましょう。
   離れているかたまりは、セルが複数であろうとも、１つであろうともGroupとします。
   またgroupedの中の１つ１つはpieceとよびましょう。

   

8. さらにカウント変数のiと動的配列として、Dim newArray() As Variantを宣言します。

   

9. for eachで受け取った範囲のgroupedを回します。
   For Each piece In grouped
   Next piece

   

10. でもgroupedはjoinText で複数受け取っていますから、2重For文にしてやる必要があります。
    For Each grouped In joinText
    　　For Each piece In grouped
    　　Next piece
    Nextgrouped

    

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

11. 外側のFor文では、受け取ったjoin textの一つの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

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

    

13. 次に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

    
14. この文字列結合のユーザー定義関数は、離れたセル範囲を複数指定できる便利な関数です。ぜひ使ってみてください。