なぜ個人用マクロブック(PERSONAL.XLSB)を使うのか

ExcelにはVBAで記述されたマクロを任意のブックで再利用するための常駐型のブックが存在し、これを「個人用マクロブック」あるいは「PERSONAL.XLSB」と呼ばれる。

この記事では、私がなぜ個人用マクロブックを使っているのか。どんなことに気をつけて使っているのか説明する。

個人用マクロブックとは
- 個人用マクロブックを使ってみる
なぜ個人用マクロブックを使うのか
個人用マクロブック活用のコツ
個人用マクロブックの応用例
- 個人用マクロブックのPERSONAL.XLSBのThisWorkbookモジュールに書くだけで、開いた全てのExcelブックで勝手にA1にカーソルが配置されるVBA
最後に

個人用マクロブックとは

個人用マクロブックについて一言で説明すると「いつも開きっぱなしにしているマクロが記載されたブック」のことである。

例えば、パソコンを立ち上げた時に自動でウイルス対策ソフトやチャットソフトが開くと思う。同じように、Excelを立ち上げた時に裏で開かれるのが個人用マクロブックである。

個人用マクロブックは、Excelを使っているときにいつでも呼び出して使えることから、Excelの機能を拡張するような形で活用する。

代表的なマクロとしては、全シートのセルをA1が見えるようにスクロールするマクロや、列の表示名をA,B,C,D...から1,2,3,4...を変更するものなどがある。

個人用マクロブックを使ってみる

さて、個人用マクロブックを使ったことが無いパソコンでは、新たに作成するか、別のパソコンからコピーを配置する必要がある。

初めて個人用マクロブックを使うのであれば、「マクロの記録」で「マクロの保存先」を「個人用マクロブック」にしてから記録を開始することで、初期設定をExcelが自動でやってくれるのでオススメである。

記録を止めてから、Excelで Alt+F11 キーを押してVBEを開くと、 VBAProject (PERSONAL.XLSB) が増えていることに気がつくと思う。これが個人用マクロブックを記載するワークブックである。

個人用マクロブック「PERSONAL.XLSB」は、マクロが使いやすくなるような設定をして専用の名称を付けて役割を明確にしているだけで、よく使用されるxlsm形式のマクロ有効ブックと同等のものである。

例えば、以下の設定がされている。

Excelを起動したら自動的にバックグラウンドで開かれる
C:\Users\ユーザー名\AppData\Roaming\Microsoft\Excel\XLSTART に保存されている
（上記パスが信頼されているため）自動的にマクロの有効化が行われる
開くのが軽い（らしい）バイナリ形式のエクセルブック（xlsb）になっている
ワークシートのウィンドウが非表示になっている

従って、任意のブックを同じように設定すれば、なんでも個人用マクロブックの代わりにすることができるが、Excelが機能としてフォローしてくれるため手軽に使い始められるのが個人用マクロブックなのである。

なぜ個人用マクロブックを使うのか

次に、私がなぜ個人用マクロブックを使っているのかを説明する。

マクロがすぐに使える態勢になっているから

上記で説明したとおり、マクロの利用環境として最適な設定がされているため、任意のxlsmファイルを探して、開いて、マクロを有効化して・・・という操作が必要ないし、不要なシートウィンドウは出ない。

任意のマクロを実行するときは、Alt+F8を押して PERSONAL.XLSB を選んで絞り込まれたマクロの一覧から選んで実行となる。

頻繁に使うマクロは、リボンそのものやクイックアクセスツールバーに登録して実行できる。

ファイル名が固定されているため、絞り込む時の名前で迷わないし、リボンに登録したマクロが参照切れになる心配がない。

まあ実際には、VBEでコードを見て書いて、その場でF5実行することも多い気がする。

使う時に、余計な思考や手間がかからないのが良いのである。

普通のブックにはマクロが記載できないから

Excelのワークブックには、マクロを認めないxlsx形式と、マクロを認めるxlsm形式やxlsb形式がある。

マクロが含まれるということは、ウイルスなど悪意を持ったコードが含まれている可能性を意味している。このファイル名には、Excelはもちろん、メールフィルタリング機能や、セキュリティシステムに危険性を伝える役割を持っており、詳しい調査や制限をかけるかどうかの指標に使われている。

人としても、誰かからxlsやxlsmやxlsb形式のファイルが送られてきたら、ウイルスではないかと疑ってかかる必要があるが、xlsx形式なら安心して開くことが出来るというわけである。

