VBAの画面のちらつきがたった1行で解決して処理高速化も

excel-vba Excel-VBA
スポンサーリンク

こんにちは、とりっぷぼうるです。

元々エクセルとは全く縁のない生活をしていましたが、人員不足などで少しでもデータ入力作業や集計作業を効率化するしか方法が無く、仕方がなくエクセルに取り組み始まました。

なぜならば、大きな会社ならば専用ソフトやクラウド上でデータ集計や管理を行うのでしょうけど、小さなところではそのようなお金もないのでエクセルで解決するしか方法が無いのですよね。

このような理由からエクセルに取り組み始めましたので、当然ながらエクセルの専門知識もなく、学校に通うこともできずにただ単にエクセルの書籍とにらめっこしながら取り組んでいます。

波乗りアヒル
波乗りアヒル

でも書籍のままだと実用的でないことが多く、VBAコードを組み合わせてみたら動いた!という実用例・体験談を書いています。

Excel-VBAの専門学校通学や・資格があるわけではありませんので、あくまでエラーにならずに動いてくれているVBAコードになりますので、掲載のVBAコードが同じように動く保証はできません。
実際に動いているエクセルは2009と2016です。

目障りな一瞬の画面ちらつきを防止したい

エクセルでセルに値を入力する時にスクロールをして、目的のセルを表示させてから文字や数字を入力するのが当たり前ですよね。

当然わざわざ自分自身の目で見ながらセルを移動させるのは非常に非効率で、ボタンひとつワンクリックで目的のセルが移動・表示させることができれば作業効率化できますよね。

波乗りアヒル
波乗りアヒル

このページにたどり着いたということは、ユーザーフォームを使うようになると、同じ思いをしている方だと思います。

コマンドボタンを押すと画面が一瞬ちらつく!

本来は、というよりも、画面の見えないところで並べ替えやデータの抽出などを行ってから、元の画面に戻るようにVBAコードを書いているのに、その作業中の画面が移動して見えてしまう。

VBAコードでの処理のデータ量にもよりますが、ほんの数秒あるかどうかの時には一瞬画面が切り替わりちらついているようになってします。

波乗りアヒル
波乗りアヒル

目障りなので何とかしたいと思いましたよね、だから検索してこのページにたどり着いたと思います。

Application.ScreenUpdatingで更新停止

Excel-VBAの書籍には処理内容に合わせて画面表示が更新されて、画面がちらつき処理が遅くなるのを防止するためにScreenUpdatingプロパティを使うと記載されています。

例えば、シート1を表示していてシート2のセルAA1~AZ1000に「1」を入力したら、シート1のセルA1に移動するというコードだけを書くと、下記のようになります。

Sub 画面更新停止()
Sheets(“sheet2”).Select
Range(“AA1:AZ1000”).Value = “1”
Sheets(“sheet1”).Select
Range(“A1”).Select
End Sub

実際にこのようなVBAコードは使いませんが、一瞬画面がちらつきますので、ScreenUpdatingプロパティを使うとちらつきが防止できます。

Sub 画面更新停止()
Application.ScreenUpdating = False
Sheets(“sheet2”).Select
Range(“AA1:AZ1000”).Value = “1”
Sheets(“sheet1”).Select
Range(“A1”).Select
Application.ScreenUpdating = True
End Sub

使い方は簡単で、VBAコード処理の前にApplication.ScreenUpdating = Falseと書いて、画面更新処理を停止します。

最後に(End Sub)の前にApplication.ScreenUpdating = Trueと書いて、画面更新処理を再開(元に戻す)します。

波乗りアヒル
波乗りアヒル

Falseが停止、Trueが再開です。

簡単でしょう!

ScreenUpdatingを使うことにより、ちらつき防止のついでに無駄な更新もなくなるので、VBAコードの処理も早くなりました。
VBA書籍にはTrueを省略可(マクロ実行終了で自動的に戻る)となっていたので、使い始めた当初は省略していましたが、時々フリーズすることがあったので、今は省略せずに必ず記載しています。記載してからはフリーズしていません。

ユーザーフォームで威力を発揮する実用例

Application.ScreenUpdatingは今ではほとんどのコマンドボタンに使っています。

ラベル一括変換後

ユーザーフォームで作った参加選手修正フォーム

このユーザーフォームはボウリングのリーグ戦での成績表エクセルファイルで使っています。

このユーザーフォームでは、登録済みの名前(画像ではJR常磐線の駅名)が並んでいますが、修正する時に、名前(Label)をクリックして【修正登録】ボタンをクリックすると、その名前が記載してあるセルを探して見つけたら移動して、書き換える為のオリジナルフォームです。

VBAコードはこのようになっています。

