VBAで、エクセルシート上の40ほどのセルに計算結果を(forループで)連続して書き込む処理をすると、異常に長くかかりました。

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

回答の条件
  • 1人10回まで
  • 登録:2007/08/18 23:15:12
  • 終了:2007/08/23 09:35:30

ベストアンサー

id:Mook No.5

Mook回答回数1312ベストアンサー獲得回数3912007/08/19 16:44:04

ポイント130pt

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


対象がアクティブシートであれば、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 実行時間の表示

id:hathi

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秒を

   示しました。

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

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

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

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

2007/08/19 19:11:37

その他の回答(5件)

id:KUROX No.1

KUROX回答回数3542ベストアンサー獲得回数1402007/08/18 23:22:56

ポイント3pt

私が選んだポイントは、

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

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

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

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

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

http://kakaku.com/stock/

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

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

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

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

id:hathi

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

2007/08/19 11:31:13
id:Mook No.2

Mook回答回数1312ベストアンサー獲得回数3912007/08/19 00:16:23

ポイント50pt

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

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高速化テクニック(目次)

id:hathi

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

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

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

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

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%前後が数秒続きました。

2007/08/19 11:23:59
id:F-15X No.3

F-15X回答回数111ベストアンサー獲得回数132007/08/19 00:33:55

ポイント15pt

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

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


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

Application.ScreenUpdating = False

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

Application.Calculation = xlManual


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

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

id:hathi

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

効果は出ませんでした。

2007/08/19 10:25:01
id:Mook No.4

Mook回答回数1312ベストアンサー獲得回数3912007/08/19 11:58:32

ポイント20pt

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

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


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

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


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

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

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

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

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

id:hathi

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

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

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

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

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

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

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

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

   0.015秒でした。

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

2007/08/19 14:53:01
id:Mook No.5

Mook回答回数1312ベストアンサー獲得回数3912007/08/19 16:44:04ここでベストアンサー

ポイント130pt

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


対象がアクティブシートであれば、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 実行時間の表示

id:hathi

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秒を

   示しました。

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

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

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

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

2007/08/19 19:11:37
id:airplant No.6

airplant回答回数220ベストアンサー獲得回数492007/08/21 02:20:59

ポイント30pt

中々解決せず、大変そうですね。同じ経験あります。


気が付いた点をコメントします。

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

 これは、誤解です。1回当たりにかかった時間に単に掛け算されていますので、回数に比例した値が出てきて、問題ありません。

 start = ストップウォッチ開始
 for ・・・
    print 現時刻 - start
 next
  print 現時刻 - start

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

 With Worksheets("200512月次")

 (途中省略)

.Cells(104, 7) = Timer - huy

  End With

 同じコードを貼り付けて動かしてみましたが、どうしても、0.1秒以内で終わってしまいます。値は数字のみを入れて確かめました。

大きなExcelファイル(2MB程度)を同時に開いておいて実行しても同様です。


 以下のことを試してみてはどうでしょうか?

●一度Excelを終わらせてみる

マクロをデバッグしていて完成したあかつきに動かしたら、非常に動作が鈍くて使い物にならないことがありました。

いくら見直しても悪いところはなかったので、あきらめたのですが、試しにExcelを一度全部閉じて再度動かしたらさくっと高速に動きました。

●書式で特別なものは?

コピー元のセル書式が「配置=折り返して全体を表示する」などの場合は、確実に遅くなります(ただし、「ScreenUpdating = False」の場合は劇的には落ちない)

●式は記載していませんか?

対象セルにコピーしていく先と関連している式を書いているとおかしくなるかも。

●他のブックとの連係有無は?

テストに使っている他ブックとの連係も関係している可能性あり。

少なくとも、hathiさんの計測サンプルを素のbook1.xlsに貼り付けて、全く関係ない大きなExcelファイルを同時に開いてもスピードはダウンしないと思われます。

●プリント関連を外す

PageSetup.PrintArea がありますが、他にプリント関連の記載ありますか? プリント関連は異様にマクロが遅いことが分かっています。

●同時に開くExcelファイルのマクロでメモリをつぶしている

他のマクロでメモリを使われて動きにくくなっているのかも知れません。

次の値を確かめて、freeメモリが小さいときに遅くなっていれば、他のExcelを開かないでやるか、何か手段を考える必要あります。
Application.MemoryTotal: (A)Excelが使える総容量
Application.MemoryUsed: (B)使用中容量
Application.MemoryFree: (C)空き容量 (=(A)-(B))

