会社パソコンってどこまでバレてるの? 管理者が解説してみた

【ExcelVBA処理高速化】時間計測をして比較実験してみた

マクロ処理を高速化する記述をご紹介します
システム開発メーカー勤務

エクセルVBAの処理速度を上げる方法を教えてほしい

上記のお悩みを解決します。

本記事の内容
  • たった2行追加することで、エクセルVBAの処理速度向上が期待できます
本記事の根拠
  • 時間計測をして、早くなっていることを確認済です
本記事を読んでほしい人
  • Excelマクロを多用している方

それでは本題に入ります。

筆者はこんな人

中小企業の小規模情シス(総員2名)勤務歴まもなく10年目。
特技はSSD換装。

トランスフォーマーや漫画が大好きなオタクでもあります。
車1台は買えるくらいトランスフォーマーに注ぎ込んできました。

職場で日経パソコンや日経コンピュータを読み漁る日々。
おかげで、最新のパソコン機器やデバイスの知識は常にアップデート状態。

私が雑誌に読みふける一方で、取引先のシステム開発メーカーの方はプログラム作成・修正に追われている。
次第に、システム開発メーカーの中堅の方を憂うようになる。

システム開発屋さんから情シスに転職したい方の手助けをするためにブログを立ち上げた。
狙うは『中小企業の小規模情シス』。

一度きりしかない人生を少しでもより良いものにしませんか?

目次

処理時間を早めるなら画面更新OFF/ONのコード

結論です。

マクロの最初と最後のほうに下記のコードを入れることで画面更新をOFF/ONすることができます。

マクロの最初

Application.ScreenUpdating = False ’画面更新OFF

マクロの最後

Application.ScreenUpdating = True ’画面更新ON

上記の2行のコードを記述するだけです。

下記の処理は、罫線を適当に引いてから数値入力&コピペしています。(繰り返し10回)

画面更新なしの場合(高速化)クリックで動きます

画面遷移なしの高速化バージョン

処理時間=0.703125

画面更新ありの場合(遅め)クリックで動きます

画面が遷移するので体感的には遅め

処理時間=6.64453125

画面更新をオフにすることで、約1/9.45に処理時間を短縮することができました。

画面更新とは?

マクロ実行時に画面がチカチカすることはありませんか?

画面チカチカは、マクロで実行している作業を画面表示しているだけなんです。
マクロに精通されていない方だと、チカチカ画面を見ると『すげぇ!』ってなります。

しかし、システム管理者からすると処理時間が余分かかっている重たい処理なんです・・・。(もったいない)

今回は、マクロ処理が重たくなるのを防ぐために『画面チカチカをオフにしよう』というお話です。

たった2行追加するだけなので、とても簡単かつ効果絶大です。

やらないのは損ですよぉ~。

実験・検証してみた

今回の実験では、画面更新をオフにすることで約4倍も処理が早くなりました。

実験内容

新シート追加→表作成の動作を10回繰り返す。
画面更新あり・なしで、それぞれ10回の処理時間の平均値を算出する。

結果

  • 画面更新ありの平均値は『0.455078』
  • 画面更新なしの平均値は『0.113672

画面更新なしは、画面更新ありと比べて、約4倍も処理速度が上がっていることが分かりました。

テストに使用したコード

こんなテストコードを用意したよ

Sub 画面更新あり処理時間計測テスト()

'画面更新off
'Application.ScreenUpdating = False


'時間計測 変数定義
Dim start_point As Double
Dim end_point As Double
Dim processing_time As Double
Dim i As Long

'スタート時間取得
start_point = Timer

'新シート追加
Sheets.Add After:=ActiveSheet

'メインルーチン
Dim z As Integer
For z = 1 To 10

'罫線引く
Dim x As Long, countmax As Long, no As Long

countmax = 100

For y = 2 To countmax

Range("A" & y & ":F" & y).Borders.LineStyle = xlContinuous

Next

'新シート追加
Sheets.Add After:=ActiveSheet

