Excel VBA業務効率化

あなたのExcel定型業務をVBAで効率化、省力化をお手伝いします。

詳細はこちら

VBA練習帳、超初心者向けVBA解説、繰り返し処理を使ってプログレスバーを作る。操作の進捗状況を表示するプログラムを作ります。

エクセルVBA
この記事は約10分で読めます。

プログレスバー表示

■ 作業の流れ

エクセルでVBAを作っていると、例えばある操作で何らかの計算をした結果を知らせるプロシージャを作ったとして、そのプロシージャの処理ははほとんどの場合その処理はほんの一瞬で終わってしまいますが、まれに(その処理の内容によっては)何秒か(1~数秒)かかる場合があります。

その場合、エクセルの表示上では画面が静止状態になりフリーズしたかのようになります。1,2秒なら何とかなりますが、それが数十秒、数分に及ぶ場合は困った状況になります。

そういう場合プログレスバーを表示して処理の進捗状況を知らせることでユーザーに安心感を与えることができます。

そこで今回はそのプログレスバーを表示するプログラムを作ってみます

手順は次のようになります

コマンドボタンを押す重い処理を再現進捗の表現フォームを作る動作の仕上げ

■ シートにコマンドボタンを貼り付ける

まず最初に何らかの処理を開始させるためのボタンを作ります

下図のようにエクセルの一番上にある赤囲みのツールバーの部分をマウスでポイントして右クリックします。そこにメニューが表示されるので、メニュー最上部の「クイックアクセスツールバーのユーザー設定(C)」をクリックします。

次に下図の設定画面になるので、左側上部にある「コマンドの選択(C)」のところで
【すべてのコマンド】を選択してからその下のリストの中から【コントロールの挿入】を探してクリックしたら「追加(A)>>」ボタンを押します。右側のリストに【コントロールの挿入】が追加されたので「OK」ボタンを押して設定画面を閉じます。

次にエクセル上部のツールバーの最後に先ほどの【コントロールの挿入】アイコンが表示されているので、それをクリックします。

次にメニューリストにシートに貼り付けられるコントロールのリスト中の赤まるの【コマンドボタン】をクリックします。

上図で【コマンドボタン】コントロールをクリックしたことで、エクセルはその動作を記憶したのです。

なので、シートの適当な場所をクリックしたままドラックして長方形を形成します。するとコマンドボタンが配置できました。ボタンをクリックして抑えたままマウスを引きずるとボタンを好きな場所に移動できます。

 

次にこのコマンドボタンをクリックすることで何らかの処理を開始するように設定します。

エクセル上部のツールバーの中の「デザインモード」アイコンをクリックすると、コマンドボタンの大きさを変えたり配置を移動させたりできるようになります。この状態でボタンをダブルクリックするとVBAの編集画面が表示されます

デザインモードのアイコンがないときは、一番最初にやったコントロールの追加のやり方を参考にして追加してください。)

Private Sub CommandButton1_Click() と End Sub の間に何らかの処理を記述します。

同様にしてコマンドボタンを更に3個貼り付けます

コマンドボタン1に重い処理を書く

デザインモードでボタンCommandButton1をダブルクリックしてVBAコードエディターを開く

Private Sub CommandButton1_Click() と End Sub の間に下記のコードを記述する

 

実際のコードエディターの様子です

赤線四角で囲った部分がメインの処理です。
繰り返し処理For~Nextを使ってシートのセルに縦横10行10列の数字を並べる動作を定義しています。後からこの処理の説明をしますが、とりあえずコードをコピペしたら、実際に動作を確認してみます。

赤①と②の部分はこの処理全体の時間を計測して表示する処理です

早速、デザインモードをオフにして(アイコンをクリック)コマンドボタン1をクリックしてみます

その結果は上図のようになりました。横一列に1~10までの数字が並びそれが10行分書き込まれています。その下にその数字を書き込んだ処理にかかった時間を表示しています。

0.0078・・・秒ですから約千分の8秒ですかね、ほとんど同時です。

これでは重い処理の進捗状況をプログレスバーで表すまでもなく終わってしまいますから、これにちょっと手を加えて重い処理を再現します。

■ 時間のかかる(重い処理)処理の再現

デザインモードにして今度は、コマンドボタン2をダブルクリックして、コマンドボタン1のコードをそのままコピペします

 

実際のコードの画面で赤四角で囲った部分がそのコードです。

一回一回の繰り返しの都度0.1秒の待ち時間を作っています。

このコードをコピペしたら、先ほどシートに書き込まれた数字の行列を手動で消しておきます。

そうしたら、デザインモードをオフにしてコマンドボタン2を押してみます。

どうでしょう、0.1秒づつズレて数字が書き込まれる様子が分かるようになりました。つまり一行書き込むのに約1秒で10行分だと約10秒ですから、その結果が表示されたと思います。

これなら、十分重い処理になりました。シートに数字を並べ書き込む処理ではなく実際目には見えない処理だったらメッセージがでるまで10秒間止まったままになるのですから、十分操作する人を不安にさせると思います。これが数分だったらぞっとするほどですから、その進捗を知らせる方法は絶対必要だとおもいます。次ではその具体的な方法を解説します。