では、検討を祈ります。

p.s.

いつも配列を使うという方式が適当かどうかも気になるのですが、それよりも、通常・・・

 → 多くのセルを扱う場合、セルに1つずつ値を入れるより、配列で一気に行った方が高速です。まあ、言うまでもないと思いますが。。。

 一気コピーと1つずつobjectのメンバを参照してのforでのコピーとを考えると格段に違うと思います。

id:hathi

申し訳ありません。回答の存在を見逃していました。

1 ループカウンタの件

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

> これは、誤解です。1回当たりにかかった時間に単に掛け算されていますので、

> 回数に比例した値が出てきて、問題ありません。

⇒ 所要時間の計測目的は、なぜ、どこの部分で時間が掛かっているのかを

  見たかったのです。

  for全体で6秒かかっているのが、なにかのタイミングで起きるのか、

  ループ毎に時間が掛かっていくのかを見たつもりでした。

  時間は(単純増加)であることが分かったので、益々、なぜこんなことで

  時間が掛かるのだと疑問が深まったのです。

2 excelの終了や、PCの再立ち上げ、他のアプリの停止などもやってみました。

  デフラグもやってみました。

  後になってですが、BOOKのそのものを作り直すこともやりました。

3 これはまだ、やっていません。 後で確認してみます。

  なお、処理には関係のないシートのセルに256文字を超えるところがあり、

  これを直したら、速くなりました。ただし再現実験はまだ成功してません。 

> コピー元のセル書式が「配置=折り返して全体を表示する」などの場合は、

> 確実に遅くなります。

4 式は色々複雑に絡み合っていて、VBAのコードで書き込んだセルを元に

  つながっていくところはものすごくあります。

  また式そのものを条件で全く書き直すVBAコードもあるし、そうなった

  結果取扱を変更するセルの集計方法を変えるために、Sumproduct関数も

  使っていたりしています。

  当初どうなるのか不安だったので、自分なりに1つづつ動作を確認して

  来たつもりだたのです。

  それでも、目立ったおかしさや処理時間の延長は感じていなかったのです。

> 対象セルにコピーしていく先と関連している式を書いているとおかしくなるかも。

5 大きなファイルを開いていても関係がないことは、後になって分かりました。

  ただし、[ごく小さなファイルでも、VBAの処理に関係ないファイルでも、

  開いている別のexcelファイルに文字の入ったセルと作り、そのセルを

  リンクの図にして貼り付けてあり]そのファイルを開いていると、

  そのファイルには関係がない処理をしているVBAのcells出力には

  異常な時間が掛かります。

> 全く関係ない大きなExcelファイルを同時に開いてもスピードはダウンしないと

> 思われます。 

6 PageSetup.PrintArea の処理に時間が掛かったとしても、今回はこれを

  除いてしまうと、実用に耐えなくなってしまうので、操作者に入力等の

  操作を許す前や、印刷命令をVBAコードで発する前には、

  PageSetup.PrintArea を置くことになります。

  ただ、今回の場合で言えば、画面の一部アリアをクリアし、色を変える

  前に、印刷領域を指定しても、後で指定しても、問題はなかったようです。

7 配列の使い方はなれていなくて、excelのcellを計算等にも使ってしまう

  のですが、少しずつ、データ処理はメモリ上だけ、添え字がいる場合は

  配列を使って、入出力の時にexcelにするというようにしたいと思います。

8 rangeのコピーペーストも、destinationの方が速いのですが、まだ

  よく分かっていなくて、変数へ置き換えorクリップボードに取り込んで、

  目的のrangeに置いたりしているので、今後勉強していきたいと思います。

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

