VBA 正規表現今日のトリビア ¥s は半角の空白しかマッチしないので名簿の姓名の正規表現は次のようになる

正規表現(Regular Expression)

仮にこれから、ここで日本の郵便番号を表示するとすれば、これから郵便番号をすべてならべる必要がある。
https://www.post.japanpost.jp/zipcode/
赤平市の赤平は
079-1143
共和町は
079-1141
ということを延々と続ける必要がある。
これらをすべて並べるのではなく、記号ですべてを表すと簡単になる。
日本の郵便番号は数字3桁ハイフン数字4桁で成り立っている。
これに着目すると日本の郵便番号は
[0-9]{3}-[0-9]{4}
と表すことができる。正規表現とはこのように法則性がある文字列の確認、抽出、置換を行う場合に効果がある。

お約束

ネットではスラッシュにみえるが、日本の場合VBE(vbs)では¥で表示される。

公式の解説はVBScriptに

VBAで使われる正規表現の解説はVBSのメソッドやプロパティとして散らばっている。
RegExp オブジェクト

• Regular Expression オブジェクトのプロパティとメソッド
  Regular Expression オブジェクトは、簡単な正規表現をサポートします。※確かに.Net、C#とそのクラスなどに比べれば見劣りがする。というかVBAの正規表現はIE5.5のまま一切進化をしていない。

• メソッド

• Execute メソッド

• Replace メソッド
  　通常のReplaceとは違い、Submatchの文字を入れ替えることができる。

• Test メソッド

• 以下プロパティ

• Global プロパティ
  文字列全体をみつけたら終わる(True)のか、一度見つかったら終わるのか(false：既定)、という設定。これはTrueの場合、Multilineとの組み合わせに注意。

• IgnoreCase プロパティ
  Pattern検索ではなく、普通に正規表現のプロパティ（ここではマッチあるいは検索の条件）なのだが。
  Matches コレクション
  MatchesコレクションはExecuteを実行すると生成される。0から値が入る。 For imatch=0 to MC.Count-1とする。
  Match オブジェクト

• Length プロパティ

• Value プロパティ

• FirstIndex プロパティ

• SubMatches コレクション
  SubMatchesが発動するにはPatternでかっこ()に囲まれた部分が必要となる。$1$2で表示されるのがそれ。SubMatchはMatchオブジェクトの下位。値(Valueプロパティ)しか返さず、FirstIndexやLengthはない。このためLenを使用する必要がある。

Pattern プロパティ
Patternに特殊な文字の定義がある。またキャレットやドル記号が場面に応じて意味が変わる。

Wordの正規表現

VBAを使わなくても、ある程度Wordの検索・置換で正規表現に近い表現ができる。
Word ：ワイルドカード を使った検索と置換を極める

日本の名簿

人名欄の法則

人名を記載した名簿を考えると、概ね
姓▲名となっている。
ついつい全角の正規表現
[一-龠〃々〆〇]
を使ってしまいそうになるが、▲をみるとここは空白である。

スペースかスペースではないか

そこに着目すると
\S\s\S
というのが基本となる。
https://excel-ubara.com/excelvba4/EXCEL232.html
¥s 任意のスペース文字にマッチします。 （公式は「スペース、タブ、フォームフィードなどの任意の空白文字と一致します。」）
¥S 任意の非スペース文字にマッチします。 （公式は「空白文字のない部分と一致します」）

しかし半角と全角がある

日本の場合ダブルバイト文字（全角文字）の空白がある。
&H3000
これはスペース文字に入りそうである。
Microsoft VBSCript Regular Expression 5.5に参照設定する。

Sub test()
Dim Reg As RegExp: Set Reg = New RegExp
Dim MC As MatchCollection, M As Match, iMatch As Long, SBMc As SubMatches
Dim iFrist As Long
Dim MLength As Long
With Reg
.Global = True
.IgnoreCase = False ' Trueでも同じ
.MultiLine = True 
.Pattern = "\s"
Debug.Print .test(ChrW(12288))
End With
Set Reg = Nothing
End Sub

答えは

False
公式の解説からはそんなことはわからないが実は\sは全角の空白は受け付けず、マッチ（一致）しない。。

全角と半角の空白

つまり全角と半角のスペースが混在している、その可能性がある場合、
\s|　
縦棒をいれて全角空白
例外をいえばキリがないが、通常はこれでOK。
これをさらにグループ化する
(\s|　)
全角文字は分かりづらいので下記のサンプルではChrWを使った。

Sub test2()
Dim Reg As RegExp: Set Reg = New RegExp
Dim MC As MatchCollection, M As Match, iMatch As Long, SBMc As Subdocuments
With Reg
.Global = True
.IgnoreCase = False ' Trueでも同じ
.MultiLine = True
.Pattern = "\s"
Debug.Print .test(ChrW(12288)) ' False
.Pattern = "\s" & "|" & ChrW(12288)
Debug.Print .test(ChrW(12288)) ' True
End With
End Sub

