VBA に関する質問です。


Excelで帳票を作っているのですが、20拠点分を一括でやってます。

現状
①ExcelでDBへ接続
②データをそれぞれのシートに貼り付け
③BVAで拠点のコードに書き換え
④式を消すためにコピー&ペースト
③~④を必要な数だけ自動で繰り返します。

実行速度にまだ満足できず、質問に至りました。
①~②はすぐ終わるのですが、コピー&ペーストで時間がかかってるように思います。
シートの式を値に変える方法で、オススメな方法があったら教えてください。
※PCのスペックアップ以外でお願いします。

回答の条件
  • 1人2回まで
  • 13歳以上
  • 登録:2011/04/10 17:48:32
  • 終了:2011/04/12 10:28:02

回答(2件)

id:taknt No.1

きゃづみぃ回答回数13539ベストアンサー獲得回数11982011/04/10 20:12:38

ポイント70pt

速くするコツです。

Cells.Select
Selection.Copy
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
:=False, Transpose:=False
Range("A1").Select

というソースの場合は いちいち セルを選択(Select)してたら遅くなります。

ま、ここらへんは 表示をやめればいいのかもしれませんが

プログラミングとしては おすすめしません。

これを 以下のようにすればいいのです。

 Range("A1:F10").Copy
 Range("A1:F10").PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks:=True

範囲は "A1:F10"と適当に指定していますが、一つずつのセルを 処理するのではなく

シート全体の範囲を指定して 一回で やったほうが 速いかと思います。

id:kia_44

シート全体と必要分(1発で指定できる範囲)で試してみたいと思います。

2011/04/11 13:51:48
id:readmaster No.2

readmaster回答回数74ベストアンサー獲得回数52011/04/11 02:09:26

ポイント70pt

お役に立てるかなと思い回答致します。

1の方の指摘は最もかと思いますので、プラスアルファで。


具体的なコードは書けないのですが、2つほど。

まず1つは、

「③~④を必要な数だけ自動で繰り返します。」

この処理は、

「必要な数」とは、例えば20拠点が全てだとして、

例えば10拠点とか3拠点とか、必要な分をどこかで判断しているかと思います。

ループ分でいえば、回数を変数指定したり、またはセルに直接入力している(20とか)かもしれません。

もし判断されていないのであれば、20拠点全部回っているので、その部分は処理を少なくできそうです。


あともう1つは、

「④式を消すためにコピー&ペースト」

式を消す部分が固定なら値も固定できそうです。

当然、変数よりも固定値のほうが処理は早くなるかと思います。

あと、消す処理もいくつかありそうです。どれが早いかを計測してみるのも面白そうです。


参考までに、

処理を早くするには、

1)処理を減らす

2)効率的な処理にする(関数やロジック)

3)一度使った変数をなるべく使う(無駄にクリアしない)

などかなと考えられます。


回答になってないかもしれませんが、以上です。

id:kia_44

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

独学でやってる上に一人でちまちま進めてるんでコツといったものに行きつくまでに時間がかかっております。

今回の質問で、3分も短くなりました。

›あと、消す処理もいくつかありそうです。どれが早いかを計測してみるのも面白そうです。

これについて、何らかの具体案があればぜひお願いします。

今のところ

シートを複製して、全エリアコピー&ペーストでやってます。

2011/04/11 14:28:33
  • id:SALINGER
    基本だけど
    Application.ScreenUpdating = False
    はやってますか?
  • id:kia_44
    データ取得時(①~②)に入れてるんですが、
    ③のタイミングでセルを書き換える→シートの計算をするので、いったんONにしないといけないんです。
    切ったり入れたりしてるんで変だとは思ってます…。
  • id:kia_44
    ↑自己レスです。
    何言ってるんでしょうか。
    画面だけ止めればいいんですよね。おっしゃる通り。
    画面の更新は止まっててもいいはず。
    自動計算と画面の更新をごっちゃにしてました。

    ほかにもあればお願いします。
  • id:ken3memo
    横から、失礼ます。
    [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 で検索してみると例が載っているのでは?
    の1行でした。

    無事に解決されることを願いつつ、コメントで逃げるように失礼します。
  • id:kia_44
    お二方とも、せっかく回答いただいてるので上にコピペしていただけると嬉しいです。

    現在4分ほどかかるので、どれだけ短くなるか試してみます。
  • id:ken3memo
    もしかして、
    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
    だと、速さが違うとか?

  • id:taknt
    工夫するのでしたら
    >④式を消すためにコピー&ペースト
    の部分かな?


    ここで どのようなことをやってるのか ソースを提示してもらえれば
    速度アップに直接つながる回答ができると思います。
  • id:kia_44
    Application.ScreenUpdating = False
    画面更新停止で1分程度短くなったようです。

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

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

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

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

  • id:kia_44
    コピペの部分ですが、単純ですよ。

    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分程度になりました。


  • id:kia_44
    皆さん、一言二言でも回答欄に書いていただけると嬉しいです。
  • id:kia_44
    ken3memo さんのコメント見て汗が。

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

  • id:taknt
    よくよく見たら いらぬ回答でした。
  • id:kia_44
    takntさん

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


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


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

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

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

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

    これも やってみてどうかぐらいしか いえませんが・・・。
  • id:kia_44
    なるほど。
    VBAの間で時間をとるって発想面白いですね。
    試してみます。

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

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

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

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