実際には非常に一般的なケース: 10 つまたは複数のマクロを特定の時間または特定の頻度で実行する必要がある場合。 たとえば、XNUMX 分間更新する大きくて重いレポートがあり、朝、出勤する XNUMX 時間前に更新を実行したいとします。 または、指定した頻度で従業員に電子メールを自動的に送信するマクロがあります。 または、ピボットテーブルを操作するときに、XNUMX 秒ごとにオンザフライで更新する必要があります。
Excel と Windows がこれを実装できる機能を見てみましょう。
特定の頻度でマクロを実行する
これを行う最も簡単な方法は、組み込みの VBA メソッドを使用することです。 アプリケーション.オンタイム指定された時間に指定されたマクロを実行する です。 これを実際の例で理解しましょう。
タブの同じ名前のボタンで Visual Basic エディターを開きます 開発者 （デベロッパー） またはキーボードショートカット 他の+F11、メニューから新しいモジュールを挿入します インサート – モジュール そこに次のコードをコピーします。
Dim TimeToRun '次回の実行時間が格納されるグローバル変数 'これはメイン マクロです Sub MyMacro() Application.Calculate 'ブックを再計算します Range("A1").Interior.ColorIndex = Int(Rnd() * 56) 'fillランダムな色のセル A1 :) Call NextRun 'NextRun マクロを実行して、次回の実行時間を設定します End Sub 'このマクロは、メイン マクロの次回の実行時間を設定します Sub NextRun() TimeToRun = Now + TimeValue("00: 00:03") '現在の時刻に 3 秒を追加します Application.OnTime TimeToRun, "MyMacro" '次の実行をスケジュールします End Sub '繰り返しシーケンスを開始するためのマクロ Sub Start() NextRun を呼び出す End Sub '繰り返しシーケンスを停止するためのマクロSub Finish() Application.OnTime TimeToRun, "MyMacro", , False End Sub ここに何があるかを考えてみましょう。
まず、マクロの次の実行時刻を格納する変数が必要です – 私はそれを呼び出しました 実行時間. この変数の内容は、後続のすべてのマクロで利用可能でなければならないことに注意してください。 全体的な、つまり、最初のモジュールの前のモジュールの最初で宣言します サブ.
次はメインのマクロです マイマクロ、主なタスクを実行します – メソッドを使用してブックを再計算します アプリケーション.計算. わかりやすくするために、式 =TDATE() をセル A1 のシートに追加しました。これにより、日付と時刻が表示されます。再計算すると、その内容が目の前で更新されます (セルの秒の表示をオンにするだけです)。フォーマット）。 さらにお楽しみとして、セル A1 をランダムに選択した色で塗りつぶすコマンドもマクロに追加しました (色コードは 0..56 の範囲の整数で、関数によって生成されます)。 Rnd 整数関数に切り上げます int型).
マクロ ネクストラン 前の値に追加します 実行時間 さらに 3 秒待ってから、メイン マクロの次の実行をスケジュールします。 マイマクロ この新しい時間のために。 もちろん、実際には、関数の引数を設定することで、必要な他の時間間隔を使用できます 時間値 hh:mm:ss の形式で。
そして最後に、利便性のために、より多くのシーケンス起動マクロが追加されました。 ホーム そしてその完成 終了. 最後のものは、XNUMX 番目のメソッド引数を使用してシーケンスを中断します。 時間通りに 等しい ×.
マクロを実行した場合の合計 ホーム、次にこのカルーセル全体が回転し、シートに次の図が表示されます。
それぞれマクロを実行することで、シーケンスを停止できます。 終了. 便宜上、コマンドを使用して両方のマクロにキーボード ショートカットを割り当てることができます。 マクロ – オプション タブ 開発者 (開発者 — マクロ — オプション).
スケジュールに従ってマクロを実行する
もちろん、上記のすべては、Microsoft Excel を実行していて、ファイルが開いている場合にのみ可能です。 次に、より複雑なケースを見てみましょう。特定のスケジュールに従って Excel を実行する必要があります。職場に到着するまでに準備しておいてください 🙂
このような状況では、使用する方が良いです Windowsスケジューラ – 指定されたアクションをスケジュールに従って実行できる Windows の任意のバージョンに特別に組み込まれたプログラム。 実際、PC は定期的にアップデートをチェックし、新しいウイルス対策データベースをダウンロードし、クラウド フォルダーを同期するなど、知らず知らずのうちに既に使用しています。これらはすべてスケジューラーの仕事です。 したがって、私たちのタスクは、既存のタスクに、Excel を起動して指定されたファイルを開く別のタスクを追加することです。 そして、イベントにマクロを掛けます ワークブック_開く このファイル - そして問題は解決されました。
スケジューラを操作するには高度なユーザー権限が必要になる場合があることをすぐに警告したいので、オフィスの仕事用コンピューターで以下に説明するコマンドと機能が見つからない場合は、IT スペシャリストに連絡してください。
スケジューラの起動
それでは、スケジューラを開始しましょう。 これを行うには、次のいずれかを実行できます。
- ボタンを右クリック 開始 選択して コンピューター管理 （コンピュータマネジメント）
- コントロール パネルで選択します。 管理 – タスク スケジューラ (コントロール パネル — 管理ツール — タスク スケジューラ)
- メインメニューから選択 スタート – アクセサリ – システム ツール – タスク スケジューラ
- キーボードショートカットを押す Win+R、 入る taskschd.msc を押し 入力します
The following window should appear on the screen (I have an English version, but you can also have a version):
タスクを作成する
ステップバイステップの簡単なウィザードを使用して新しいタスクを作成するには、リンクをクリックします。 簡単なタスクを作成する (基本タスクの作成) 右側のパネル。
ウィザードの最初のステップで、作成するタスクの名前と説明を入力します。
ボタンをクリックします Next （次） 次のステップでは、トリガーを選択します。起動頻度またはタスクを起動するイベント (コンピューターの電源を入れるなど) です。
Hourlyを選択した場合は、 毎日 （毎日）、次のステップでは、特定の時刻、シーケンスの開始日、およびステップ (2 日ごと、5 日ごとなど) を選択する必要があります。
次のステップは、アクションを選択することです – プログラムを実行する (プログラムを開始):
最後に、最も興味深いのは、正確に開く必要があるものです。
プログラムまたはスクリプト （プログラム・台本） Microsoft Excel へのパスをプログラムとして入力する必要があります。つまり、Excel 実行可能ファイルに直接入力する必要があります。 Windows と Office のバージョンが異なる別のコンピューターでは、このファイルは別のフォルダーにある可能性があるため、その場所を見つけるいくつかの方法を次に示します。
- アイコン (ショートカット) を右クリックして、デスクトップまたはタスクバーで Excel を起動し、コマンドを選択します。 材料 （プロパティ）、次に開いたウィンドウで、行からパスをコピーします ターゲット:
- 任意の Excel ワークブックを開いてから、 Task Manager （タスクマネージャー） Ctrlキー+他の+ 行を右クリックして Microsoft エクセル、コマンドを選択 材料 （プロパティ）. 開いたウィンドウで、パスをコピーできます。 最後にバックスラッシュと EXCEL.EXE を追加することを忘れないでください:
- Excel を開き、キーボード ショートカットで Visual Basic エディターを開きます 他の+F11、パネルを開く 即時の の組み合わせ Ctrlキー+Gで、次のコマンドを入力します。
? アプリケーション.パス
…そしてクリック 入力します
結果のパスをコピーし、 最後にバックスラッシュと EXCEL.EXE を追加することを忘れないでください.
引数を追加する（オプション） (引数を追加 (オプション)) 開きたいマクロを含むブックへのフル パスを挿入する必要があります。
すべて入力したらクリック Next その後 終了 （終了）. タスクを一般リストに追加する必要があります。
作成したタスクは右側のボタンで管理すると便利です。 ここで、タスクをすぐに実行してテストできます （走る）指定時間待たずに。 タスクを一時的に無効にすることができます （無効）休暇中など、一定期間実行を停止します。 ボタンを使用して、いつでもパラメーター (日付、時刻、ファイル名) を変更できます。 材料 （プロパティ）.
ファイルを開くマクロを追加する
ここで、ファイルを開くイベントで必要なマクロの起動を本にぶら下げる必要があります。 これを行うには、ブックを開き、キーボード ショートカットを使用して Visual Basic エディターに移動します。 他の+F11 またはボタン ビジュアルベーシック タブ 開発者 （デベロッパー）. 左上隅に開くウィンドウで、ツリーでファイルを見つけ、ダブルクリックしてモジュールを開く必要があります 本書 (このワークブック).
Visual Basic エディターにこのウィンドウが表示されない場合は、メニューから開くことができます。 表示 — プロジェクト エクスプローラー.
開いたモジュール ウィンドウで、上部のドロップダウン リストからブックを開くイベント ハンドラーを選択して追加します。 ワークブック и Open、それぞれ：
手順テンプレートが画面に表示されます。 ワークブック_開く、行の間 プライベートサブ и End Subの また、この Excel ワークブックが開かれたときに自動的に実行される VBA コマンドを挿入する必要があります。これは、スケジューラがスケジュールに従ってワークブックを開くときに行います。 オーバークロックに役立ついくつかのオプションを次に示します。
- ThisWorkbook.RefreshAll – すべての外部データ クエリ、Power Query クエリ、およびピボットテーブルを更新します。 最も汎用性の高いオプション。 デフォルトで外部データへの接続を許可し、リンクを更新することを忘れないでください ファイル – オプション – セキュリティ センター – セキュリティ センター オプション – 外部コンテンツ、そうしないと、本を開くと標準の警告が表示され、Excelは何も更新せずに、ボタンをクリックするという形であなたの祝福を待ちます コンテンツを有効にする （コンテンツを有効にする）:
- ActiveWorkbook.Connections(“接続名”).Refresh — Connection_Name 接続のデータを更新しています。
- Sheets("Sheet5“).PivotTables(“ピボットテーブル1«).PivotCache.Refresh – という名前の単一のピボット テーブルを更新する ピボットテーブル1 シートに Sheet5.
- アプリケーション.計算 – 開いているすべての Excel ワークブックの再計算。
- Application.CalculateFullRebuild – すべての数式を強制的に再計算し、開いているすべてのワークブックのセル間のすべての依存関係を再構築します (すべての数式を再入力するのと同じです)。
- Worksheets(“レポート”).PrintOut – プリントシート 写真.
- MyMacroに電話する – という名前のマクロを実行します マイマクロ.
- ThisWorkbook.Save – 現在の本を保存
- ThisWorkbooks.SaveAs “D:ArchiveReport” & Replace(Now, “:”, “-”) & “.xlsx” – 本をフォルダに保存する D:アーカイブ 名前の下で 写真 名前に日付と時刻が追加されます。
スケジューラによってファイルが午前 5:00 に開かれたときにのみマクロを実行し、ユーザーが営業日にワークブックを開くたびに実行したくない場合は、次のように時間チェックを追加するのが理にかなっています。
If Format(Now, "hh:mm") = "05:00" Then ThisWorkbook.RefreshAll
それで全部です。 ワークブックをマクロ有効形式 (xlsm または xlsb) で保存することを忘れないでください。Excel を安全に閉じて、コンピューターの電源を入れたまま家に帰ることができます。 特定の時点で (PC がロックされている場合でも)、スケジューラは Excel を起動して指定されたファイルを開き、マクロがプログラムされたアクションを実行します。 そして、あなたの重いレポートが自動的に再計算される間、あなたはベッドで贅沢になります – 美しさ! 🙂
- マクロとは、マクロの使用方法、Excel で Visual Basic コードを挿入する場所
- Excel 用の独自のマクロ アドインを作成する方法
- 個人用マクロ ブックを Excel のマクロのライブラリとして使用する方法