2007/08/23 09:21:36
  • id:Mook
    コメントを有効にしていただいてありがとうございます。

    確認ですが、シートモジュールは何も無い状態でしょうか?
    配列の計算はもちろんそれだけでも速いのですが、それ以外にシートイベントが発生しないという利点もあります。

    もしシートモジュールがあるなら、これを全て削除した状態で実行してみても変わらないでしょうか。

    ただ、これに関しては、
    Application.EnableEvents = False
    を実行することで制御できるはずなのですが・・・。

    他の点ももう少し詳しく見てみます。
  • id:hathi
    シートモジュールの有無の意味がよく分かりません。
    シート上には、チェックボックスあって特定のセルにTrue、Falseなどを反映させています。
    またオプションボタンがあってそれをクリックすることでsubを起動させています。
    sheet13(今テストをしているシートのことです)のコードを開くと下記のように記載されています。

    Private Sub CheckBox1_Click()

    End Sub

    Private Sub CheckBox2_Click()

    End Sub

    Private Sub CheckBox3_Click()

    End Sub

    Private Sub OptionButton1_Click()

    End Sub

    Private Sub OptionButton2_Click()

    End Sub

    Private Sub Worksheet_SelectionChange(ByVal Target As Range)

    End Sub
    これを削除してテストするとなると………
    エディタで単に上記の記載を削除しただけで、試してみました。
    546KBのBOOKで、forループの前後で
    5.31秒、6.15秒 7.03秒 7.86秒 8.67秒 9.63秒でした。
    1回ごとに0.8秒くらいづつ時間が延長するようです。

    Application.EnableEvents = Falseをいれて続けてみました。
    10.48秒 11.28秒 12.05秒 12.84秒
    状況に変わりは見えません。

    これ以外のSUBにも 単純に10×40回くらいのデータの転写(Worksheets("Sheet34").Cells(?,?)=Worksheets("Sheet54").Cells(#,#)に近い単純なコードを持つsubが幾つかあるので、
    先行きが気になって、めげています。
    いま使っているマシンとは別のものでも、この問題にしているsubで処理が長時間になったので、マシン特有のことではないと思っています。 エクセルは両方とも2003です。
  • id:Mook
    シートモジュールは、シートタブを右クリックして「コードを表示」で表示される部分です。
    VBE の シート名をダブルクリックしても同じものが開きます。

    シートモジュールにかかれたものが、前述のものですべてであれば、あまり影響はなさそうですね。


    プロジェクトウィンドウの部分に Thisworkbook という名前が見えると思いますが、この部分をダブルクリックした際に表示される部分は空でしょうか。
  • id:hathi
    昨日と同じ状態なのかはっきりしませんが、昨日のbookを開いて、コードを見たところ、ThisWorkbookのコードは、開いた瞬間は何も表示されませんでした。
    (General)を(Workbook)と切り換えたら、
     Private Sub Workbook_open()

    End Sub
    が表示されました。
    昨夜考えたのですが、同じsubを実行する都度に所要時間が増加するということも不思議でなりません。
  • id:Mook
    当初は簡単な原因だと思いましたが、なかなか原因がわかりませんね。

    ThisWorkbook(ブックモジュール)は何も無い状態だったということは、関係なかったですね。
    General や その隣のリストを選択すると勝手にイベント処理のプロシージャが作成されるので、削除しておいてください。

    ファイルを閉じて、開くとまた処理時間が戻るのでしょうか。
    あまり頻繁には無いことですが、ブック自体が不具合を起こしていることも稀にあります。
    手数がかかる方法ですが、新しいブックを作成しシートの必要部分と必要なコードのみをコピーしてみてどうでしょうか。

    直接ファイルを見れるとよいのですが、なかなか歯がゆいものですね。
  • id:airplant
    回答し終わってから思い出したので、参考までに書いておきます。

    ●変数は、型を定義したほうがいい
     Option Explicit
     Dim i as Integer
    のように。

     型を定義しないと、全部Variant扱いになって、メモリを食うのと、処理が遅くなります。
     今回くらいの回数とメモリならばあまり問題ないかと思います。
    他のシートで、Variantで大きな配列を取っていたりすると、それに圧迫されているということもあるかも。
     また、変数定義を最初にしておくことで、見やすくなり、バグも出にくくなります。
  • id:hathi
    ありがとうございます。
    BOOKを新たに作って、同じコードを走らせることを試みたいと思っています。
    ところでdellのPCに異常が発生し、使えない状態になりました。
    dellサポートによると、バッテリ、マザーボード、HDD、OSがおかしいとのことした。
    もしかするとそうした事情が関係していたのかもれません。
    昨日から実験が(簡単には)できない状況になりました。
    他のマシンでもできることは試してみますが、実験していたフィルを使えないので、ちょっと………です。
    この質問の期限までには、他のマシンで実験を再開します。

    変数の定義をする件ですが、今回はPUBLIC以外は極力定義しないで作っています。定義する場合には、integer long variantは区別しています。なお今回は重要な処理の殆どはエクセル上の関数を使っていること、VBAは日付処理、入力項目のチェックとメッセージ表示、関数式のセルへの入力、シート追加、台帳追加、BOOK名の変更と保存程度などで、変数の使用も少ししかありません。混乱はしないことと、作成途中のテストで仮に変数を使うので未定義変数の排除をすると面倒だからです。
    また最近tanakaさんが、valiantでも速度には影響しないということを書かれていたのを読んだので、カウンタのような近い方をすることに決めているもの以外は、valiantやstringを使うようにしています。
    http://officetanaka.net/excel/vba/speed/s6.htm
  • id:airplant
    昨日、回答もしてありますので、よろしかったらオープンしてみてください。
    色々書きすぎたかも知れません。コメントに書いたのは、一例です。

    マシン、早く直るといいですね。遅くなったのは、あまりマシンとは関係なさそうです。ファイルのバックアップは大丈夫でした?
  • id:hathi
    本題のことは調べが進んでいません。

    変数の定義のことで調べもしないで失礼なことを書きました。
    済みませんでした。

    変数を定義した場合の処理時間を試してみました。
    二重のforループ(6000×5000=30百万回)のループカウンタについて
    定義を変えて、処理時間を調べました。
     ループ内は6変数ありますが、全て定義してあり、excelへの入出力は
     ありません。ループの外に時間計測の書き込みがあります。
    普段使っているPCが壊れた状態なので、この試験は松下CF-W2 XPSP2
    EXCEL2007を使いました。それぞれについて4回試行して測定しました。
    (平均処理時間=秒 指数=カウンタ変数をlong定義としたものを100)

    カウンタ変数 long integer variant 定義しない
    平均処理時間 16.99 17.06 17.35 17.78
    4回の標本偏差 0.274 0.397 0.443 0.146
    指数 100.0 100.4 102.1 104.6
    でした。
    ちなみに、ループ内の6変数も定義なしで試行したところ
    ループ内変数   定義済み  定義なし   同日本語変数
    カウンタ変数   定義しない 定義しない  同日本語変数
    平均処理時間   17.78 20.42 21.20
    4回の標本偏差 0.146 0.084 0.171
    指数 104.6 120.2 124.7
    でした。

    変数を定義しないと処理時間が余分にかかるようです。
    ループカウンタは i,jのみですが、定義しない(variantで処理される)のと
    variantで定義したのでも、処理時間は違いました。
    ただし、各回のバラツキも大きいので、実際上は同じ程度と考えて良いと思います。
    integerよりも、longの方が確実に速いようですが、実際上差はないと思います。
    (longやinteger)は(variant)より速いですが、VBAでループをどの程度回すか
    と考えると、大した問題にはならないように思います。
    変数を多数使う場合に、スペルミスをしないために定義するという実用上のことは思っていましたが、定義するのと定義しないので2割も速度が変わるとは思っていませんでした。

    但し、一連の処理が元もと1秒以下で終わるような処理であるならば、デフォルト(未定義、variant扱い)でも、かまわない、作成者が変数名をこんがらからなければ問題はないと思いました。
      (変数を2バイト文字にするだけで、処理時間が延長しました)
    こうした測定精度がどの程度一般的な意味を持つのかよくわかりませんが、VBAの処理時間を決定づける要因はたくさんありそうなので、あまり細部にこだわってもそれほど効果を得られないような気もしました。
  • id:Mook
    今回試された結果は及び結論も hathi さんが参照された tanaka さんのサイトの内容と同様ですね。
    私もこの点には特に意義はありません。

    ただ、Option Explicit はできれば使用する習慣をつけた方が良いかと思います。
    今回のような小規模な例ではそれほど大きな差異はないかもしれませんが、大規模なプログラムになれば、変数の書き間違いのためにデバッグに時間を費やすというようなケースが少なくありません。

    変数宣言を必要とするオプションは、そういう労力を回避するものですし、個人的にはスマートなプログラミングのスタイルだと思います。

    ところで、これまで確認していませんでしたが、お使いなのは EXCEL2007なのでしょうか。
    EXCEL のバージョンによっては動作・仕様が異なるので、これまで(EXCEL 2003)の常識が通じないケースもあるかと思います。

    いずれにせよ、何とか解決できると良いのですけれどね。
  • id:hathi
    ご指導ありがとうございます。 最近は、Option Explicit を使用する習慣を付けるようには、自分なりに努力しているのですが、途中でテストが必要になることもあって、面倒なので、そのときは、Option Explicitを削除してしまいます。 また最初にきちんと仕様を決めることをしないで始めるので、その辺りも直していかないといけないとは思っています。

    使用しているのは基本的にexcel2003です。2007は使いにくいので、2003に戻して使っています。偶々通常使用のPCが使えない状態になったので、手近にあった松下は2003に戻していなかったので、2007だっただけです。
    会社では2003ですが、壊れたdellには2007,2003の両方を入れていますが、2003を使っています。

    なお、壊れたDELLから、問題のファイルを取り出せました。
    今夜テストしてみます。
  • id:hathi
    今回起きている問題の対策できることが判明しました。

    BOOKの問題ではないようです。
    新規ブックにVBAコードを移し、全てのシートを作成し、テストしたところ同じ現象が発生しました。(PCの差も殆ど感じません)
    シートを削除していったところ、あるセルに多数の文字の入っていて移しきれなかったシートを削除したところ、0.015秒程度の変化がありました。(再現確認は不十分です)
    あるシートにシート上のあるエリアをリンク貼り付けしている部分が2つあり、リンクしている図を2つとも除くと、問題のコードは瞬時に実行されました。
    2つのうち1つのリンクの図を作ると4.5秒ほど、問題のコードの実行時間が掛かります。
    2つのリンク図で、倍の時間が掛かりました。
    同じようなことですが、リンクの図ではなくて、通常の図の貼り付けにしたところ、2つの図があっても、瞬時にコードは完了しました。
    リンクの図貼り付けをさらに増やしたところ、1つについて4~5秒問題のコードの実行時間が延長しました。

    結果、(別のシート)特別の計算式も多く使っていないような表を図でリンク貼り付けすると、コードの実行に時間が掛かるし、コードの対象となっているBOOKにそうした、リンクの図がなくても、同時に開いているBOOKにリンクの図が貼り付いていると、excel表のセルへの出力VBAコードの実行が遅くなるらしい。
    当面の対策は、リンク貼り付けをしないで図の貼り付けにするか、EXCELへの出力ルーチンを終えた後で、必要な箇所を図で貼り付けて整形し体裁を整えるsubルーチンを作れば良いということのようです。

    これまでアドバイスをありがとうございました。何とかここまで来ました。
    最終確認は後でします。
  • id:airplant
    解決して良かったですね。
    リンク図が問題だったのですね。

    確かめてみたら、、、何と! 確かにカメラツールで撮影したものがあると、オブジェクトへのアクセスが異様に遅い。
    通常のオートシェープは、あってもアクセスへは影響を与えないようです。
    勉強になりました。

    回答に書いたかどうか定かではないのですが、オブジェクトへのアクセスはやっぱり結構時間がかかる場合があります。
    今回のもオブジェクトが増えて、時間がかかるようになった気がします。

    配列にして処理が問題なければ、配列で処理したほうがいいですね。

    P.S. もし、よろしければ、もう役にたたないかもしれませんが、回答を開けてみてもらえると嬉しいです。遅くなるかも知れない要因を書いておきました。
    真ん中にあって、分かりにくかったのかも知れません。

    >ハ 1ループ毎の所要時間はなぜか増加します。
     → forの1ループ毎の時間と捉えていました。しかし、これは、マクロ1回毎のという意味だったのですね。
  • id:hathi
    forループの全体でも遅かったのですが、ループカウンタ1回毎の計測でも
    時間が掛かっていました。 
    (下記の.Cells(J+82,21)=Timer - huyの値が増加していたので)

    いま考えると、Cells(k,l)=4+変数 のようなコードを実行する度に
    なにかの時間が掛かっていたのではないかと思います。
     (何をしているのか分かりませんが、どこかにリンクのオブジェクト
      図があると、横道にそれてから戻って来るらしい)

    Mookさんのご指摘で、未読の回答があることが分かりました。
    回答頂いたairplantさんには失礼をしてしまいました。
    ご指摘ありがとうございました。


    >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秒です。前の実験よりステップは増えていますが、時間は短い。
  • id:Mook
    これは予想外な結果でしたが、
    何はともあれ解決したようで何よりです。

    確かに外部リンクがあると、処理に影響がありそうですね。
    私も勉強になりました。

    ほとんど自力で解決されたようなものですが、ポイントとイルカ賞ありがとうございました。

    追伸
    先の私のコメントで
     誤:意義はありません
     正:異議はありません
    でした。

    発音は同じでも、意味は正反対ですね(^^;;)。

この質問への反応(ブックマークコメント)

「あの人に答えてほしい」「この質問はあの人が答えられそう」というときに、回答リクエストを送ってみてましょう。

これ以上回答リクエストを送信することはできません。制限について

絞り込み :
はてなココの「ともだち」を表示します。
回答リクエストを送信したユーザーはいません