VBAでフォームを使う-2プログレスバーを作るコードを紹介&解説

プログレスバーを作る為のコード&解説

前回記事ではプログレスバー(ユーザーフォーム)の使用感が分かる動画も見ていただきました。

イメージは提供できました。続いて実態を提供していきます。

プログレスバーを自作する為のコードを紹介していきます。

EnjoyExcel
EnjoyExcel

ユーザーフォームについて勉強する事になります。VBAで出来る事の幅が広がります。

関連記事

前回記事です。プログレスバーの概要、動画を用意しております。

この本で勉強しました

ユーザーフォームは本やネットでの情報が本当に少ないので良い情報源を確保するのが難しいです。

そのような環境の中で私のおすすめの書籍を紹介します。

各種リンク先の試し読み等で確認いただくとかなり深いところまで勉強できる事がわかります。

\おすすめ書籍/

フォームの作り方

一連のシリーズの中で説明しています。

リンク先を確認しなくても分かる様にこの記事でもフォームを用意する方法を解説する事にします。

各モジュールの追加

まずはユーザーフォームを1つ表示させます。

VBEで標準モジュールを表示させるときの作業と同じです。

標準モジュールのすぐ上の選択肢がユーザーフォームです。

ひとまずユーザーフォームを1つ表示させておきましょう。

加えて標準モジュールも1つ使いますので表示させましょう。

ユーザーフォームの追加方法
VBEで標準モジュールを用意する方法と同じ方法でユーザーフォームを準備出来ます。

最終的なプロジェクトエクスプローラの状況です。

フォームのツリーにUserForm1が1つです。

標準モジュールのツリーにはModule1が1つです。

ツリーの状態
同じような建付けにしてください。

フォームにコントロールを追加する

コードウインドウにあるフォームを選択します。

一緒にツールボックスが画面上に表示されます。

まずはデフォルトのコントロールでは足りない分を追加します。

以降は以下コントロールをフォーム上に展開します。

ツールボックスが消えてしまったときはこちらをご覧ください。

ツールボックスの表示方法

フォームとツールボックス
コードウインドウにユーザーフォームとツールボックスが表示されます。

プログレスバーのコントロールを表示させます。

ツールボックスの上で右クリック。

「その他のコントロール(A)」を選択。

Microsoft ProgressBar Contorol ,Version6.0を選択。

OKボタンを押下。

これでコントロール上にアイコンが表示されます。

他にもいろいろな機能がありますので別途試してみてください。

ツールボックスで右クリック
ツールボックスの上で右クリック。
プログレスバーを選択
画像と同じものを選択してください。

今回は3つのコントロールを使います。

  • ラベル
  • ProgressBar
  • コマンドボタン

各コントロールはツールボックスでアイコン選択後フォームをクリックすると形状が展開されます。

配置や大きさ、名前についてはプロパティウインドウから指示をします。

コントロールのカスタマイズ

各コントロールをカスタマイズします。

プロパティウインドウを使いましょう。

デフォルトだとおそらくVBEの左下に配置されています。

プロパティウインドウは各コントロールの属性を表示しています。

値を変える事でカスタマイズできます。

ラベルの属性を見たい時はラベルをクリックしましょう。

するとプロパティウインドウに属性が表示されます。

以下画像はフォームを選択した時の画像です。

すでにカスタマイズされている要素もあります。ご注意下さい。

プロパティウインドウ
プロパティウインドウの画像です(フォームを選択しています)

分かりにくいかもしれないのでガイドを用意しました。

フォーム、ラベル、プログレスバー、コマンドボタンの属性を以下添付ファイルの様に変更してください。


画像のような配置になっていれば正解です。

簡単な仕様ですがシンプルで見やすくて良いですよね。

完成の状態
完成のイメージです。

なお配置だけでは各コントロールに名前が付いているのか分かりません。

オブジェクト名も確実に書き換える様ご注意ください。