だから、ブックは極力xlsx形式のままにしておく必要があり、xlsx形式のままマクロを使いたい場合に個人用マクロブックが便利なのである。

※個人用マクロブックを使うのはアプローチの一つであり、別途xlsmファイルを作ったりアドインを使う方法を否定しているわけではない。

以下は、各種Excelの形式に対する個人的な心構えである

安全
- .xlsx 普通のワークブック
危険（送信元によっては受け取らないし、開くときは細心の注意が伴う）
- .xlsm マクロ有効ブック
- .xls 古いエクセルブックでマクロ入りの恐れあり
- .xlsb この形式である理由が無いしマクロ入りの恐れあり
論外
- .xla / .xlam アドインを受け取る機会は無いので送られてきたらウイルスと考える

マクロを保存せずに閉じてしまうことがあるから

読者は「じゃあ保存しろよ！」と思ったことだろう。

しかし、これは2つの理由がある。

新規ブックを決まったフォルダに適切な名前を付けて保存するのが面倒くさいから。そんなことより、今、頭に浮かんでいるコードを忘れないうちに書き出したい。名前を考えるのは後にしたい。結果としてマクロが書かれているのを忘れてExcelを閉じてしまうのである。
意図せずしてExcelがクラッシュしてしまうから。イメージを書き出すのに必死で不完全なコードを実行したり、デバッグ中に無茶なコード修正をすることで、クラッシュしやすい状況が整っている。結果はご想像の通り。

その点、既にファイルが存在する個人用マクロブックなら、Ctrl+S 一発で保存でき、保存してから実行する癖が付いている人には十分なリスク回避策となるのである。

仮に保存し忘れていても、Excelの閉じる時に「個人用マクロブックの変更を保存しますか？」とストレートに聞いてくれるので、誤って保存しないを選ぶ確率が下がると期待できる。

個人用マクロブック活用のコツ

次に個人用マクロブックを使う際に気をつけた方が良いことや、過去に生じたトラブルや疑問などを説明する。

ブックやシートはアクティブなものを使うが、必ず変数に格納してから使用し省略もしない

個人用マクロブックに書くマクロでは、プロシージャの先頭で真っ先にActiveSheetやActiveWorkbookを変数に代入してから使用するのが適切である。

Sub Test()
    Dim ws As Worksheet
    Set ws = ActiveSheet
    
   '処理
End Sub

ブックやシートオブジェクトの省略されたWorksheet、Range、Cellsは、Active～が省略されたかのような挙動をする。（シートモジュールは除く）

そのため、省略表記でも問題ないと考えるかもしれないが、マクロの途中でシートやブックを切り替えたときに、意図しないオブジェクトに対して実行されるというリスクがある。

従って、xlsmでマクロを書くときと同じように、ブックやシートのオブジェクトは省略せずに書くべきである。

同じように、ActiveWorkbookは変化するため、xlsmではThisWorkbookで通用したコードでも、そのままActiveWorkbookに置き換えると安全に動く保証は無いので注意が必要である。

Sub Test()
    ThisWorkbook.Worksheets(1).Range("A1").Value = "これはA1"
    Workbooks("aaa.xlsx").Activate
    ThisWorkbook.Worksheets(1).Range("A2").Value = "これはA2"
    '自分のブックのA2に書き込まれる
End Sub

Sub Test()
    ActiveWorkbook.Worksheets(1).Range("A1").Value = "これはA1"
    Workbooks("aaa.xlsx").Activate
    ActiveWorkbook.Worksheets(1).Range("A2").Value = "これはA2"
    'aaa.xlsxブックのA2に書き込まれる
End Sub

結論としては、プロシージャの先頭でActive~を変数に代入するのが最適解となる。

個人用マクロブックのシートに設定を書き込むのは便利だがリスクが存在する

先の通り個人用マクロブックは普通のワークブックと大差ないため、ワークシートを表示→再表示→PERSONAL.XLSB→OKから表示して、マクロで使いたい設定値などを保存しておくことができる。

ところが、個人用マクロブックを使っている時に誤ってThisWorkbookから始まるコードを実行したことで、個人用マクロブックのシートに書き込んでしまうことがある。

知らず知らずのうちに、設定値が壊れて使い物にならなくなるのだ。（しかも個人用マクロブックはバックアップしてないことが多い）

私は何度かデータが読み込まれ/書き込まれなくて、頭を抱えたことがある。

設定値の保存に使わないとしても、こういう危険性があるのは認知しておいた方が良い。

