人力検索はてな
モバイル版を表示しています。PC版はこちら
i-mobile

VBAで、エクセルシート上の40ほどのセルに計算結果を(forループで)連続して書き込む処理をすると、異常に長くかかりました。
通常なら、この程度の処理は瞬時に終わるのですが、今回は非常に時間がかかりました。
同じマシンで他に特にアプリを動作させているのでもないのに、VBAの処理時間が非常にかかるのは、EXCELシートの記載内容とかに関係しているのでしょうか。
VBAがセルに情報を書き込むのに必要な処理時間の増大に影響する要素にはどんなことがあるのでしょうか。

●質問者: hathi
●カテゴリ:インターネット 学習・教育
✍キーワード:Excel VBA アプリ エクセル セル
○ 状態 :終了
└ 回答数 : 6/6件

▽最新の回答へ

1 ● KUROX
●3ポイント

私が選んだポイントは、

(1)証券会社が倒産する可能性が低いこと

(2)手数料がある程度安いこと

(3)自分のやりたいことができること

(4)維持費が安いこと(無料?)

でオリックス証券選びました。

http://kakaku.com/stock/

この3ポイントであうところを選んではどうかと思います。

あと、売買はしないけど、口座を開いている証券会社

も選択しました。主に情報を取る(有料もあり)目的

です。手数料が高いので、売買はしません。

◎質問者からの返答

ご本人から、勘違いで他の質問の回答を書かれたとのメールを戴きました。 お知らせありがとうございます。


2 ● Mook
●50ポイント

シートを変更することによって発生するイベント処理、自動計算、(あまり関係なさそうですが画面更新)、あたりがあやしいですが、処理の前後に下記の処理を追加しても変化はないでしょうか。

Sub Sample()
'---(1) 画面の更新の停止
 Application.ScreenUpdating = False
'---(2) イベントの処理の停止
 Application.EnableEvents = False
'---(3) 自動計算の停止
 Application.Calculation = xlCalculationManual
 
 処理
 
 
'---(3') 自動計算の再開
 Application.Calculation = xlCalculationAutomatic
'---(2') イベントの処理の再開
 Application.EnableEvents = True
'---(1') 画面の更新の再開
 Application.ScreenUpdating = True
 
End Sub

VBA高速化テクニック(目次)

◎質問者からの返答

イベント処理はまだ停止したことがないので、これから試してみます。

イベント処理の停止を試して見ましたが効果はありませんでした。

ちなみに、次のことを実験で確認できました。

? 問題にしている実験の処理は次の通りです。

For j = 26 To 69

Cells(j + 82, 6) = Cells(j, 1)

Cells(j + 82, 7) = Cells(j, 2)

Cells(j + 82, 8) = Cells(j, 4) - Cells(j, 6)

Cells(j + 82, 9) = Cells(j, 6)

Next j

? この処理の対象のbookは 568KBで8シートありました。

? このbookから、?の処理に関係のないシート7枚を削除し、名称を変えて保存したところ173KBになりました。

? ?のBOOKのみを開いて、?の処理をさせたところ、瞬時に終了しました。

? そのままで、568KBのbookを開いて、

?の173kBのbook(1sheetしかない)で

?を試したところ、非常に時間が掛かりました。

そのままで、568KBのbookを閉じて、

?の173kBのbook(1sheetしかない)で

?を試したところ、瞬時に終わりました。

なお、PCは 2GH 2GBメモリ 物理アドレス拡張

XPSP2

パフォーマンスオプションはパフォーマンス優先

仮想メモリ 総ページファイル1024MB

タスクマネジャで見ると

173KBのみを?で試行したとき CPU使用率2~4%

568KBも開いて、173KBに対して、?で試行したとき

CPU使用率4~56%で、56%前後が数秒続きました。


3 ● F-15X
●15ポイント

自動計算と画面のレンダリング(更新表示)に時間がかかります。

以下のようにすると良いです。


画面更新を停止するステートメント

Application.ScreenUpdating = False

自動計算を手動するステートメント

Application.Calculation = xlManual


http://www.relief.jp/itnote/archives/001259.php

http://www.res-system.com/item/55

◎質問者からの返答

この両方は試した結果でした。

効果は出ませんでした。


4 ● Mook
●20ポイント

