【VBA】処理の高速化 について

Question

お世話になっています

今回はエクセルVBAに関する質問です
文字列処理がメインのコードを作成しています
以下のコードのように　
判定対象文字列に文字列(例として 果物の名前)が含まれているかを
チェックしたいのですが
実際には 判定すべき文字列は　くだものの他にも いくつかあります

現在はループで回していますが
少しでも 処理速度を速くしたいのですが
何かよい方法は　ないでしょうか？

Sub fruits_check()


    Dim くだもの判定(5) As String
    
    くだもの判定(1) = "桃"
    くだもの判定(2) = "パイナップル"
    くだもの判定(3) = "みかん"
    くだもの判定(4) = "バナナ"
    くだもの判定(5) = "檸檬"
    
    
    
    exist_flag = False
    
    For i = 1 To 5
       
        If (InStr(判定対象文字列, くだもの判定(i)) > 0) Then
            exist_flag = True
            Exit For
        End If
        
    Next i
    
    
    If exist_flag = True Then
    
       MsgBox ("ありました")
    
    End If
    

End Sub

Comments

[m.ts10806]

現時点でも遅いと感じるようなことはあるのでしょうか？

[m.ts10806]

部分一致を前提として回答しました。（完全一致だと別の関数があったはず）

Answer 1

既に試されている方が居られました。
VBAで特定の文字が含まれているのかの確認方法の処理速度比較

結果からはlike演算子。
但し、以下注釈がありました。

ただしWin7 64bit Excel 2010 32bitの環境ではInStrの方が早かった記憶があるので環境によっては変化するかも

#測定条件

• 実行環境：Windows 10 Pro 64bit / Microsoft Excel 2016 64bit
• 検索文字列：2文字目が"b"、残りが"a"からなる10万文字の文字列 "abaaa....a"
• ループ回数：1万回
• 時間測定方法：VBAのTimer関数を用いた簡易計測

#測定結果

方法	時間（秒）
InStr関数	0.141
InStrRev関数	4.352
Like演算子	0.000
正規表現（事前バインド）	0.008
正規表現（実行時バインド）	0.141
Replace関数	1.563

追記

高速化についての小技的なものが紹介されています。
Excel VBA の高速化

Comments

[hatena19]

このリンク先のテスト結果は私のテスト結果とかなり異なった結果ですね。
10万文字中の2文字目に一致する文字があるというかなり特殊なサンプルデータだからでしょうかね。

[sazi]

そうですね。
個人的には内部処理的にinstr()が一番単純で高速だと思うのですが、likeが高速な場合は、データや検索条件によって、最速なパターンがあるのかなって感じです。

[sazi]

他の関数にしても、内部的なソートのロジックはあるでしょうから、取得したデータやOSが扱えるbit数によって性能が変わる可能性は大きい気がします。

Answer 2

文字列の検索、という事でしたら、経験上 InStr 一択でしょう。

もちろん、正規表現を使いたいのであれば、Like や RegExp を使えばいいですが、
早そうだから、などという安易な気持ちで使ってしまうと、
[*]等の正規表現で使わていれる文字が検索値に含まれた場合にバグになる可能性がある、
という事も留意しなければなりません。

そこまで考えず、とりあえず文字を探すだけなら 、
InStr が高速かつ意図しない結果を返さないので安心して使えるかと。

また、環境によって左右されるのかもしれませんが、
当方で試した環境（Win7 32bit, Office2010 32bit)では、
InStr 、Like で差は得られなかったです。

※ランダムな10000文字(アルファベット)に対して、
ランダムな10文字を、10万回検索する、という方法を数十回試しました。
※Like実行時に、"" & [変数] & "" とすると、文字列の演算処理の時間まで計測されるので、
元から変数に[*]を結合させて計測しています。

回答ではないですが、何かを判定する関数のテンプレを載せておきますので、参考まで。
※ポイントは、対象外の場合は Exit Function 、最後まで辿り着いたら TRUE を返す点です。
こう記述する事で、何度も戻り値を書く必要がなく、
Ifのネストが激減するのでコードが簡潔になり見やすくなります。

Public Function IsNumber(Value As String) As Boolean
    
    If IsNumeric(Value) = False Then Exit Function
    
    Dim i   As Long
    For i = 1 To Len(Value)
        If InStr(1, "0123456789", Mid$(Value, i, 1)) = 0 Then Exit Function
    Next
    
    IsNumber = True
    
End Function

Answer 3

VBAでInStrを使わない方法というと Like演算子か RegExpオブジェクト（正規表現）になりますね。

