Excel2010VBAのセルの入力が遅い

質問

Excel2010のVBAなんですが、処理時間が非常に遅いです。プログラムの中でも遅い部分はセルに入力する部分です。多分24時間ぐらいプログラムを走らせておきましたがそれでも終わっていませんでした。
シートは6個あり、それぞれ関数の計算がされており、シート1からシート3までの計算されたものがシート4に出力され、そのシート4からプログラムで計算してシート5に出力されるコードです。
シート5に出力するデータは、「年」、「月」、「日にち」、「時刻」、「データ1」、「データ2」、「データ3」、「データ4」の8つの項目です。これは全ての行に入力するわけではなく、入力しなくてもいい空白の部分もあります。空白でもいいセルには入力の処理はしないようになっています。
処理時間が遅いというのは、データ量が非常に多いということもあるのですが最初に参照するデータは50万件以上（実際はこれよりはるかにあるのですが区切っています。）あります。
全てのコードはここには記載しませんが、特に遅いセルの入力の部分は、次の通りです。

For～

With Workbooks("ブック名.xlsm").Worksheets("シート5")

.Cells(b, 1) = Year(日付)
.Cells(b, 2) = Month(日付)
.Cells(b, 3) = Day(日付)
.Cells(b, 4) = TimeValue(日付)
.Cells(b, 5) = Application.Round(データ1, 3)
.Cells(b, 6) = Application.Round(データ2, 3)
.Cells(b, 7) = Application.Round(データ3, 3)
.Cells(b, 8) = Application.Round(データ4, 3)

End With

Next

この部分の処理を早くするにはどうしたらいいでしょうか？

回答よろしくお願いします。

回答No.1

keithin · ベストアンサー

とりあえず次のようにすると，早くなります

application.screenupdating = false
For～

With Workbooks("ブック名.xlsm").Worksheets("シート5")
application.calculation = xlcalculationmanual
.Cells(b, 1) = Year(日付)
.Cells(b, 2) = Month(日付)
.Cells(b, 3) = Day(日付)
.Cells(b, 4) = TimeValue(日付)
.Cells(b, 5) = Application.Round(データ1, 3)
.Cells(b, 6) = Application.Round(データ2, 3)
.Cells(b, 7) = Application.Round(データ3, 3)
.Cells(b, 8) = Application.Round(データ4, 3)
application.calculation = xlcalculationautomatic
End With

Next
application.screenupdating = true

もっと途中で再計算が必要な場合は，もちろんそこで再計算させます。

お礼

回答ありがとうございます。

「application.screenupdating」というのは画面描画（関数などの）で、「application.calculation」は、関数の自動更新機能のことなんですね。
試してみます。

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

回答No.4

374649

Excelをマクロで処理するプログラムを多用しています、処理の重さという問題に苦労しています。

同じマクロを実行すると

Excel2003　＞　Excel2007　＞　Excel2010

という現実が有ります、昔は表計算は上から読み込まれるので上下に作るより横長にシート設計をするほうが処理が速いと教わりましたがメモリーもCPUの能力もアップしている現代では事情が違ってます。

しかし関数を多用すればそれだけ処理に時間が掛かりますし、マクロの書き方でも差が出てきます。マクロの一部分をみて速くしたいといってもそれだけではアドバイスは難しいと思います。

具体的には関数の数を減らす、処理を分散して処理速度を上げる（同じ処理はルーチン化するなど）など基本的にシートの設計から見直すことも処理速度を上げます。

＞処理時間が遅いというのは、データ量が非常に多いということもあるのですが最初に参照するデータは50万件以上（実際はこれよりはるかにあるのですが区切っています。）

これではExcelの処理の問題というよりアクセスなどのデータベース処理のソフトが向いているのでは。

PCに付いては何も書かれていませんがExcelは多コアに対応しているのでCPUの能力を上げるのも一手段です私も場合Excel2010では特に重たくなるのでExcel2007で走らせています、２コアより４コアの方が明らかに処理能力は優れてます。

回答にはなりませんが上記のようなことをやってます。

お礼

回答ありがとうございます。

データ量が多くて本当はExcelでは行数が足りないと思っていたのですがデータベース処理のソフトですか。ちょっとお金の方がなくて考えていないないのですが、確かにその方がそのような処理に向いていますね。

パソコン自体の性能も重要ですね。約1年前に購入したのですがその時は最新のものだったので性能の方は大丈夫だろうと思っていたのですが、思いのほかメモリを食っています。

関数の数を減らすというのは、これから考えてできるかどうかやってみることにします。

お金があればほとんど満足できるものが手に入れることができるんでしょうが今のExcelで何とか頑張ってみます。

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

回答No.3

keithin

書き忘れました。
言わずもがなですが，マクロの「実行中」にシートのどこが再計算「されている必要があるか」に応じて，最大

application.screenupdating=false
application.calculation = xlcalculationmanual

for 行のループ
cells(b, 1) =
　：
cells(b, 8) =
next 行

application.calculation = xlcalculationautomatic
application.screenupdating=true

まで再計算を省略できます。
つまりご質問のマクロだと８×行数回イチイチ再計算が走っていたのを，いちばん最後に１回だけ再計算するとこまで手間を減らすワケですね。

先のマクロはご質問で掲示を省略したマクロの部分で，とりあえず１行分記入する都度一回，セルへの記入を受けてナニかしなきゃならない状況があるかもしれない？を想定しています。

＃余談
折角マクロを使うのでしたら，いまシートに埋めている「ナニか不明の重たい関数式」をそもそももっと軽くて高速化できるようにもっと工夫し，「それを実現できるためのシートの処理」をマクロで実現するのも合理的な解決策です。
今回のご質問は「掲示されたマクロ部分に関する改善策」なので，ここではこれ以上は突っ込みませんが，必要に応じて具体的な状況を添えて別途ご相談を投稿してみてください。

お礼

2度も回答をいただき、ありがとうございます。

再計算しなければならないのは質問で記載したコードの最後のみです。今までセルの入力のたびに再計算の処理が行われていたということになります。
ということは、
application.calculation = xlcalculationautomatic
application.screenupdating=true
は、一番最後にくればいいということですね。

>先のマクロはご質問で掲示を省略したマクロの部分で，とりあえず１行分記入する都度一回，セルへの記入を受けてナニかしなきゃならない状況があるかもしれない？を想定しています。

これは質問に書いていなかったので説明不足でして、すいません。セルに入力するたびにプログラムで何かをしなければならないということではありません。
あくまで、入力し終わったら最後に再計算させればよいということです。

「余談」で書いていただきましたが、また何か分からないことがあればより具体的に質問させていただきます。

大変感謝しております。ありがとうございました。

回答No.2

mu2011

一例です。
セルの更新に処理時間が割かれるので一旦、内部配列経由としては如何でしょうか。

Dim wk(7)
Application.ScreenUpdating = False
For～
With Workbooks("ブック名.xlsm").Worksheets("シート5")
wk(0) = Year(日付)
wk(1) = Month(日付)
wk(2) = Day(日付)
wk(3) = TimeValue(日付)
wk(4) = Application.Round(データ1, 3)
wk(5) = Application.Round(データ2, 3)
wk(6) = Application.Round(データ3, 3)
wk(7) = Application.Round(データ4, 3)
With Workbooks("ブック名.xlsm").Worksheets("シート5")
.Cells(b, 1).Resize(1, 8) = wk
End With
Application.ScreenUpdating = True

お礼

回答ありがとうございます。

セルに入力するところで、「.Resize」というのを使うんですね。
試してみます。

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