コメントを有効にしていただければ、そちらで補足したいと思いますが、まず実行したコードはどのモジュールに記載したのでしょうか。標準モジュールでしょうか。

だとしたら、アクティブなシートが対象になると思うのですが、その点は問題ないですか。


明示的に対象シートを限定したいならば、With ステートメントでシートを指定し、Cell を .Cell に変更することで、想定外の部分への影響は限定できます。

あるいは、シートモジュールに記載してはどうでしょうか。


原因がわからないようであれば、ステップ実行をしてみてはどうでしょうか。

VBE の For 文の最初(あるいはSub の先頭の部分で)で Break(F9) を設定してから処理を開始すると、そこで停止します。

その後F8でステップ実行できますので、予想外の処理が走っていないか確認できます。

EXCELでお仕事 作成したマクロの動作の確認方法

EXCELでお仕事 マクロを「書いてみた」「動かした」「ダメでした」。

◎質問者からの返答

Mookさん ありがとうございます。

イ 実行コードは標準モジュールに記載してあります。

シートを増やし、シート名を変更して作業する処理なので、

対象シートの限定は好ましくなく、アクティブシートを対象とさせています。

ロ 568KBのbookで、forループの終了に9.3秒、9.8秒、10.3秒懸かりました。

ハ with でシートを限定して、同様の計測をしたら、10.9秒、11.9秒でした。

どんどん所要時間が長くなります。

ニ 173KBのbookだけを開いて実行したところ

0.015秒でした。

ホ ステップ毎のチェックでは、順番にコードが実行されるだけで、特別に時間の掛かるステップもありません。


5 ● Mook
●130ポイント ベストアンサー

以降やり取りが続くようでしたら、コメントを有効にしていただけないでしょうか。


対象がアクティブシートであれば、With の指定は不要です。

これだけの処理にこの時間がかかるはずはありませんので、セルの変更により何かしらの処理が動いているように思えます。


まず確認ですが、実行している処理は提示されたものだけでしょうか。つまり、

Sub foobar
 For j = 26 To 69
 Cells(j + 82, 6) = Cells(j, 1)
 Cells(j + 82, 7) = Cells(j, 2)
 Cells(j + 82, 8) = Cells(j, 4) - Cells(j, 6)
 Cells(j + 82, 9) = Cells(j, 6)
 Next j
End Sub

だけで、これ以外に処理は記述されていませんか?


ここ以外はほとんど処理が無いと思っていたところで大きな処理があり、実際に時間がかかっている処理をデバッグの対象からはずしてしまっている、といった思い込みによる見当違いはありがちですので、もう一度前後をご確認ください。


また、Step 実行をした際は Sub の先頭から End Subまで、実行してみたでしょうか。For 文を終わったからといってやめていませんか?


それでだめなら、For 文の中に

 Debug.Print Time

を埋め込んで、実際に1ループにかかる時間を表示してみてください。


デバッグは思い込みによって対象を限定してしまうと、真の原因を取りこぼしてしまいますので、まさかと思うようなことも疑って調べてみる必要があります。


既に実行されているかもしれませんが、ご参考までに。

VBA 実行時間の表示

◎質問者からの返答

Mookさん ありがとうございます。

イ subの中の全体(実験していたまま)は次の通りです。

Sub 表示位置()

With Application

.Calculation = xlManual

End With

Application.ScreenUpdating = False '画面表示更新を停止

MsgBox "pause1"

With Worksheets("200512月次")

huy = Timer

For j = 26 To 69

.Cells(j + 82, 6) = .Cells(j, 1)

.Cells(j + 82, 7) = .Cells(j, 2)

.Cells(j + 82, 8) = .Cells(j, 4) - .Cells(j, 6)

.Cells(j + 82, 9) = .Cells(j, 6)

Next j

.Cells(104, 7) = Timer - huy

End With

Columns("A:E").EntireColumn.Hidden = True

MsgBox "pause2"

Rows("1:82").EntireRow.Hidden = True

ActiveSheet.PageSetup.PrintArea = "$F$83:$U$154"

Range(Cells(108, 17), Cells(151, 20)).ClearContents

Range(Cells(108, 12), Cells(151, 13)).ClearContents

Range(Cells(108, 12), Cells(151, 12)).Interior.ColorIndex = 35

