【Excel VBA】マクロの処理速度について

はじめまして。

マクロの処理速度について教えて頂きたいです。

現在、ボタンクリックにて実行される2つのマクロを作成しています。

1つ目のマクロで、ボタンクリックされると表1より転記にて表2を作成し、
最後に2つ目のマクロ(サブルーチン)を呼び出しています。

2つ目のマクロ(サブルーチン)では、作成した表2を参照して
更に別の表3を作成しています。

ボタンクリックでは無く直接、
「マクロ→マクロの表示→実行」で実行すると正しく処理されるのですが、
ボタンをクリックで実行すると、表3の作成が途中で止まっている？ようで正しく作成されません。
(表3は3行分作成されて止まりますが、マクロ終了時に表示される終了メッセージは表示されます)

素人の為、重いマクロを作成してしまっているのかもしれないのですが、
このような事象の解決策をご存知でしたらお願いいたします(>_<)

質問者からの補足コメント

• 

  現時点でのコードを追記いたします。
  
  ①異動元、異動先毎に分解して転記し、増減を表示するマクロ
  https://ideone.com/e.js/2F8zAK
  
  ②①で作成した表より、半期、クウォーター(1年を4分割)毎に増減をカウントして表示するマクロ
  https://ideone.com/e.js/M2DvWc
  
  何かお気づきの点があればご指摘頂けますと助かります(>_<)

    補足日時：2018/09/19 16:41

No.1 回答者： Zincer 回答日時： 2018/09/12 17:49

マクロの内容が不明のため正確な指摘はできませんが、

それらの表を表示しているシートはどのようになっていますか？
データのもと（表2ならば表1の表示されているシートなど）の参照はどのようになっているのでしょうか？
「sheets("シート名").cells･･･」のように常に特定しているのであれば問題ないのですが、「ActiveSheet」のような変化するシートを指定すると誤動作（想定していない動作）をすることがあります。
そのようなシートの指定をしていないか確認してみてください。

この回答へのお礼

ご回答頂きありがとうございます。
また、お礼が遅くなりまして申し訳ございません。

データの参照についてですが、各シートをWorksheetとして定義して、下記のように指定しています。

sh1と定義しているデータの内容は、このサブルーチンを呼ぶマクロで作成しているためその辺が
影響している？と思うのですが・・・

他の方に、コードを公開できるサイトを教えて頂いたので、後ほどこちらにコードを公開させて頂きます。

【マクロサンプル】
Sub bu_sub()
Dim sh1 As Worksheet
Dim sh2 As Worksheet
Dim sh3 As Worksheet

Set sh1 = Worksheets("元データ")
Set sh2 = Worksheets("参照データ")
Set sh3 = Worksheets("先データ")

'編集処理
sh3.Cells(row, "A").Value = sh1.Cells(v - 1, "A").Value
End Sub

お礼日時：2018/09/19 10:31

No.2 回答者： WindFaller 回答日時： 2018/09/13 11:02

こんにちは。

『ボタンクリックされると表1より転記にて表2を作成し、
－>2つ目のマクロ(サブルーチン)では、作成した表2を参照して
更に別の表3を作成しています。』
'--------------------------------------------
たぶん、シートオブジェクトがマクロごとに二重・三重になっているのではありませんか？
　　同じものなら、オブジェクトを引数で渡してやればよいはずです。

それと、最初のマクロ起動で、
　　Application.Calculation = xlCalculationManual
　　 Application.ScreenUpdating = False
　　　(終わったら戻したほうが良い)
と、画面を不活性にしていないのではないでしょうか。
　　他にも、
　　Application.EnableEvents = False
を使って、余計なイベント型マクロの介入をさせない方法もあります。

この回答へのお礼

こんにちは、ご回答頂きましてありがとうございます。
また、お礼が遅くなりまして大変申し訳ございません。

＞たぶん、シートオブジェクトがマクロごとに二重・三重になっているのではありませんか？
　　同じものなら、オブジェクトを引数で渡してやればよいはずです。

はい、マクロ①よりサブルーチンのマクロ②を呼んでいるのですが、シートの定義はそれぞれで行っています。
素人で申し訳ないのですが、オブジェクトとは、シートの中のデータという意味なのでしょうか？


＞それと、最初のマクロ起動で、
　　Application.Calculation = xlCalculationManual
　　 Application.ScreenUpdating = False
　　　(終わったら戻したほうが良い)
と、画面を不活性にしていないのではないでしょうか。
　　他にも、
　　Application.EnableEvents = False
を使って、余計なイベント型マクロの介入をさせない方法もあります。


画面不活性の指定(終了時に戻す処理も)を追記しました。

