Excelでマクロを使っていると、「あの処理、前も書いたのに…」と毎回同じような関数を書き直している方は多いのではないでしょうか。
私も以前は同じ状況でした。ファイルごとにVBAを書き直したり、過去のブックからコピー&ペーストしたりと、非効率な作業が続いていました。
この問題を根本から解決してくれるのが、個人用マクロブックと自作アドインです。
どちらも「一度書いた汎用関数を、どのExcelファイルからでも呼び出せる仕組み」を作るためのものです。この記事では、2つの使い分けと具体的な作成手順・実用コード例をまとめて解説します。
個人用マクロブック（PERSONAL.XLSB）とは？
個人用マクロブックとは、「PERSONAL.XLSB」という名前で自動的に作成される特殊なExcelファイルです。
このファイルの最大の特徴は、Excelを起動するたびに自動的に非表示で開かれることです。つまり、ここに書いたマクロや関数は、どのExcelファイルを開いていても常に利用できる状態になります。
通常のブックにVBAを書いた場合、そのブックを開いているときしかマクロは使えません。しかし個人用マクロブックに書いておけば、「このファイルを開かないとあのマクロが使えない」という不便さがなくなります。
保存場所はどこ？
個人用マクロブックは以下のフォルダに保存されます。
C:\Users\ユーザー名\AppData\Roaming\Microsoft\Excel\XLSTART\
このフォルダに置かれたファイルは、Excelの起動時に自動的に読み込まれます。AppDataフォルダは隠しフォルダになっていますので、エクスプローラーで「隠しファイルを表示する」設定をオンにしてから確認してください。
個人用マクロブックの作成手順
個人用マクロブックは「マクロの記録」機能を使うと自動的に作成されます。
手順1：マクロの記録を開始する
「開発」タブ → 「マクロの記録」をクリックします。「開発」タブが表示されていない場合は、「ファイル」→「オプション」→「リボンのユーザー設定」から「開発」にチェックを入れると表示されます。
手順2：保存先を「個人用マクロブック」に変更する
マクロの記録ダイアログが開いたら、「マクロの保存先」のドロップダウンを個人用マクロブックに変更してOKをクリックします。ここが一番大事なポイントです。
手順3：記録を停止する
何か適当な操作（セルをクリックするだけでOKです）をしてから、「記録終了」をクリックします。これだけでPERSONAL.XLSBが作成されます。
手順4：VBEで直接編集する
Alt+F11でVBE（Visual Basic Editor＝VBAを書くエディタ）を開くと、左側のプロジェクトツリーに「VBAProject (PERSONAL.XLSB)」が表示されています。ここにモジュールを追加して、汎用関数を書いていきます。
自作アドイン（.xlam）とは？
自作アドインは、VBAのコードを「.xlam」という専用形式で保存したファイルです。
個人用マクロブックとの大きな違いは、他の人に配布・共有できることです。アドインファイルを渡すだけで、受け取った人のExcelでも同じ関数が使えるようになります。
また、Excelのオプション画面から機能のオン/オフを切り替えられるため、不要になったときの管理も簡単です。
個人用マクロブックとアドインの使い分け
| 項目 | 個人用マクロブック | 自作アドイン(.xlam) |
|---|---|---|
| 対象 | 自分だけ | 自分＋他の人にも配布可能 |
| 有効になるタイミング | Excelを起動すると自動で読み込まれる | オプションから手動で登録が必要 |
| 向いている用途 | 個人の作業効率化ツール | チームや複数PC環境への展開 |
| 管理のしやすさ | VBEから直接編集できる | ファイルを再保存して差し替える |
実務で役立つ汎用関数サンプル
ここからは、実際に私が個人用マクロブックに登録して使っている汎用関数を紹介します。
①文字列の全角・半角を統一する関数
帳票を処理するとき、入力された文字列の全角・半角が混在していることがよくあります。これを統一する関数です。
まず、セルの文字列を半角に変換したい場合を例に説明します。StrConv関数にvbNarrowを指定すると、全角を半角に変換できます。
' 文字列を半角に統一して返す
Function F半角に変換する (str As String) As String
F半角に変換する = StrConv(str, vbNarrow)
End Function
逆に全角に統一したい場合はvbWideを指定します。呼び出す際は他のブックからPERSONAL.XLSB!ToHalf(セルの値)と記述します。
②指定したシートが存在するか確認する関数
処理の前に、シートが存在するかどうかを確認する汎用関数です。シートが存在しない状態で処理を進めるとエラーになるため、この確認は欠かせません。
シート名を引数にとり、存在すればTrue、存在しなければFalseを返します。
' 指定した名前のシートが存在するかTrue/Falseで返す
' ws省略時はThisWorkbookを対象にする
Function ISシートの存在確認(sheetName As String, Optional wb As Workbook=Nothing) As Boolean
If wb Is Nothing Then Set wb = ThisWorkbook
Dim ws As Worksheet
On Error Resume Next
Set ws = wb.Worksheets(sheetName)
On Error GoTo 0
ISシートの存在確認 = Not ws Is Nothing
End Function
使い方はこのようになります。If文と組み合わせて処理の分岐に使います。
If ISシートの存在確認("集計") Then
' 何かしらの集計シートが存在する場合の処理
End If
③指定列の最終行番号を返す関数
データの最終行を取得する処理は非常に頻繁に使います。列番号を引数にして、その列の最終行番号を返す関数です。
End(xlUp)で上方向に空白を飛ばして最終セルを探す方法を使っています。
' 指定列の最終行番号を返す（空白行が途中にある場合でも対応）
' ws省略時はActiveSheetを対象にする
Function 指定列の最終行を取得する(col As Long, Optional ws As Worksheet=Nothing) As Long
If ws Is Nothing Then Set ws = ActiveSheet
指定列の最終行を取得する = ws.Cells(ws.Rows.Count, col).End(xlUp).Row
End Function
呼び出し例は次のとおりです。A列（列番号1）の最終行を取得します。
Dim lastR As Long
lastR = 指定列の最終行を取得する(1)
自作アドインの作り方と登録手順
次に、自作アドイン（.xlam）の作り方を説明します。
手順1：新規ブックにVBAを書く
新しいExcelブックを開き、Alt+F11でVBEを開きます。「挿入」→「標準モジュール」を選択して、アドイン化したい関数をここに書きます。
手順2：.xlam形式で保存する
「ファイル」→「名前を付けて保存」で、ファイルの種類をExcelアドイン（\*.xlam）に変更して保存します。保存先は次のフォルダが管理上おすすめです。
C:\Users\ユーザー名\AppData\Roaming\Microsoft\AddIns\
手順3：Excelオプションからアドインを登録する
「開発」→「Excelアドイン」→自作アドインにチェックを入れます。
これでExcel起動時にアドインが自動的に読み込まれます。
VBEの「ツール」タブ→「参照設定」から自作アドイン名を探してチェックします。（下図参照）
これで外部のブックから使用可能になります。
汎用関数を整理・管理するコツ
汎用関数が増えてくると、どこに何があるかわからなくなりがちです。私が実践している管理のコツを紹介します。
モジュールの管理と使い分け
モジュールについては、汎用関数で１つのモジュール、忘れがちな定型文の登録に１つのモジュールくらいでざっくりとした管理でよいです。
使い分け一覧
- 個人用マクロブック（PERSONAL.XLSB）：自分専用の汎用関数ライブラリとして使う。Excelを起動するだけで自動的に使える状態になる。
- 自作アドイン（.xlam）：自分専用の汎用関数ライブラリとして使う。ファイルを渡すだけで他の人も同じ関数を使えるようになる。
まとめ
今回は個人用マクロブックと自作アドイン作成の方法について解説しました。
いずれも汎用関数を登録しておいて、蓄積できます。そして同じワークブック内ですぐ使えるのは非常に便利です。
是非、活用してみてください。
コメント