■ 重い処理の進捗を表現する

フォームを作る

赤カッコのフォームを右クリックして表示するメニューから挿入→ユーザーフォームをクリックします。次に、右側のウインドウに新しいフォームの雛形が表示されます

上図の3ヶ所の赤矢印→のところを変更します

オブジェクト名  UserForm1 → proguress1
Caption                UserForm1 → プログレスバー1
ShowModal   True    → False

更に下図のようにラベルをドラックしてフォームに2個のラベルを貼り付けてプロパティーを変更します。

Label1のプロパティー

オブジェクト名 Label1 → load1
Caption    Label1 → load1
SpecialEffect  0 → 2

 

Label2のプロパティー

オブジェクト名 Label2 → ran1
BackColor           グレー → グリーン
Caption    Label2 → ran1

★バックカラーの変更の仕方

①②③の順にクリックすると変更できます。

フォームやコントロールの名付けについてのお詫び
フォームやラベル、テキストボックス等コントロールを使うときはなるべく正しい英語を使うのが良いんでしょうが、私はローマ字表記を使ています。
ここで貼り付けたラベル1とラベル2のオブジェクト名にはそれぞれload1、ran1とつけています
load(道路)をran(走る)というニュアンスですが、道路はroad、走るはrunなので間違って名付けています。エクセルのVBAではコードに日本語を使用することもできますが、かっこつけで英語風のローマ字を使っています。英語も間違いだらけですが、ある程度コードを書いてしまったので修正するの面倒なのでこのままでも問題ないのでよろしく読み替えてください

 

結果は下図のようになります

■ プログレスバーの初期化

①のフォーム上のコントロールのない部分をダブルクリックするとClick イベントのプロシージャ雛形が表示されるのでウインドウ右上のコンボボックスから②Initializeの項目を探してクリックすると初期化用のイニシャライズイベントプロシージャ雛形ができます。

Private Sub UserForm_Initialize() と End Sub の間のコードをコピペします

 

■ プログレスバーの動作定義

シートのコマンドボタン3をダブルクリックしてできたコードエディターの
Private Sub CommandButton3_Click() と End Subの間にブログレスバーの動作を定義するコードを書き込みます。

 

コマンドボタン2で作った重い動作のコードを貼り付けます

 

プログレスバーの動作を追加します①②③の部分です

‘②=======ここにプログレスバーの動作を規定します
progress1.ran1.Width  = progress1.load1.Width / 10 * r
ラベルload1の長さを10で割って繰り返す回数を掛けた値をラベルran1の幅の長さに与えます
10は繰り返す上限、rは外側のFor~nextのその都度の繰り返した値です

DoEvents
エクセルでは繰り返しの処理のときその処理が終了するまでその処理がCPUを占有してしまってほかの処理ができません。(内部的には処理は進みますがエクセルの表示更新ができないためran1の幅が変更になっても止まって見える)繰り返しの度DoEventsでその処理がWindows側に戻ってExcelの表示の更新ができるようになります。

‘====================================

シートに戻ってデザインモードをオフにしたらコマンドボタン3を押してみます

どうでしょう。

数字が連続して1行ずつ表示されながら、プログレスバーが表示されたと思います。

それでは次に内側のFor~Nextにもプログレスバーを追加してみます

■ 仕上げ

先ほどのフォームにプログレ②バーのもう一組のラベルを追加します

二組目のラベルのオブジェクト名をload2ran2に変更しておきます。

次にこのフォームのイニシャライズプロシージャに初期化情報を追加します

 

実際のコードエディターの様子です。

次にコマンドボタン4の動作を定義します。

シートのデザインモードをオンにしてコマンドボタン4をダブルクリックすると下図のようになるので、コマンドボタン3のクリックイベントプロシージャの内容をコピーして貼り付けます。次に赤四角囲みの部分に、上で追加したラベルの動作を定義します。

外側のFor~next のラベルの動作

progress1.ran1.Width = progress1.load1.Width / 10 * r
’DoEvents

を参照して以下のように書きます。青字の部分を赤字のように書き換えています。
DoEventsは内側のForのなかに書きます。(外側のDoEventsはコメント化して止めておきます。)

progress1.ran2.Width = progress1.load2.Width / 10 * i
意味は前の項で説明していますが、load2の長さを繰り返す総回数10で割り算して繰り返すごとの回数iを掛け算した結果をran2の長さに割り当てています。
DoEvents

これでコードの設定は終了です。

シートに戻ってデザインモードをオフにしたらコマンドボタン4をクリックしてみます

どうでしょう。横列の数字が連続して書き込む様子が下のプログレスバーが表現して、同時に縦行の書き込み状況を上側のプログレスバーが担当して動いていきましたね。

思うように動作しないときはコードを点検してください。わからなければご連絡ください。

下記はフォームのイニシャライズコードコマンドボタン4の全コードを示しています。

 

■ ダウンロード

今回のプログラムを収納したファイルは下記からダウンロードできます。
ダウンロードファイルはエクセル2013で作成しています。マクロが含まれているので警告メッセージが表示されます。編集可能にするボタンを押してください。

コメント

タイトルとURLをコピーしました