各オブジェクト名がダウンロードファイルと異なる際はコードを実行させる時にエラーになります。

コード

今回のコードは長いです。いざ書こうと思うと大変です。

コードを選択する事でコピー出来る様にしておきましたので必要に応じて使用ください。

書く場所

結論から申し上げますと今回は以下2点です。

  • UserForm1(フォーム)
  • Module1(標準モジュール)

この2つにコードを書く事になります。

書き方

ユーザーフォームにコードを書く方法です。

プロジェクトエクスプローラ内でフォームを選択後右クリック。

すると「コードの表示」が出てきます。

ここにコードを書きましょう。

標準モジュールには通常の方法でコードを記入しましょう。

フォームが持っているコードウインドウににコードを書く方法
フォームが持っているコードウインドウにコードを書きます。
コード

ユーザーフォームに書き込むコードはこちらになります。

(Option Explicitは必要に応じてコピーしてください)

Option Explicit
'キャンセル処理用フラグ
Public IsCancel As Boolean

'初期化
Private Sub UserForm_Initialize()
'キャンセルフラグにFalseを設定
IsCancel = False
End Sub

'キャンセルボタンクリックイベント
Private Sub BtnCancel_Click()
'キャンセルフラグにTrueを設定
IsCancel = True
End Sub

標準モジュールには以下コードを書いてください。

(Option Explicitは必要に応じてコピーしてください)

Option Explicit
'いつもの様にあえて変数は日本語を使っています
Sub プログレスバー()
'**********************************************************************
'1_変数の定義
Dim 最終行 As Long
Dim 最終列 As Long
Dim 行 As Long
Dim 列 As Long

'フォームで使う変数
Dim percent As Long 'frmPBで使用 処理の進捗
Dim count As Long  'frmPBで使用 変数percentの分母
Dim p As Long: p = 1 'frmPBで使用 変数percentの分子
'**********************************************************************
Application.ScreenUpdating = False
'**********************************************************************
'2_データ範囲取得
最終行 = Cells(Rows.count, 2).End(xlUp).Row
最終列 = Cells(6, Columns.count).End(xlToLeft).Column
'**********************************************************************
'3_PB初期設定
'フォームで使う変数percentの分母に値をセット  変数の後のマイナスは開始列までの値
count = 最終列 - 1

'FrmPBを表示
frmPB.Show vbModeless

'フォームの表示位置を決める(適当)
frmPB.StartUpPosition = 0
frmPB.Top = 220
frmPB.Left = 550

'フォームの最小値を設定
frmPB.ProgressBar1.Min = 1
'フォームの最大値を設定
frmPB.ProgressBar1.Max = count
'フォームの現在値を設定
frmPB.ProgressBar1.Value = 1

'アイコンを待機中に固定
Application.Cursor = xlWait
'**********************************************************************
'4_ループ処理
'今回のコードはただセルを選択するだけ
'その間でキャンセルボタンが押された時の処理待ちや進捗の値を更新している
For 列 = 2 To 最終列
    For 行 = 6 To 最終行
        'セルを選択するだけ
        Cells(行, 列).Select

   '5_キャンセル処理と進捗確認
       '進行中の処理をキャンセルした時の処理とfrmPBに進捗度を表示させる為の処理
       '進行中の処理ををキャンセル 
        If frmPB.IsCancel = True Then
            'フォームを閉じる
            Unload frmPB
            'マウスカーソルをデフォルトに戻す
            Application.Cursor = xlDefault
            MsgBox "処理を中断しました"
            End
        '途中までの結果は無しにして処理を終了
        End If
   
        'frmPBに進捗度を表示
        If frmPB.ProgressBar1.Min <= p And _
        frmPB.ProgressBar1.Max >= p Then
            'フォームのLabel表示を更新
            percent = CInt(p / count * 100)
            frmPB.lbl1.Caption = percent & "%完了"
            'フォームの値を更新
            frmPB.ProgressBar1.Value = p
            'フォームに値をセットする為のコード
        'コードの実行から一時抜けてVBA側に制御を戻して進捗表示をフォームに表示させる
            'DoEventsをどれだけ拾うかはデータ量次第で調整
            '以下コードは1列分の処理を終了したところでDoEventsが走る様に設定
            If 行 = 最終行 Then
                DoEvents
            End If
        End If
    Next
    p = p + 1