Like演算子は前にテストした時は、InStrより遅かったです。
RegExpはやったことがないので、テストした結果を教えてほしいです。

Public Sub fruits_check()  
   Dim 判定対象文字列 As String  
   Dim reg As Object  
     
   Set reg = CreateObject("VBScript.RegExp")  
     
   With reg  
       .Pattern = "桃|パイナップル|みかん|バナナ|檸檬"  
       .IgnoreCase = True  
       .Global = False  
   End With  
     
   判定対象文字列 = "あああああああ檸檬ああああああ"  
 
   If reg.Test(判定対象文字列) Then MsgBox "ありました"  
 
End Sub  

テストしてみました

時間の計測は、VBAでミリ秒以下の高精度で処理時間計測 の関数を使いました。マイクロ秒の精度で計測てきますので、ループさせる必要なく計測できます。
判定対象文字列は、Wikipedia:引用のガイドラインから5500字エディターにコピーして真ん中ぐらいに「檸檬」を挿入しました。それをA1セルにコピペしました。
実行環境：Windows 10 Home 64bit / Microsoft Excel 2016 32bit

テストコード

Public Sub fruits_check()

    Const 検索パターン = "桃|パイナップル|みかん|バナナ|檸檬"

    Dim くだもの判定(5) As String
    くだもの判定(1) = "桃"
    くだもの判定(2) = "パイナップル"
    くだもの判定(3) = "みかん"
    くだもの判定(4) = "バナナ"
    くだもの判定(5) = "檸檬"

    Dim 判定対象文字列 As String
    判定対象文字列 = Range("A1").Value

    Dim lateReg As Object
    Set lateReg = CreateObject("VBScript.RegExp")

    Dim earlyReg As RegExp
    Set earlyReg = New RegExp

    Dim result As Boolean, i As Long, j As Long

    For j = 1 To 2
        result = False
        SWStart
        With lateReg
            .Pattern = 検索パターン
            .IgnoreCase = True
            .Global = False
            result = .Test(判定対象文字列)
        End With
        SWStop
        SWShow "lateReg : " & result & " "

        result = False
        SWStart
        With earlyReg
            .Pattern = 検索パターン
            .IgnoreCase = True
            .Global = False
            result = .Test(判定対象文字列)
        End With
        SWStop
        SWShow "earlyReg: " & result & " "

        result = False
        SWStart
        For i = 1 To 5
            If (InStr(判定対象文字列, くだもの判定(i)) > 0) Then
                result = True
                Exit For
            End If
        Next i
        SWStop
        SWShow "InStr   : " & result & " "

        result = False
        SWStart
        For i = 1 To 5
            If 判定対象文字列 Like "*" & くだもの判定(i) & "*" Then
                result = True
                Exit For
            End If
        Next i
        SWStop
        SWShow "Like    : " & result & " "

        '一致なし用のサンプルデータ
        判定対象文字列 = Replace(判定対象文字列, "檸檬", "")
        Debug.Print ""
    Next j

End Sub

計測結果

lateReg : True  1.0089
earlyReg: True  0.6539
InStr   : True  0.0472
Like    : True  0.0526

lateReg : False  1.5676
earlyReg: False  1.2376
InStr   : False  0.0501
Like    : False  0.0558

単位はミリ秒です。
当方の環境、用意したサンプルでは、InStrが最も速いという結果になりました。

Comments

[sazi]

共にcreateobjectや配列の作成などは事前に行って比較のみの時間を、
API使ってマイクロ秒までで計測してみました。
instr()は0.005ミリ秒。
.RegExpは0.015ミリ秒。
誤差はあるとは思いますが、性能差は2～3倍程度でした。

[hatena19]

テストした結果を追記しました。当方の環境、用意したサンプルデータでは、instrとregexpでは10倍以上の差がでました。データによってかなり差がでるのかも。どちらにしてもInstrが速いようです。

Answer 4

「部分一致」とすると下記記事の中盤に出てくるfilter関数が使えそうです。

• 【VBA入門】配列の内容を検索する方法

Filter関数を使用して配列を検索する方法

ループをしない分、多少は速く、コードも短くなるとは思いますが、
もっと大量データで検証しないとその差は分からないかもしれません。

Comments

[sazi]

リンク先は配列内を検索で、質問は検索対象を検索パターン（配列）で検索ですよ。
完全一致なら逆でもいけそうですが、部分一致だと不能。

[m.ts10806]

ご指摘ありがとうございます。
ちょっと別の方法考えてみます。