VBA に関する質問です。

基本だけど
Application.ScreenUpdating = False
はやってますか？

データ取得時(①～②)に入れてるんですが、
③のタイミングでセルを書き換える→シートの計算をするので、いったんONにしないといけないんです。
切ったり入れたりしてるんで変だとは思ってます…。

↑自己レスです。
何言ってるんでしょうか。
画面だけ止めればいいんですよね。おっしゃる通り。
画面の更新は止まっててもいいはず。
自動計算と画面の更新をごっちゃにしてました。

ほかにもあればお願いします。

横から、失礼ます。
[Excel 自動更新 遅い]で検索すると、
http://www.relief.jp/itnote/archives/002327.php
は、グラフが付いていると遅い？
で、
テストで、
http://www.relief.jp/itnote/archives/000046.php
みたいに、自動計算をOFF(手動、F9を押すにして)
処理が速かったら、
[Excel 自動更新 VBA] で 検索すると、
リンクの更新 ActiveWorkbook.RefreshAll
や 再計算 Calculate Or ActiveSheet.Calculate
とか転がっていると思います。

んっ、あっ、
>④式を消すためにコピー＆ペースト
↑計算後に式を消したいので(なぜだか分りませんが)、自動計算じゃないと正しい値がセットされないのか
？
もしかして、逆にループ内に、念のためActiveSheet.Calculateとか全体を再計算させながら回している部分があるとか？

なんて、勝手な妄想・推理ゲームになってしまうので、遅そうな該当箇所のコードをアップした方が達人から良いアドバイスがつくのでは？(※でも、ソースを丸ごと開示すると、丸投げかよ と 思われたり、人の心もソースコード同様に複雑なのねって違うか(ぉぃぉぃ))

長々書きましたが、一言で言うと
[再計算]が怪しいのでは、Calculate で検索してみると例が載っているのでは？
の１行でした。

無事に解決されることを願いつつ、コメントで逃げるように失礼します。

お二方とも、せっかく回答いただいてるので上にコピペしていただけると嬉しいです。

現在4分ほどかかるので、どれだけ短くなるか試してみます。

もしかして、
http://www.happy2-island.com/excelsmile/smile04/capter00202.shtml
をみると、まとまっているのかも？
あと、部分コピーでまわしているのを
Range("D2").Select
Selection.Copy
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
:=False, Transpose:=False
を
シート全体を一度に変換みたいに
Cells.Select
Selection.Copy
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
:=False, Transpose:=False
Range("A1").Select
だと、速さが違うとか？

工夫するのでしたら
＞④式を消すためにコピー＆ペースト
の部分かな？


ここで どのようなことをやってるのか ソースを提示してもらえれば
速度アップに直接つながる回答ができると思います。

Application.ScreenUpdating = False
画面更新停止で1分程度短くなったようです。

④式を消すためにコピー＆ペースト
これについて
シートを2枚使っているんですが、
1枚目に式が700個
2枚目に式が1100個ぐらいあるんですよ。
これを単純に20組増やすと自動保存で泣きそうになります。

データベースに5回クエリを投げる。
それぞれの結果(帳票のソース)をそれぞれのシートに貼り付ける。
それをVLOOKUPやHLOOKUP、INDIRECTなんかを組み合わせて入れ替えてるんです。
1セル替えれば2000個近い式変えられるんです。
生み出したシートには式が不要(親が変わるとリンクが切れる)なので、リソースの確保のためにも削除してます。

基幹システムで出される帳票をexcelにしたいという要望があり自分で作ってみた次第です。
意外に便利ですよ。1度作ってその担当者に投げたら勝手にやってくれるんで。

独学なんでまだまだです。
そのうえ、同時にPHPいじったりVBやってみたりflash試してみたりでして
記憶がぐちゃぐちゃになってます。

コピペの部分ですが、単純ですよ。

Sub 初期設定_ボタン8_Click()

Application.ScreenUpdating = False


Dim tenpocd As Variant
Dim i As Integer
Dim m As Integer

tenpocd = Array("1", "2", "3", "4", "5", "6", "7", "8", "9", _
"と続きましてトータル21個(抜けもある)" _
"99")
m = UBound(tenpocd)

While i < m


Application.Calculation = xlCalculationManual


With Worksheets("店舗A")
.Range("A1").Value = tenpocd(i)
End With

'アクティブになったときだけ計算させることにしました。

Worksheets("店舗A").Calculate
Worksheets("店舗B").Calculate

ThisWorkbook.Sheets("店舗A").Copy after:=Worksheets(Worksheets.Count)
ActiveSheet.Name = tenpocd(i) & "A"

Cells.Select
Selection.Copy
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
:=False, Transpose:=False
Range("A1").Select
ThisWorkbook.Sheets("店舗B").Copy after:=Worksheets(Worksheets.Count)
ActiveSheet.Name = tenpocd(i) & "B"
Cells.Select
Selection.Copy
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
:=False, Transpose:=False
Range("A1").Select
i = i + 1
Wend


Application.ScreenUpdating = True


End Sub

Worksheets("店舗A").Calculateで必要か所で計算を行うことで早くなりました。
Application.Calculation = xlCalculationAutomaticにするとブック全体更新しちゃうのかも。

とりあえず現状1分程度になりました。

皆さん、一言二言でも回答欄に書いていただけると嬉しいです。

ken3memo さんのコメント見て汗が。

Worksheets("店舗A").Calculate
Worksheets("店舗B").Calculate
だけじゃ足りなかった。
処理シート(中間テーブルみたいなもの)を更新し忘れてました。
危険な匂いがします。

よくよく見たら いらぬ回答でした。

takntさん

いえいえそんなことないですよ。
とりあえず1分内に収まったのですが、試しに必要な分にしてみようと思いました。
今のものだと全エリアをコピペしてますんでもしかしたら早くなるかも！


自分の質問をよく見てみたらまずい点があることに気が付きました。
①ExcelでDBへ接続
②データをそれぞれのシートに貼り付け
③BVAで拠点のコードに書き換え
★ここに、シートの複製×2が抜けてました。
④式を消すためにコピー＆ペースト(ここもVBA)


あ、BVAって。

で ソースを良く見て 思ったのですが、まず どこで時間がかかってるのか調べることが重要ですよね。

セルに 時刻を入れて 時刻の推移を調べたらいいです。
1番目のセル
2番目のセルと言う具合に。

一番ネックとなりそうなのが シートのコピー。
最初からコピーしておいて 処理はできないのか？
など 処理方法の検討をしてみたらいいかと思います。

再計算処理で 時間がかかってるならば VBAで計算して 値をセットするようにして
ワークシートの数式を使わないようにするなど。

これも やってみてどうかぐらいしか いえませんが・・・。

なるほど。
VBAの間で時間をとるって発想面白いですね。
試してみます。