- ベストアンサー
VBAのプロシージャが大きすぎる！エラーメッセージが出る原因と対処法
- VBAのプロシージャが大きすぎてエラーメッセージが表示される場合、以下の対処方法を試してみてください。
- 1. プロシージャを分割する：プロシージャを複数のサブに分割することで、エラーメッセージが表示されなくなる場合があります。
- 2. オブジェクトを利用する：オブジェクトを利用してプロシージャを短縮することで、エラーメッセージを回避することができます。
- みんなの回答 （3）
- 専門家の回答
質問者が選んだベストアンサー
ん～～～～～・・・えっと～～～～～・・・ まずいろいろ突っ込みどころ満載なソースコードですのでエラー云々よりも基本的な部分で指摘させてもらいますね。疑問点はいろいろありますが、おそらくやりたいのはこうであろうなという内容的にはエラー無しで可能そうですので。致命的そうなものから順に書きます 1.「.Cells(nR, nC + 2) = Worksheets(1)Cells(i, 14)」という感じのコードを延々縦42×横18個分丁重にコードを記載したのでしょうか？ 2.i～i41まで変数が使用されていますが変数の宣言はiのみである 3.しかも2で指摘した変数はi以外は意味を成していない 4.そのうちに行数カウントnRが65535を突破してExcelが書き込める行数を超えたということでエラーが発生する(エラー回避のコーディングの問題) 感じからするとマクロで元データのシートであることを確認した後、貼り付け先のシートを縦に調べて空白があったところから元データの内容をコピーしているものと思います。あくまでそういう目的であることを前提として回答しますと次のような修正になります。 1.i～i41についてはおそらくコピーする元のアドレスを縦41列先までの行番号を格納しておきたいと思ったときがあった名残だと思います。実際にはiをNowReadRowという1個の変数名にしてしまって差し支えないと思います。 2.二次元的な値の貼り付けもループ処理で行いましょう。延々ソースコードを書いていますが、nR・nCにWriteRowOffsetとWriteColumnOffsetの2個の変数を加えるとこんな書き方で代用可能です For WriteRowOffset = 0 To 41 'ここに見出し行(計とか燃料とかそういうもの)の記述をするソースを記入 For WriteColumnOffset = 2 To 17 Worksheets(2).Cells(nR + WriteRowOffset, nC + WriteColumnOffset).Value = Worksheets(1).Cells(NowReadRow+WriteRowOffset, 12 + WriteColumnOffset).Value Next Next NowReadRow = NowReadRow + 51 '(質問のソースのi = i + 51に相当します) 質問者の質問内容だけですとこれだけになるのですけれどちょっと余談で2個書き連ねます。興味がありましたらどうぞ。 1個目は >.Range(Cells(nR, nC + 2), Cells(nR, nC + 17)).Value = Worksheets(1).Range(Cells(i, 14), Cells(i, 29)).Value これがエラーで動かないとあります。 これは初心者がRangeプロパティーを使用するときに非常に多い質問・問題で有名です。動かない理由としては非常に簡単で、中にあるRangeプロパティーとCellsプロパティーがどのシート所属の物かがねじれてしまっているためです。実際にエラーはプロシージャーが大きすぎるではなくアプリケーション定義またはオブジェクト定義のエラーと出るはずです。では先ほどの例を使って説明しましょう。 まず左辺の.Range(Cells(nR, nC + 2), Cells(nR, nC + 17)).Valueですが、まずはじめの.RangeはWith Worksheets(2)の中なのでWorksheets(2)に所属するRangeになります。一方、Rangeの中にある2個のCellsプロパティーはどちらもCellsの前にピリオドがありません。この場合はActiveSheet.Cells(アドレス)という風に勝手に解釈され、そのマクロを実行する瞬間にアクティブになっているシート所属のCellsになるのです。つまり、この命令が実行された瞬間にWorksheets(2)のシートがアクティブならこのマクロはエラーを起こしませんが、他のシートがアクティブな場合はエラーを起こしてしまうのです。分かりにくい身近な説明で言うと、とある車Pの燃費を別のある車Oを走らせて測定しなさいと言っているようなものです。Pの車の燃費を走らせて調べるにはPの車を走らせなければ分かりませんよね？Pの車なのになぜそこでOが出てくるの？ってのでExcel側がエラーで教えてくれているのです。(厳密にはいろいろ違いますがそういうイメージの捉え方ということで) ですので、 .Range(.Cells(nR, nC + 2), .Cells(nR, nC + 17)).Value = Worksheets(1).Range(Worksheets(1).Cells(i, 14), Worksheets(1).Cells(i, 29)).Value というように省略せずに書き込むとエラーを起こさなくなると思います。当方はMeキーワードなどわざわざ書かなくてもいいものを書くなとよく突っ込みを受けますが、必要なときはきちんと書き込むことによってバグ回避という意味で強固なプログラムを書くことができるのです。今回の問題もその典型です。どこまでを書いてどこからを省略するかのさじ加減がまた難しいのですけどね。 2個目の補足です。 ソースを見たところ、結構な情報量を処理するものだと見受けられます。現在のソースコードではデータが増えるごとにどんどん処理時間が増えていってしまいます。ですので、配列を使用した一斉書き写しをお勧めします。詳しくは参考URLを見てもらいたいのですが、簡単な原理のソースだけにとどめさせてもらいます。 Dim varRangeData() as Variant varRangeData = コピー元シート.Range(コピー元アドレス).Value コピー先シート.Range(コピー先アドレス).Value = varRangeData これだけです。コピー元アドレスとコピー先アドレスの縦横の数はきちんと合わせてくださいね。1個1個丁寧に書き込むよりもずっと高速です。その代わり値しかコピーできない弱点もありますけどね。後は、貼り付ける前に値の内容をいろいろいじることもでき、そのいじる作業が非常に高速にできるのがその方法の一番の売りです。興味がありましたらぜひ参考にしてみてください。それでは
その他の回答 (2)
- Randomize
- ベストアンサー率70% (38/54)
#1です。 提示されたコードを実行させたところ、こちらでもエラーは確認しましたが、原因は非常に簡単です。 >Worksheets(2).Cells(nR + WriteRowOffset, nC + WriteColumnOffset).Value = Worksheets(1).Cells(NowReadRow + WriteRowOffset, 12 + WriteColumnOffset).Value というところでエラーが発生していますが、一番初めにNowReadRowに値を入れ忘れており、その結果NowReadRow + WriteRowOffsetは0となり、0行目というExcelでは存在しないセルを参照しようとしているためです。仮にNowReadRowに初期値1を与えたところ、問題なく動作します。 前回補足で申しました値を配列に入れると高速になるという件で、 >行列については、 >>その代わり値しかコピーできない弱点もありますけどね。 >値だけでなく、文字列も入っている列があるので、難しいかと思います。 と、数字はコピーできても文字はコピーできないという風に勘違いされておられますので更に補足します。 この手法でコピーできるのはセルのValueプロパティーの中身のみであるという意味で「値のみ」という表現をしました。数字でも文字列でも取扱可能です。ただし、Valueプロパティー以外の値、すなわちセルや文字の色付け・文字揃え・罫線・フォント・結合状態・関数の式自体(結果はコピー可能)・グラフや図形はこの手法ではコピーできません。ですので、この方法を使用するときは色付けや罫線等を設定する時に条件付き書式などを多用することになります。 以上、補足回答です。
お礼
Randomize様、ありがとうございました。仰っているように、NowReadRowに値を入れたら、問題なく動きました。行列についても、よく分かりました。本当にありがとうございました。
- cistronezk
- ベストアンサー率38% (120/309)
まあ、上を見たら切がありませんが、何はともあれ「For ・・・ Next」をきちんとマスタすることを強くお勧めします。それを身に付けるだけでも、ご提示のコードはかなりすっきりします。 詳細は、参考書やWebにゴマンとあります。
補足
Randomize様、 ご丁寧に教えていただいて本当にありがとうございます。 行列については、 ＞その代わり値しかコピーできない弱点もありますけどね。 値だけでなく、文字列も入っている列があるので、難しいかと思います。 教えていただいたように、WriteRowOffsetとWriteColumnOffsetを使って書いてみましたが、「アプリケーション定義またはオブジェクト定義のエラーです」というエラーメッセージが出てしまいます。 Dim nR As Long Dim nC As Long Dim WriteRowOffset As Long Dim WriteColumnOffset As Long Dim NowReadRow As Long nR = 2 nC = 2 For WriteRowOffset = 0 To 41 Worksheets(2).Cells(nR + WriteRowOffset, nC).Value = Worksheets(1).Range(Worksheets(1).Cells(68, 12), Worksheets(1).Cells(110, 12)).Value For WriteColumnOffset = 2 To 17 Worksheets(2).Cells(nR + WriteRowOffset, nC + WriteColumnOffset).Value = Worksheets(1).Cells(NowReadRow + WriteRowOffset, 12 + WriteColumnOffset).Value Next Next NowReadRow = NowReadRow + 51 End Sub よろしければ、ご指摘をいただければと思います。 どうぞよろしくお願いいたします。