ExcelのVBA環境の設定事項について

質問

ExcelのVBA環境の設定事項について

ExcelのVBA実行で異常終了します。その状況は…
１．CSVファイルオープンでエラー発生(１００４)
　　(個別VBAではオープンできる。ファイルは正常)
２．何の表示もなくExcel画面が消えます。また異常終了します

同じVBAを他のPC環境で実行するとノーマル終了できるためVBAに主因はありません。

異常終了するPC環境は…
Windows7,Office2010,64bitCPUです

正常終了するPC環境は
WindowsXP,Office2002,32bitCPUです

お聞きしたいことは…
VBA環境の設定で制限事項はありますか、その変更方法について?
(時間制限、件数制限、コミット要否など)

回答No.4

tsubuyuki

とりあえず伝え忘れたことがありますので、前言を翻して一点だけ。

1004のエラーを再現するのは簡単です。
　　Range("A0").Select
これだけででます。
"A0"セルが「ありません」と言うエラーです。

　　Workbooks.Open Filename:="D:\test___.csv"
これでも多分、出ます。
こちらは"D:\test___.csv"と言うファイルが「ありません」と言うエラーです。

1004と言うのは、主にそういうエラーです。
踏まえて

Sheets(shNitiji).Select
Range("B4").Select
yNitijiLast = Selection.End(xlDown).Row
For yNitiji = 4 To yNitijiLast Step suAdv ' adv
nmNitiji = Cells(yNitiji, 2)
mjDate = Mid(nmNitiji, 1, 10)
If mjDate >= stDate And mjDate <= edDate Then
err -> Workbooks.Open Filename:=drName & nmNitiji ' csv

ここの「err」で1004が出るという事は、
「drName & nmNitiji」と言うファイルが無い、と考えるのがまずは妥当です。
想定している「drName & nmNitiji」と言うファイルが必ず存在するのであれば、
今度はこの変数「drName & nmNitiji」の中身を疑います。
　　Workbooks.Open Filename:=drName & nmNitiji
この行の上に「debug.Print drName & nmNitiji」を追加し、
イミディエイトウィンドウで確認するなどしてみると、原因も見えるかもしれません。

蛇足ではありますが、
http://office.microsoft.com/ja-jp/excel-help/HP010342495.aspx
マイクロソフト社が公式に発表しているエクセル２０１０の制限はリンク先の通りです。
これを超えるならおそらく、オーバーフロー的なエラーが出ると思われます。
試していないので、これ以上は何とも言えません。

お礼

ありがとうございます。ひとつひとつ絞り込んでいくため時間が掛かります。

メーカ(東芝)にエコ機能から制限がでるかを問い合わせました。
回答はMicoroSoftに聞いて関与する処を聞いて欲しいということでした。

メーカが云うMicoroSoftのoffceに聞くことと、このWebで聞くこととは担当者が違うでしょうが、
Web担当者とOffice担当とは連携を取っていることと思います。

Windows7がAP処理中のCPU使用量から、Eｘcelを切り離すことがありますか。
今回の件のなかで実際に何度も、エラー表示もなく、Excelは画面が消えました。

それから、ＣＳＶファイルオープンのエラー検出した直後の、変数内のデータはステップインで確認をしましたが正常です。誤ったファイル名ではありません。なぜエラーとなるか…

ＣＳＶファイルオープン中では沢山のことが行われているために我々には見えません。
この中のことをフォローして戴けるなら原因が掴めると思います。

再現がいるなら場合によってはＣＤを送付してもいいです（全ファイル１４００ほど、Ｅｘｃｅｌ）。
この対応は取って戴けそうですか。

補足

正常終了するケースが見つかりました。

Basic Editを開いている状態で”実行”すると最後まで処理ができます。
同じ構造の５個のマクロのいずれも最後まで処理します。

タグ内の図形ボタンから実行するとＣＳＶファイルオ－プンでエラーします。
フォームから実行すると”応答なし”になります。

この違いは何かわかりませんが、
何か対策ができるでしょうか。

以上

回答No.3

tsubuyuki

お返事が遅れまして、すいません。

宅のＰＣは３２ビットＯＳです。
職場のＰＣが６４ビットＯＳですので、こっそりためしてみました。
結論から言うと、同様の処理に関しても問題なく終了しました。

・・・と言っても、所詮は私が用意したランダムな数値の羅列、
実際に質問者さまが扱っていらっしゃるデータとは全く違います。
（文字列もあるでしょうし、日付もあるでしょうし。）
ＶＢＡの構成も大きく違うでしょう。
私のテストでは単純なコピー貼り付けしかしていませんから。
（その分、Forよりも負担が大きいDoを選択はしましたが。）