MsgBox "pause6"

With Application

.Calculation = xlAutomatic

End With

MsgBox "pause7"

Application.ScreenUpdating = True '画面表示更新を再開

Cells(82, 19).Select

End Sub

ロ ご覧の通り、計測はforループを挟んだ前後で行っています。

ハ 1ループ毎の所要時間はなぜか増加します。

次のようにコードを変更して計測した結果は次の通りです。

With Worksheets("200512月次")

huy = Timer

For j = 26 To 69

.Cells(j + 82, 21) = Timer - huy

.Cells(j + 82, 6) = .Cells(j, 1)

.Cells(j + 82, 7) = .Cells(j, 2)

.Cells(j + 82, 8) = .Cells(j, 4) - .Cells(j, 6)

.Cells(j + 82, 9) = .Cells(j, 6)

Next j

.Cells(104, 7) = Timer - huy

End With

cells(?,21)の値 0.00 0.17 0.31 0.44 0.58 0.70 0.84 0.97 1.11 1.23

1.38 1.52 ……… ……… 5.56 5.70 5.84 6.00

今回は6秒です。前の実験よりステップは増えていますが、時間は短い。

ニ 全く同じVBAプロジェクト(全てのモジュール)をもつbook(ただし

sheetは"200512月次"のみ(ファイルサイズ173KB))で同じく実行すると

cells(?,21)の値 0.00 0.00 0.00 0.00 ……… と続いて ………0.0156

0.0156 0.0156 0.0156 ……… と続いて 最後まで0.0156になります。

使用しているbookが小さいと時間が掛からないようです。


ホ 上記173KBのブックは開いたままで、

前述のブック(571KBになっていました)をマクロを無効にして開きます。

(結果2つのファイル名の異なるブックが開いています)

173KBのシートをアクティブにして、sub 表示位置()を実行すると、

cells(?,21)の値 0.00 0.18 0.33 0.48 0.62 0.78 0.93 1.08 中略

2.00 2.14 2.30 2.45 2.61 2.77 2.92 中略……… と続いて

5.95 6.13 6.28 6.45 6.61 6.78 で 最終6.78秒でした。

対象でないbookがどう影響するのか、見当もつきません。

へ 最後の手段というか、計測の問題理解のために配列を使ってみました。

コードは次の通りです。

Sub 表示位置()

Dim varcell

' With Application

' .Calculation = xlManual

' End With

'Application.ScreenUpdating = False '画面表示更新を停止

hyu = Timer

varcell = Range(Cells(26, 1), Cells(69, 6))

For i = 1 To 43

varcell(i, 3) = varcell(i, 4) - varcell(i, 6)

Next i

Range(Cells(108, 6), Cells(151, 9)) = varcell

Columns("A:E").EntireColumn.Hidden = True

Rows("1:82").EntireRow.Hidden = True

ActiveSheet.PageSetup.PrintArea = "$F$83:$U$154"

Range(Cells(108, 17), Cells(151, 20)).ClearContents

Range(Cells(108, 10), Cells(151, 13)).ClearContents

Range(Cells(108, 12), Cells(151, 12)).Interior.ColorIndex = 35

'With Application

' .Calculation = xlAutomatic

'End With

'Application.ScreenUpdating = True '画面表示更新を再開

Cells(104, 7) = Timer - hyu

Cells(82, 19).Select

End Sub

実行しているbookは521KB 同時に開いているbookは174KBと571KB

sub 表示位置()を実行した結果、Cells(104,7)は、0.875秒を

示しました。

同時に開いているbookを閉じて、521KBのみで実行したら0.672秒を

示しました。

画面表示更新中止、計算中止などの処理をせず、他の処理も含んだ

処理の全体を計測しているので比較は不適当ですが、

とにかく個々にセルに表示するのとは圧倒的に速度が違います。

いつも配列を使うという方式が適当かどうかも気になるのですが、それよりも、通常の処理でなぜこのような差が生じるのかを、知りたいと思います。


1-5件表示/6件
4.前の5件|次5件6.
関連質問


●質問をもっと探す●



0.人力検索はてなトップ
8.このページを友達に紹介
9.このページの先頭へ
対応機種一覧
お問い合わせ
ヘルプ/お知らせ
ログイン
無料ユーザー登録
はてなトップ