Excel のワークシート関数で正規表現を使う【Mac版】

はじめに

　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 を指定すると、大文字小文字の違いを無視した検索を行います。

返値

　マッチした文字列を返します。

ソースコード

　開発環境は以下の通りです。

Microsoft Excel for Mac 2011 Version 14.1.0 (110310)
Mac OS X 10.8.5
PHP 5.3.29 Zend Engine v2.3.0

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