ですので、その原因を切り分けるために、デバッグが必要なのです。
１月１日から起算し、エラーが発生するのが５月２９日。
１日に１件のＣＳＶファイルが存在するとして、今年（閏年）で奇しくも１５０日目。
対して、テストなさったと言う５月１日起算で考えると、８月３１日は１２３日目。
これが９月３０日まで、と言う条件だと１５３日目ですね。
この範囲で走らせて、やはり１５０日目（９月２７日のファイル）でエラーが起きるなら、
「処理ファイル数による制約」の疑いが出てきます。
（私のテストでは５００回のループに耐えた＝５００ファイル全て処理できたわけですが。）

また、どんな範囲で処理をしても「６０分で止まる」と言うのであれば、
「時間による制約」の可能性が出てきますね。
（止まる「時間」も明白ではないので断定はできません。）
この場合、スクリーンセーバーオフ、ハイバネーションオフにして、
常に処理状況を確認できるようにしておかないといけませんが。

ファイルの数によらず、処理のボリュームも考慮しなくてはいけません。
日によってＣＳＶファイルの行数が変わるのであれば、
処理何回目（何行目）で止まるのか？も問題になるのかもしれません。
数回テストして、何度やっても「近似値」で止まるのであれば、
この場合は「処理回数による制約」がある可能性も見えてきます。

これらがバラバラなのであれば、これらの「制約」によってエラーが発生しているとは言えないのです。

「時間の無駄」「通常のやり方ではない」とおっしゃるかもしれませんが、
これらを切り分ける作業はとても重要です。
使う端末機・ＰＣによって得られる結果が違うマクロなら、
それは欠陥マクロだと言わざるを得ませんから。
どのＰＣでも、誰が扱っても同じ結果が得られるようにすることが
マクロによる自動化の目的の一つですからね。

まぁそもそも、エクセルは大量のデータを扱う事に長けたソフトではありません。
ＣＳＶファイルにどのような処理を施し、どのように集計するマクロをお使いなのか
今現在も結局サッパリわからないままではありますが
（ＣＳＶファイルを開く部分しか情報がありませんから）、
上記も踏まえて、アクセスなどのデータ処理を目的としたソフトへの移行も含めて
マクロの見直しなど検討してみてはいかがでしょうか？

どちらにしても、私の環境では再現できないエラーです。
私が業務で扱っている
「社内ＬＡＮによるサーバーに置いてある、
　平均１０００行×３５列のＣＳＶファイルを
　　・３つの条件で分岐させて再計算
　　・キー列への条件をつけて行削除
　　・余分な列を削除
　　・エクセルでピボットテーブル・グラフなどへの再加工
　　・日付ごとに９７－２００３ブックで保存
　以上の作業がデイリーの業務ですが、
　これらを同様に５００回繰り返すようにしたマクロ」
を走らせましたが、これも６４ビットＯＳ機で滞りなく完了したことを書き添えて、
私からの回答はこれで終わりとします。

グダグダ申し上げましたこと、お詫びいたします。

お礼

丁寧な対応をして戴きました。感謝します。あれから…

マクロ全文の構文分解を行い変数の未定義、誤定義の有無を全て検証しました。 結果はok
コードチェックはマクロ全文を再度実施しました。 結果は ?
マクロの主要部分はステップインによりプロセスと処理データを検証しました。結果は ?
データはエラー発生ファイルに全行、目を通しました。 結果は ?
処理範囲を変更したり、他の同構造の処理(５種類)も実行を試みました。 結果は同じ…

エラー発生はExcel出力行で、１０３行、１０２行の後にでることがわかりました。
今のところは出力行数が１０４行を超えて結果が出たことがありません。

今回ファイルオープンで出るエラーは、以前にディレクトリーやドライブ指定方法でも起きました。
この辺りを試みようと思います。もし確実なファイルオ－プンのマクロ例があれば教えてください。

暫く究明を続けます。もし原因が判明した折にはご報告します。
また、何か心あたりに気づいた場合はお知らせ戴けると幸いです。

ご協力ありがとうございました。

回答No.2

tsubuyuki

なるほど、理解しました。
複数のＣＳＶファイルに対して繰り返し処理をするマクロで、
初めのうちの数ファイルは処理が出来るが、
“途中のＣＳＶファイル”で処理が止まる、
ということですね。

> VBA実行で異常終了(1004)する件について、
> 多量処理でエラーするファイル(20120529) も少量処理ではエラーせず正常に終わります。
> だから時間制限をしない、件数制限を解除するＶＢＡが用意されているのではないかと…
>
> 取り込み対象範囲を変えて実行しました。正常に終了します。以下は例示です。
> 2012-01-01 ～ 2012-11-30 （エラーした処理範囲）
> 2012-05-01 ～ 2012-08-31　（正常に終了した処理範囲,　 2012-05-29を処理している）

当初の質問文から上記の内容は読み取れなかったもので、失礼いたしました。

当方（Win7、Excel2010、Core i3-3.30GHz、メモリ4GB）の環境で
（実際のデータとは全く違うモノですが）1000行×20列のＣＳＶファイル（約１ＭＢ）を
家庭内ＬＡＮでつないであるＰＣ（ＸＰ、Pen4-1.6GHz、メモリ2GB）に用意しました。
ＣＳＶファイルの中身は３桁から５桁のランダムな数値です。
このＣＳＶファイルに対し、

