【 2016年9月15日  サイト 移転のお知らせ 】
      AddinBox サイトを [ DION ] から [ さくら インターネット ] へ移転しました。  なお、旧サイト は 2017年10月 まで残します。
      この ページ の 移転先 URL  ⇒⇒  http://addinbox.sakura.ne.jp/ProgressBarTopic.htm  
 
ロゴ(青) プログレスバー ロゴ(緑)

1.プログレスバー(ProgressBar)とは

  時間の掛かる処理を実行している時には、「今動いてるのか?止まっているのか?」という思いで
ユーザーは不安になるものです。そこで、その不安を解消するとともに、「どの位進んだのか」を知ら
せる為のインターフェースとして使われるのが『プログレスバー』です。エクセルブックを開く時や、
インターネットでダウンロードする時に表示される『青く伸びるバー』の事です。

VBAで『プログレスバー』を使うには、いくつかの方法がありますので順に説明していきます。
  (1) ウィンドウ左下のステータスバーを利用する
  (2) プログレスバーコントロールを利用する
  (3) ラベルコントロールを使ってプログレスバーを自作する
  (4) アドインソフトを利用する
  (5) ループ処理をUserFormモジュールに移さないで、UserFormのプログレスバーを動かす
      (モードレス表示のユーザーフォームを利用するのでExcel2000 以上限定) (2002/1/11追記)

2.プログレスバーを作るのに必要な事

  プログレスバーというものは、処理の『進捗率』を示すものです。判り易く言えば「今、何%進んだ」
という情報です。逆に言えば「何%進んだか」がVBA側から認識できなければ、プログレスバー
は作れません。

  プログレスバーを作れない例としては、先ず
    ・ファイルオープン/保存処理
    ・フィルター処理
    ・ソート処理
    ・データベースのクエリー処理
などが挙げられます。

  これらは、どんなに時間の掛かる処理であったとしても、VBAから見れば、只の『1メソッドの実行
完了待ち』です。処理そのものはエクセルやDBへの『丸投げ』であり、それが完了するまでは
VBAに戻ってきません
。つまり『0%の次は100%』であり、『今40%進んだ』という捉え方は出来
ませんから、プログレスバーを摘要しようがありません。

  もうひとつ
    ・何回ループするのか判らない
という場合もプログレスバーは作れません。

  進捗率を求めるには、『全部でX回ループする内、現在Y回目のループ実行中』という判断が
出来る必要があります。それなのに
    ・終わってみるまで何回ループするかは判らない
    ・とにかく、処理を進めて、そのうち「終了マーク」が現れたら、その時が終わる時
というのでは『進捗率=Y÷X』が求められませんから、このようなマクロでは摘要できません。
判り易く言えば、[For ‥‥ Next]で組まれているマクロにはプログレスバーを摘要できます
が、[Do ‥‥ Loop]で組まれているマクロでは摘要できません。特に[While/Until]条件も
無く、ループの中から[Exit Do]で抜け出すしか終了方法が無いようなマクロでは絶対無理です。

  このような[Do ‥‥ Loop]で組まれているマクロの場合には、処理前に『何回ループするのか』を
カウントする処理を組み込んで、[For ‥‥ Next]に組み直す必要があります。

  [For ‥‥ Next]でも、コレクションに対するループ処理[For Each ‥‥ Next]の場合には、カウ
ンターがありませんので、別個にループ回数をカウントする変数を用意して、ループ内で自分でカ
ウントアップさせる必要があります。全ループ回数は、ループ前に『そのコレクションの[Count]プロ
パティ』で取得しておいてください。

3.ステータスバーを利用したプログレスバー

 右図のようにステータスバー上に■と□を並べて進捗を
表示する方法で、最も簡単なものです。既に作り上げた
マクロに後から組み込む場合でも大した修正を必要と
しません。


Dim blnSaveStatus As Boolean
Dim strBarNow As String
Dim strBarWk As String
Dim int進捗率 As Integer
Dim intマス数 As Integer
Dim i As Integer