Next
'**********************************************************************
'6_後処理
'フォームを消す
Unload frmPB
'マウスカーソルをデフォルトに戻す
Application.Cursor = xlDefault
'**********************************************************************
Application.ScreenUpdating = True

MsgBox "作業完了", vbInformation, "確認"

End Sub

今回は単純なコードです。「ただセルをSelectするだけ」というコードです。

プログレスバーの動きを見る為にある程度時間がかかる処理をさせる為この様なコードを用意しています。

データシート

最後にセルが配置された画面。ここには1万行×7列の個人情報風の情報が並んでいます。

データシートの画像

この値を1つ1つSelectするというコードを用意しました。

程よく手数のかかる作業でプログレスバーの動きを確認出来る様な情報量にしているつもりです。

私がセルに値を入れている理由は値の増減でデータ量を変えられる為です。

皆様各自でデータを用意するのが面倒であればループに絶対値を記入してください。

セルをSelectしてるだけですので値は無くてもコードは組めます。

解説

ではここからコードの解説をしていきます。

フォーム

プロシージャを2つ用意しています。

  • Initializeプロシージャ
  • IsCancelプロシージャ
Initializeプロシージャ

フォームが立ち上がる前に変数 IsCancel に False を設定するプロシージャです。

IsCancelプロシージャ

もう1つはフォームのキャンセルボタンが押された際に実行されるプロシージャですね。

変数IsCancel に True が代入されます。

標準モジュールのコード内で変数 IsCancel に True が入った時の処理を担当します。

マウスのカーソルをぐるぐるから元に戻しメッセージボックスを出してから処理を止めます。

標準モジュール

標準モジュールのコードは以下6つの構成から成り立っています。

コードの構成
  • 1
    変数の定義

    使用する変数を定義します

  • 2
    データ範囲取得

    リストの範囲を確定させるための作業をします

  • 3
    PB初期設定

    プログレスバーに初期値をセットします

  • 4
    ループ処理

    作業を繰り返します

  • 5
    キャンセル処理と進捗管理

    以下詳細をご覧ください。

  • 6
    後処理

    カーソルを元に戻す等作業完了に向けて準備をします

1_変数の定義

変数の宣言(定義)です。

コード内で使う変数とプログレスバーで使う変数を宣言しておきます。

2_データ範囲取得

リストの最終行と最終列を探しに行きます。

リストの最終行を取得する為のコードについては別記事で詳しく書いています。

必要に応じてご覧ください。

3_PB初期設定

ユーザーフォームfrmPBの各種プロパティに値をセットします。

フォームの表示位置のTopとLeftは各自のPCで調整してください。

4_ループ処理

ひたすら値をSelectです。

この4番の作業の中に5番の「キャンセル処理と進捗確認」が配置されています。

5_キャンセル処理と進捗確認

処理を途中でキャンセルしたい時はユーザーフォームのキャンセルボタンを押します。

キャンセルボタンを押すとフォームのコードで変数 IsCancel の値を変更しコードを中断させます。

変数 IsCancel が  False のうちはコードを実行し続けます。

キャンセルボタンが押されて変数 IsCancel が  True になったらコードを抜けます。

是非ご自身でコードを書いて動きを確認してみてください。

進捗率について

進捗確認は全体の仕事量に対して現状何%進んだのかを計算してラベルを書き換えています。

フォームにラベルを用意した際文字数より大きめの範囲を指定したのはこの為です。

単純に変数同士を割り算して比率を出して計算するというだけです。

最初は「処理中」という値が入っています。

