teratail
回答率
85.30%
teratail header banner
teratail header banner
質問するログイン新規登録
トップVBAに関する質問【VBA】処理の高速化 について

編集履歴

回答編集履歴

5

コード修正

2018/06/09 15:10

投稿

hatena19
hatena19

スコア34367

answer CHANGED
@@ -10,7 +10,7 @@
10
10
  Set reg = CreateObject("VBScript.RegExp")
11
11
 
12
12
  With reg
13
- .Pattern = "もも|パイナップル|みかん|バナナ|檸檬"
13
+ .Pattern = "桃|パイナップル|みかん|バナナ|檸檬"
14
14
  .IgnoreCase = True
15
15
  .Global = False
16
16
  End With
@@ -32,7 +32,7 @@
32
32
  ```vba
33
33
  Public Sub fruits_check()
34
34
 
35
- Const 検索パターン = "もも|パイナップル|みかん|バナナ|檸檬"
35
+ Const 検索パターン = "桃|パイナップル|みかん|バナナ|檸檬"
36
36
 
37
37
  Dim くだもの判定(5) As String
38
38
  くだもの判定(1) = "桃"

4

書式の改善

2018/06/09 15:10

投稿

hatena19
hatena19

スコア34367

answer CHANGED
@@ -23,7 +23,7 @@
23
23
  ```
24
24
 
25
25
  テストしてみました
