ExcelでVBAを利用してファイルを開こうとするとアプリケーションが落ちる

Question

こんにちは、引き続きファイルを希望のオーダー通りに処理するVBAをエクセルで組んでいます。
Microsoft Excel for Office 365 MSO (16.0.12430.20198) 64ビット環境で実行しています。

ここにきて

Private Function fileOpener(File_Path As String)
            Workbooks.Open File_Path
End Function

の処理でアプリケーションが落ちることがままある（必ずではない）ことに頭を悩ませています。

このfileOpenerを呼び出す親ルーチンは

        (略)
	For cnt = LBound(File2Process) To UBound(File2Process)
		If (File2Process(cnt) <> "") Then
			fileOpener (File2Process(cnt))
		End If
	Next cnt
　　　　 （略) 

で、このFile2Process配列はString型の配列で、ファイルのフルパスが入っています。
配列の内容に問題があると思えない根拠は、Workbook.openの際にブレイクポイントを入れて
ステップ実行すると成功するからです（5～10ファイル／50フォルダで確認ずみ）

お知恵をお借りいたしたく存じます。よろしくお願いします。

追記：Do Eventをカマす方法も考えてみましたがいままで、より大量のファイルを処理してきた
経験（同じサブルーチン内で、という限定はありますが）とできれば迅速に処理を行いたい事を鑑みて、可能な限り別の方法を募りたいと思っています。

Comments

[sinzou]

File2Process(cnt)>256バイト　　ってことないですよね。

[vbNetBeginner]

ありません。業務上必要な（濁すようですみませんが100以下）です。
ただしファイル自体は巨大です。

[imihito]

質問の問題とは関係無いでしょうが、`(File2Process(cnt) <> "")`や`(File2Process(cnt))`の外側のカッコが不要なことはご存知でしょうか？

[vbNetBeginner]

存じております。そして無関係だと思っています。可読性、他言語からVBAに移って来た方には弊害だとも考えています。しかしVBA環境が特殊だと思い知りましたので、以後、参考にして何かの際には試してみたいと思います。ありがとうございます。

Answer 1

