EXCEL VBAでfor文のループ中にセットしたラベルのCaptionをfor文ループ中に表示させたい。

Question

Excel VBAで、アプリケーションを作成しています。

処理時間がが長いので、For文中に進捗状況を表示させるために、ActiveXのラベルを設置して、そのCaptionに進捗を表示させるようにしたのですが、DoEventsを使っても、Sleepで少し待っても、ScreenUpdatingをTrueにしても、処理が完了するまで、ラベルのCaptionにセットした文字が表示されません。

つまり、処理中にはラベルのCaptionがずっと何も表示されず、処理終了後に、最後にセットしたCaptionが表示されるという状態です。

ちなみに、デバッグモードでステップ実行をすると、ループ中でもCaptionにセットした文字が表示されます。

また、ソースはこのように書いてます。

For i = 0 To 100000

    Application.ScreenUpdating = True
    
    DoEvents
    
    Sleep 1000
    
    Worksheets(1).lblMassage.Caption = i & "番目を処理中"
    
    DoEvents
    
    Application.ScreenUpdating = False
    
    (VBAの処理)

Next

DoEvebts、ScreenUpdating 、Sleepなどを、このようにつけたり、無くしたり、いろいろな組み合わせでやってみましたが、結果は何も変わりませんでした。

もちろん、ScreenUpdatingの記述を完全に無くしてもみました。

ActiveXのラベルのCaprionは、処理中にセットしたものは、処理が終わるまで表示されないものなのでしょうか？

Answer 1

Excel2010で試したところ問題なく更新されました。
ちなみにこれだけ。

For i = 0 To 100000
    Worksheets(1).lblMassage.Caption = i & "番目を処理中"
    DoEvents
Next

提示されているコードでも問題ありませんでした。
別の原因があるような気がします。

Comments

[GiveAHand]

ttyp03様

ご回答ありがとうございます。

おっしゃるとおり、このプログラムで、私のほうでもきちんと表示されました。

そして、原因わかりました。
原因は、プログラム自体を関数にして、標準モジュールに入れていたのですが、それが問題だったようです。

プログラム自体を、シートに書いたら問題なく更新されました！

動作検証して、動くという報告を頂いて私も方でもピンときました。
ありがとうございました！

Answer 2

私もttyp03さん同様、Excel2010環境で検証しましたが、問題なく1件づつ再描画されました。

メインのVBA処理ではScreenUpdating = Falseにしたいと考えると、最低限以下のコードがあればラベル再描画が行われると思います。

Sub Test()
    For i = 0 To 100000
    
        Application.ScreenUpdating = True
        DoEvents
        Worksheets(1).lblMassage.Caption = i & "番目を処理中"
        DoEvents
    
        Application.ScreenUpdating = False
    
        '(VBAの処理)
    
    Next
End Sub

これで再描画されないのであれば、前後の処理や環境の違いなどが原因かもしれません。

100000回というループ数に対して毎回DoEventsするのはいたずらに処理を重くしているような気がしますが、それはまた別の話。
lblMassageがMessage(メッセージ)ではなくMassage(マッサージ)なのも気になりましたが、それもまた別の話。

Comments

[GiveAHand]

jawa様

ttyp03さんの回答にも書きましたが、原因は、プログラム自体を関数にして、標準モジュールに入れていたのですが、それが問題だったようです。

プログラム自体を、シートに書いたら問題なく更新されました！

非常にご丁寧な検証をありがとうございました！
これが無ければ気が付かなかったです。

ありがとうございました！

Answer 3

ActiveXって、懐かしい響きがしますね。
直接的な回答ではないですが、ステータスバーを使うという代替案ではダメですか？

Application.StatusBar = i & "番目を処理中"

こちらのほうがよほど簡単ですし、無駄に時間とられるより
現実的な対応をするというのもひとつだと思いますがいかがでしょう。

Comments

[GiveAHand]

BJCRobot様

ご回答ありがとうございます。

そうですね。。。
フォームではなく、シートに直接オブジェクトを貼り付けていますので。。。

Answer 4

少し扱いがメンドクサイ AND 1秒ずつ更新しかできませんが
Application.OnTime
とかー

Comments

[退会済みユーザー]

あれ？lblMassageってなんだろう
labelじゃないのか・・・

[GiveAHand]

dot-asterisque様

ご回答ありがとうございます。

こんなのもあるんですね。
ただ、確かに扱いめんどくさそう。。。＾＾；