・ネットワークパスを変数に代入
・ＣＳＶのファイル名を変数に代入
・変数を使い、このファイルを開く
・新規ブックを用意
・ＣＳＶファイルの最終行まで（Ａ列が空白になるまで）Do～Loopで１行ずつコピー
・新規ブック、シート１に貼り付けてLoop
・ＣＳＶ閉じる
・新規ブックに名前を付けて保存（ＨＤＤ，Ｄドライブ直下に“book"&回数）
・新規ブックを閉じる

と言う作業を５００回、For～Nextで繰り返す、と言うテストを３回繰り返しました。
特筆すべきでもありませんが、ＶＢＡに特殊な設定は施していません。

処理行数１０００×５００＝５０万行、所要時間はざっくりですが平均５４分程かかりましたが、
処理自体は滞りなく正常終了しました。
ちなみに所要時間はGetTickCountで取ったモノなので、ほんとにザックリです。

やはり、処理件数や時間による制約はないものと考えますが、いかがでしょう？
それをはっきりさせるために、デバッグをしてみてはいかがですか？と申し上げた次第です。

補足

ExcelのVBA環境の設定事項について （３）

そうですか…　　もう一点聞かせてください。
エラーの起きているPCは６４ｔbitです。エラーが起きないＰＣは３２ｂｉｔです。

そちらでランニングテストしたＰＣは３２ｂｉｔですかね。
話しによると６４ｂｉｔでは不安定だと聞いたことがありますが、ｏｆｆｉｃｅ２０１０は３２ｂｉｔ仕様ですか？

もしそうなら３２ｂｉｔＰＣでしか使えません…
以上

回答No.1

tsubuyuki

問題点を洗い出すのが先決かと。
1004と言われても、実際のコードがわからないので良いアドバイスはできませんが・・

VBEを開き、問題のマクロの最初の命令文にブレイクポイント（Ｆ９キー）を置き、
（行に合わせてＦ９を押すと、茶色にハイライトされます）
「普段通りのやりかたで」マクロを走らせます。
ブレイクポイントで一時停止しますので、Ｆ８キーで１行ごと動かしていきます。
これで「エラーを生じて停止する行」がはっきりしますので、
その行、あるいはそこまでの記述がどこか間違えていると判断できます。

残念ながら、
> １．CSVファイルオープンでエラー発生(１００４)
> 　　(個別VBAではオープンできる。ファイルは正常)
> ２．何の表示もなくExcel画面が消えます。また異常終了します
状況だけ言われても、実際のコードやCSVファイルの状態などがわからないとアドバイスも難しいです。

まして、あなたがお使いのＰＣ（ＥＸＣＥＬ・ＶＢＡ等ソフト含む）の設定もわからないので、
どこをどういじっているのか、どういじればいいか、判断は出来ないです。

ハッキリ言えることは、CSVを取り込む行為をマクロ化するにあたって
「特殊な設定が必要」なんてことはありませんし、
２００２で取り込める件数なら、２０１０でも十分に収まります。
ご存知とは思いますが、２００７以降、行数も列数も飛躍的に増えていますから。

補足

ExcelのVBA環境の設定事項について　(2)

VBA実行で異常終了(1004)する件について、
多量処理でエラーするファイル(20120529) も少量処理ではエラーせず正常に終わります。
だから時間制限をしない、件数制限を解除するＶＢＡが用意されているのではないかと…

取り込み対象範囲を変えて実行しました。正常に終了します。以下は例示です。
2012-01-01 ～ 2012-11-30 （エラーした処理範囲）
2012-05-01 ～ 2012-08-31　（正常に終了した処理範囲,　 2012-05-29を処理している）

この処理を全件、ブレークポント＆ステップインで終わらせることは”通常”ではないですね。
他のアドバイスを戴けますか。

以下はファイルとプログラム です。
-------
csv file
1301,極洋,東証1部,水産・農林業,181,181,179,181,103000,18559000
1305,ETFTPX,東証,その他,740,749,736,749,112900,83861640
1306,TOPIX投,東証,その他,738,746,734,746,1165780,861146980
1308,上場TPX,東証,その他,729,737,724,737,572000,416132900
　　　…

-------
vba
drName = "D:\gm\kabu\stockT\"
…

Sheets(shNitiji).Select
Range("B4").Select
yNitijiLast = Selection.End(xlDown).Row
For yNitiji = 4 To yNitijiLast Step suAdv ' adv
nmNitiji = Cells(yNitiji, 2)
mjDate = Mid(nmNitiji, 1, 10)
If mjDate >= stDate And mjDate <= edDate Then
err -> Workbooks.Open Filename:=drName & nmNitiji ' csv
…
errAction:
dtErr = Err.Number
Resume Next
----------
以上