プログレスバー表示
■ 作業の流れ
エクセルでVBAを作っていると、例えばある操作で何らかの計算をした結果を知らせるプロシージャを作ったとして、そのプロシージャの処理ははほとんどの場合その処理はほんの一瞬で終わってしまいますが、まれに（その処理の内容によっては）何秒か（１～数秒）かかる場合があります。
その場合、エクセルの表示上では画面が静止状態になりフリーズしたかのようになります。１，２秒なら何とかなりますが、それが数十秒、数分に及ぶ場合は困った状況になります。
そういう場合プログレスバーを表示して処理の進捗状況を知らせることでユーザーに安心感を与えることができます。
そこで今回はそのプログレスバーを表示するプログラムを作ってみます
手順は次のようになります
コマンドボタンを押す→重い処理を再現→進捗の表現フォームを作る→動作の仕上げ
■ シートにコマンドボタンを貼り付ける
まず最初に何らかの処理を開始させるためのボタンを作ります
下図のようにエクセルの一番上にある赤囲みのツールバーの部分をマウスでポイントして右クリックします。そこにメニューが表示されるので、メニュー最上部の「クイックアクセスツールバーのユーザー設定（C）」をクリックします。
次に下図の設定画面になるので、左側上部にある「コマンドの選択（C)」のところで
【すべてのコマンド】を選択してからその下のリストの中から【コントロールの挿入】を探してクリックしたら「追加（A)＞＞」ボタンを押します。右側のリストに【コントロールの挿入】が追加されたので「OK」ボタンを押して設定画面を閉じます。
次にエクセル上部のツールバーの最後に先ほどの【コントロールの挿入】アイコンが表示されているので、それをクリックします。
次にメニューリストにシートに貼り付けられるコントロールのリスト中の赤まるの【コマンドボタン】をクリックします。
上図で【コマンドボタン】コントロールをクリックしたことで、エクセルはその動作を記憶したのです。
なので、シートの適当な場所をクリックしたままドラックして長方形を形成します。するとコマンドボタンが配置できました。ボタンをクリックして抑えたままマウスを引きずるとボタンを好きな場所に移動できます。
次にこのコマンドボタンをクリックすることで何らかの処理を開始するように設定します。
エクセル上部のツールバーの中の「デザインモード」アイコンをクリックすると、コマンドボタンの大きさを変えたり配置を移動させたりできるようになります。この状態でボタンをダブルクリックするとVBAの編集画面が表示されます
（デザインモードのアイコンがないときは、一番最初にやったコントロールの追加のやり方を参考にして追加してください。）
Private Sub CommandButton1_Click() と End Sub の間に何らかの処理を記述します。
同様にしてコマンドボタンを更に3個貼り付けます
デザインモードでボタンCommandButton1をダブルクリックしてVBAコードエディターを開く
Private Sub CommandButton1_Click() と End Sub の間に下記のコードを記述する
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 |
Dim startTime Dim endTime As Double '開始時間取得 startTime = Timer '------------------------------------- 'A列の1行目から縦横に10行10列分、数字を並べる Dim i As Integer Dim r As Integer '縦行の繰り返し For r = 1 To 10 '横列の繰り返し For i = 1 To 10 '数字をセルに書き込む処理 ActiveSheet.Cells(r, i) = i Next i Next r '------------------------------------ '終了時間取得 endTime = Timer '処理時間計算 MsgBox endTime - startTime |
実際のコードエディターの様子です
赤線四角で囲った部分がメインの処理です。
繰り返し処理For～Nextを使ってシートのセルに縦横10行10列の数字を並べる動作を定義しています。後からこの処理の説明をしますが、とりあえずコードをコピペしたら、実際に動作を確認してみます。
赤①と②の部分はこの処理全体の時間を計測して表示する処理です
早速、デザインモードをオフにして（アイコンをクリック）コマンドボタン１をクリックしてみます
その結果は上図のようになりました。横一列に１～10までの数字が並びそれが10行分書き込まれています。その下にその数字を書き込んだ処理にかかった時間を表示しています。
０．００７８・・・秒ですから約千分の8秒ですかね、ほとんど同時です。
これでは重い処理の進捗状況をプログレスバーで表すまでもなく終わってしまいますから、これにちょっと手を加えて重い処理を再現します。
■ 時間のかかる（重い処理）処理の再現
デザインモードにして今度は、コマンドボタン２をダブルクリックして、コマンドボタン１のコードをそのままコピペします
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 |
'処理時間に待ち時間を作る '============================================ Dim startTime Dim endTime As Double '開始時間取得 startTime = Timer '------------------------------------- 'A列の1行目から縦横に10列分、数字を並べる Dim i As Integer Dim r As Integer '縦行の繰り返し For r = 1 To 10 '横列の繰り返し For i = 1 To 10 '数字をセルに書き込む処理 ActiveSheet.Cells(r, i) = i '待ち時間を作る（0.1秒） Application.Wait [Now()] + 0.1 / 86400 Next i Next r '------------------------------------ '終了時間取得 endTime = Timer '処理時間計算 MsgBox endTime - startTime & "秒" '------------------------------------------- '書き込まれたデータを削除する ActiveSheet.Range("A1").CurrentRegion.ClearContents |
実際のコードの画面で赤四角で囲った部分がそのコードです。
一回一回の繰り返しの都度0.1秒の待ち時間を作っています。
このコードをコピペしたら、先ほどシートに書き込まれた数字の行列を手動で消しておきます。
そうしたら、デザインモードをオフにしてコマンドボタン２を押してみます。
どうでしょう、0.1秒づつズレて数字が書き込まれる様子が分かるようになりました。つまり一行書き込むのに約1秒で10行分だと約10秒ですから、その結果が表示されたと思います。
これなら、十分重い処理になりました。シートに数字を並べ書き込む処理ではなく実際目には見えない処理だったらメッセージがでるまで10秒間止まったままになるのですから、十分操作する人を不安にさせると思います。これが数分だったらぞっとするほどですから、その進捗を知らせる方法は絶対必要だとおもいます。次ではその具体的な方法を解説します。
■ 重い処理の進捗を表現する
フォームを作る
赤カッコのフォームを右クリックして表示するメニューから挿入→ユーザーフォームをクリックします。次に、右側のウインドウに新しいフォームの雛形が表示されます
上図の3ヶ所の赤矢印→のところを変更します
オブジェクト名 UserForm1 → proguress1
Caption UserForm1 → プログレスバー1
ShowModal True → False
更に下図のようにラベルをドラックしてフォームに2個のラベルを貼り付けてプロパティーを変更します。
Label1のプロパティー
オブジェクト名 Label1 → load1
Caption Label1 → load1
SpecialEffect ０ → ２
Label２のプロパティー
オブジェクト名 Label2 → ran1
BackColor グレー → グリーン
Caption Label2 → ran1
★バックカラーの変更の仕方
①②③の順にクリックすると変更できます。
結果は下図のようになります
■ プログレスバーの初期化
①のフォーム上のコントロールのない部分をダブルクリックするとClick イベントのプロシージャ雛形が表示されるのでウインドウ右上のコンボボックスから②Initializeの項目を探してクリックすると初期化用のイニシャライズイベントプロシージャ雛形ができます。
Private Sub UserForm_Initialize() と End Sub の間のコードをコピペします
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 |
Private Sub UserForm_Initialize() 'プログレスバー１の初期化 ' Dim takasa As Integer ’高さ用変数定義 Dim nagasa As Integer ’長さ用 Dim hosei As Integer ’指定位置補正 takasa = load1.Height / 2 ’ran1の高さをload1の高さの半分に設定 nagasa = 0 ’ran1の表示幅を0に設定 hosei = 3 ’ran1の表示位置の補正量 ' ran1.Width = nagasa ran1.Height = takasa ran1.Top = load1.Top + hosei ran1.Caption = "" load1.Caption = "" End Sub |
■ プログレスバーの動作定義
シートのコマンドボタン３をダブルクリックしてできたコードエディターの
Private Sub CommandButton3_Click() と End Subの間にブログレスバーの動作を定義するコードを書き込みます。
コマンドボタン２で作った重い動作のコードを貼り付けます
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 |
Private Sub CommandButton2_Click() '処理時間に待ち時間を作る '============================================ Dim startTime Dim endTime As Double '開始時間取得 startTime = Timer '------------------------------------- 'A列の1行目から縦横に10列分、数字を並べる Dim i As Integer Dim r As Integer '縦行の繰り返し For r = 1 To 10 '横列の繰り返し For i = 1 To 10 '数字をセルに書き込む処理 ActiveSheet.Cells(r, i) = i '待ち時間を作る（0.1秒） Application.Wait [Now()] + 0.1 / 86400 Next i Next r '------------------------------------ '終了時間取得 endTime = Timer '処理時間計算 MsgBox endTime - startTime & "秒" '------------------------------------------- '書き込まれたデータを削除する ActiveSheet.Range("A1").CurrentRegion.ClearContents End Sub |
プログレスバーの動作を追加します①②③の部分です
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 |
Private Sub CommandButton3_Click() '処理時間に待ち時間を作る '============================================ Dim startTime Dim endTime As Double '開始時間取得 startTime = Timer '------------------------------------- 'A列の1行目から縦横に10列分、数字を並べる Dim i As Integer Dim r As Integer '①=======ここでプログレスバーを表示します progress1.Show '==================================== '縦行の繰り返し For r = 1 To 10 '②=======ここにプログレスバーの動作を規定します progress1.ran1.Width = progress1.load1.Width / 10 * r DoEvents '==================================== '横列の繰り返し For i = 1 To 10 '数字をセルに書き込む処理 ActiveSheet.Cells(r, i) = i '待ち時間を作る（0.1秒） Application.Wait [Now()] + 0.1 / 86400 Next i Next r '------------------------------------ '終了時間取得 endTime = Timer '処理時間計算 MsgBox endTime - startTime & "秒" '------------------------------------------- '書き込まれたデータを削除する ActiveSheet.Range("A1").CurrentRegion.ClearContents '③=====最後にプログレスバーを閉じます Unload progress1 '====================== End Sub |
‘②=======ここにプログレスバーの動作を規定します
progress1.ran1.Width = progress1.load1.Width / 10 * r
ラベルload1の長さを10で割って繰り返す回数を掛けた値をラベルran1の幅の長さに与えます
10は繰り返す上限、ｒは外側のFor～nextのその都度の繰り返した値です
DoEvents
エクセルでは繰り返しの処理のときその処理が終了するまでその処理がCPUを占有してしまってほかの処理ができません。（内部的には処理は進みますがエクセルの表示更新ができないためran1の幅が変更になっても止まって見える）繰り返しの度DoEventsでその処理がWindows側に戻ってExcelの表示の更新ができるようになります。
‘====================================
シートに戻ってデザインモードをオフにしたらコマンドボタン３を押してみます
どうでしょう。
数字が連続して1行ずつ表示されながら、プログレスバーが表示されたと思います。
それでは次に内側のFor～Nextにもプログレスバーを追加してみます
■ 仕上げ
先ほどのフォームにプログレ②バーのもう一組のラベルを追加します
二組目のラベルのオブジェクト名をload2とran2に変更しておきます。
次にこのフォームのイニシャライズプロシージャに初期化情報を追加します
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 |
Private Sub UserForm_Initialize() 'プログレスバー2の初期化 ' Dim takasa As Integer Dim nagasa As Integer Dim hosei As Integer takasa = load1.Height / 2 nagasa = 0 hosei = 3 ' ran1.Width = nagasa ran1.Height = takasa ran1.Caption = "" ran1.Top = load1.Top + hosei load1.Caption = "" ’以下が追加したラベルの初期化です ran2.Width = nagasa ran2.Height = takasa ran2.Caption = "" ran2.Top = load2.Top + hosei load2.Caption = "" End Sub |
実際のコードエディターの様子です。
次にコマンドボタン４の動作を定義します。
シートのデザインモードをオンにしてコマンドボタン４をダブルクリックすると下図のようになるので、コマンドボタン３のクリックイベントプロシージャの内容をコピーして貼り付けます。次に赤四角囲みの部分に、上で追加したラベルの動作を定義します。
外側の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
これでコードの設定は終了です。
シートに戻ってデザインモードをオフにしたらコマンドボタン４をクリックしてみます
どうでしょう。横列の数字が連続して書き込む様子が下のプログレスバーが表現して、同時に縦行の書き込み状況を上側のプログレスバーが担当して動いていきましたね。
思うように動作しないときはコードを点検してください。わからなければご連絡ください。
下記はフォームのイニシャライズコードとコマンドボタン４の全コードを示しています。
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 |
Private Sub UserForm_Initialize() 'プログレスバー１の初期化 ' Dim takasa As Integer Dim nagasa As Integer Dim hosei As Integer takasa = load1.Height / 2 nagasa = 0 hosei = 3 ' ran1.Width = nagasa ran1.Height = takasa ran1.Top = load1.Top + hosei ran1.Caption = "" load1.Caption = "" ' ran2.Width = nagasa ran2.Height = takasa ran2.Caption = "" ran2.Top = load2.Top + hosei load2.Caption = "" End Sub |
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 |
Private Sub CommandButton4_Click() '処理時間に待ち時間を作る '============================================ Dim startTime Dim endTime As Double '開始時間取得 startTime = Timer '------------------------------------- 'A列の1行目から縦横に10列分、数字を並べる Dim i As Integer Dim r As Integer '=======ここでプログレスバーを表示します progress1.Show '==================================== '縦行の繰り返し For r = 1 To 10 '=======ここにプログレスバーの動作を規定します progress1.ran1.Width = progress1.load1.Width / 10 * r 'DoEvents '==================================== '横列の繰り返し For i = 1 To 10 '=======ここにプログレスバーの動作を規定します progress1.ran2.Width = progress1.load2.Width / 10 * i DoEvents '==================================== '数字をセルに書き込む処理 ActiveSheet.Cells(r, i) = i '待ち時間を作る（0.1秒） Application.Wait [Now()] + 0.1 / 86400 Next i Next r '------------------------------------ '終了時間取得 endTime = Timer '処理時間計算 MsgBox endTime - startTime & "秒" '------------------------------------------- '書き込まれたデータを削除する ActiveSheet.Range("A1").CurrentRegion.ClearContents '=====最後にプログレスバーを閉じます Unload progress1 '====================== End Sub |
■ ダウンロード
今回のプログラムを収納したファイルは下記からダウンロードできます。
ダウンロードファイルはエクセル2013で作成しています。マクロが含まれているので警告メッセージが表示されます。編集可能にするボタンを押してください。
コメント