Excel 個人用マクロブック(Personal.xlsb) 最初に必ず入れておく高速化用コード

※この記事は、下記2点の記事により事前準備していることを前提にしています。
Excelに「個人用マクロブック」をまだ作っていないとかいう方は、下記の記事の通り、個人用マクロブックを作成・準備して下さい。

Excel 個人用マクロブック マクロ追加可能状態にする

2019年4月7日

Excel Personal.xlsb(個人用マクロブック)を追加しよう

2021年2月13日

今回の記事では、Excelの「個人用マクロブック」に、全ての基本として入れておきたいものを述べていく。

といってもこの基本をしっかり押さえ盛り込んでいる人は、そう多くないと思うので、今回の内容を押さえればセンスあるマクロ使いの第一歩を踏み出せるだろう。

何も考えなくて良いのでまず最初に個人用マクロブックに追加しておくコード

Excelの個人用マクロブックPersonal.xlsbにはまず絶対に、下記のコードを追加しておきたい。
中身については後で述べるので、まずは何も考えず、下記のコードを追加だ。

追加の方法は、
Alt+F11
でVBEというものの画面を開き、個人用マクロブックの「モジュール」にコードをコピペすれば良い。

「モジュール」については前回記事を参照。

Excel 個人用マクロブック マクロ追加可能状態にする

2019年4月7日

このソースコードを追加さえすれば、今回の記事の、後の方は読まなくても特に問題ないくらいだ、と思う。

Excel マクロの自動記録とVBA

上記「高速化基本マクロ」そのものの解説の前に、マクロとかVBAとかいうものの基本について言及する。

Excelの「マクロ」と「VBA」の厳密な違いを解説するのは難しいが、「VBA」というのは一種のプログラム言語のこと。

なので「VBA」では、上記のようなコードを書いていくことになる。

それに対し単なる「マクロ」であれば、単にExcelを操作してみてそれを自動記録するだけでとりあえず作成できる。

「とりあえず作成できる」というだけであって、この自動記録で作成したマクロについても、内部的にはVBAと同じくコードが生成されている。

Excelのマクロを使おうと思うなら、この内部的に生成されたコードというやつをそのままにせず、手入れできるようにならないといけない。

その手入れの知識がないまま、単なる自動記録のマクロだけでやってる人というのが、どれだけいるのか私には分からない。
少なくとも私は、全てイチから「VBA」でコードを書いていく派であって、自動記録を使う方が圧倒的に少ない。

というか、「自動記録でしかマクロやれません」というレベルの人が、自動記録したマクロがどういう法則性で動作するのかを把握しながら使用できるとは私には想像できない。

少なくともそういう人たちは、今回の記事で述べるような高速化テクニックを有効に使えている可能性は極めて低い。
あなたはそうはならず、無駄を省き効率的に速く動作するマクロを使っていこう。

VBA 本文ソースの基本解説

それでは上記の「高速化基本マクロ」の本文(ソースとか、ソースコードといいます)について述べていく。

というかまず、基本的なソースの読み方からという話。
まず、「’」文字(シングルクォーテーション)を付した文字は、VBA言語において「コメント」というものだ。

この「コメント」にした部分はVBAプログラムの動作において、一切無視する部分。
文字通り自分のコメントやメモを書く時に使うし、VBAの動作にモロ使うコードを書いたけどひとまず無効化しておきたいときにも使う。

上記「高速化基本マクロ」のコードでいえば

の部分は先頭に「’」文字が付いているけど、これでこのコードを無効化している。

まあこれは、このEnableEventsとかいうやつを有効化してもいいかもしれないけど私が自信を持てないという話で。

VBAに限らずあらゆるプログラミングにおいて、この「コメント」は凄く重要な要素。
プログラムを動かす本体部分より、このコメントの方が記述分量が多くてもいいくらい。

そして「 Sub」から「 End Sub」までで区切られたものが一つのマクロの塊だ。
すなわち上記「高速化基本マクロ」では、「appSet」「appReset」の2つのマクロが書かれているということだ。

Excel VBA 高速化

んで、Excel VBA において必ず意識しておかないといけないのが、余計な処理プロセスを削って高速化することだ。

普段使うExcelは、われわれ人間が見て分かるようにするために、色々とエネルギーを使っている。