社内の辞めていった先輩たちの資料を読み漁っているうちになぜか以下のように不要としか思えない
代入を行うことですんなりプログラムが機能いたしました。2019年末のメモでしたので現行のシステムでも
同様の症状を引き起こす可能性があると考えています。もし、こういうレアな障害を経験した方の
対処方法や何に起因するものなのかを引き続きご教授いただければ幸いです。

        Sub orderMaker()
        
	(略)
        Dim fName As String
        Dim File2Process() As String
	(略）
        For cnt = LBound(File2Process) To UBound(File2Process)
            If (File2Process(cnt) <> "") Then
                fName = File2Process(cnt)
                fileOpener (fName)
            End If
        Next cnt

Comments

[Y.H.]

一連の処理のどこかでWindowsAPIや外部DLLをDeclareして呼び出したりしていませんか？
もしあるなら何処かでメモリ壊している可能性もあります。

[vbNetBeginner]

FileSystemObject利用(Scripting.FileSystemObject)のためにMicrosoft Scripting Runtimeを参照利用していますがそれ以外はadd-onも使っておらずWindowsAPI（シェル関連含む）も外部DLLも利用しておりません。

情報ありがとうございます。別案件で悩んだときは参考にします。

Answer 2

ステップ実行すると成功するからです

ならば、Sleepなどを入れて少し待ってあげると、
いいんでは？

http://officetanaka.net/excel/vba/tips/tips116.htm

Comments

[vbNetBeginner]

負荷が原因のものではないと考えたため、今回は見送らせていただきました。
またお知恵を拝借できる機会があればよろしくお願いします。

[mattuwan]

いやいや、
ステップ実行で成功するなら、
待ってあげれば成功するという話で、
CPU使用率は関係ないかな。。。。
ま、いいんだけれども。。。
多分、ブックが開かないうちに次のブック次のブックと命令だけ先にいって
エクセルがパニックになっているかと。。。
確実に開いてから、次、という風にして待ってやるといいと思います。
Sleepはその一例。他の回避策もあるでしょう。

[sinzou]

https://alwaysclimb.net/fileopen_excelcrash_vba_macro-202
こちらの方はEXCELのファイル操作にOSの処理が追っつかない？
OSに処理を渡すDoEvents()有効なようです

[vbNetBeginner]

上記に県の回答につきましては、自己回答とはなりましたが
テンポラリの変数に代入後に全く同等の処理を行うと問題が再発しないため
無関係であると判断しています。時間があるときにSleepを入れるケースと
Do Eventを入れるケースでサブルーチンを組んでテストしたいと思います。
ご指摘ありがとうございました。

Answer 3

Excelで開くことができるブック数は、使用可能メモリとシステム リソースに依存します。
(出典：Excel の仕様と制限 )

質問のプログラムは、ファイルパスの配列であるFile2Processの全てのファイルを開こうとするものですから、使用可能なメモリが不足するなどの事態が起きる可能性が考えられます。メモリやリソースが極端に不足するとプログラムを実行する事ができなくなって、アプリケーションが異常終了する事もあり得ます。

リソースモニターを起動しておいてから、質問のプログラムを実行すると、使用可能メモリやシステムリソースの使用量がリアルタイムで判りますので、観察してみてください。リソース不足が起きそうなら、マシンの性能が低すぎるか、プログラムがリソースを浪費しすぎているのだと思われます。

＝
質問のプログラムは、むやみにブックを開き続けてから、何かをしようとしているのでしょうが、本当にそれが必要でしょうか？
「100個のブックからデータを収集して、それを１つのブックに集める」というようなプログラムなら、最初に集めた結果を入れるブックを開き、
・「一つ目のブックを開けて、そこからデータを収集して結果を入れるブックに書き込む。一つ目のブックは閉じる」
・「二つ目のブックを開けて、そこからデータを収集して結果を入れるブックに書き込む。二つ目のブックは閉じる」
というように順にファイルを開いては処理して使い終わったファイルは閉じる、というような操作を繰り返せば、最初っから100個のブックを開くよりも少ないリソースで処理が可能になります。

プログラムの設計を見直してみては、いかがでしょう。

Comments

[vbNetBeginner]

普段の作業量とデバッグ用ファイルの量から、普段80万レコードを超えるファイルを数個開きつつ作業をしておりましたので、ファイルのオープン数の事を考慮に入れ忘れておりました。ご指摘ありがとうございます。とりあえずファイルをオープンする必要性はなく、処理順のオーダー通りにファイルを開ければいいためworkbook型変数に開いたファイルをSetしてメッセージを表示してCloseするコードに変更しました。
これからデバッグを行います。ありがとうございました。

[vbNetBeginner]

Private Function fileOpener(File_Path As String)
Dim wb As Workbook
Set wb = Workbooks.Open(File_Path, False)
MsgBox File_Path & vbCrLf & "Closeします"
wb.Close
End Function
と変更し、デバッグ中ですがlogやエラー表示、一時保存など全くなく突然終了する場合がまだあります。
今回はデバッグ対象をしぼり、同じフォルダ（8ファイル内包、エクセルファイル1個１MB弱、.xlsx形式）で試していますが質問提議の時点と同じように、順次ファイルを開いてくれる場合もあり相違がわかりません。他のアプリケーションはEdge（Teratail参照用）以外起動していません（DaemonなどOS規定で開いているものはあると思いますが）スペック的に難あるマシンではなく(i7, Memory16G, HDD 100GB以上空き）CPUに関しては一瞬60を超えますが、CPU利用率やメモリ、ディスクともに逼迫した数値とは思えません。疑わしきは何なのかご教授願えれば幸いです。

[sinzou]

https://teratail.com/questions/104881
より
Function抜ける前に
Set wb = NOthing
つけるとか

[vbNetBeginner]

今回は、以前自己回答をした手法（引数として渡す文字列をテンポラリのString型変数に代入して、引数にはテンポラリの変数を使う）で切り抜けられたのでこの不可解な解法はオフィシャルなのかバグなのか、経験者は多いのか少ないのか等が知りたく、質問をCloseせずにいましたが、
>Set aBook = Nothing
>で即座にリソースを開放します。
という有用な情報が知れたことを大変うれしく思います。
今回の問題は上記説明の通りスペック的な問題ではなく少々大きなファイルも開ける事が明らかになってきています（厳密な結果が出てないので断言できないのが残念ですが）今のところ問題は発生しておりません。

それはさておきcloseでメモリを即座に開放しないこと
variant型に入ったワークブック変数をnothingにすることで開放される事
勉強になりました。ありがとうございました。