進捗表示の為に数値に変わるのでCInt関数を使って文字列が入るはずのラベルを数値に変えています。

ループ処理の中にキャンセル処理と進捗確認を入れ込む事で以下の様に立ち回るという構造です。

  • ループが進めば進捗率が上がる
  • キャンセル処理が入ればループの途中で処理が止まる(処理を抜ける)
コードの中断について

最後に DoEvents ですね。

DoEventsは「なんかったらどうぞ」です。

「裏でたまってた処理あったらやってしまってください」という関数です。

コードの70行目から72行目あたりでコードでラベルの進捗率を変えたりプログレスバーの値を変えてます。

しかしScreenUpdatingが効いているのでコードが走っている間は表示は変わりません。

そんな中でもフォームの表示だけはリアルタイムに変えたいです。

DoEvents関数を使ってできるだけリアルタイム表示に近づける

矛盾が起こる中でこの矛盾を解決させるのがDoEventsです。

走ってしまったコードの処理から抜けて一旦VBAに処理(制御)が返ってきます。

DoEvents関数が実行される際フォームに反映されていないラベルの進捗率を実態に合わせて変えています。

DoEvents関数はキャンセルボタンの処理にも関わる

キャンセルボタンが押された時の処理もDoEventsが実行されたタイミングで稼働します。

私が用意したコードでは77行目の条件分岐の時に状況に応じてDoEventsが実行されます。

列は7列ですのでプログレスバーの表記は6回変わって7回目に変化する時は作業完了という事です。

DoEventsの使用回数をコントロールする理由

なんでこんなことをするのかと言いますとDoeventsは多用するとコードが遅くなります

前回記事はバーの進みを遅くしたかった為行毎にDoEvents関数が発生する様にしています。

実際使う時はある程度サクッとバーが進まないと気持ち良くないです。

DoEventsが走る回数をコントロールしたという次第です。

ここは使い方や対象のデータ量でも変わります。

各自でDoEventsを通過する回数をコントロールしてください。

6_後処理

フォームを見えない様にしたりマウスカーソルを元に戻す作業をします。

作業終了のメッセージボックスを表示させる等後処理を行います。

ポイント

プログレスバーの100%となる作業を何で取るか」がポイントです。

今回はセルを対象にしているのでセルのSelectが進めば進捗は上がっていきます。

これはプログレスバーをご自身の仕事の中に盛り込んでいただく事で使用感を掴んでください。

進捗率の分母と分子を何で取るのかが分かる様になるとコードをマスターしたと思っていただいて良いです。

  • ユーザーフォームを勉強する為のステップとして最適
  • 作業の進捗も分かるようになる
  • 処理を途中で抜けられる

などなど良い事だらけの機能を持ったプログレスバーについて勉強しました。

まとめ

最後にシートにボタンを用意してマクロを実行してみましょう。いかがでしょうか。

動いた方はおめでとうございます。

オートシェイプでボタンを作るボタンの作り方についてはコチラをご覧ください

ボタンへマクロを登録する
お好みでボタンを用意してください。

動かなかった方はエラーが出ていますね。どこかがおかしいです。

間違いを探して直しましょう。プログレスバーの説明としては以上となります。

マクロは家で勉強する事が出来ます

エクセルは色々な事が出来るアプリケーションです。

色々な事が出来過ぎるので作業をマクロ化するつもりで自分の仕事と全く一緒の仕事をしているコードをWeb上で探しても中々見付けられないです。おそらく無いと思った方が良いです。

自分の仕事をマクロ化する為には色々な情報を集めてマクロを構築する為のスキルが必要です。

自身で無料の説明会に参加してきました。マクロを勉強したい方やイチから勉強をやり直したい方におすすめ出来るコンテンツでしたので紹介します。

キャンペーン中 こちらでVBA勉強出来ます
\価格を抑えたい方は是非この機会に受講を検討ください/
タイトルとURLをコピーしました