• 数値を入力したらその瞬間に計算してくれて、関係するあちこちのセルを即更新してくれる。
• 上書き保存する前の確認メッセージなど、警告・確認を出してくれる。

といったことだ。
これらは親切なのだが、自動化を旨とするVBAにおいては削っていくべきものだ。

人間の場合で考えてみると良い。「5+5×6」みたいな割と簡単な計算問題を、自分の頭の中で考えて答えを出すだけなら、おそらく3秒で出来るだろう。

しかしこれを、掛け算を先にやる決まりなんだよとか解説や注意書きを、他人の目に見える形に表してやるプロセスが加わるとすると、そんな一筋縄の時間では済まないだろう。

Excel VBAでは、処理プロセスの中で極力多い割合を、人間で言えば「楽勝な問題を自分の頭の中で秒で片付ける」ことに限りなく近付けないといけない。

そのために初めに言われる3点のコツが、

ということだ。

描画を省略

入力した文字が人間に見えるように、はっきり画面に表示するのに、Excelは普段エネルギーを使っている。

「描画を省略」というのは、画面を単純な真っ白画面のままにしてでも、そのエネルギーを削るということだ。

自動で計算を更新するのをやめさせる

もうこれについては、こちらを参照。

Excel 自動計算と手動計算 ～Excelは自動的に計算してくれるとは限らない～

2020年3月21日

警告メッセージを省略させる

たとえばExcelでは上書き保存する前に確認メッセージが出るものだが、それを出させず、確認するまでもなく上書き保存するようにするといったこと。

上記「高速化基本マクロ」の「appSet」マクロで、それら3点の処理が書かれていることになる。

・・・しかしなのだが、これら3点は基本中の基本で、あちこちのサイトで当然紹介されるのだが、

Application.ScreenUpdating = False ‘描画を省略

の一文だけを単体で利用する、

Application.Calculation = xlCalculationManual ‘手動計算

の一文だけを単体で利用する・・・ といった方法ばかりが解説されているように思うのだ。

3点セットが3点セットでなく、単体でぶった切られているのだ。

これら3点セットは、ほぼ必ず一体にして扱うものだ。
まあ、計算方法を手動にしちゃまずい場合とか例外はあるよ？でもそっちの方が稀なので。

で、「高速化基本マクロ」は3点セットを一体にまとめてるというわけ。

繰り返すワードを省略可能にする「with」

もう一度「高速化基本マクロ」を書く。

コードの中に「With」で始まり「End With」で終わる塊がある。

「高速化基本マクロ」をまともに、というか要領の悪い方法で記述すると

というようになるのだけど、先頭に3回も「Application」文字が繰り返されていて、慣れてくるとこういう繰り返しが無性に我慢ならなくなってくる。

この先頭文字の繰り返しを省けるようにするのが「With」～「End With」だ。

「高速化基本マクロ」で「With」～「End With」の中にある文は「.」ピリオドから始まっていて、まるで「、」読点から始まる日本語みたいで不自然な感じだが、その不自然を自然にしてくれるのが「With」～「End With」というわけだ。

「With」～「End With」の中では、繰り返すワードは省略可能であり、省略できるのはこうやって、「.」ピリオドが後ろにくっつく構文の場合だ。

そもそもどういう場合に「.」ピリオドが付くのかということは、色々と難しいけど、機会があれば詳細に解説する。

1行ずつコードを実行してみよう

それでは最後にちょっとした作業。
今回VBEにコピペしたソースコードで「appSet」マクロのコードのどこかをクリックしてF8キーを押してみよう。

1行ずつ順番に、背景色が黄色くなっていくはずだ。

マクロっていうのは普通、何千行もあるコードであっても超高速でいっぺんに処理されるんだけど、このF8キーを押す「ステップイン」というやつで1行ずつ順番に止めながら実行していける。

F8キーを連打していけば、マクロ「appSet」一式が全部実行される。

そのままだと、「手動計算」の設定が残っているはずだ(その辺の確認方法はこちらの記事を参照)。

だから今度はマクロ「appReset」についてF8キーを連打して実行し、「自動計算」の状態に戻そう。

F8キーを押して1行ずつ実行する方法を述べたが、1行ずつじゃなくていっぺんに全部実行したい場合はF5キーでOK。これも試してみよう。