Private Sub Label44_Click()
Application.ScreenUpdating = False
Range(“AA1”).Value = “1”
msg = MsgBox(“選手1を修正しますか?”, Buttons:=vbYesNo + vbQuestion)
If msg = vbYes Then
Dim mycell As Range
Set mycell = Range(“AA3:AA42”).Find(What:=Range(“AA1”).Value, LookAt:=xlWhole)
If Not mycell Is Nothing Then
mycell.Select
UserForm1.TextBox1.Value = mycell.Offset(0, 1).Value
UserForm1.TextBox2.Value = mycell.Offset(0, 2).Value
UserForm1.TextBox3.Value = mycell.Offset(0, 4).Value
Else
End If
End If
Application.ScreenUpdating = True
End Sub
  1. 「ラベル44をクリックしたら実行する」というマクロの記述を開始
  2. 画面の移動を停止する
  3. セルAA1の値を1にする
  4. メッセージで「選手1を修正しますか?」を表示して「はい」「いいえ」で答える
  5. もし「はい」が選択されたら
  6. 変数「mycell」を宣言して
  7. 「mycell」はセルの範囲(表の範囲)AA3:AA42の中でセルAA1に記載された値を探す
  8. もし「mycell」が見つからない、ではない(見つかった場合)
  9. 「mycell」を選ぶ
  10. ユーザーフォーム1のテキストボックス1の値はmycellの同じ行の1列右のセルの値とする
  11. ユーザーフォーム2のテキストボックス1の値はmycellの同じ行の2列右のセルの値とする
  12. ユーザーフォーム3のテキストボックス1の値はmycellの同じ行の4列右のセルの値とする
  13. 見つからなかったら
  14. Ifステートメント終了(検索終了)
  15. Ifステートメント終了(メッセージ)
  16. 画面の移動停止を再開する
  17. マクロ記述終了

Application.ScreenUpdatingの間にいろいろなコードがあるけど、これをやっておかないと、mycellが変数なので、ここが変わるたびに画面があっちこっちへ飛んでしまうので見苦しい!

それが、Application.ScreenUpdatingを使うと画面は常に一定しているので、処理も早くなりました。

画面ちらつきも無くなり、処理も早くなるApplication.ScreenUpdatingを使うことをお勧めします。

ちなみにここでご紹介したサンプルVBAコードの詳細は下記よりご覧ください

メッセージボックスではいといいえを選択させる方法
エクセルで操作をしたらメッセージを表示させることありませんか?その時にはいいいえを選択させて、それぞれ処理を振り分けることしたくありませんか?
表の中から同じ検索条件で条件に合うデータだけを順に取り出す方法
成績表や住所録などの表の中から条件に合うデータだけ取り出してリストアップして一覧表を作りたいって事ありませんか?

VBA作成中によくあるエラー

エクセルVBAで様々な表などを作っていると、今まで出来ていたことが急にできなくなったりした経験はありませんか?

そんな個人的な経験を書いてみました。

各コントロールまとめて転記シリーズ

ユーザーフォームを使うと配置したコントロール(テキストボックスやラベルなど)とセルとの間で値のやり取りが多くなります。

よく使うのでまとめてみました。

値の場所転記したい場所説明ページ
セルの値ラベル名VBAコード
セルの値テキストボックスの値VBAコード
セルの値コマンドボタンの表示名VBAコード
セルの値セルの値VBAコード
テキストボックス入力値セルの値VBAコード
波乗りアヒル
波乗りアヒル

成績表作成には知っておきたいExcel-VBAもくじ

ボウリングハンデキャップのつけ方
HDCPの種類はリーグのルールにより複雑に ボウリングの成績表(リーグ戦)で必ず必要になるのが、ハンデキャップ(HDCP)です。 ボウリングご予約の時に一番多く使われているのが、女性にだけハンデキャップをつけてあげることが多いですね。 だいだい何点ぐらいのハンデキャップが多いの?
ボウリングリーグ対戦表の数字を名前に自動変換する方法
リーグ戦の対戦表をエクセルで作る時、数字で表されている番号を名前に自動的に変換できると作業時間が短くなり効率的ですね。たった2つのエクセル関数を使うだけで、簡単にできるんです。今回の回戦数を入力するだけで一発変換完了。もちろんお客様にスケジュール対戦表として渡すことも簡単ですよ。
人数に応じてリーグ対戦表を自動選択する方法
複数の表があり、条件に合った表を選んで、さらにセルの値を選んで数字を名前に自動変換できると、とても便利なエクセルができますよね。ボウリングのリーグ対戦表も人数により表が異なるので、参加人数が決まったら、自動的に表を選んで対戦番号がこれも自動的に参加者名に変換されると便利で作業効率がアップして時間短縮になりますよ。
波乗りアヒル
波乗りアヒル

エクセルの勉強お疲れ様です。ちょっとひと休みしませんか?

コメント