MSアクセスVBAでメモリ不足？

質問

MSアクセスのVBAマクロを使い、テーブルから取得したデータを、エクセルブックに貼り付けるアプリを作ったのですが、
エクセルに値をセットするステップ（Cells(x,y)value = 取得したデータ）で
『実行時エラー1004：アプリケーション定義またはオブジェクト定義のエラーです』
というエラーが発生します。

エラーをなくすにはどうしたらいいのでしょうか。

ブック内のシート数は、全部で何十シートにもなるのですが、
データによって、エラーが発生するシートが違うこと、
10～20シートごとに1回はエラーが発生すること、
エラーが発生した時に、原因になった行をコメントアウトしても、その次の行でエラーになること、
などから、もしかしたら処理の途中でメモリ不足になっているのかもしれない、と感じているのですが、
処理の途中でエクセルブックを保存したり、一度閉じて再びOpenしたり、してみましたが、エラーは出てしまいます。

メモリは関係ないのでしょうか。。。

回答No.2

catpow · ベストアンサー

エクセルVBAに限らず、Ｃ言語とか、Ｃ＃でも開発したプログラムを実行していると、よく分らないエラーが発生することあります。
そして「もしかするとメモリ不足かも？」と思えるようなことあります。

でも、そんな予想ができたとしても、あるいは、明確にメモリー不足とエラーが表示されていようが、そのエラーが出ないようにしないといけない・・・。
そして、ソースを見ていても、問題の箇所が見つからない・・・。

こんなときは、エラーが無くなるまでコードを削っていく「消去法」で調べるのが早いでしょう。（元のソースはバックアップしておく）
たとえば、

１．テーブルからデータを読み込む
２．データを加工する
３．データをシートに貼り付ける

っていう処理を繰り返しているなら、２と３の処理を抜きにして、１．だけを繰りかえしてみる。
これでエラーが出ないなら２．も処理に加えてみる。
それでもエラーが出ないなら３．を加える。ここでエラーが出たら３．が怪しそう、となります。

まあ、こんな感じで問題箇所を絞りこんでいけばいいと思いますね。

ちなみに私も先日、千件～３００万件を越えるいくつかのデータをOracleから読んで処理するＣ＃のプログラムを書いてテストしていました。
そうしたら、およそ１１０万件以下はＯＫだけど、それを越えるようなデータを処理すると、メモリー不足で中断するデータがいくつか発生しました。
「ソースでは、処理の途中で、確保したメモリを解放しているから、メモリー不足になるはずない！」
と思っていたのですが、上記の方法で調べていたら、実際にはクリアされてない部分があって、そのメモリが肥大化していたようでした。
それをクリアする命令を追加したら正常動作しました。

泥臭い方法だとは思いますが「訳わか」状態になったときは、この手法がいいと思います。

お礼

実経験からの的確なアドバイスありがとうございます。
他の方の回答も参考にしながらコピー＆ペーストしている箇所をコメントアウトするとエラーは発生しなくなり、原因が絞り込めました。

各シートの中で
１．罫線＆書式を揃える為に前の行(x-1行)をコピー
２．(x行に)貼り付け
３．Cells(x,y)value = 取得したデータ
４．Cells(x,y+1)value = 取得したデータ
・・・
というロジックになっており、３．でエラーが発生していました。
おそらく貼り付け処理が完了する前に値をセットしようとしてエラーになっていたものと思われます。
３．と４．の間にDoEvents命令を入れることで解決しました。

回答No.4

kkkkkm

> エクセルに値をセットするステップ（Cells(x,y)value = 取得したデータ）で
> 『実行時エラー1004：アプリケーション定義またはオブジェクト定義のエラーです』
> というエラーが発生します。

指定したセル番地やシート名などが存在しない時にでるエラーですね。Cells(x,y).valueの xかyが0とかマイナスとか最大行数や列数を超えてるとかですが、次の行でもエラーという事ですので、それとは別のような気もしますが、次の行のコードがわからないので、とりあえず参考までに。

回答No.3

IDii24

sheetの参照後Nothingで開放してますか？Excelオブジェクトも何度も起動させていることは無いとは思いますが念のため見直してください。
そして何よりもExcel側のタイミングがあってないのでは？Excelを待つ処理を入れるべきでしょう。
シートを移るときにWaitなどで1秒ほど待った方がよいでしょう。

お礼

ありがとうございます。
エクセルのタイミングにも気を配らないといけないのですね。

回答No.1

masatsan

わけのわからないエラーが出るときがありますよね。
データベースの最適化をすると直る時が多々あります。
とくにデータベースが大きくなった時。

お礼

ありがとうございます。
今回は別の原因だったようですが、何かのときに思い出して参考にさせて頂きます。