【Excel　VBAマクロを実行するための初期設定】(４回目)

カテゴリ:

公開日: 2024/3/29

前回、次回は長いマクロを書くとお話しましたが、その前にマクロを書く中で実施しておくと、便利なコードをお伝えしていきたいとおもいます。

その後に少し長いコードも実施していければとおもいます。

実施しておくと便利なコードとは、４つの停止マクロです。

その４つ停止を事前におこなっておけば、マクロは早く動くようになるので、高速化をするのによく使われます。

なぜ、高速化をするかというと、ある程度長い処理を実施した場合、高速化を実施しているとしていないのとでは、実行する時間が全然違うからです。

すごく違う時は、１０分で終わるマクロも高速化すると半分以下になったりします。

便利なコードなので、初心者の方はスタートから書くようにするといいと思います。

1. 「Application.ScreenUpdating」の説明と事前準備

「Application.ScreenUpdating」の説明を行いたいと思います。

「Application.ScreenUpdating」は、画面を制御するためのマクロになります。

まずは「Application.ScreenUpdating」を実行していないときの画面を止めない動きを試していきたいと思います。

読んでいる方にも、実行していただけるようにコードも合わせて記載させていただきます。

①前回実施したマクロボタンを作成します

テストを実施するだけなので、ボタンはそのままにしています。

②「ボタン１」を右クリックし、「マクロの登録」画面から「新規作成」ボタンをクリックします

③「Workbooks.Add」を１０回書きます

「Workbooks.Add」の意味は、workbookを新規で作成することです。

Option Explicit

Sub ボタン1_Click()

Dim st As Double, et As Double
st = Timer

Workbooks.Add
Workbooks.Add
Workbooks.Add
Workbooks.Add
Workbooks.Add
Workbooks.Add
Workbooks.Add
Workbooks.Add
Workbooks.Add
Workbooks.Add

et = timer - st
msgbox et

End Sub

［補足］：「Time」の変数を書いてありますが、これを書いておくと、マクロが終了後にどのくらいの時間で完了したかが出力されます。

「st = Timer」はスタートの時間を取得し変数に格納します。

「et = timer - st」終了した時間とスタートの時間を引き算し変数に格納します。

最後にメッセージボックスに計算した時間が表示されます。

④書き終わったら、「ボタン１」をクリックします。

⑤「Book1」から「Book10」までが画面で一つづつ開いたことを確認してください

ここで確認してほしいのは、１つづつファイルが開いたことです。

開いた10個のファイルは一旦閉じてください。

また、メッセージボックスに時間が表示されていると思いますので、覚えておいてください。

2. 「Application.ScreenUpdating」の作成

次に「Application.ScreenUpdating」を書いていきます。

①「Application.ScreenUpdating = False」をはじめに記載し、「End Sub」の前に「Application.ScreenUpdating = True」を記載していきます。

「Application.ScreenUpdating = False」は、画面停止です。

「Application.ScreenUpdating = False」は、画面実行です。

先ほどのコードに書いていきます。

Option Explicit

Sub ボタン1_Click()

Application.ScreenUpdating = False

Dim st As Double, et As Double
st = Timer

Workbooks.Add
Workbooks.Add
Workbooks.Add
Workbooks.Add
Workbooks.Add
Workbooks.Add
Workbooks.Add
Workbooks.Add
Workbooks.Add
Workbooks.Add

et = Timer - st
MsgBox et

Application.ScreenUpdating = True

End Sub

②書き終わったら、「ボタン１」をクリックします。

③先ほどが一つ一つファイルが開かれたのに対して、一括でExcelが10ファイル開いたと思います。

10個のファイルは閉じてください。

実際は10個のファイルと少ないファイル数でしたので、少ししか差異がなかったと思いますが、「Application.ScreenUpdating 」を書いた後の方が、完了した時間が早かったと思います。

3. 「Aplication.Calculation」の説明と事前準備

「Application.Calculation」の説明を行いたいと思います。

「Application.ScreenUpdating」は、計算方法を変更するためのマクロになります。

まずは「Application.ScreenUpdating」で自動計算をしている時の動きを試していきたいと思います。

読んでいる方にも、実行していただけるようにコードも合わせて記載させていただきます。

①前回実施したマクロボタンをそのまま使用します。

テストを実施するだけなので、ボタンはそのままにしています。

右クリックし、「マクロの登録」-「編集」をクリックします。

②このままマクロを変更していきます。

「Application.ScreenUpdating = False」と「Application.ScreenUpdating = True」はコメントアウトしてください。

「Workbooks.Add」はすべて削除してください。

削除するとこんな感じです。

③②の画面の「'②のコードはここから下に記載」より下に以下コードを記載します。

E列の1行から5000行まで計算式の「=2+5」を入れるというコードになります。

説明もなく、いきなり長くなってしまいましたが、テストするには必要なので、コピーして使用してください。

Dim i As Long
Dim ws As Worksheet
Set ws = Worksheets("Sheet1")

For i = 1 To 5000

ws.Cells(i, "E") = "= 2 + 5 "

Next

上記のコードをすべて書くと以下のような感じになります。

④書き終わったら、「ボタン１」をクリックします。

E列に「７」の数字が出ていると思いますのでE列を選択し、列ごとクリアしてください。

また、メッセージボックスに時間が表示されていると思いますので、覚えておいてください。

4. 「Application.Calculation」の作成

次に「Application.Calculation」を書いていきます。

①「'Application.ScreenUpdating = Fale」のコメントのすぐ下に「Application.Calculation = xlCalculationManual」のコードを書きます。

次に「’Application.ScreenUpdating = True」のすぐ下に「pplication.Calculation = xlCalculationAutomatic」のコードを書きます。

「Application.ScreenUpdating = True」は、手動計算です。

「Application.Calculation = xlCalculationAutomatic」は、自動計算です。

全部書くとこんな感じになります。

Sub ボタン1_Click()

Dim st As Double, et As Double
st = Timer

'Application.ScreenUpdating = False
Application.Calculation = xlCalculationManual

Dim i As Long
Dim ws As Worksheet
Set ws = Worksheets("Sheet1")

For i = 1 To 5000

ws.Cells(i, "E") = "= 2 + 5 "

Next

et = Timer - st
MsgBox et

'Application.ScreenUpdating = True
Application.Calculation = xlCalculationAutomatic

End Sub

②書き終わったら、「ボタン１」をクリックします。

③メッセージボックスが先ほどと同様に出力され、「Application.Calculation」を記載した後の方がマクロの実行時間が早くなっていると思います。

［補足］このコード説明がありませんでしたのでコメントで記載させていただきます。

Dim i As Long　’「i」を数字の変数とする
Dim ws As Worksheet　「ws」をワークシートの変数にする
Set ws = Worksheets("Sheet1")　’「Sheet1」シートを「ws」に代入する

For i = 1 To 5000　’Forは「1」から「5000」まで「i」にいれていくという意味になります。

ws.Cells(i, "E") = "= 2 + 5 "　「=2+5」の数字気を「i」行にいれていれていく。

Next　’次へ

1行目から5000行目まで「=2+5」が入力されるマクロになります。

5. 最後に

「Application.ScreenUpdating」と「Application.Calculation」を実行してみて、私の環境では時間が約半分になりました。

他のコードでも試してみましたが、計算とかが多めに入っているシートや他シートを交互に処理を行うものは今日書いた２つのコードがあると半分くらいの時間短縮になりました。

次回残りの２つを紹介していきたいとおもいます。