Next z

'初期位置に戻る
Sheets("Sheet1").Select
Range("A1").Select

'エンド時間取得
end_point = Timer

'処理時間表示
processing_time = end_point - start_point
MsgBox "処理時間:" & processing_time


'画面更新on
'Application.ScreenUpdating = True

End Sub

Sub 画面更新なし処理時間計測テスト()

'画面更新off
Application.ScreenUpdating = False


'時間計測 変数定義
Dim start_point As Double
Dim end_point As Double
Dim processing_time As Double
Dim i As Long

'スタート時間取得
start_point = Timer

'新シート追加
Sheets.Add After:=ActiveSheet

'メインルーチン
Dim z As Integer
For z = 1 To 10

'罫線引く
Dim x As Long, countmax As Long, no As Long

countmax = 100

For y = 2 To countmax

Range("A" & y & ":F" & y).Borders.LineStyle = xlContinuous

Next

'新シート追加
Sheets.Add After:=ActiveSheet

Next z

'初期位置に戻る
Sheets("Sheet1").Select
Range("A1").Select

'エンド時間取得
end_point = Timer

'処理時間表示
processing_time = end_point - start_point
MsgBox "処理時間:" & processing_time


'画面更新on
Application.ScreenUpdating = True

End Sub

実験結果の表

実験結果の表だよ

回数画面更新あり画面更新なし
10.4218750.117188
20.4492190.109375
30.4492190.105469
40.4726560.109375
50.4257810.109375
60.4648440.125000
70.4882810.105469
80.4765630.121094
90.4609380.105469
100.4414060.128906
平均値0.4550780.113672
画面更新あり・なしの実験結果

実験をするごとにエクセルファイルを閉じてから開き直しをしています。

ご覧になって分かるように、『画面更新をオフ』にした方が処理速度が早いことが証明されました。

注意点

マクロの最初に、

Application.ScreenUpdating = False

マクロの最後に、

Application.ScreenUpdating = True

この2つのコード追記するだけですが、ちょっとしたミスでマクロを壊してしまう可能性もあります。

普段からVBAコードを触る方なら問題ないと感じますが、初めて触るよって方は要注意です。

作業前には必ずエクセルファイルのバックアップを取ることをオススメします。(バックアップと言っても、ファイルコピーするだけですが)

まとめ

ポイントと注意点をまとめておきます。

ポイント注意点
たった2行を追加するだけで、約4倍も処理速度が上がったよ!マクロに追記する場合には、ファイルのコピーを取っておこう!
ポイントと注意点

複雑なマクロ処理ほど、効果絶大です。(特にコピペ多用マクロは画面遷移が多いですからね)

普段の業務でマクロを使われている方は、ぜひいちど試してみることをオススメします。以上です。

※本記事の内容で実行された結果について、筆者は一切の責任を負いませんので、ご了承ください。 必ず自己責任で実行してください。

\34インチ湾曲ウルトラワイドモニター没入感ハンパナイ/

1000Rの湾曲ウルトラワイドモニターでグランツーリスモ7をやってみた

本業情シスの私が、厳選しまくって選んだのが湾曲率1000R34インチウルトラワイドモニター。
自分だけのプライベート空間で、圧倒的没入感を感じてみたい方には特にオススメします。

/本業情シスの私がセッティングした自慢のゲーミングルームをみてみる\

湾曲ウルトラワイドモニターの有効活用は、下記の記事もオススメ

Twitterでみんなに知らせる!
  • URLをコピーしました!

この記事を書いた人

らもさんのアバター らもさん ひとり情シス待ったなし

中小企業の小規模(総員2名)情シスに勤務して、まもなく10年目。

取引先の多忙なシステム開発メーカーを見て感じたこと。
『この人たちがうちの情シスに来たら無双できるのに・・・もったいない』

中小企業の小規模情シスのリアルを暴露しつつ、
システム開発メーカー勤務の方が安心して、情シスに転職できるポイントと注意点を全力で発信します。

目次