blnSaveStatus = Application.DisplayStatusBar
Application.DisplayStatusBar = True
strBarNow = "現在 0%:□□□□□□□□□□"
Application.StatusBar = strBarNow
For i = 1 To ループ回数
    〜〜 ループ内の処理 〜〜
            :
            :
    int進捗率 = Int(i * 100 / ループ回数)    ' 進捗率(%)
    intマス数 = Int(i * 10 / ループ回数)     ' 10個マスだから10%単位
    strBarWk = "現在 " & int進捗率 & "%:" & _
                     String(intマス数,"■") & String(10 - intマス数,"□")
    If (strBarWk <> strBarNow) Then
        Application.StatusBar = strBarWk    ' 変化がある時だけバー更新
        strBarNow = strBarwk
    End If
Next i
Application.StatusBar = False      ' ステータスバーの制御をExcelに戻す
Application.DisplayStatusBar = blnSaveStatus

4.プログレスバーコントロールの利用

 「プログレスバーコントロール」というActiveXコント
ロールを利用して、UserForm上にプログレスバーを
表示させる方法です。右図のように、プロパティの
指定で、
  ・ボックスが並んでいくパターン
  ・連続的にバーが伸びていくパターン
の2種類を選択できます。


 ただし、標準では「プログレスバーコントロール」は利用できる状態になっていませんので、
下図のようにして、ツールボックス内に追加してください。
    (注) プログレスバーコントロールはExcel には含まれていません。Visual Basic に付属するコントロールです
            したがって、Visual Basic がインストールされていない環境では利用できません。尚、Excel で使う為の
            目的でプログレスバーコントロールを含むライブラリを個別にインストールする行為はライセンス違反です。
            Office のインストール又はSPアップデートにより、プログレスバーコントロールが入っている
                  mscomctl.ocx ファイルが正規にインストールされる事が判りました(2006/7/27)。




 追加できたら、それをUserForm上に適当なサイズで貼り付けます。
規定ではボックスが並ぶ形式ですが、上図のように[Scrolling]プロパティを[Smooth]に変えれば
連続したバー表示になります。あとは、[Min, Max, Value]のプロパティを操作してやれば出来
上がります(Value プロパティは実行時のみ有効なプロパティなので、プロパティウィンドウには
表示されません)。

  使い方は、「150回ループする処理」ならば、[Min : 0] [Max : 150] [Value : 0]と設定し、ループ
処理の中で『1回ループする都度、[Value]を[1]カウントアップ』していけば、進捗率に応じて自動
的にバーが伸びていきます。

  いま、「150回ループする処理ならば[Min : 0] [Max : 150] 」と書きましたが、For ステートメント
の(開始値-1)/終了値を各々Min/Maxに設定し、カウンター値をValueに代入してもOKです。
ただし、これは『Step = 1』の場合に限ります

  進捗率の数値表示は「ラベル」をプログレスバーコントロールの近くに配置して、その[Caption]
プロパティで対応します。
  ---- 2002/1/9 追記 --------------------------------------------
  但し、そのままでは「バーは伸びていくけど、ラベルの%数値は変わらない」という結果になります。
プログレスバーの[Value]プロパティ更新による『再描画』対象がプログレスバーコントロールのみ
だからです。ラベルの部分が再描画されない為、処理が全て終わった所で突然[100%]と表示され
るという見え方になります。ラベルも一緒に再描画させて『%数値』が変わるようにするには以下の
ようにします。
        その「%数値」表示用ラベルコントロールのサイズより一回り大きな『フレームコントロール』
      を配置します(このフレームコントロールは「キャプション無し、枠無し」にします)。
      次にラベルコントロールを、このフレームコントロールの中に移動させます。それから、フレーム
      ごと、今までラベルが在った位置へ戻してください。
        ループ処理内では、ラベルの内容を変更したら、
              Frame1.Repaint
      という命令でそのフレーム内を再描画させます。フレームを使う理由はこれと同じです。
  -------------------------------------------------------------

ここで大事な事をひとつ
UserFormでプログレスバーを処理する為には『ループ処理そのもの』をUserFormモジュールに
記述する事になります。そして、標準モジュールに、そのUserFormを表示する【Show メソッド】を
記述します。

  既に標準モジュールで組み上げたマクロにプログレスバーを追加する場合を想定すると、
