インハムシステムの自動化ノート

現役エンジニアが、Excel VBA・Google Apps Scriptによる業務効率化のヒントをお届けします。

【Excel VBA】個人用マクロブックで自作マクロをどのファイルでも使う方法

便利なマクロを作ったのに、そのブックを開いたときしか使えない。別のファイルで使いたくて、毎回コードをコピーして貼り付けている。こうした手間は、個人用マクロブック(PERSONAL.XLSB)を使うと解消できます。

個人用マクロブックに置いたマクロは、Excelを起動している間はどのファイルからでも呼び出せます。この記事では、その作り方と実務での使い方を解説します。


個人用マクロブックとは

Excelの起動時に、裏側で自動的に開かれる非表示のブックです。

  • ファイル名は PERSONAL.XLSB で固定
  • 起動時に自動で開き、画面には表示されない
  • ここに書いたマクロは、他のどのブックからでも実行できる
  • 自分のパソコンの中だけで有効(他の人には配布されない)

「セルの書式を整える」「余計な空白を消す」といった、ファイルを問わず使う汎用マクロの置き場所として最適です。


作成方法1:マクロの記録から作る(最も簡単)

個人用マクロブックは、初回だけ作成の手順が必要です。マクロの記録機能を使うのが一番手軽です。

  1. 「開発」タブ →「マクロの記録」をクリック
    • 「開発」タブが無い場合は、「ファイル」→「オプション」→「リボンのユーザー設定」で「開発」にチェックを入れる
  2. 「マクロの保存先」を「個人用マクロブック」に変更する
  3. 「OK」を押し、何もせずすぐに「記録終了」をクリック

これで PERSONAL.XLSB が作成されます。VBE(Alt+F11)を開くと、プロジェクトエクスプローラーに VBAProject (PERSONAL.XLSB) が表示されているはずです。

あとはこの中の標準モジュールに、好きなマクロを書いていきます。


作成方法2:VBEから手動で作る

すでにVBEでの作業に慣れている場合は、こちらでも構いません。

  1. 新規ブックを作成する
  2. 名前を付けて保存で、ファイルの種類を 「Excelバイナリブック(*.xlsb)」 にする
  3. ファイル名を PERSONAL.XLSB にする
  4. 保存先を次のフォルダにする
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!選択範囲の空白を削除 のように表示されます。


ショートカットキーに割り当てる

毎回マクロ一覧から選ぶのは面倒なので、よく使うものはショートカットキーに登録します。

  1. 「開発」タブ →「マクロ」
  2. 対象のマクロを選び、「オプション」をクリック
  3. ショートカットキーに任意の文字を入力(例: q → Ctrl+Q)

Ctrl+C や Ctrl+V など、既存のショートカットと重複するキーは避けてください。 登録すると、Excelの標準機能より自作マクロが優先されてしまいます。Ctrl+Shift+文字 の形(大文字を入力する)にすると衝突しにくくなります。


クイックアクセスツールバーに登録する

キーを覚えるのが苦手なら、画面左上のツールバーにボタンとして置く方法もあります。

  1. クイックアクセスツールバーの「▼」→「その他のコマンド」
  2. 「コマンドの選択」で 「マクロ」 を選ぶ
  3. 個人用マクロブックのマクロが一覧に出るので、追加する
  4. 「変更」からアイコンと表示名を設定する

常時表示されるため、複数人で共用しているパソコンでも迷わず使えます。


他のブックを操作するときの注意点

個人用マクロブックのコードで 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の実務的なテクニックを紹介していますので、ぜひチェックしてみてください。