無色無味無臭の毒を除去：VBAとワークシートのTrim関数の違い

先日から、「賞状印刷用データ作成マクロ」に挑戦している。
前回は、一位から四位までの選手の氏名を取得する関数を作成してみた。
infoment.hatenablog.com
今日も、前回の続きから。
ja.wikipedia.org
※毒：Poisonとポアソン：Poissonを絡めた駄洒落。今回の内容にポアソン分布等は登場しませんので、悪しからずｍ（＿＿）ｍ。

前回登場したトーナメント表について。

こちらに使用される選手の名前は、各支部から提出される名簿を元に作成される。
初見で読めない名前も散見されるため、名簿を見ながらの転記は非常に危険だ。
そこで、トーナメント表作成の際は、提出されたデータからそのままコピペする。
しかし、全ての支部の先生方が、同じ方法でデータを作成くださるとは限らない。
特に、スペースの使い方が複数あったりする。

例）

山田花子　← 姓と名の間に、半角スペースが一つある。
山田花子　← 姓と名の間に、半角スペースが二つある。
山田　花子　← 姓と名の間に、全角スペースが一つある。
山田花子　← 名前の後に、半角スペースが一つある。

上記1. のパターンを想定してマクロを作成して、それ以外のものが偶にあって
上手くいかないケースを、誰もが一度は体験しているのではないだろうか。

特に4. の、文字列最後の半角スペースの存在は、まず見た目で気づけない。
業務中、これに何度か苦しめられたことがある。その気づけなさ具合から、
私は個人的にこれを「無色無味無臭の毒」と呼んでいる。

文字列前後の空白を取り除くには、Trim関数が便利だ。ただし、VBAとワーク
シート関数では挙動が少し違うので、注意が必要になる。

VBAの場合：文字列前後のスペース文字を除去（半・全角を問わず）。
ワークシートの場合：文字列前後のスペース文字を除去。文字列内に連続する
スペース文字がある場合、連続するグループ毎に一文字残してこれも除去。
半・全角が混在する場合、半角スペースが優先的に残される（らしい）。

Sub テスト()
    Dim Src As String
        Src = "   　山田     　    花子    "
        MsgBox "元の文字：" & Src & vbNewLine & _
               "ＶＢＡ：" & Trim(Src) & vbNewLine & _
               "ワークシート：" & WorksheetFunction.Trim(Src)
End Sub

両者に一長一短ありそうで、状況に合わせて使い分けることが望ましい。
今回は上記1. のパターンにしたいので、以下の作戦で処理する。

姓名内のスペース文字を、全て半角スペースに置換する。
ワークシートのTrim関数で、前後の半角スペースがあれば除去。

↓ 昨日の「生徒の名前」関数を改修。

Function 生徒の名前() As Variant
    Dim r As Range
    ' 名前格納用配列。
    Dim arr As Variant
        arr = Array()
        
    ' 選んだ順に配列に格納。
    ' 団体戦などは３チームしか出ない場合もあるため、配列のサイズは可変とする。
        For Each r In Selection
            ' セルが結合されている場合に備えて、空白判定を行う。
            If r <> vbNullString Then
                ReDim Preserve arr(UBound(arr) + 1)
                ' 余分なスペース文字を除去したのち、配列に格納する。
                arr(UBound(arr)) = WorksheetFunction. _
                                    Trim(Replace(r.Value, "　", " "))
            End If
        Next
        
        生徒の名前 = arr
End Function

傾向として全文字数が３の場合、スペース文字で調整されることが多いようだ。

次回は、賞状用データ（Word）を作成します。

参考まで。