『ループ処理そのもの(必要なら、その前後の処理も)』は、UserFormモジュールに移します。
実行確認用のコマンドボタンもUserFormに配置するなら、そのコマンドボタンのクリックイベント
コマンドボタン無しならば[UserForm_Activate ]イベントに移します。そして、いままで『ループ
処理』があった場所(標準モジュール)には、『UserForm1.Show 』を記述します。

  「標準モジュールに残っている処理」と「UserFormに移った処理」の間で共通に使う変数
標準モジュールの先頭(宣言セクション)で[Public ]により定義してください。


==== 標準モジュール ====
Public 〜〜〜〜  ' UserForm1側に移った処理と共通に使う変数
Sub ループ処理の親ルーチン()
      :
      :
    'いままで「ループ処理」があった場所
    UserForm1.Show    ' ループ処理の呼び出し
        :
        :
End Sub

==== UserForm1 モジュール ====
Private Sub UserForm_Initialize()

  ProgressBar1.Min = 0
  ProgressBar1.Value = 0
  Label1.Caption = ""
End Sub

Private Sub CommandButton1_Click()
Dim i As Integer
  ProgressBar1.Max = ループ回数
  For i = 1 To ループ回数
      〜〜 ループ内の処理 〜〜
              :
              :
      ProgressBar1.Value = ProgressBar1.Value + 1
      Label1.Caption = Int(i * 100 / ループ回数) & "%"
      Frame1.Repaint    ' Frame1内にLabel1を配置しておく
  Next i
  Unload Me    ' 処理が終わったのでフォームを閉じて[標準モジュール]側に戻る
End Sub

  どのPCでも動くように(動かすPCにプログレスバーコントロールが追加されているか不明)という
マクロを作るなら、次の『ラベル利用の方法』の方が確実です。

5.ラベルを利用したプログレスバーの自作

 プログレスバーコントロールと同様の外観はラベルを
使って自作する事もできます。
  右図のプログレスバーは凹状形態にした『フレーム
コントロール』の上に青地背景の『ラベルコントロール』
を重ねて作ってあります。
  ループの進捗に応じて、ラベルの[Width]プロパティ
[ゼロ]から[フレーム幅]まで動的に更新することで
バーを伸ばしていきます。
  %表示はラベルの上に更に背景透過にした『ラベル
コントロール』を重ねて表示します。


  このプログレスバー用フォームのマクロへの組み込み方は、前述の「プログレスバーコントロール」
の場合と同じです。


==== 標準モジュール ====
Public 〜〜〜〜  ' UserForm2側に移った処理と共通に使う変数
Sub ループ処理の親ルーチン()
        :
        :
  'いままで「ループ処理」があった場所
  UserForm2.Show    ' ループ処理の呼び出し
        :
        :
End Sub

==== UserForm2 モジュール ====
Private Sub UserForm_Initialize()
  '『バー』用ラベルの初期設定(フレームより1ピクセルずつ内側)
  With Label1
    .Top = 1
    .Left = 1
    .Height = Frame1.Height - 2
    .Width = 0
    .BackColor = &H800000
  End With
  Label2.Caption = ""    ' %表示用ラベル
End Sub

Private Sub CommandButton1_Click()
Dim i As Integer
Dim sngBarMaxWidth As Single
Dim intPercent As Integer
Dim intBeforePercent As Integer
  sngBarMaxWidth = Frame1.Width - 2
  intBeforePercent = 0
  For i = 1 To ループ回数
      〜〜 ループ内の処理 〜〜
              :
              :
    intPercent = Int(i * 100 / ループ回数)
    If (intPercent <> intBeforePercent) Then
        Label1.Width = sngBarMaxWidth * intPercent / 100
        Label2.Caption = intPercent & "%"
        Frame1.Repaint    ' バーの再描画
    End If
    intBeforePercent = intPercent
  Next i
  Unload Me    ' 処理が終わったのでフォームを閉じて[標準モジュール]側に戻る
End Sub


 「プログレスバーコントロール」と違って、「ラベルコントロール」ではプロパティ更新が即画面表示
