Excelマクロツールをアドイン化してバージョン管理と配布を効率的にやる方法

これは、[Visual Basic Advent Calendar 2020](https://qiita.com/advent-calendar/2020/vb)と[Spreadsheets/Excel Advent Calendar 2020](https://adventar.org/calendars/5097)の23日目の記事となります。

# はじめに
今の会社に転職して早3年が経ちました。多数のExcelファイルと秘伝のタレ的なマクロツールで業務が回っており、入社当初は「絶対にマクロを駆逐してやる！」とExcel撲滅を心に誓ったものですが、[Excelマクロ開発を効率化するライブラリ](https://qiita.com/takeruko/items/832eb0036e017137a026)作っちゃたり、[VBAソースコードを簡単にGit管理する仕組み](https://medium.com/@saso_33429/excel%E3%83%9E%E3%82%AF%E3%83%AD%E3%81%AEvba%E3%82%BD%E3%83%BC%E3%82%B9%E3%82%B3%E3%83%BC%E3%83%89%E3%82%92azure-devops%E3%81%A7%E3%83%90%E3%83%BC%E3%82%B8%E3%83%A7%E3%83%B3%E7%AE%A1%E7%90%86%E3%81%99%E3%82%8B%E6%96%B9%E6%B3%95-d20b751ddc30)を作っちゃったりしているうちに、益々Excelマクロツールが増えてしまいました。
Excelマクロツールの増加に伴い、問題となるのがツールの管理。ユーザーに配布したツールのバージョンアップは頭痛の種。「バージョンアップしてね」とアナウンスしても、実際にユーザーがツールを使うまでバージョンアップする人はいないので、使う頃にはアナウンスのことなんか忘れており「なんか動かないんですけど」「それ古いバージョン使ってるからや」みたいなやり取りが頻繁に。
あまりにもExcelマクロツールの管理がしんどいので、バージョン管理や配布を効率的にやる方法を編み出してしまいました。

# どうやるの？
基本的なアイデアは下記3点。
1. Excelマクロのロジック部分をアドイン化(_*.xlam_ファイル化)し、ツール本体からはアドインを呼び出すだけの構成にする
2. 共有フォルダ上にアドインフォルダを作成してアドインファイルを格納、配布したツール本体から参照させる
3. アドインフォルダをGitで管理し、`git pull`で最新版を行う

つまり、Excelマクロツールを画面（＝ツール本体）と裏側のロジック部分（＝アドイン）に物理的に分離し、ロジックのみの更新時にツール本体の配布を不要にしよう、という作戦です。
以下、具体的な手順を書きます。

## Excelマクロのロジック部分をアドイン化
Excelマクロをアドイン化する手順自体は複雑ではありません。ファイル保存時に拡張子を「__Excel Add-in (*.xlam)__」にするだけです。
![image.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/81556/0bcd7119-084e-fb39-9865-723e53f41349.png)

ただし、アドイン化する際に注意点があります。

### 1. VBAプロジェクト名をツール名（もしくはツールのアドインであると分かる名前)に変更する。
VBAプロジェクト名は、後述するツール本体からアドインの参照設定を行う際、アドインの名称になります。
![image.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/81556/cef82cb9-8125-a930-b189-24a39fb31e40.png)

### 2. ツール本体にコードを極力残さないようにする（特にSheetモジュール）
ツール本体に残ったコードの修正時にツール本体の配布が必要になってしまうので、できる限りアドインの方に移してしまいましょう。せいぜい`Workbook_Open`や`xxxButton_Click`などのイベントトリガーと、その内部でのアドイン呼び出しやSheetからのツール設定値取得程度のコードのみ残すくらいに抑えましょう。

### 3. アドイン内でのThisWorkbookプロパティ参照が適切か注意する（大抵はActiveWorkbookの方が適切）
複数のExcelブックを取り扱うマクロでは、ツール本体のシートへの参照を確実に取得するために`Set sh = ThisWorkbook.Worksheets("Sheet1")`とすることがありますが、このコードをアドインファイルに引っ越しさせると`ThisWorkbook`プロパティはアドインファイルを指すので、コードの動きが変わってしまいます。ツール本体の`Workbook`オブジェクトは`ActiveWorkbook`プロパティを使って取得するように変更しましょう。

### 4. アドインのバージョンをツール本体から参照できるようにする
ユーザーからの問い合わせの際にアドインのバージョンを報告してもらうなど、ツール本体からアドインのバージョンを確認できる手段を用意したほうがよいです。
まず、アドインにバージョンを記述するために、下記のような__AddinInfo__モジュールをアドイン内に作成し、アドインのバージョンを定数で取得できるようにします。

```vb:AddinInfo.bas
Option Explicit
Public Const MODULE_VERSION As String = "2.2"
```

次に、ツール本体側にアドインのバージョンの定数を表示する機能を実装します。自分の場合は、下記のようにツール本体のシート上にアドインのバージョンを表記するようにし、`Workbook_Open`で自動更新するようにしています。

```vb:ThisWorkbook.cls
Option Explicit

Private Sub Workbook_Open()
    ThisWorkbook.Worksheets("Tool").Range("module_version").Value = AddinInfo.MODULE_VERSION
End Sub
```

![image.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/81556/0ec2c78f-c624-f30b-7703-5b49495601ea.png)

## アドインをGit管理
アドインファイルを作成したらGitHubやAzure DevOps上にGitリポジトリを作成し、アドインファイルをcommitしましょう。README.mdにアドインの使い方やリリースノートも書くようにすると良いです。
また、GitHub/Azure DevOps上でソースコードのDiffを見たりレビューしたりできるように、VBAのソースコードもcommitするとステキです。手前味噌で恐縮ですが、Azure PipelinesやGit ActionsのようなCI/CDの仕組みを使ってVBAソースコードを自動抽出・自動commitするようにすると楽ちんです。

> [ExcelマクロのVBAソースコードをAzure DevOpsでバージョン管理する方法](https://medium.com/@saso_33429/excel%E3%83%9E%E3%82%AF%E3%83%AD%E3%81%AEvba%E3%82%BD%E3%83%BC%E3%82%B9%E3%82%B3%E3%83%BC%E3%83%89%E3%82%92azure-devops%E3%81%A7%E3%83%90%E3%83%BC%E3%82%B8%E3%83%A7%E3%83%B3%E7%AE%A1%E7%90%86%E3%81%99%E3%82%8B%E6%96%B9%E6%B3%95-d20b751ddc30)

## 共有フォルダ上にアドインフォルダを作成
共有フォルダ上にアドインフォルダを作る時は、GitHub/Azure DevOps上のリモートリポジトリを`git clone`すればよいです。

```powershell:powershell
cd <共有フォルダ上のアドイン置き場>
git clone <リモートリポジトリのURL>
```

アドインフォルダを作成した後に、1つやるべきことがあります。それは__アドインファイル(*.xlam)を読み取り専用にする__こと。
![image.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/81556/ea4a4b23-68da-b95c-c3bd-486252c9a108.png)

アドインファイルは、それを参照するツール本体のExcelブックが開かれた時に、編集ロックがかかってしまいます。編集ロックがかかった状態だとアドインファイルの更新ができなくなってしまうので、ロックがかかるのを防止するために予め読み取り専用にしておきます。
アドインファイルが複数ある場合は、下記のようなPowershellコマンドを実行すればOKです。

```powershell:アドインフォルダ配下の*.xlamを一括して読み取り専用にする
Get-ChildItem -File -Recurse <アドインフォルダのパス> -Include "*.xlam" | Set-ItemProperty -Name IsReadOnly -Value $true
```

逆に、読み取り専用属性を外す時は`Set-ItemProperty`に渡す値を`$false`にすればOKです。

```powershell:アドインフォルダ配下の*.xlamから一括して読み取り専用を解除する
Get-ChildItem -File -Recurse <アドインフォルダのパス> -Include "*.xlam" | Set-ItemProperty -Name IsReadOnly -Value $false
```

## ツール本体から共有フォルダのアドインを参照させる
ツール本体にアドインを組み込むには、Visual Basic Editorのメニューから[__ツール__]-[__参照__]を選択し、参照設定ダイアログで[__参照__]ボタンからアドインファイルを選択すればOKです。

![image.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/81556/5d37325d-829d-2c00-c01d-a8f8e6cb8415.png)

ファイル選択ダイアログで、右下のファイルタイプのプルダウンから__*.xlam__を指定すれば、アドインファイルを選択できます。
![image.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/81556/2e4654de-eb13-1ed2-36fb-78e232cd9107.png)

アドインを追加すると、Visual Basic Editorのプロジェクトエクスプローラー上に表示されます。
![image.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/81556/1340557d-99e0-caf2-7599-eb609421f1e1.png)

## `git pull`でアドインの最新化
アドインを最新版にアップデートする際は、共有フォルダ上のアドインフォルダにて`git pull`すればよいです。
ただし、`git pull`の前後に読み取り専用属性の解除・再設定が必要なので、以下のような手順になります。

```powershell:powershell
cd <共有フォルダ上のアドイン置き場>
Get-ChildItem -File -Recurse . -Include "*.xlam" | Set-ItemProperty -Name IsReadOnly -Value $false
git pull
...
Get-ChildItem -File -Recurse . -Include "*.xlam" | Set-ItemProperty -Name IsReadOnly -Value $true
```

上記手順を毎回タイプするのは煩雑なので、自分は以下のようなスクリプトを作って一括実行してます。

```powershell:up2date.ps1
Param(
    [String]$Branch = "main"
)
$ADDIN_DIR = "."
Get-ChildItem -File -Recurse $ADDIN_DIR -Include "*.xlam" | Set-ItemProperty -Name IsReadOnly -Value $false
git checkout $Branch
git pull
Get-ChildItem -File -Recurse $ADDIN_DIR -Include "*.xlam" | Set-ItemProperty -Name IsReadOnly -Value $true
```

## 開発時と配布時でツール本体からの参照先を切り替える

アドインの修正を行う際は、GitHub/Azure DevOps上のリモートリポジトリ→開発用PCのローカルに`git clone`して修正を行いますが、テスト用のツール本体のアドイン参照設定をローカルのアドインファイルに変更する必要があります。
ここでハマりポイントがあるのですが、あるアドインファイルの参照設定を別のパスにある同じ名前のアドインファイルに変更しても、__ツール本体を開き直した時になぜか参照先が元に戻ってしまいます__。
例えば、__MyApp__アドインを`M:\VBA_Addin\myapp\MyApp.xlam`から`C:\work\myapp\MyApp.xlam`に変更した場合、ツール本体を開き直すと、__MyApp__アドインの参照先が`M:\VBA_Addin\myapp\MyApp.xlam`に戻ります。

回避方法は以下のように「アドインの参照を一度外してツール本体を保存・終了する」というステップを挟むとよいようです。

1. アドインの参照を一旦外してツール本体を保存→Excelを終了させる
![image.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/81556/26686985-bbc0-110a-c1af-54f04beef442.png)
2. ツール本体を再度開き、アドインの参照を変更先のアドインファイルに設定しなおす

毎回上記手順を行うのは煩雑なので、以下のようなBuildスクリプトを作ると楽です。

```powershell:build.ps1
Param(
    [String]$TargetFile = ".\MyTool.xlsm",
    [String]$AddinDir = "."
)
# Key=アドインのプロジェクト名、Value=アドインのファイル名
$addins = @{
    "MyApp" = "MyApp.xlam"
}

$TargetFilePath = Convert-Path ($TargetFile.Replace('[', '`[').Replace(']', '`]'))

Write-Output "Build Excel Macro"
Write-Output "Target File: $TargetFilePath"
Write-Output "Addin Dir: $AddinDir"
Write-Output "Addins:"
foreach ($key in $addins.keys) {
    Write-Output "  $key : $($addins[$key])"
}
Write-Output "Remove old references ..."
$excel = New-Object -ComObject Excel.Application
$excel.Visible = $false
$excel.EnableEvents = $false
Write-Output "Open $TargetFilePath ..."
$book = $excel.Workbooks.Open($TargetFilePath)
$excel.EnableEvents = $true

foreach ($ref in $book.VBProject.References) {
    if ($addins.Contains($ref.Name)) {
        $addinName = $ref.Name
        Write-Output "Remove $addinName"
        $book.VBProject.References.Remove($ref)
    }
}
# 古い参照設定を削除するために、一旦Bookを保存してExcelを終了させる
$book.close($true)
$excel.quit()

Write-Output "Import addins ..."
$excel = New-Object -ComObject Excel.Application
$excel.Visible = $false
$excel.EnableEvents = $false
$book = $excel.Workbooks.Open($TargetFilePath)
$excel.EnableEvents = $true
foreach ($addin in $addins.keys) {
    $addinFile = Convert-Path ($AddinDir + "\" + $addins[$addin]).Replace('[', '`[').Replace(']', '`]')
    Write-Output "Import $addin from $addinFile ..."
    [void]$book.VBProject.References.AddFromFile($addinFile)
}

Write-Output "Update $TargetFilePath ..."
$book.close($true)
$excel.quit()

Write-Output "Build completed!"
exit 0
```

なお、上記スクリプトを実行するには、予めExcelのマクロ設定を変更し、プログラムによるVBAプロジェクトの参照を許可する必要があります。
やり方は、Excelメニューの[__ファイル__]から[__オプション__] – [__セキュリティ センター__] – [__セキュリティ センターの設定__] – [__マクロの設定__]を開き、__VBA プロジェクト オブジェクト モデルへのアクセスを信頼する__にチェックを入れればよいです。

![image.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/81556/0e6276eb-3f3b-bfe3-46a2-526edd39c68b.png)

# 雑感
ツールのロジック部分をアドイン化するようにしてから、メンテナンスがかなり楽になりました。ただ、まだ数十個近くあるツールのいくつかをアドイン化して運用している状況なので、手作業での`git pull`によるアドイン更新が回っていますが、全ツールをアドイン化した際には、アドイン更新漏れが発生するかもしれません。その際には、Azure PipelinesでSelf-Hostedエージェントを使った自動リリースとかやってみようかなーと思ってます。