便利なマクロを作ったのに、そのブックを開いたときしか使えない。別のファイルで使いたくて、毎回コードをコピーして貼り付けている。こうした手間は、個人用マクロブック(PERSONAL.XLSB)を使うと解消できます。
個人用マクロブックに置いたマクロは、Excelを起動している間はどのファイルからでも呼び出せます。この記事では、その作り方と実務での使い方を解説します。
個人用マクロブックとは
Excelの起動時に、裏側で自動的に開かれる非表示のブックです。
- ファイル名は
PERSONAL.XLSBで固定 - 起動時に自動で開き、画面には表示されない
- ここに書いたマクロは、他のどのブックからでも実行できる
- 自分のパソコンの中だけで有効(他の人には配布されない)
「セルの書式を整える」「余計な空白を消す」といった、ファイルを問わず使う汎用マクロの置き場所として最適です。
作成方法1:マクロの記録から作る(最も簡単)
個人用マクロブックは、初回だけ作成の手順が必要です。マクロの記録機能を使うのが一番手軽です。
- 「開発」タブ →「マクロの記録」をクリック
- 「開発」タブが無い場合は、「ファイル」→「オプション」→「リボンのユーザー設定」で「開発」にチェックを入れる
- 「マクロの保存先」を「個人用マクロブック」に変更する
- 「OK」を押し、何もせずすぐに「記録終了」をクリック
これで PERSONAL.XLSB が作成されます。VBE(Alt+F11)を開くと、プロジェクトエクスプローラーに VBAProject (PERSONAL.XLSB) が表示されているはずです。
あとはこの中の標準モジュールに、好きなマクロを書いていきます。
作成方法2:VBEから手動で作る
すでにVBEでの作業に慣れている場合は、こちらでも構いません。
- 新規ブックを作成する
- 名前を付けて保存で、ファイルの種類を 「Excelバイナリブック(*.xlsb)」 にする
- ファイル名を
PERSONAL.XLSBにする - 保存先を次のフォルダにする
C:\Users\(ユーザー名)\AppData\Roaming\Microsoft\Excel\XLSTART
XLSTART フォルダに置かれたブックは、Excel起動時に自動で開かれます。保存後、Excelを再起動すると認識されます。
なお AppData は隠しフォルダです。エクスプローラーのアドレス欄に %APPDATA%\Microsoft\Excel\XLSTART と入力すると直接開けます。
実際にマクロを置いてみる
VBEで PERSONAL.XLSB を右クリック →「挿入」→「標準モジュール」を選び、次のようなマクロを書きます。
Sub 選択範囲の空白を削除() Dim c As Range If TypeName(Selection) <> "Range" Then Exit Sub Application.ScreenUpdating = False For Each c In Selection If VarType(c.Value) = vbString Then ' 前後の空白と、全角スペースを除去する c.Value = Trim(Replace(c.Value, " ", " ")) End If Next c Application.ScreenUpdating = True End Sub
Sub 数式を値に変換() Dim rng As Range If TypeName(Selection) <> "Range" Then Exit Sub Set rng = Selection rng.Value = rng.Value ' 数式を計算結果で上書きする End Sub
これらは、どのブックを開いていても「開発」タブ →「マクロ」から選んで実行できます。マクロ名は PERSONAL.XLSB!選択範囲の空白を削除 のように表示されます。
ショートカットキーに割り当てる
毎回マクロ一覧から選ぶのは面倒なので、よく使うものはショートカットキーに登録します。
- 「開発」タブ →「マクロ」
- 対象のマクロを選び、「オプション」をクリック
- ショートカットキーに任意の文字を入力(例:
q→ Ctrl+Q)
Ctrl+C や Ctrl+V など、既存のショートカットと重複するキーは避けてください。 登録すると、Excelの標準機能より自作マクロが優先されてしまいます。Ctrl+Shift+文字 の形(大文字を入力する)にすると衝突しにくくなります。
クイックアクセスツールバーに登録する
キーを覚えるのが苦手なら、画面左上のツールバーにボタンとして置く方法もあります。
- クイックアクセスツールバーの「▼」→「その他のコマンド」
- 「コマンドの選択」で 「マクロ」 を選ぶ
- 個人用マクロブックのマクロが一覧に出るので、追加する
- 「変更」からアイコンと表示名を設定する
常時表示されるため、複数人で共用しているパソコンでも迷わず使えます。
他のブックを操作するときの注意点
個人用マクロブックのコードで ThisWorkbook と書くと、それは PERSONAL.XLSB 自身を指します。操作したいのは今開いている作業中のブックなので、ActiveWorkbook を使います。
Sub 全シート名を一覧化() Dim ws As Worksheet Dim newSheet As Worksheet Dim i As Long ' ThisWorkbook ではなく ActiveWorkbook を対象にする Set newSheet = ActiveWorkbook.Worksheets.Add newSheet.Name = "シート一覧_" & Format(Now, "hhmmss") newSheet.Cells(1, 1).Value = "シート名" i = 2 For Each ws In ActiveWorkbook.Worksheets If ws.Name <> newSheet.Name Then newSheet.Cells(i, 1).Value = ws.Name i = i + 1 End If Next ws newSheet.Columns(1).AutoFit End Sub
| 書き方 | 指すもの |
|---|---|
| ThisWorkbook | コードが書かれているブック(= PERSONAL.XLSB) |
| ActiveWorkbook | 現在アクティブなブック(= 作業中のファイル) |
| Workbooks("名前.xlsx") | 名前で指定したブック |
この違いを押さえておかないと、「マクロを実行したのに何も起きない」という状態になります。実際には非表示のPERSONAL.XLSBの中で処理が完了しているためです。
保存とバックアップ
個人用マクロブックを編集したあとは、保存が必要です。
Excelを閉じるときに「PERSONAL.XLSBに対する変更を保存しますか?」と聞かれるので「保存」を選びます。VBE上で Ctrl+S を押して保存することもできます。
バックアップは必ず取ってください。 パソコンの入れ替えや初期化で、XLSTART フォルダごと失われると、蓄積したマクロがすべて消えます。
C:\Users\(ユーザー名)\AppData\Roaming\Microsoft\Excel\XLSTART\PERSONAL.XLSB
このファイルを定期的にクラウドストレージや外部ドライブにコピーしておきます。新しいパソコンへ移す場合も、同じ場所にコピーするだけで元通りになります。
使うときの注意点
配布には向かない
個人用マクロブックは自分の環境専用です。他の人にマクロを渡したい場合は、通常の .xlsm ファイルやアドイン(.xlam)として配布します。「自分のパソコンでは動くのに」というトラブルの典型例です。
非表示なので存在を忘れやすい
画面に出ないため、「表示」タブ →「再表示」で PERSONAL を選ぶと編集できます。ただし、作業後は必ず再度非表示に戻してください。 表示したままだと、他のブックを閉じてもExcelが終了しなくなることがあります。
マクロが増えたら整理する
すべてを1つの標準モジュールに書くと、あとで探せなくなります。「ファイル操作」「書式整形」「集計」のようにモジュールを分けると管理しやすくなります。
まとめ
- 個人用マクロブック(PERSONAL.XLSB)に置いたマクロは、どのブックからでも使える
- 作成は「マクロの記録」で保存先を個人用マクロブックにするのが最も簡単
- ショートカットキーやクイックアクセスツールバーに登録すると実用性が上がる
- 他のブックを操作するときは
ThisWorkbookではなくActiveWorkbookを使う - ファイルのバックアップを取っておく
「毎回同じ手作業をしている」と感じたら、その処理を個人用マクロブックに移していくと、日々の作業が少しずつ軽くなります。
他の記事でもExcel VBAの実務的なテクニックを紹介していますので、ぜひチェックしてみてください。