質問をすることでしか得られない、回答やアドバイスがある。

15分調べてもわからないことは、質問しよう!

ただいまの
回答率

90.75%

  • VBA

    1641questions

    VBAはオブジェクト指向プログラミング言語のひとつで、マクロを作成によりExcelなどのOffice業務を自動化することができます。

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

解決済

回答 4

投稿

  • 評価
  • クリップ 0
  • VIEW 353

Beginner_ABC

score 4

お世話になっています

今回はエクセル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
  • 気になる質問をクリップする

    クリップした質問は、後からいつでもマイページで確認できます。

    またクリップした質問に回答があった際、通知やメールを受け取ることができます。

    クリップを取り消します

  • 良い質問の評価を上げる

    以下のような質問は評価を上げましょう

    • 質問内容が明確
    • 自分も答えを知りたい
    • 質問者以外のユーザにも役立つ

    評価が高い質問は、TOPページの「注目」タブのフィードに表示されやすくなります。

    質問の評価を上げたことを取り消します

  • 評価を下げられる数の上限に達しました

    評価を下げることができません

    • 1日5回まで評価を下げられます
    • 1日に1ユーザに対して2回まで評価を下げられます

    質問の評価を下げる

    teratailでは下記のような質問を「具体的に困っていることがない質問」、「サイトポリシーに違反する質問」と定義し、推奨していません。

    • プログラミングに関係のない質問
    • やってほしいことだけを記載した丸投げの質問
    • 問題・課題が含まれていない質問
    • 意図的に内容が抹消された質問
    • 広告と受け取られるような投稿

    評価が下がると、TOPページの「アクティブ」「注目」タブのフィードに表示されにくくなります。

    質問の評価を下げたことを取り消します

    この機能は開放されていません

    評価を下げる条件を満たしてません

    評価を下げる理由を選択してください

    詳細な説明はこちら

    上記に当てはまらず、質問内容が明確になっていない質問には「情報の追加・修正依頼」機能からコメントをしてください。

    質問の評価を下げる機能の利用条件

    この機能を利用するためには、以下の事項を行う必要があります。

質問への追記・修正、ベストアンサー選択の依頼

  • mts10806

    2018/06/09 16:19

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

    キャンセル

  • mts10806

    2018/06/09 16:27 編集

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

    キャンセル

回答 4

+5

既に試されている方が居られました。
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 の高速化

投稿

編集

  • 回答の評価を上げる

    以下のような回答は評価を上げましょう

    • 正しい回答
    • わかりやすい回答
    • ためになる回答

    評価が高い回答ほどページの上位に表示されます。

  • 回答の評価を下げる

    下記のような回答は推奨されていません。

    • 間違っている回答
    • 質問の回答になっていない投稿
    • スパムや攻撃的な表現を用いた投稿

    評価を下げる際はその理由を明確に伝え、適切な回答に修正してもらいましょう。

  • 2018/06/09 23:54

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

    キャンセル

  • 2018/06/10 00:04

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

    キャンセル

  • 2018/06/10 11:27 編集

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

    キャンセル

checkベストアンサー

+1

文字列の検索、という事でしたら、経験上 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

投稿

  • 回答の評価を上げる

    以下のような回答は評価を上げましょう

    • 正しい回答
    • わかりやすい回答
    • ためになる回答

    評価が高い回答ほどページの上位に表示されます。

  • 回答の評価を下げる

    下記のような回答は推奨されていません。

    • 間違っている回答
    • 質問の回答になっていない投稿
    • スパムや攻撃的な表現を用いた投稿

    評価を下げる際はその理由を明確に伝え、適切な回答に修正してもらいましょう。

+1

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が最も速いという結果になりました。

投稿

編集

  • 回答の評価を上げる

    以下のような回答は評価を上げましょう

    • 正しい回答
    • わかりやすい回答
    • ためになる回答

    評価が高い回答ほどページの上位に表示されます。

  • 回答の評価を下げる

    下記のような回答は推奨されていません。

    • 間違っている回答
    • 質問の回答になっていない投稿
    • スパムや攻撃的な表現を用いた投稿

    評価を下げる際はその理由を明確に伝え、適切な回答に修正してもらいましょう。

  • 2018/06/09 21:05

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

    キャンセル

  • 2018/06/09 23:45

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

    キャンセル

0

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

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

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

投稿

  • 回答の評価を上げる

    以下のような回答は評価を上げましょう

    • 正しい回答
    • わかりやすい回答
    • ためになる回答

    評価が高い回答ほどページの上位に表示されます。

  • 回答の評価を下げる

    下記のような回答は推奨されていません。

    • 間違っている回答
    • 質問の回答になっていない投稿
    • スパムや攻撃的な表現を用いた投稿

    評価を下げる際はその理由を明確に伝え、適切な回答に修正してもらいましょう。

  • 2018/06/09 17:24

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

    キャンセル

  • 2018/06/09 19:57

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

    キャンセル

15分調べてもわからないことは、teratailで質問しよう!

  • ただいまの回答率 90.75%
  • 質問をまとめることで、思考を整理して素早く解決
  • テンプレート機能で、簡単に質問をまとめられる

関連した質問

  • 受付中

    VBA cpu使用率取得

    プログラミング初心者のものです。管理者の制約で、学校でタスクマネージャを開くことができません。そこでエクセルvbaでタスクマネージャを再現しようと思い、日々精進しております。学校に

  • 解決済

    VBA高速化について

    20個のエクセルファイルを読み込み、特定のシートにあるテーブルから特定の値を探し出し、その右横にあるセルの値を取り出します。 集計用のエクセルのテーブルでも、同じ特定の値をテーブル

  • 解決済

    powerpointでタイトル(文字列+連続変数)をVBAで一括変換

    パワーポイントで、タイトルの位置に テキスト+数字 の形でFor文を使って作りたいです。 例)こんにちは1(スライド1枚目) こんにちは2(スライド2枚目) ...といった形です。

  • 解決済

    VBA ユーザーフォーム内でコマンドボタン等を移動させる方法

    PowerPointのVBAでユーザーフォームを作成しています。 そこで以下のようなプログラムを作成しています。 ①フォーム内のコマンドボタンをクリックすると新たなコマンドボタンを

  • 解決済

    textファイルを文字列でエクセルに貼り付けたい

    エクセルのマクロで下記のようなコードを書いて動かしているのですが、文字列でシート1に貼り付けされません。 読み込むtextファイルは、タブ形式で区切られており、その区切りごとに各列

  • 解決済

    コンボボックスのリストの動的配列

     前提・実現したいこと ユーザーフォームのコンボボックスのリスト用の一覧があります。 A列が市町村、B列がそのグループです。 シート名は「一覧」です。 グループは、市町村がそのまま

  • 解決済

    VBAの配列についての質問

    エクセル2007を使用しています。 VBAを使って以下の事がやりたいです。 「シート1」に以下の表があります。 A列に商品、B列にロットNoとあります。 CD列、EF列、

  • 受付中

    エクセルVBAでチェックボックスのチェック状態を取得したい

     前提・実現したいこと エクセルのVBAでチェックボックスにチェックが入っているかを確認するマクロを作りたいです。  発生している問題・エラーメッセージ 動くには動くのですが、

同じタグがついた質問を見る

  • VBA

    1641questions

    VBAはオブジェクト指向プログラミング言語のひとつで、マクロを作成によりExcelなどのOffice業務を自動化することができます。