26
- ---
26
+ ---
27
27
  時間の計測は、[VBAでミリ秒以下の高精度で処理時間計測](https://hatenachips.blog.fc2.com/blog-entry-377.html) の関数を使いました。マイクロ秒の精度で計測てきますので、ループさせる必要なく計測できます。
28
28
  判定対象文字列は、Wikipedia:引用のガイドラインから5500字エディターにコピーして真ん中ぐらいに「檸檬」を挿入しました。それをA1セルにコピペしました。
29
29
  実行環境：Windows 10 Home 64bit / Microsoft Excel 2016 32bit

3

編集時に間違って必要な部分も上書きしてしまったので修正

2018/06/09 14:39

投稿

hatena19
hatena19

スコア34367

answer CHANGED
@@ -1,122 +1,107 @@
1
1
  VBAでInStrを使わない方法というと Like演算子か RegExpオブジェクト（正規表現）になりますね。
2
2
 
3
- ~~Like演算子は前にテストした時は、InStrより遅かったです。~~（今回テストしたら違った結果になりました。）
3
+ Like演算子は前にテストした時は、InStrより遅かったです。
4
4
  RegExpはやったことがないので、テストした結果を教えてほしいです。
5
-
5
+ ```vba
6
+ Public Sub fruits_check()
7
+ Dim 判定対象文字列 As String
8
+ Dim reg As Object
9
+
10
+ Set reg = CreateObject("VBScript.RegExp")
11
+
12
+ With reg
13
+ .Pattern = "もも|パイナップル|みかん|バナナ|檸檬"
14
+ .IgnoreCase = True
15
+ .Global = False
16
+ End With
17
+
18
+ 判定対象文字列 = "あああああああ檸檬ああああああ"
19
+
20
+ If reg.Test(判定対象文字列) Then MsgBox "ありました"
21
+
22
+ End Sub
23
+ ```
24
+
25
+ テストしてみました
26
+ ---
27
+ 時間の計測は、[VBAでミリ秒以下の高精度で処理時間計測](https://hatenachips.blog.fc2.com/blog-entry-377.html) の関数を使いました。マイクロ秒の精度で計測てきますので、ループさせる必要なく計測できます。
28
+ 判定対象文字列は、Wikipedia:引用のガイドラインから5500字エディターにコピーして真ん中ぐらいに「檸檬」を挿入しました。それをA1セルにコピペしました。
29
+ 実行環境：Windows 10 Home 64bit / Microsoft Excel 2016 32bit
30
+
31
+ **テストコード**
6
32
  ```vba
7
33
  Public Sub fruits_check()
8
-
34
+
9
35
  Const 検索パターン = "もも|パイナップル|みかん|バナナ|檸檬"
10
-
36
+
11
37
  Dim くだもの判定(5) As String
12
38
  くだもの判定(1) = "桃"
13
39
  くだもの判定(2) = "パイナップル"
14
40
  くだもの判定(3) = "みかん"
15
41
  くだもの判定(4) = "バナナ"
16
42
  くだもの判定(5) = "檸檬"
17
-
43
+
18
44
  Dim 判定対象文字列 As String
19
45
  判定対象文字列 = Range("A1").Value
20
-
46
+
21
47
  Dim lateReg As Object
22
48
  Set lateReg = CreateObject("VBScript.RegExp")
23
-
49
+
24
50
  Dim earlyReg As RegExp
25
51
  Set earlyReg = New RegExp
26
52
 
27
- Dim result As Boolean, i As Long
28
-
29
- SWStart
30
- With lateReg
31
- .Pattern = 検索パターン
32
- .IgnoreCase = True
33
- .Global = False
34
- result = .Test(判定対象文字列)
35
- End With
36
- SWStop
37
- SWShow "lateReg : " & result & " "
38
-
39
- result = False
40
- SWStart
41
- With earlyReg
42
- .Pattern = 検索パターン
43
- .IgnoreCase = True
44
- .Global = False
45
- result = .Test(判定対象文字列)
46
- End With
47
- SWStop
48
- SWShow "earlyReg: " & result & " "
49
-
50
- result = False
51
- SWStart
52
- For i = 1 To 5
53
- If (InStr(判定対象文字列, くだもの判定(i)) > 0) Then
54
- result = True
55
- Exit For
56
- End If
57
- Next i
58
- SWStop
59
- SWShow "InStr : " & result & " "
60
-
61
- result = False
62
- SWStart
63
- For i = 1 To 5
64
- If 判定対象文字列 Like "*" & くだもの判定(i) & "*" Then
65
- result = True
66
- Exit For
67
- End If
68
- Next i
69
- SWStop
70
- SWShow "Like : " & result & " "
71
-
72
- '一致なし用のサンプルデータ
73
- 判定対象文字列 = Replace(判定対象文字列, "檸檬", "")
74
- Debug.Print ""
75
-
76
- result = False
77
- SWStart
78
- With lateReg
79
- .Pattern = 検索パターン
80
- .IgnoreCase = True
81
- .Global = False
82
- result = .Test(判定対象文字列)
83
- End With
84
- SWStop
85
- SWShow "lateReg : " & result & " "
86
-
87
- result = False
88
- SWStart
89
- With earlyReg
90
- .Pattern = 検索パターン
91
- .IgnoreCase = True
92
- .Global = False
93
- result = .Test(判定対象文字列)
94
- End With
95
- SWStop
96
- SWShow "earlyReg: " & result & " "
97
-
98
- result = False
99
- SWStart
100
- For i = 1 To 5
101
- If (InStr(1, 判定対象文字列, くだもの判定(i), vbBinaryCompare) > 0) Then
102
- result = True
103
- Exit For
104
- End If
105
- Next i
106
- SWStop
107
- SWShow "InStr : " & result & " "
108
-
109
- result = False
110
- SWStart
111
- For i = 1 To 5
112
- If 判定対象文字列 Like "*" & くだもの判定(i) & "*" Then
113
- result = True
114
- Exit For
115
- End If
116
- Next i
117
- SWStop
118
- SWShow "Like : " & result & " "
119
-
53
+ Dim result As Boolean, i As Long, j As Long
54
+
55
+ For j = 1 To 2
56
+ result = False
57
+ SWStart
58
+ With lateReg
59
+ .Pattern = 検索パターン
60
+ .IgnoreCase = True
61
+ .Global = False
62
+ result = .Test(判定対象文字列)
63
+ End With
64
+ SWStop
65
+ SWShow "lateReg : " & result & " "
66
+
67
+ result = False
68
+ SWStart
69
+ With earlyReg
70
+ .Pattern = 検索パターン
71
+ .IgnoreCase = True
72
+ .Global = False
73
+ result = .Test(判定対象文字列)
74
+ End With
75
+ SWStop
76
+ SWShow "earlyReg: " & result & " "
77
+
78
+ result = False
79
+ SWStart
80
+ For i = 1 To 5
81
+ If (InStr(判定対象文字列, くだもの判定(i)) > 0) Then
82
+ result = True
83
+ Exit For
84
+ End If
85
+ Next i
86
+ SWStop
87
+ SWShow "InStr : " & result & " "
88
+
89
+ result = False
90
+ SWStart
91
+ For i = 1 To 5
92
+ If 判定対象文字列 Like "*" & くだもの判定(i) & "*" Then
93
+ result = True
94
+ Exit For
95
+ End If
96
+ Next i
97
+ SWStop
98
+ SWShow "Like : " & result & " "
99
+
100
+ '一致なし用のサンプルデータ
101
+ 判定対象文字列 = Replace(判定対象文字列, "檸檬", "")
102
+ Debug.Print ""
103
+ Next j
104
+
120
105
  End Sub
121
106
  ```
122
107
 
@@ -132,5 +117,6 @@
132
117
  InStr : False 0.0501
133
118
  Like : False 0.0558
134
119
  ```
120
+
135
121
  単位はミリ秒です。
136
122
  当方の環境、用意したサンプルでは、InStrが最も速いという結果になりました。

2

コードの修正

2018/06/09 14:37

投稿

hatena19
hatena19

スコア34367

answer CHANGED
@@ -5,34 +5,7 @@
5
5
 
6
6
  ```vba
7
7
  Public Sub fruits_check()
8
- Dim 判定対象文字列 As String
9
- Dim reg As Object
10
8
 
11
- Set reg = CreateObject("VBScript.RegExp")
12
-
13
- With reg
14
- .Pattern = "もも|パイナップル|みかん|バナナ|檸檬"
15
- .IgnoreCase = True
16
- .Global = False
17
- End With
18
-
19
- 判定対象文字列 = "あああああああ檸檬ああああああ"
20
-
21
- If reg.Test(判定対象文字列) Then MsgBox "ありました"
22
-
23
- End Sub
24
- ```
25
-
26
- テストしてみました
27
- ---
28
- 時間の計測は、[VBAでミリ秒以下の高精度で処理時間計測](https://hatenachips.blog.fc2.com/blog-entry-377.html) の関数を使いました。マイクロ秒の精度で計測てきますので、ループさせる必要なく計測できます。
29
- 判定対象文字列は、Wikipedia:引用のガイドラインから5500字エディターにコピーして真ん中ぐらいに「檸檬」を挿入しました。それをA1セルにコピペしました。
30
- 実行環境：Windows 10 Home 64bit / Microsoft Excel 2016 32bit
31
-
32
- **テストコード**
33
- ```vba
34
- Public Sub fruits_check()
35
-
36
9
  Const 検索パターン = "もも|パイナップル|みかん|バナナ|檸檬"
37
10
 
38
11
  Dim くだもの判定(5) As String
@@ -63,6 +36,7 @@
63
36
  SWStop
64
37
  SWShow "lateReg : " & result & " "
65
38
 
39
+ result = False
66
40
  SWStart
67
41
  With earlyReg
68
42
  .Pattern = 検索パターン
@@ -73,6 +47,7 @@
73
47
  SWStop
74
48
  SWShow "earlyReg: " & result & " "
75
49
 
50
+ result = False
76
51
  SWStart
77
52
  For i = 1 To 5
78
53
  If (InStr(判定対象文字列, くだもの判定(i)) > 0) Then
@@ -83,9 +58,10 @@
83
58
  SWStop
84
59
  SWShow "InStr : " & result & " "
85
60
 
61
+ result = False
86
62
  SWStart
87
63
  For i = 1 To 5
88
- If 判定対象文字列 Like くだもの判定(i) Then
64
+ If 判定対象文字列 Like "*" & くだもの判定(i) & "*" Then
89
65
  result = True
90
66
  Exit For
91
67
  End If
@@ -93,9 +69,11 @@
93
69
  SWStop
94
70
  SWShow "Like : " & result & " "
95
71
 
72
+ '一致なし用のサンプルデータ
96
73
  判定対象文字列 = Replace(判定対象文字列, "檸檬", "")
97
74
  Debug.Print ""
98
75
 
76
+ result = False
99
77
  SWStart
100
78
  With lateReg
101
79
  .Pattern = 検索パターン
@@ -106,6 +84,7 @@
106
84
  SWStop
107
85
  SWShow "lateReg : " & result & " "
108
86
 
87
+ result = False
109
88
  SWStart
110
89
  With earlyReg
111
90
  .Pattern = 検索パターン
@@ -116,9 +95,10 @@
116
95
  SWStop
117
96
  SWShow "earlyReg: " & result & " "
118
97
 
98
+ result = False
119
99
  SWStart
120
100
  For i = 1 To 5
121
- If (InStr(判定対象文字列, くだもの判定(i)) > 0) Then
101
+ If (InStr(1, 判定対象文字列, くだもの判定(i), vbBinaryCompare) > 0) Then
122
102
  result = True
123
103
  Exit For
124
104
  End If
@@ -126,29 +106,31 @@
126
106
  SWStop
127
107
  SWShow "InStr : " & result & " "
128
108
 
109
+ result = False
129
110
  SWStart
130
111
  For i = 1 To 5
131
- If 判定対象文字列 Like くだもの判定(i) Then
112
+ If 判定対象文字列 Like "*" & くだもの判定(i) & "*" Then
132
113
  result = True
133
114
  Exit For
134
115
  End If
135
116
  Next i
136
117
  SWStop
137
118
  SWShow "Like : " & result & " "
119
+
138
120
  End Sub
139
121
  ```
140
122
 
141
123
  **計測結果**
142
124
  ```text
143
- lateReg : True 1.08
125
+ lateReg : True 1.0089
144
- earlyReg: True 1.0607
126
+ earlyReg: True 0.6539
145
- InStr : True 0.0694
127
+ InStr : True 0.0472
146
- Like : True 0.0034
128
+ Like : True 0.0526
147
129
 
148
- lateReg : False 2.5398
130
+ lateReg : False 1.5676
149
- earlyReg: False 2.0252
131
+ earlyReg: False 1.2376
150
- InStr : False 0.0751
132
+ InStr : False 0.0501
151
- Like : False 0.0031
133
+ Like : False 0.0558
152
134
  ```
153
135
  単位はミリ秒です。
154
- 当方の環境、用意したサンプルでは、圧倒的にLikeが速いという結果になりました。
136
+ 当方の環境、用意したサンプルでは、InStrが最も速いという結果になりました。

1

テストコードの追加

2018/06/09 14:25

投稿

hatena19
hatena19

スコア34367

answer CHANGED
@@ -1,6 +1,6 @@
1
1
  VBAでInStrを使わない方法というと Like演算子か RegExpオブジェクト（正規表現）になりますね。
2
2
 
3
- Like演算子は前にテストした時は、InStrより遅かったです。
3
+ ~~Like演算子は前にテストした時は、InStrより遅かったです。~~（今回テストしたら違った結果になりました。）
4
4
  RegExpはやったことがないので、テストした結果を教えてほしいです。
5
5
 
6
6
  ```vba
@@ -21,4 +21,134 @@
21
21
  If reg.Test(判定対象文字列) Then MsgBox "ありました"
22
22
 
23
23
  End Sub
24
- ```
24
+ ```
25
+
26
+ テストしてみました
27
+ ---
28
+ 時間の計測は、[VBAでミリ秒以下の高精度で処理時間計測](https://hatenachips.blog.fc2.com/blog-entry-377.html) の関数を使いました。マイクロ秒の精度で計測てきますので、ループさせる必要なく計測できます。
29
+ 判定対象文字列は、Wikipedia:引用のガイドラインから5500字エディターにコピーして真ん中ぐらいに「檸檬」を挿入しました。それをA1セルにコピペしました。
30
+ 実行環境：Windows 10 Home 64bit / Microsoft Excel 2016 32bit
31
+
32
+ **テストコード**
33
+ ```vba
34
+ Public Sub fruits_check()
35
+
36
+ Const 検索パターン = "もも|パイナップル|みかん|バナナ|檸檬"
37
+
38
+ Dim くだもの判定(5) As String
39
+ くだもの判定(1) = "桃"
40
+ くだもの判定(2) = "パイナップル"
41
+ くだもの判定(3) = "みかん"
42
+ くだもの判定(4) = "バナナ"
43
+ くだもの判定(5) = "檸檬"
44
+
45
+ Dim 判定対象文字列 As String
46
+ 判定対象文字列 = Range("A1").Value
47
+
48
+ Dim lateReg As Object
49
+ Set lateReg = CreateObject("VBScript.RegExp")
50
+
51
+ Dim earlyReg As RegExp
52
+ Set earlyReg = New RegExp
53
+
54
+ Dim result As Boolean, i As Long
55
+
56
+ SWStart
57
+ With lateReg
58
+ .Pattern = 検索パターン
59
+ .IgnoreCase = True
60
+ .Global = False
61
+ result = .Test(判定対象文字列)
62
+ End With
63
+ SWStop
64
+ SWShow "lateReg : " & result & " "
65
+
66
+ SWStart
67
+ With earlyReg
68
+ .Pattern = 検索パターン
69
+ .IgnoreCase = True
70
+ .Global = False
71
+ result = .Test(判定対象文字列)
72
+ End With
73
+ SWStop
74
+ SWShow "earlyReg: " & result & " "
75
+
76
+ SWStart
77
+ For i = 1 To 5
78
+ If (InStr(判定対象文字列, くだもの判定(i)) > 0) Then
79
+ result = True
80
+ Exit For
81
+ End If
82
+ Next i
83
+ SWStop
84
+ SWShow "InStr : " & result & " "
85
+
86
+ SWStart
87
+ For i = 1 To 5
88
+ If 判定対象文字列 Like くだもの判定(i) Then
89
+ result = True
90
+ Exit For
91
+ End If
92
+ Next i
93
+ SWStop
94
+ SWShow "Like : " & result & " "
95
+
96
+ 判定対象文字列 = Replace(判定対象文字列, "檸檬", "")
97
+ Debug.Print ""
98
+
99
+ SWStart
100
+ With lateReg
101
+ .Pattern = 検索パターン
102
+ .IgnoreCase = True
103
+ .Global = False
104
+ result = .Test(判定対象文字列)
105
+ End With
106
+ SWStop
107
+ SWShow "lateReg : " & result & " "
108
+
109
+ SWStart
110
+ With earlyReg
111
+ .Pattern = 検索パターン
112
+ .IgnoreCase = True
113
+ .Global = False
114
+ result = .Test(判定対象文字列)
115
+ End With
116
+ SWStop
117
+ SWShow "earlyReg: " & result & " "
118
+
119
+ SWStart
120
+ For i = 1 To 5
121
+ If (InStr(判定対象文字列, くだもの判定(i)) > 0) Then
122
+ result = True
123
+ Exit For
124
+ End If
125
+ Next i
126
+ SWStop
127
+ SWShow "InStr : " & result & " "
128
+
129
+ SWStart
130
+ For i = 1 To 5
131
+ If 判定対象文字列 Like くだもの判定(i) Then
132
+ result = True
133
+ Exit For
134
+ End If
135
+ Next i
136
+ SWStop
137
+ SWShow "Like : " & result & " "
138
+ End Sub
139
+ ```
140
+
141
+ **計測結果**
142
+ ```text
143
+ lateReg : True 1.08
144
+ earlyReg: True 1.0607
145
+ InStr : True 0.0694
146
+ Like : True 0.0034
147
+
148
+ lateReg : False 2.5398
149
+ earlyReg: False 2.0252
150
+ InStr : False 0.0751
151
+ Like : False 0.0031
152
+ ```
153
+ 単位はミリ秒です。
154
+ 当方の環境、用意したサンプルでは、圧倒的にLikeが速いという結果になりました。