これでマッチし、結果はTrueになる。

名簿の正規表現

よって、
名前欄が姓▲名となっているかを確認する正規表現は、
\S(\s|　)\S
となる。
サンプルによっては
\S+(\s|　)\S+
としている場合もある。
そして、姓、名抜き出したい、置換したい場合、
(\S+)(\s|　)(\S+)
とする。
これでSubMatcesが使える。$1から$9までで表記される。今回は$1が姓、$3が名となる。

Sub TestRegreplace()
Dim Reg As RegExp: Set Reg = New RegExp
Dim MC As MatchCollection, M As Match, iMatch As Long, SBMc As SubMatches
Dim iFrist As Long
Dim MLength As Long
With Reg
.Global = True
.IgnoreCase = False
.MultiLine = True
.Pattern = "(\S+)(\s|　)(\S+)"
Debug.Print .Test("A B")
Set MC = .Execute("A B")
If MC.Count > 0 Then
Debug.Print MC.Item(0) ' 0から入っている
Set M = MC(0)
Set SBMc = M.SubMatches
If M.SubMatches.Count > 0 Then
Debug.Print SBMc.Item(0) ' 0から入っている
Debug.Print Len(SBMc.Item(0)) ' SubMatchesにLengthはない
Debug.Print .Replace("A B", "$3$2$1") ' "B A"
End If
End If
End With
Set Reg = Nothing
End Sub

参照設定なし版

VBSも考えてすべてASをコメントアウト。
VBSでもSubとEnd Subを外し、Debug.PrintをWscript.Echoにすると動く。

Sub TestRegreplace()
Dim Reg : Set Reg = CreateObject("VBScript.RegExp")
Dim MC 'As MatchCollection, 
Dim M 'As Match, 
Dim iMatch 'As Long,
Dim SBMc 'As SubMatches
Dim iFrist 'As Long
Dim MLength 'As Long

With Reg
.Global = True
.IgnoreCase = False
.MultiLine = True
.Pattern = "(\S+)(\s|　)(\S+)"
Debug.Print .test("A B")
Set MC = .Execute("A B")
If MC.Count > 0 Then
Debug.Print MC.Item(0) ' 0から入っている
Set M = MC(0)
Set SBMc = M.SubMatches
If M.SubMatches.Count > 0 Then
Debug.Print SBMc.Item(0) ' 0から入っている
Debug.Print Len(SBMc.Item(0))
Debug.Print .Replace("A B", "$3$2$1") ' "B A"
End If
End If
End With
End Sub

使い方

特に曖昧な入力ができてしまうExcelで名簿を作っているときが有効。
Wordで名簿を作ったとしても面倒だが、できないことはない。（まずテーブル（表）の指定から入る。いわゆるR1C1形式しかない）

• 名簿の姓 名の間が半角スペースになっているか、全角スペースになっているか。（入力チェック）
• そのように記載されている場合、姓、名を抜き出す。（入力されたデータの活用）

このようなことはLen,Mid等を組み合わせる必要があるが、正規表現により比較的簡単にできる。
ただし、正規表現は正しく規則性を見つけないと、誤ったものにマッチ、ただしいものが抜けることもよく発生する。
今回の例では、姓名の間にスペースが入っていない場合である。あるいは姓自体の間に見栄え等を重視してスペースを入れている場合である。
この点は単純にコードがかけず、実態のデータがどのようになっているか観察しないと難しいので、時間がかかる。
なので正規表現はデータが多いほど100％とはいかないことが多い。
ただし、よくサンプルとして挙げられる郵便番号は日本国内限定ではこの法則が必ず当てはまるため強力。
また、これを応用し、郵便番号は7桁の数字の"文字列"として入力し、表示形式、あるいは正規表現で切り出だすことができる。こうすることでハイフン一つだけデータが少なくなる。

.Pattern = "([0-9]{3})([0-9]{4})"
Debug.Print .Replace("0123456", "$1-$2")

あるいはハイフンかハイフンマイナスかわからない123‐4567、123ー8905
これは非数字が一つあると考える。

.Pattern = "([0-9]{3})(\D)([0-9]{4})"
Debug.Print .Replace("123" & ChrW("0045") & "8905", "$1" & ChrW("0045") & "$3")
Debug.Print .Replace("123" & ChrW("8208") & "8905", "$1" & ChrW("0045") & "$3")
Debug.Print .Replace(StrConv("123" & ChrW("8208") & "8905", vbNarrow), "$1" & ChrW("0045") & "$3")
Debug.Print .Replace(StrConv("123" & ChrW("2796 ") & "8905", vbNarrow), "$1" & ChrW("0045") & "$3")

半角、全角数字がある場合はStrConvを使う。
漢数字がある場合はPatternを変える。

マイクロソフトの{n}の解説は怪しい。

