あなたのマクロをスピードアップする10の方法 - あなたのExcelマクロが堅牢で複雑になるにつれて、ダミー

Excelマクロが堅牢で複雑になるにつれ、パフォーマンスが低下することがあります。マクロについて議論するとき、 パフォーマンス という単語は、通常 速度 と同義語です。スピードとは、VBAプロシージャが目的のタスクをどのくらい迅速に実行するかです。以下は、Excelマクロを最適なパフォーマンスレベルで実行するための10の方法です。

シート計算を停止する

<！ - 1 - >

スプレッドシートの式に影響するセルが変更または操作されるたびに、Excelがワークシート全体を再計算することを知っていますか？多くの数式を含むワークシートでは、この動作によりマクロが大幅に遅くなる可能性があります。

あなたはアプリケーションを使うことができます。手動計算モードに切り替えるようExcelに指示するための計算プロパティ。ブックを手動計算モードにすると、F9キーを押して明示的に計算をトリガするまで、ブックは再計算されません。

<！ - 2 - >

Excelを手動計算モードにしてコードを実行し、自動計算モードに戻ります。

Sub Macro1（）アプリケーション。 Calculation = xlCalculationManual 'ここにマクロコードを配置します。 Calculation = xlCalculationAutomatic End Sub

計算モードをxlCalculationAutomaticに戻すと、自動的にワークシートの再計算が行われるため、マクロの実行後にF9キーを押す必要はありません。

<！ - 9 - >

シート画面の更新を無効にする

マクロを実行すると、画面がかなりちらつくことがあります。このちらつきは、ワークシートの現在の状態を表示するために画面を再描画しようとするExcelです。残念ながら、Excelは画面を再描画するたびに、メモリリソースを消費します。

あなたはアプリケーションを使うことができます。 ScreenUpdatingプロパティを使用して、マクロが完了するまで画面の更新を無効にします。画面の更新を無効にすると、時間とリソースが節約され、マクロを少し速く実行できます。マクロコードの実行が終了すると、画面の更新を元に戻すことができます。

Sub Macro1（）アプリケーション。計算= xlCalculation手動アプリケーション。 ScreenUpdating = False 'ここにマクロコードを配置します。計算= xlCalculationAutomaticアプリケーション。 ScreenUpdating = True End Sub

ScreenUpdatingプロパティをTrueに戻すと、Excelは自動的に画面の再描画をトリガします。

ステータスバーの更新をオフにする

Excelウィンドウの下部に表示されるExcelのステータスバーには、通常、特定のアクションの進行状況がExcelで表示されます。マクロが大量のデータを処理している場合、ステータスバーにはいくつかのリソースが使用されます。

画面の更新をオフにすることは、ステータスバーの表示をオフにすることとは別物であることに注意することが重要です。画面更新を無効にしてもステータスバーは更新され続けます。アプリケーションを使用することができます。 DisplayStatusBarプロパティを使用してステータスバーの更新を一時的に無効にし、マクロのパフォーマンスをさらに向上させることができます。

Sub Macro1（）Application。計算= xlCalculation手動アプリケーション。 ScreenUpdating = Falseアプリケーション。 DisplayStatusBar = False 'ここにマクロコードを配置します。計算= xlCalculationAutomaticアプリケーション。 ScreenUpdating = Trueアプリケーション。 DisplayStatusBar = True End Sub

Excelにイベントを無視するように伝える

イベントプロシージャとしてマクロを実装し、ワークシートまたはブックの変更時に特定のコードを実行するようにExcelに指示できます。

時々、標準マクロはイベントプロシージャをトリガする変更を行います。たとえば、Sheet1の複数のセルを操作する標準マクロがある場合、そのシート上のセルが変更されるたびに、Worksheet_Changeイベントの実行中にマクロが一時停止する必要があります。

EnableEventsプロパティを使用して、マクロの実行中にイベントが無視されるようにExcelに指示することで、パフォーマンスをさらに高めることができます。

マクロを実行する前に、EnableEventsプロパティをFalseに設定します。マクロコードの実行が終了したら、EnableEventsプロパティをTrueに戻すことができます。

Sub Macro1（）アプリケーション。計算= xlCalculation手動アプリケーション。 ScreenUpdating = Falseアプリケーション。 DisplayStatusBar =誤ったアプリケーション。 EnableEvents = False 'ここにマクロコードを配置します。計算= xlCalculationAutomaticアプリケーション。 ScreenUpdating = Trueアプリケーション。 DisplayStatusBar = Trueアプリケーション。 EnableEvents = True End Sub

ページ区切りを隠す

行数を変更したり、列の数を変更したり、ワークシートのページ設定を変更したり、Excelが表示されたページ区切りを再計算するのに時間がかかるシート上に

マクロを開始する前にページ区切りを隠すだけで、この動作を回避できます。

ページ区切りを非表示にするには、DisplayPageBreaksシートプロパティをFalseに設定します。マクロの実行後に改ページを表示したい場合は、DisplayPageBreaksシートプロパティをTrueに戻します。

