VBAを使った最強の業務効率化テク

この記事はこのツイートから始まっています。

どなたか個人用マクロブックにマクロを作成して
ショートカットキーに登録までの流れを
自身のHPかブログで説明している人いませんか？

ブログ記事から参考として外部リンクを貼りたいのです

— 筒井@Excel関数擬人化絵描き (@Tsutsui0524) February 13, 2021

ブログでも本記事をご紹介いただきました。
あかアカ |個人用マクロブック 選択セルに循環する色を設定するマクロ ショートカット用VBAコードを公開します

次の記事ではマクロの記録の開始画面で設定するものについて解説しました。

マクロの記録を開始するウィンドウ
https://www.yosato.net/?p=3756

同じような考え方のもと、個人用マクロブックにマクロを記録しておくと極端に業務効率化することができるので、私がいつも使っている実例をもとに解説したいと思います。

個人用マクロブックとは

個人用マクロブックとは、そのパソコンのExcelに共通する設定を保存しているブックで、普段は意識していないのですがいつも裏側でいつのまにか開かれているブックなのです。システム的なブックなので、Excel本体に近いフォルダに格納されているファイルです。

個人用マクロブックの実際のファイルの名称はPersonal.xlsbという名前です。この拡張子のxlsbのbはおそらくバイナリーという意味なのだと思いますが、Excelが異常終了したときに作業中のファイルが自動的に保存されたものにもこの拡張子がつくこともあって、私的にはバックアップという意味で捉えていて、あまり直接は触ってはいけないファイルなのではないかなと思っています。

個人用マクロブックが見えない

VBEで確認しても見えていない場合があります。割と私がそうです。

個人用マクロブックにいろんな設定が保存されていてそれで使っているExcelに悪影響を与えるかもしれないと思って、色々な所で設定を保存しないような設定をしているのですが、そのためか、Personal.xlsbがいつのまにか消されている時があるようなのです。私はPersonal.xlsbがどこに入っているかわからないので直接消したということはないので、いつのまにか消えているということがあります。

もし見えない時は、新規ブックを開いて、表示タブからマクロの記録で適当なマクロ名でマクロの保存場所を個人用マクロブックに設定してマクロ記録開始し、この時点で、VBEでPersonal.xlsbが確認できるので、その中のモジュールを解放します。モジュールのエクスポートは不要です。

その後にマクロの表示をして、作成したマクロを削除します。
それでExcelを終了すれば、次のようなメッセージが表示されるのではいをクリックします。

これで、次に Excel を開いた時には、Personal.xlsbは復活しています。
以前に作成していたマクロの内容は消えてしまうのであらかじめどこかに作成した内容をバックアップしておきましょう。

Personal.xlsbが書き変わって次のExcel起動時は、起動がかなり遅いということも覚えておいてください。

個人用マクロブックに VBA を作成する

マクロの記録によって個人用マクロブックにマクロを作成することもできます。

それ以外にもPersonal.xlsbにVBAを作成することによって、そのマクロをそのパソコンで開いた全てのExcelブックに対して有効にできます。

Personal.xlsbも他のブックと同じようにモジュールを追加することができます。

VBEでPersonal.xlsbを右クリックして挿入の中の標準モジュールをクリックします。

標準モジュールが追加されます。

ユーザーフォームでもクラスモジュールでも同じように追加することができます。

ではこの中に VBA を書いていきましょう。

今回作成するもの

個人用マクロブックに保存するということは、全てのブックに対して使えるような便利なツールであると良いと思います。

私の場合、できることは出来るのですが、関数を組み合わせて計算式を作っていくということに物凄く面倒くささを感じているので、それを解消できるものがあるといいなと思って、IFERROR文による、もしエラーだったら空白になるという処理を、範囲選択した中のセルで計算式になっているものに行うマクロを設定しています

マクロのコードは、次のようなものになります。