には反映されません。[DoEvents]によって一旦VBAからエクセルに制御を渡すか、[Repaint]によって
明示的に再表示させる必要があります。では、[DoEvents]と[Repaint]のどちらを使うのが良いかというと
[Repaint]の方が2つの点で有利です。
    ・[DoEvents]では画面表示処理以外にも色々な処理が裏側で動く為、繰り返し実行した場合
      画面表示だけの[Repaint]の方がレスポンス上で有利です。
    ・[DoEvents]では、UserForm全体が再表示される為、UserFormの大きさによってはチラツいて
      見えますが、[Repaint]ではFrame単位での再表示指定が出来るので、再表示範囲をプログレス
      バー部分だけに範囲を狭めることでチラツキを抑えることが可能です。
凹状形態の下地は『ラベルコントロール』でも代用できますが、そうすると再描画範囲が、どうしても
UserForm全体にならざるを得ないのでチラツキが出てしまいます。敢えてFrame を使っているのは
この為です。



6.アドインソフトを利用したプログレスバー

 私の公開ソフト『プログレスバー表示アドイン』で
は右図のようなプログレスバーフォームが表示され
ます。なお、全く同じものが『kt関数アドイン』にも
収録されています。
  このアドインでは、ユーザー側でUserFormを用意
する必要がありません。また、長時間処理向けに
『中断ボタン』も設けてあります。


 プログレスバー表示アドインでも『ループ処理』はプロシジャーを分離させますが、分離先は
標準モジュール】です。そして、その分離した『ループ処理』プロシジャーは必ず【Public
宣言し、且つ[Variant]属性の引数(ダミーです)をひとつ設けます。これは「マクロの実行
ダイアログ」に表示させない為の処置です。なお、ループ処理の親ルーチンが元々「標準モジュ
ール」なら、同じモジュール内に作っても構いません。

  プログレスバー表示アドインでは、[ktRunPrgsWait][ktInitPrgsWait][ktDispPrgsWait]の3つの
プロシジャー呼び出しによってバー表示を行ないます。各プロシジャーの引数説明はこちら
参照してください。仕組みについては、こちらをご覧下さい。
※プログレスバー表示アドインを利用するには、アドインへの【参照設定】が必要です※

==== 標準モジュール ====
Public 〜〜〜〜    ' 分離したループ処理と共通に使う変数
Sub ループ処理の親ルーチン()
        :
        :
    'いままで「ループ処理」があった場所
    Call ktRunPrgsWait("LoopSub", "Module2", ThisWorkbook.Name)
        :
        :
End Sub

==== 標準モジュール:Module2 ====
Public Sub LoopSub(ByVal Dummy As Variant)
' マクロの実行ダイアログに表示されないようにダミーの引数をひとつ設ける(必須)
Dim i As Integer
Dim MsgResp As Integer
Dim CancelClick As Boolean  ' 中断ボタンのOn/Off受け取り

  Call ktInitPrgsWait("時間の掛かる処理を実行中です", _
                        "しばらくお待ちください", _
                        "『中断ボタン』で処理中断します", _
                        MaxLoop:=ループ回数, _
                        CancelInterval:=10)

  For i = 1 To ループ回数
      〜〜 ループ内の処理 〜〜
              :
              :

    Call ktDispPrgsWait(CancelClick)  ' プログレスバーの更新
    If (CancelClick = True) Then
        MsgResp = MsgBox("中断ボタンが押されました" & _
                         vbCrLf & "中断しますか?", _
                         vbExclamation + vbYesNo + vbDefaultButton2)
        If (MsgResp = vbYes) Then
          Exit For
        End If
    End If
  Next i
End Sub



7.ループ処理をUserFormモジュールに移さないでUserFormのプログレスバーを動かす

  「4.および 5.」の方法では、ループ処理をUserFormモジュール内に記述する必要があります
が、Excel2000 から利用できるUserFormのモードレス表示』を使うと、ループ処理を【標準
モジュールに置いたまま→改造しなくても、今のままで良いという事】でプログレスバーを表示
させる事ができます。

  モーダル表示(普通の表示方法)の場合、ShowメソッドによりUserFormが表示されると、それ
以降はVBAの実行制御がUserFormに移ります。したがって、『表示元ルーチン』からUserForm
のコントロールを操作する事はできません。Showメソッドの次にUserFormのコントロールを操作
する命令を記述しても、その命令が実行されるのは、UserFormが[UnLoad/Hide]された後です
から、UserFormを表示しながら動的に表示を変えさせるという事はできないのです。

  モードレス表示(Showメソッドに[vbModeLess]のパラメータを指定:Excel97では不可)の場合