Sub Macro1（）アプリケーション。計算= xlCalculation手動アプリケーション。 ScreenUpdating = Falseアプリケーション。 DisplayStatusBar =誤ったアプリケーション。 EnableEvents = Falseアクティブシート。 DisplayPageBreaks = False 'ここにマクロコードを配置します。計算= xlCalculationAutomaticアプリケーション。 ScreenUpdating = Trueアプリケーション。 DisplayStatusBar = Trueアプリケーション。 EnableEvents = Trueアクティブシート。 DisplayPageBreaks = True End Sub

ピボットテーブルの更新の中断

大きなデータソースを含むピボットテーブルをマクロが操作する場合、ピボットフィールドを動的に追加または移動するときにパフォーマンスが低下することがあります。

すべてのピボットフィールドの変更が行われるまで、ピボットテーブルの再計算を中断することにより、マクロのパフォーマンスを向上させることができます。ピボットテーブルを設定するだけです。 ManualUpdateプロパティをTrueに設定して再計算を延期し、マクロコードを実行してピボットテーブルを設定します。 ManualUpdateプロパティをFalseに戻して、再計算をトリガーします。

Sub Macro1（）ActiveSheet。ピボットテーブル（ "ピボットテーブル1"）。 ManualUpdate = True 'あなたのマクロコードをActiveSheetに置きます。ピボットテーブル（ "ピボットテーブル1"）。 ManualReport = False End Sub

コピー&ペーストの操縦

マクロレコーダーはVBAコードを書くことで時間を節約できますが、最も効率的なコードを書くとは限りません。マクロレコーダーが録音中に実行するコピーアンドペーストのアクションをキャプチャする方法の代表例があります。

仲介人を切り抜いて、あるセルから宛先セルに直接コピーを実行することで、マクロにわずかな向上を与えることができます。この代替コードでは、Destination引数を使用してクリップボードをバイパスし、セルA1の内容をセルB1に直接コピーします。

範囲（ "A1"）。コピー先：=範囲（ "B1"）

（書式や数式ではなく）値のみをコピーする必要がある場合は、Copyメソッドをすべて一緒に避けることで、パフォーマンスをさらに向上させることができます。宛先セルの値をソースセル内の同じ値に設定するだけです。このメソッドは、Copyメソッドを使用するよりも約25倍高速です：

Range（ "B1"）。値=範囲（ "A1"）。値

1つのセルから別のセルに式のみをコピーする必要がある場合（値または書式ではなく）、宛先セルの式をソースセルに含まれる同じ数式に設定できます。

）。数式=範囲（ "A1"）。式

Withステートメントの使用

マクロを記録するときに、同じオブジェクトを複数回操作することがよくあります。 Withステートメントを使用して、特定のオブジェクトに対して複数のアクションを1回実行することで、時間を節約し、パフォーマンスを向上させることができます。

次の例で使用されるWithステートメントは、すべての書式変更を一度に適用するようにExcelに指示します。

With Range（ "A1"）。フォント。ボールド= True。イタリック= True。 Underline = xlUnderlineStyleSingle End With

Withステートメントでアクションをチャンクするのは、マクロの実行を速くするだけでなく、マクロコードを読みやすくするためです。

Selectメソッドの回避

Macro Recorderは、Selectメソッドを使用してオブジェクトを明示的に選択してからアクションを実行することが大好きです。一般的にオブジェクトを選択する必要はありません。実際、Selectメソッドを使用しないことでマクロのパフォーマンスを大幅に向上させることができます。

マクロを記録したら、生成されたコードを変更してSelectメソッドを削除することを習慣にします。この場合、最適化されたコードは

Sheets（ "Sheet1"）のようになります。範囲（ "A1"）。 Formula1C1 = "1000" Sheets（ "Sheet2"）。範囲（ "A1"）。 Formula1C1 = "1000" Sheets（ "Sheet3"）。範囲（ "A1"）。 Formula1C1 = "1000"

何も選択されていないことに注意してください。このコードでは、オブジェクト階層を使用して必要なアクションを適用するだけです。

ワークシートへのトリップの制限

マクロの速度を上げるもう一つの方法は、コード内のワークシート・データを参照する時間を制限することです。メモリからではなく、ワークシートからデータを取得することは常に効率的ではありません。つまり、ワークシートと繰り返し対話する必要がなければ、マクロは非常に高速に実行されます。

たとえば、次の単純なコードは、VBAがSheets（ "Sheet1"）に継続的に戻るようにします。 Range（ "A1"）：Ifステートメントで比較を実行するために必要な数値を取得します。

For ReportMonth = 1 To 12 Range（ "A1"）。 Value = ReportMonth Then MsgBox 1000000 / ReportMonth End If Next ReportMonth

はるかに効率的な方法は、シート（ "シート1"）の値を保存することです。範囲（ "A1"）を変数MyMonthに設定します。この方法で、コードはワークシートの代わりにMyMonth変数を参照します。

Dim MyMonth as Integer MyMonth = Range（ "A1"）。 ReportMonthの値= 1〜12 If MyMonth = ReportMonth Then MsgBox 1000000 / ReportMonth End If Next ReportMonth

ワークシートを直接参照するのではなく、変数を利用してメモリ内のデータを処理することを検討してください。