{x} 正規表現のちょうど x個の直前の文字にマッチします。
{n} n には、0 以上の整数を指定します。直前の文字と正確に n 回一致します。たとえば、"o{2}" は、"Bob" の "o" とは一致しませんが、"foooood" の最初の 2 つの o とは一致します。

このバグ？仕様？により郵便番号も8桁の数字になるとおかしくなる。

.Pattern = "([0-9]{3})([0-9]{4}))"
Debug.Print .Replace("01234567", "$1-$2") ' 012-34567

このサンプルでは、{4}は4つ以上にマッチしてしまう。4個の直前の文字にマッチしていない。
余計な数字がついていそうなら、もう一つ0個以上の数字をマッチさせる。
数字かわからないときはPattern = "([0-9]{3})([0-9]{4})(.+)"
ただし、これは正常な123-4567という文字列を除外してしまう。

RegEx.Replaceの注意点

もう一つ要注意なのはマッチに失敗すると、RegEx.Replaceはそのまま文字を返す点である。

.Pattern = "([0-9]{3})([0-9]{4}))"
Debug.Print .Replace("01234567", "$1-$2") ' 012-34567
.Pattern = "([0-9]{3})([0-9]{4})([0-9]{0,})"
Debug.Print .Replace("01234567", "$1-$2") ' 012-3456
.Pattern = "([0-9]{3})([0-9]{4})(.+)"
Debug.Print .Replace("01234567a", "$1-$2") ' 012-3456

とくに郵便番号の正規表現で4桁で終わるとすると
^[\d]{3}[\d]{4}$が厳密。しかし

.Pattern = "^([0-9]{3})([\d]{4})$"
Debug.Print .test("01234567") ' False
Debug.Print .Replace("01234567", "$1-$2") ' 01234567

これはハねるだけなので、データを活かすなら、

.Pattern = "^([0-9]{3})([\d]{4})"
Debug.Print Left(.Replace("01234567", "$1"), 3) & "-" & Left(.Replace("01234567", "$2"), 4)

やはり{3}もマッチの値がおかしいので、Leftで切っている。

このように、正規表現だけが正しくてもデータがすべて規則通りに入力されているとは限らず、調整していくことはよくある。
Replaceを使う場合はIF RegEx.TestでTrueに必ず限定する、ということになる。

また、住所だと、都道府県から始まっていない住所を見つけたいとき
東京都
北海道
大阪府
京都府
県は
和歌山県と鹿児島県が3文字であとは2。
ということを考える。

.Pattern = "(東京都|大阪府|京都府|[\S]{2,3}県)"
Debug.Print .test("千代田区東京都") '単純にやると Trueになってしまうが間違い
.Pattern = "^(東京都|大阪府|京都府|[\S]{2,3}県)"
Debug.Print .test("千代田区東京都") ' 東京都から始まっていないのでFalse
Debug.Print .test("東京都千代田区") ' 東京都から始まっていないのでFalse

このように「ある単語はじまっている場合」、とするときはキャレットをつける。そうしないと語中の単語が同じでもマッチする。
正規表現は結構考えることが多く、コードというよりはパズルのような印象がある。
ちょうど年末年賀状など住所を参照する機会があるので、紹介した。大した目あたらしい点はないが(\S)(\s|　)(\S)という発想はないと思われる。
もちろん都道府県などはもっとうまい書き方があるかもしれない。
単純であるほど実行時間は短くなる。おそらく否定は肯定より時間がかかる。SQL（クエリ）でも否定はすべて読み込むため時間がかかるからだ。
10000も20000も住所をExcelで管理するのは無理があるが、仮にそうした状況で郵便番号や氏名をチェックするには、
短時間で動作するコード、正規表現が必要となる。
例えば今回のコードは1回マッチすればいいので、GlobalはFalseでもよい。また、マッチするかしないかなら、Mc.Countではなく、Testのほうが早くなる。
ただ、このような制約はマッチの漏れ、誤マッチを生じさせる可能性も増加する。
例えば、郵便番号ではこのようにしても999-9999のような規則には整合するが、実際は存在しない文字列は排除できない。
また{4}でもなぜか5桁にマッチするなど、公式に書いてあることと実際は違う。こういうことはよくある。
今回のテーマでも同様に代表取締役 何何何何という文字列も排除できない。規則にはあっているからだ。

現実的には

正規表現だけ詰めるのではなく、複数の手段で誤りを排除することが現実的なコードだろう。
また、日付かどうかであればIsDateを使うなど、正規表現以外のものが用意されていればそちらを使う。
なので、サンプルはGlobalなどは広めにして、あえて誤マッチが入る可能性があるようになっている。
そして、テストしながら例外を絞り込んでいくためにどうするかを考えていく。
いきなり全部に適用するのではなく、調べたいデータから、10、100といくつか抜き出して結果を比較し、次第に大きくするという形でコードを作ったほうが良い。また、このテスト段階では誤った入力は訂正しない。訂正すべき値が他にもあるしどう誤っているのかが重要だからだ。