プロジェクト名の先頭にAAAやZZZを付与すると、プロジェクトエクスプローラの最上位や最下位に固定されて便利

VBEのプロジェクトエクスプローラは、アルファベット順に並ぶという決まりがある。

ワークブックのプロジェクト名は、既定値が VBAProject のため、未設定な場合は後に続くファイル名によって並び順が決められる。

そのため、 PERSONAL.XLSB は他に開いているブックによって場所が変動するため、下図のようにプロジェクト名を変えておくと、使い勝手が良くなる。

プロジェクト名は、プロジェクト名の部分を右クリックして、 VBAProjectのプロパティ から設定できる。

個人用マクロブックでよく使うライブラリを一通り参照設定しておくと、開発中の参照設定が簡単になるので便利

VBAの参照設定は面倒くさい。特にスクロールして目的のライブラリを見つけるのが面倒くさい。

しかし、開いているブックで使用中のライブラリは、上に集中するという性質がある。

従って、常時開かれている個人用マクロブックに、よく使うライブラリを一通り設定しておくと、探す手間が大幅に省くことができる。

これについては、以前記事を書いたことがあるので、こちらにも目を通すと良いだろう。

www.excel-chunchun.com

ステータスバーに「マクロの記録」ボタンを表示すると、リボンの切り替えをせずにマクロの記録が開始できて便利

マクロの記録中は、ステータスバー左下に停止ボタンが表示される。

実はステータスバーの設定で、記録ボタンを常時配置することができる。

個人用マクロブックとは関係ないが、マクロの記録先に個人用マクロブックを使うので、合わせて設定しておくと良い。

マクロの実行Alt+F8から、ゴミマクロを非表示にしたい。

個人用マクロブックにマクロを増やしていくと、そのうち増えすぎてわけがわからなくなる。

それを根本解決するには、適切な名前を付けるのがイチバンだが、いちいち名前を付けるほどでは無いゴミマクロがどんどん増えていくのが、個人用マクロブックである。

プロシージャにひたすらPrivateを付けていっても良いが、なんとも面倒な作業である。（しかも、モジュール外から呼べなくなる）

そんな場合は、せめてモジュール単位で整理して、ゴミマクロの集めたモジュールの先頭に、 Option Private Module と記載することで、モジュールに含まれる全てのプロシージャを非表示にすることができる。

なお、見かけ上の非表示にするだけで、名前を入力すれば実行できるので、クイックアクセスツールバーに登録してあったとしても使えなくなることは無い。

クイックアクセスツールバーに登録したマクロに名前を付けて分りやすくする

クイックアクセスツールバーのアイコンは数が限られており、マクロを見分けるのは困難だった。

Office2021のデザイン（Microsoft365のOffice 2021年12月頃の更新分）からは、クイックアクセスツールバーにラベルが表示できるようになった。

しかし、ラベルは PERSONAL.XLSB!マクロ名 となり、長すぎて画面を圧迫する。

このラベルは、設定画面から変更できるようになっていて、自分が分りやすい好きな文字列にできる。

このときのツイート：https://twitter.com/KotorinChunChun/status/1466590978425618435?s=20

個人用マクロブックを複数のパソコンで同期する

最適と言える方法は存在しない。

案としては以下のようなものがある。

ネットワークサーバーやクラウド同期フォルダに保存して、ショートカットファイルを %AppData%\Microsoft\Excel\XLSTART へ保存する
タスクスケジューラ等でファイルを同期フォルダにコピーして同期させる
ネットワークサーバーやクラウド同期フォルダに保存して、各種設定をする
ソースコードをエクスポート/インポートする仕組みを作る

最適な方法を見つけた読者は、是非とも教えて欲しい。

個人用マクロブックに記載したユーザー定義関数がワークシートから使用できない

これは仕様。既に説明したように、個人用マクロブックは xlsb形式で、普通のワークブックと同じ性質を持っている。そのため、基本的には他のブックからアクセスすることができない。

推奨はしないが、ブック名を付けて参照したり、参照設定したりすれば使えないこともない。

PERSONAL.XLSB!FUNC()

ユーザー定義関数を全ブックから使えるようにするなら、アドインにするのを検討したほうが良い。

全てのブック（Workbooksコレクション）の要素へアクセスすると、個人用マクロブック（PERSONAL.XLSB）も含まれてしまう

これも仕様。XLSB形式は普通のExcelブックと同じ扱いだからである。

全ブックに対して何かするときは、PERSONAL.XLSBだけを弾くような分岐が必須となる。