'設定オン
Application.Calculation = xlCalculationManual
Application.ScreenUpdating = False
　 Application.EnableEvents = False

　　　　　　　　　～　　　　

'設定オフ
Application.ScreenUpdating = True
Application.EnableEvents = True
Application.Calculation = xlCalculationAutomatic

色々試してみたのですが解決せず、また問題無いのであればコードを公開した方が良いのではとのご指摘も頂きましたので、公開準備中です。
本日中に公開予定ですので、お時間ございます際に確認していただけますと幸いです。

お礼日時：2018/09/19 11:00

No.3 回答者： tatsu99 回答日時： 2018/09/19 20:27

ソース拝見しました。

動作がおかしいのは、結論から言うとNo1の方の指摘通りです。
Sub bu_sub()の
最後のところの
Loop Until Cells(v, "A") = ""
が、本来、sh1(人員増減一覧)のセルを参照しないといけないのですが
sh1がない為、アクティブシートを参照しています。
従って、”人員増減一覧”を表示してマクロを実行すれば想定通りになるが、
ボタンをクリックした場合は、”実行マクロ”シートがアクティブシートの為、そのシートを参照してしまいます。
その為、誤動作します。

他にも重要な問題となるところがあります。
１．ボタン1_Click()
人員増減一覧のシートをクリアしていません。もし、前回実行した結果が１００行で、今回実行した結果が80行なら
81行～１００行はごみ（前回の残骸）が残ることになります。
最初にクリアが必要です。
２．Sub bu_sub()
ボタン1_Click()と同様です。但し、見出しはクリアしては、いけないので3行目以降をクリアします。

上記の修正版をこちらにのせておきました。
https://ideone.com/1ocER1

この回答へのお礼

ご回答頂きましてありがとうございます。

＞Loop Until Cells(v, "A") = ""
sh1がない為、アクティブシートを参照しています。

ご指摘の箇所を見落としていました。
シートの指定が無い場合、アクティブシートを参照するという知識が無かった為、ボタンクリックと直接マクロを実行した際の動きが違うことに理解が出来ず困っていました。大変勉強になりました。

＞人員増減一覧のシートをクリアしていません。

シートのクリアにつきまして、クリアしていない認識はあったのですが、クリアをしてしまうと書式設定や表の罫線が消えてしまうため盛り込めていませんでした。
3行目以降クリアの場合でも罫線と書式設定が消えてしまいますが、(書式設定で、部署別増減一覧のみ0は非表示としていました)マクロでクリア処理を行う場合、全クリアではなくブランクを挿入するイメージの記述等はありますでしょうか？

お礼日時：2018/09/20 15:49

No.4 ベストアンサー 回答者： tatsu99 回答日時： 2018/09/20 19:01

>シートのクリアにつきまして、クリアしていない認識はあったのですが、クリアをしてしまうと書式設定や表の罫線が消えてしまうため盛り込めていませんでした。

>3行目以降クリアの場合でも罫線と書式設定が消えてしまいますが、(書式設定で、部署別増減一覧のみ0は非表示としていました)マクロでクリア処理を行う場合、全クリアではなくブランクを挿入するイメージの記述等はありますでしょうか？

そういうことでしたら、
Set ws = Worksheets("人員増減一覧")
ws.Range("A1:I5000").Value = ""・・・・①


maxv = sh1.Cells(Rows.Count, "A").End(xlUp).Row
'②部署別増減一覧のクリア
If maxv > 2 Then
sh3.Range("A3:O" & maxv).Value = ""
End If

のようになります。
①は人員増減一覧の罫線を消さないクリアですが、最大行が５０００行の場合のように、予め行数がわかってる場合です。
②は部署別増減一覧の罫線を消さないクリアですが、実際にデータがある行までをクリアする方法です。

いずれにしろ、このような方法をとると、添付図の赤線で囲んだ箇所ができるのではないでしょうか。
罫線は、データがある行までのほうが良いかと思います。
つまり、赤線で囲んだところは、罫線がない方がよいかと思います。
そのようにする方法は、データを作成完了後に罫線をマクロで引く方法です。
これについては、
https://oshiete.goo.ne.jp/qa/10717384.html
「年度別にカウントする転記マクロについて」で回答します。

この回答へのお礼

お世話になっております。
お礼が遅くなりまして申し訳ございません。

①、②どちらの方法でも正しくクリアされましたが、
今回はデータの件数が決まっていない為②の最大行を求める方法の方が
合っていると思います。

別途「年度別にカウントする転記マクロについて」の方にもご回答いただいているということで、こちらは一旦締めさせて頂きます。

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

お礼日時：2018/09/25 15:22