オートメーションエラーです。起動されたオブジェクトはクライアントから切断されました

過去に正規表現により1文字違いの場合でも全て真として
値を取得する方法を相談させて頂きました。
山田太郎の場合の正規表現は

.田太郎|山.太郎|山田.郎|山田.郎

山田太郎のように検索すべき対象の文字列が
ds.Range(D:D)に500個程度あり

今朝、山田太一はパンを食べましたのような検索されるべき対総の文字列が
ds.Range(H:H)
に150程度あります。
ただし、この検索されるべき対象の文字列は上記のように短い文のこともあれば
1000文字程度の長い文もあります。

【ご質問】
下記の処理を実行した結果、2~3分程度エクセルがうなり、
"オートメーションエラーです。起動されたオブジェクトはクライアントから切断されました"
のエラーとともに処理が中断しました。意図した出力は3つぐらいは得られていたので
コード自体には問題がないと考えております。

ループの構造等に問題がありエラーが出力されて処理が中断されてしまうため、正しい処理であってもあるべき処理ではないのか、それともそもそも500 (X 正規表現の数) X 150 (文字数)を分だけvbaを使ってエクセルで実行すること自体に無理があるのでしょうか？

あまりエクセルが得意ではないため、特に後者について、感覚的に分からない部分があるため、アドバイスをいただけるとうれしいです。（エクセルでそれをやらせるのは無理があるよとかでもいいです。）

【追伸】
実際にはいまトライアルのため検索されるべき文字列の対象が500個程度となっていますが
実際には1万個を超えるデータです。

vba
1前略
2   Set reg = CreateObject("VBScript.RegExp")
3    
4     o = 1
5     For p = 2 To maxValG + 1
6      namePt = Replace(ds.Range("D" & p).Value, " ", "")
7      nameLen = Len(namePt)
8      
9       For q = 2 To maxValI + 1
10       
11          For t = 1 To nameLen
12          Patn = Patn + WorksheetFunction.Replace(namePt, t, 1, ".") + "|"
13          Next
14          Patn = Mid(Patn, 1, Len(Patn) - 1)
15          
16          reg.Pattern = Patn
17        
18        If reg.Test(ds.Range("H" & q)) = True Then
19        pts.Cells(o, 1) = ds.Range("G" & p).Value
20        pts.Cells(o, 1).Interior.Color = RGB(217, 217, 217)
21        
22        pts.Cells(o, 2) = ds.Range("G" & q).Value
23        pts.Cells(o, 2).Interior.Color = RGB(217, 217, 217)
24        
25        pts.Cells(o, 3) = namePt
26        pts.Cells(o, 3).Interior.Color = RGB(217, 217, 217)
27        o = o + 1
28        End If
29       Next
30     Next
31後略

行動規範の内容に同意します

回答1件

ベストアンサー

詳しくは見ていませんが、Patnを初期化せずに使っているので、延々と結合されてしまっているのが原因ではないでしょうか。
Patn編集ループ前に初期化してあげてみてください。

VBA
1Patn = ""        ' ←追加
2For t = 1 To nameLen
3	Patn = Patn + WorksheetFunction.Replace(namePt, t, 1, ".") + "|"
4Next
5Patn = Mid(Patn, 1, Len(Patn) - 1)

追記
処理時間がかかるような場合は、DoEventsを適宜入れるようにしてください。
Excelが固まるのが防止できますし、途中で中断することも可能になりますので。
あまり入れすぎると遅くなる可能性もあるので、１つ目のループの最後あたりに入れるなどでよいかと思います。

投稿2018/10/19 02:31

編集2018/10/19 02:37

ttyp03

総合スコア16998

SugiuraY

2018/10/19 03:02

仰るとおりで解決することが出来ました！ボンミスでしたが、悩み果ててました。 DoEventsも初めて知りました！！本当に感謝に耐えません。。御礼申し上げます。

ExcelVBAer

2018/10/19 06:21

画面更新の停止、計算方法の手動化、等は言うまでもないですが、 For文に入る前に、With pts として「pts.Cells(o, 1)」なんかを「.Cells(o, 1)」ってしたら多少早くなるかと。

ttyp03

2018/10/19 06:26

Withって高速化に効果あるんでしょうか？あくまでコード上省略できるというだけのような気がしますが。

ExcelVBAer

2018/10/20 13:30

ご指摘ありがとうございます! 昔、先輩に教わった事を鵜呑みにしていたのですが、今回のケースですと高速化の効果は無いようですね。失礼しました... Sheets("○○")等とアクセスしている場合は効果が出るようです。勉強させて頂きました。 https://excel-ubara.com/excelvba4/EXCEL228.html