には、UserFormが表示されると、直ぐに実行制御が『表示元ルーチン』に戻ってきてShowメソッドの
次の命令へ進みます。この時、UserFormは表示されたままなので、『表示元ルーチン』からUserForm
のコントロールを操作する事が可能です。

UserFormモジュールの外から、UserFormを扱うには以下のようにします。
  (1) UserFormのコントロールを操作するには
       UserForm1.ProgressBar1.Value = 0
      という風にUserFormの『オブジェクト名』で修飾する必要があります。

  (2) UserFormの表示前初期設定は、Showメソッドの前にLoadメソッドによってUserFormを
        メモリ上に読み込んでおく必要があります。Loadメソッドによりメモリ上に読み込んでから、
        各コントロールのプロパティへ初期値を代入し、それからShowメソッドという風に進めます。

      (補)実際にはLoadメソッドが無くても、プロパティへの代入は出来ます。UserFormへの
            最初のアクセスの時に「ロードされていなかったら自動的にロードする」という仕組み
            が備わっている為です。しかし、プログラムの可読性の為にもLoadメソッドを記述して
            おく事が大切です。

        初期設定値が常に同じならば、[UserForm_Initialize]イベントプロシジャーに記述しても良い
        ですし、「プロパティウィンドウ」にて初期値を設定していても良いです。しかしながら、やはり
        可読性の事を考えるなら、ループ処理前の初期設定はループ処理と同じ場所に記述して
        おいた方が良いです。

プログレスバー用のUserFormは「4.および 5.」と同じ様に作ります。

〜〜『プログレスバーコントロール』の場合〜〜
==== 標準モジュール ====
Sub ループ処理の親ルーチン()
Dim i As Integer
        :
        :
  Load UserForm1    ' UserFormの読み込み&初期設定
  With UserForm1.ProgressBar1
    .Min = 0
    .Max = ループ回数
    .Value = 0
  End With
  UserForm1.Label1.Caption = ""    ' %表示用ラベル

  UserForm1.Show vbModeless    ' UserFormを実際に表示する

  For i = 1 To ループ回数
      〜〜 ループ内の処理 〜〜
              :
              :
      With UserForm1
        .ProgressBar1.Value = .ProgressBar1.Value + 1
        .Label1.Caption = Int(i * 100 / ループ回数) & "%"
        .Frame1.Repaint    ' Frame1内にLabel1を配置しておく
      End With
  Next i
  Unload UserForm1 ' 処理が終わったのでフォームを閉じる([Me]は使えない)
End Sub


〜〜『ラベルコントロール』の場合〜〜
==== 標準モジュール ====
Sub ループ処理の親ルーチン()
Dim i As Integer
Dim sngBarMaxWidth As Single
Dim intPercent As Integer
Dim intBeforePercent As Integer
        :
        :
  Load UserForm2    ' UserFormの読み込み&初期設定
  With UserForm2
    .Label1.Top = 1
    .Label1.Left = 1
    .Label1.Height = .Frame1.Height - 2
    .Label1.Width = 0
    .Label1.BackColor = &H800000
    .Label2.Caption = ""    ' %表示用ラベル
    sngBarMaxWidth = .Frame1.Width - 2
  End With

  UserForm2.Show vbModeless    ' UserFormを実際に表示する

  intBeforePercent = 0
  For i = 1 To ループ回数
      〜〜 ループ内の処理 〜〜
              :
              :
    intPercent = Int(i * 100 / ループ回数)
    If (intPercent <> intBeforePercent) Then
        With UserForm2
          .Label1.Width = sngBarMaxWidth * intPercent / 100
          .Label2.Caption = intPercent & "%"
          .Frame1.Repaint  ' バーの再描画
        End With
    End If
    intBeforePercent = intPercent
  Next i
  Unload UserForm2    ' 処理が終わったのでフォームを閉じる([Me]は使えない)
End Sub




[ Home へ戻る ]
ロゴ(ゴールド)   ロゴ(ゴールド)

角田 桂一 Mail:addinbox@h4.dion.ne.jp CopyRight(C) 2001 Allrights Reserved.