VBAで大量のデータを扱う場合、処理が完了するまでに途方も無い時間がかかることもあります。ユーザーから最もクレームを受けやすい部分ですよね。
以下の記事で、処理時間を劇的に改善する方法をまとめています。
しかし、上記のような小手先のテクニックではどうにもならないこともあります。100万行単位でデータがあったりとか物量で責められると高速化にも限界があります。
そんな時はプログレスバーを使用するのが一番ですね。
プログレスバーを使用することで
- ユーザーがアプリを中断しなくなる(ハングしたと勘違いしない)
- 終了する時間をユーザーが推測できるようになる
というメリットがあります。
フォームアプリ標準のものは環境依存のエラーが出やすい
VBAには標準でプログレスバーが用意されているので、通常はこのプログレスバーを使用すれば良いのですが、不特定多数のユーザーに配る時には注意が必要です。
理由は、この標準のプログレスバーは開発者のExcelと使用ユーザーのExcelのバージョンが違うとエラーになりやすいからです。
まず、64bit版のOfficeにはプログレスバーが実装されていないため、表示させようとするだけでエラーになってしまいます。
(よく混同されがちですがOSが64bitでもOffice32bitの環境がほとんどなので、開発者は気づくのが難しいです)
更に使用者のMSCOMCTL.OCXのバージョンによってもエラーが出ることがあります。
以下はマイクロソフトのコミュニティでの話題です。
エクセルVBAで特定の端末で作成したプログレスバーが他の端末でエラーになる。
特定の端末のOffice2003エクセルでプログレスバー付のマクロを作成したところ、他の端末でプログレスバーが「はこのマシンでは利用できないため、オブジェクトを読み込めませんでした。」とのエラーメッセージがでてプログレスバーが消えてしまう。
実際に不特定多数のユーザーに配布したことがあるのですが、1%くらいのユーザーでバージョン差異による不具合が発生します。
(ほぼ、参照設定が参照不可になってしまったり、フォームが表示されないというものです)
その経験から、私はプログレスバーは基本的に自作をするようにしています。
ただ色のついたラベルの長さを変えていくだけ
プログレスバーを作成すると言うと難しいイメージがあるかもしれませんが、そんな事はありません。
色のついたラベルを用意しておいて、その長さを変えていくだけでOKです。
とりあえずフォーム上に以下のような長方形を用意します。


↑キャプションを空にして、BackColorの色を設定します。
キャプションは進捗率を表せるようにする場合は、0%とか書いておいてもOK。
とりあえず、処理が開始したらこのフォームを表示させてラベルの長さを1/100に設定します。
Sub test()
Dim maxWidth As Integer
UserForm1.Show vbModeless
maxWidth = UserForm1.Label1.Width
UserForm1.Label1.Width = maxWidth * (1 / 100)
End Sub

なんかプログレスバーっぽく見えますよね。
重い処理をしつつ、ラベルの横幅を伸ばしていけばいい
Sub test()
Dim maxWidth As Integer
UserForm1.Show vbModeless
maxWidth = UserForm1.Label1.Width
UserForm1.Label1.Width = maxWidth * (1 / 100)
for i = 1 to 100
doEvents
'なんか重い処理
UserForm1.Label1.Width = maxWidth * (i / 100)
next
End Sub
後は上記のような感じで、重い処理をループする中にプログレスバーの長さを変更するコードを差し込んで進捗率を上げていけばいいだけかと思います。
現在が何%か出したいのであれば、LavelのCaptionを変更していけば良いです。
悩ましいのがループしていない重い処理なのですが、これは決め打ちで%を表示するかステップ数で分けるかしか無いですね。
処理名 | 所要時間目安 |
---|---|
A処理 | 10分 |
B処理 | 20分 |
C処理 | 20分 |
D処理 | 10分 |
上記のような場合、A処理が終わったら(10/60)%、B処理が終わったら (20/60)% と進めていけば均等に進むはずです。
ただ1つの処理が大きすぎると、動かない時間が長すぎてユーザーが不安に思ってしまうので、実際は処理を細かく分割して対応すべきかと思います。
仕事上で以前組んだマクロにプログレスバーを使っていて、先日PCをリニューアルしたところ
まさに記事で書かれている「環境違いによる不具合」が発生していました。
そんな中で検索からこちらの記事を見つけ、
記事中のコードも拝借させていただき、無事に不具合回避することができました。
不具合が起きる原因とプログレスバーの自作について簡潔にかかれていて
大変気持のよくなる素晴らしい記事だと感じました。
記事の作成ありがとうございました。
FA業界で設備マシンを製造している会社で働く人