Dim wb As Workbook
For Each wbIn Workbooks
    If Not UCase(wb.Name) Like "PERSONAL.XLSB" Then
        Debug.Print wb.Name
    End If
Next

最後のブックならApplication.Quitするようにしたいのに、個人用マクロブックがあるとWorkbooks.Countが+1されて最後のブックにならない

先と同様PERSONAL.XLSBを無視して開いているブック数を数える仕組みにする必要がある。

ただ、この目的であればPERSONAL.XLSBだけではなく、Workbook.Windows.Count=0 のブックをカウントから外す必要があるかもしれない。

なお、アドインの場合はWorkbooksコレクションに含まれないので、このような対策が必要がない。

個人用マクロブックが読み取り専用になってしまう時がある

これはExcelのプロセスが多重起動されたことで発生する。

以下の条件でPERSONAL.XLSBの読み取り専用エラーが確認できた。（2021/12/30クリーンインストールされたOS下で検証）

Altを押しながらExcelを起動して、別のインスタンスで開く
Ctrl+Shiftを押しながらExcelを起動して、管理者権限のプロセスとして開く
エクスプローラのプレビューウィンドウ
外部アプリによるExcel起動（Shell / exec）

一方で以下の操作では、発生しなかった。

外部アプリによるExcel起動（COM / CreateObject）
- 別インスタンスが立ち上がるものの、PERSONAL.XLSBが開かれなかった。
Ctrlを押しながらExcelを起動して、セーフモードで開く
- これは既存のインスタンスに統合されたため

プレビューウィンドウの危険性は、以前この記事でも書いた。

www.excel-chunchun.com

個人用マクロブックとアドインは、どちらが良いのか？使い分けるのか？

私は使い分けている。

個人用マクロブック
- マクロの記録で記録されたマクロ
- 下書きのマクロ
- 使い捨てのマクロ
- 自分用のマクロ
アドイン
- 専用リボンを設定
- 他の人へ配布する
- 汎用性が高く完成したマクロ
- システムの根幹をなすプログラム
- 署名をしてパスワードロック済みにして気軽に変更されないようにする

なお個人用マクロブックにできて、アドインでは出来ないということはほぼ無い。

唯一出来ないのは、マクロの記録先に使えないということである。

個人用マクロブックの本来の用途がマクロの記録なので、そこだけは譲れない一線なのだろう。

以下の記事は、アドインと個人用マクロブックと普通のブックの使い分けについて、細かく解説されているため気になる人は読んでみると良い。

違いが分かるExcel使いになるための「マクロの保存方法3種」

個人用マクロブック PERSONAL.XLSB が突然読み込まれなくなった

稀に開いてくれない現象を観測しています。以下の手順で解消されるかもしれません。

Excel左上のファイル
オプション
アドイン
管理（左下にあるドロップダウンメニュー）の「Excelアドイン」を「使用できないアイテム」に変更
設定
PERSONAL.XLSBを選択
有効にする
閉じる
OK
Excelを再起動

個人用マクロブックの応用例

個人用マクロブックのPERSONAL.XLSBのThisWorkbookモジュールに書くだけで、開いた全てのExcelブックで勝手にA1にカーソルが配置されるVBA

Rem 個人用マクロブックのPERSONAL.XLSBのThisWorkbookモジュールに書くだけで、
Rem 勝手にA1にカーソルが配置されるVBA
Option Explicit

Private WithEvents app As Application

Private Sub Workbook_Open()
    Set app = Application
End Sub

Private Sub app_WorkbookOpen(ByVal Wb As Workbook)
    If Wb Is Me Then Exit Sub
    Dim defWs As Worksheet
    Set defWs = Wb.ActiveSheet
    Dim ws As Worksheet
    For Each ws In Wb.Worksheets
        If ws.Visible = xlSheetVisible Then
            ws.Select
            ws.Range("A1").Select
            Wb.Windows(1).ScrollRow = 1
            Wb.Windows(1).ScrollColumn = 1
            Wb.Windows(1).Zoom = 100
        End If
    Next
    defWs.Activate
End Sub

最後に

個人用マクロブックは、自分が楽をするためにVBAを使う人に向いています。

まだ使ったことが無い人は、良ければ使ってみてください。

以上

何か御座いましたらコメント欄、またはTwitterからどうぞ♪

それではまた来週♪　ちゅんちゅん(・8・)

えくせるちゅんちゅん

ことりがエクセルをちゅんちゅんするブログ