Sub エラー回避追加()
  Dim セル As Range
  Dim 計算式 As String
  For Each セル In Selection
    計算式 = セル.Formula
    If WorksheetFunction.IsFormula(セル) Then
      セル.Formula = _
      "=IFERROR(" _
      & Right(計算式, Len(計算式) - 1) _
      & ","""")"
    End If
  Next
End Sub

簡単に説明すると選択範囲を巡回して、ワークシート関数のISFORMULAを使って数式かどうか判断し、もし数式であれば、はじめの「=」「-」「+」「@」などの計算式の接頭語1文字を削除したものの前に、IFERROR関数名、後ろにエラーの時の条件である空白を設定するというものです。エラーの時の空白がダブルコーテーション4個になっているのは、後ろに追加する「,””)」自体も文字列データなので”で囲みたいのですがそれだと、 空白を表すダブルコーテーションが文字列の終わりと勘違いされてしまうので、それを回避するためにダブルコーテーションを4個指定すれば空白を表すというVBAのルールに則った形で書いています。

これをPersonal.xlsbの標準モジュールに書き込みます。

これで個人用マクロブックに計算式をエラー回避した計算式にするツールを埋め込むことができました。

作成したツールの使い勝手

作成したツールは普通のマクロと同じように表示タブのマクロの中のマクロの表示で一覧に表示されます。

あとは実行すれば良いのです。

しかしたったこれだけの動きをするツール、便利なことは便利なのですが、その度に表示タブのマクロの記録のマクロの表示をするのであれば手作業でIFERROR関数をつけてしまったほうが早いかもしれません。

そこで早くこのツールをワンタッチで呼び出す方法が2つあるので紹介します。

クイックアクセスツールバー

クイックアクセスツールバーに呼び出しボタンをつけることができます。

クイックアクセスツールバーを右クリックしてクイックアクセスツールバーのユーザー設定をクリックします。

コマンドの選択をマクロにするとその下の一覧にPersonal.xlsbに作成したVBAが表示されるのでそれをクリックして、クイックアクセスツールバーのユーザー設定をすべてのドキュメントに適用にし、真ん中の追加ボタンをクリックすると右側のウィンドウに登録されるので、必要に応じて変更ボタンをクリックして名称とアイコンを設定して、 OK ボタンをクリックします。

これでクイックアクセスツールバー上に作成したツールを呼び出すボタンを配置できました。

クイックアクセスツールバーのユーザー設定で今回はすべてのドキュメントに適用を設定しましたが、 指定したブックにのみ設定することもできます。個人用マクロブックに入れたのであればすべてのドキュメントに適用でいいと思うのですが、例外的に誰かが使う時にはこのツールは使わせたくないとかということがあるのであれば指定したブックでのみクイックアクセスツールバーから呼び出せるようにするということもできるでしょう。

ショートカットキーの設定

作成した VBA やマクロにはショートカットキーを設定することができます。

クイックアクセスツールバーだとマウスをそこまで持って行かなければいけないので何箇所もある時はちょっと不便かもしれません。そんな時はショートカットキーを設定しておいて左手のアクションだけでツールが起動するようにしておくといいでしょう。

表示タブのマクロの中のマクロの表示をクリックして該当のツールをクリックし、マクロをクリックしてオプションをクリックします。

ここで表示されたウィンドウでショートカットキーを設定することができます。

ショートカットキーはCtrlキーを押さずに指定してください。設定できるのはアルファベットのみ、他のキーとの組み合わせはShiftキーとの組み合わせだけ有効です。

左手で操作する場合はキーボードの左側のアルファベットに割り付けておくといいと思います。

もし普段使っているショートカットキーと同じショートカットキーを割り振るとマクロを起動するためのショートカットキーの方が優先されてしまうので普段使っていないショートカットキーに該当するアルファベットを設定してください。

ショートカットキーを使いたくない

ここまででかなりな効率化ができることが分かったのではないでしょうか。VBAでもっと色々なことができるようにすれば、100分の一まで作業時間の圧縮は可能です。

しかし、私はこの時点で満足できないのです！

今回のようなセルに対する操作をする場合、私はマウスで選択してそこに対してこのツールを実行するのですが、その時にクイックアクセスツールバーでは実際のセルよりも離れているし、ショートカットキーでは指がこんがらがるので、できれば選択してそのままマウスだけで操作できないかと考えるのです。

例えば、マウスのボタンを押すとキーボードの何かのキーを押した反応と同じように反応してくれるようにするソフトもあります。

以下記事参照
https://www.yosato.net/?p=167

マウスのホイールボタンを押し込むとクリックできる中ボタンというボタンがマウスにあるのですが、これは結構無駄になっているのでこのボタンを押したらCtrl+Shift+Qのキーボード入力をすると設定すれば、マウスで範囲選択して中ボタンをクリックしただけでそのセル範囲の数式が全てエラー回避された数式になります。

もしも、今回のようなある範囲に対してちょっとした事をするというツールが3個以内なら、中ボタンの他にマウスのサイドボタンも2つあるマウスが多いので、サイドボタンに設定するというのも良いでしょう。

もっと多い時は、たくさんのボタンが付いているゲーム用のマウスがあるので、それを用意するといいでしょう。その場合、マウス自体にどのボタンを押したらどのキーを押すという設定ができるので上記のようなソフトウェアもその設定もいりません。

また、マウスではなくても外付けテンキーの中にもそのキーを押したらどういうキーの組み合わせをしたのと同じにするという設定もできます。

このような外部のガジェットも1万円以内で用意できるので、個人用マクロブックにVBAを作成できるスキルが着いたら、自分が使いやすいように周辺機器も揃えていくといいかもしれません。

まとめ

個人用マクロブックに入れておけばいつでもどこでも使える業務用ツールがワンタッチで呼び出せる、そのために必要なことは。

• 個人用マクロブックを扱えるようにする設定
• VBAでツールを作るスキル（簡単なものでも）
• 個人用マクロブックのマクロをクイックアクセスバーに登録する
• もしくはショートカットキーを設定する
• もっと言うと周辺機器としてガジェットを用意してそこからマクロが起動できるようにする

ということをすれば、働き方改革、残業ゼロ、負担のないテレワークが実現していくと思います。

今回言いたかったことはもう一つあって、業務ツールでも大掛かりな案件管理システムのようなひとつの仕組みを作るためのVBAもありますが、ほんのひとつふたつの動作をVBAで書くだけでとんでもない時間の短縮ができるということもあると思います。

私の考えるVBAの利用目的は3つあって、

• 大がかりな業務で使う仕組みを作成する
• 一つ二つの操作をまとめて簡単にそれを呼び出せるようにする
• 1回しか行わない1時間しかかからない作業でも、マクロを使って繰り返したら5分でできるかも

という使い方に分かれるのではないかなと思っています。それぞれの世界があってそれぞれの使い方があってそれぞれのVBAの取り組み方は意外とバラバラなのではないかなと思います。

いずれにしても幸せになるためのVBA自動化という目的では一致しています。

簡単な動作でも呼び出しにくい機能がExcelにはたくさんあって、例えば値として貼り付けも単純にマクロの記録で値貼り付けだけの動作を記録して、そこに好きなショートカットキーを設定すれば、それだけでも時短になるかなと思っています。実は私がショートカットキーを覚えないのも、このマクロやVBAで個人用マクロブックにマクロを作って、自分の最も呼び出しやすい方法にすべての機能を割り付けられるからなのです。

範囲選択してマウスの中ボタンをクリックするだけで全部の計算式にIFERRORでエラー回避される