はじめに
Excel のワークシート関数には正規表現が使えません。ですが、ユーザー定義関数を追加することで使うことができます。
Windows 版には、すでにいくつかの作例があります。
しかし上記の作例は Windows 固有のライブラリを使用するため Mac では利用できません。本記事では Mac 版を実装してみます。
開発には Excel:mac 2011 を使用しましたが、2016 以降では別途対応が必要かもしれません。以下を参考にしてください。
実装方法
VBA からシェルコマンドで PHP を呼び出して、PHP の正規表現処理を利用しています。
Mac は バックスラッシュ（Chr(&h5C)）と円記号（Chr(%h80)）が明確に分かれているのでハマりました。
シェルの呼び出し（結果の受け取り）は以下のコードを参考にさせていただきました。
excel - VBA Shell function in Office 2011 for Mac - Stack Overflow
インストールの仕方
まず「ユーザー定義関数とはなにか」というところから知りたい場合は > こちらをご覧ください。
後述のソースコードを、どのように Excel に設定してユーザー定義関数として使えるようにするかについては > こちらをご覧ください。
使い方
関数 REGEXP_MATCH()
関数の記述例 =REGEXP_MATCH(A1, "([A-Z])\w+")
関数の定義 =REGEXP_MATCH(引数1, 引数2, [[引数3], 引数4])
引数
引数１：subject
正規表現検索を行う対象の文字列を入力します。参照先のセルアドレスでも構いません。
引数２：pattern
正規表現パターンの文字列を指定します。
引数３：subMatchIndex
オプションです。指定しない場合、最初にマッチした文字列を返します。
2 を指定すれば二番目にマッチした文字列を返します。
引数４：isIgnoreCase
オプションです。既定は FALSE です。
TRUE を指定すると、大文字小文字の違いを無視した検索を行います。
返値
マッチした文字列を返します。
ソースコード
開発環境は以下の通りです。
Option Explicit
Const REGEXP_MATCH_DEBUG_MODE As Boolean = False
Private Declare Function popen Lib "libc.dylib" (ByVal command As String, ByVal mode As String) As Long
Private Declare Function pclose Lib "libc.dylib" (ByVal file As Long) As Long
Private Declare Function fread Lib "libc.dylib" (ByVal outStr As String, ByVal size As Long, ByVal items As Long, ByVal stream As Long) As Long
Private Declare Function feof Lib "libc.dylib" (ByVal file As Long) As Long
Private Declare Function regex_match Lib "libc++.dylib" (ByVal basic_string As String, ByVal basic_regex As String, ByVal match_flag_type As Integer)
Public Sub RegisterREGEXP_MATCH()
' Application.MacroOptions Macro:="REGEXP_MATCH", _
' Description:="正規表現検索を行い、マッチした文字列を返します。", _
' Category:="文字列操作", _
' HelpFile:=""
Application.Volatile True
Application.MacroOptions _
Macro:="REGEXP_MATCH", _
Description:="正規表現検索を行い、マッチした文字列を返します。", _
Category:="文字列操作"
End Sub
Public Function REGEXP_MATCH(subject As String, _
pattern As String, _
Optional subMatchIndex As Integer = -1, _
Optional isIgnoreCase As Boolean = False) As Variant
Dim optionChars As String
Dim cmd As String
Dim result As String
Dim exitCode As Long
If isIgnoreCase Then
optionChars = "i"
End If
If subMatchIndex < 0 Then subMatchIndex = 1
cmd = CreateCommandString(subject, pattern, subMatchIndex - 1, optionChars)
result = execShell(cmd, exitCode) 'ByRef exitCode
If exitCode <> 0 Then
REGEXP_MATCH = CVErr(xlErrNA)
Debug.Print result
Else
REGEXP_MATCH = result
End If
End Function
Private Function CreateCommandString(ByVal subject As String, ByVal pattern As String, ByVal idx As String, ByVal optionChars As String) As String
Dim BS As String
Dim QQ As String
Dim SQ As String
Dim s As String
Dim p As String
s = SubjectEscape(subject)
p = RegexPatternEscape(pattern)
BS = Chr(&H5C)
QQ = """"
SQ = "'"
Dim cmd As String
cmd = "php -r " & SQ & _
"$m=" & QQ & QQ & ";" & _
"try{" & _
"if(false!==preg_match_all(" & QQ & "/" & p & "/" & optionChars & QQ & ", " & _
QQ & s & QQ & ",$m,0,0)){" & _
"echo $m[0][" & idx & "];" & _
"}" & _
"}catch(Exception $e){" & _
"var_dump($e);" & _
"exit(1);" & _
"}" & _
"exit(0);" & _
SQ
If REGEXP_MATCH_DEBUG_MODE Then
cmd = cmd & " 2>&1"
Debug.Print cmd
End If
CreateCommandString = cmd
End Function
Private Function SubjectEscape(ByVal src As String) As String
Dim BS As String
Dim QQ As String
Dim SQ As String
BS = Chr(&H5C)
QQ = """"
SQ = "'"
Dim ret As String
ret = src
ret = Replace(ret, Chr(&H80), Chr(&H5C))
ret = Replace(ret, QQ, BS & QQ)
ret = Replace(ret, SQ, BS & SQ)
ret = Replace(ret, vbLf, BS & "n")
SubjectEscape = ret
End Function
Private Function RegexPatternEscape(ByVal src As String) As String
Dim BS As String
Dim QQ As String
Dim SQ As String
BS = Chr(&H5C)
QQ = """"
SQ = "'"
Dim ret As String
ret = src
ret = Replace(ret, Chr(&H80), Chr(&H5C))
ret = Replace(ret, QQ, BS & QQ)
ret = Replace(ret, SQ, BS & SQ)
ret = Replace(ret, vbLf, BS & "n")
RegexPatternEscape = ret
End Function
Private Function execShell(command As String, Optional ByRef exitCode As Long) As String
Dim file As Long
file = popen(command, "r")
If file = 0 Then
Exit Function
End If
While feof(file) = 0
Dim chunk As String
Dim read As Long
chunk = Space(50)
read = fread(chunk, 1, Len(chunk) - 1, file)
If read > 0 Then
chunk = Left$(chunk, read)
execShell = execShell & chunk
End If
Wend
exitCode = pclose(file)
End Function
注意
- マッチするものがなければ、何も返しません。
- 深刻なエラーが起きた場合のみ、セルにはエラーが表示されます。
- PHP の preg_match_all() を使用しています。正規表現のテストだけをしたい場合は、下記のようなサイトをご利用ください。
正規表現チェッカー PHP: preg_match() / JavaScript: match()
- Big Sur でもし動かない場合は Microsoft が対応してくれるのを待つしかないかもしれません。
Missing librairies in /usr/lib on … | Apple Developer Forums
おわりに
頑張らなくてもそのうち Microsoft が標準で対応しような気がしますが...。
