これは、Visual Basic Advent Calendar 2020とSpreadsheets/Excel Advent Calendar 2020の23日目の記事となります。
はじめに
今の会社に転職して早3年が経ちました。多数のExcelファイルと秘伝のタレ的なマクロツールで業務が回っており、入社当初は「絶対にマクロを駆逐してやる！」とExcel撲滅を心に誓ったものですが、Excelマクロ開発を効率化するライブラリ作っちゃたり、VBAソースコードを簡単にGit管理する仕組みを作っちゃったりしているうちに、益々Excelマクロツールが増えてしまいました。
Excelマクロツールの増加に伴い、問題となるのがツールの管理。ユーザーに配布したツールのバージョンアップは頭痛の種。「バージョンアップしてね」とアナウンスしても、実際にユーザーがツールを使うまでバージョンアップする人はいないので、使う頃にはアナウンスのことなんか忘れており「なんか動かないんですけど」「それ古いバージョン使ってるからや」みたいなやり取りが頻繁に。
あまりにもExcelマクロツールの管理がしんどいので、バージョン管理や配布を効率的にやる方法を編み出してしまいました。
どうやるの？
基本的なアイデアは下記3点。
- Excelマクロのロジック部分をアドイン化(_*.xlam_ファイル化)し、ツール本体からはアドインを呼び出すだけの構成にする
- 共有フォルダ上にアドインフォルダを作成してアドインファイルを格納、配布したツール本体から参照させる
- アドインフォルダをGitで管理し、
git pullで最新版を行う
つまり、Excelマクロツールを画面（＝ツール本体）と裏側のロジック部分（＝アドイン）に物理的に分離し、ロジックのみの更新時にツール本体の配布を不要にしよう、という作戦です。
以下、具体的な手順を書きます。
Excelマクロのロジック部分をアドイン化
Excelマクロをアドイン化する手順自体は複雑ではありません。ファイル保存時に拡張子を「Excel Add-in (*.xlam)」にするだけです。
ただし、アドイン化する際に注意点があります。
1. VBAプロジェクト名をツール名（もしくはツールのアドインであると分かる名前)に変更する。
VBAプロジェクト名は、後述するツール本体からアドインの参照設定を行う際、アドインの名称になります。
2. ツール本体にコードを極力残さないようにする（特にSheetモジュール）
ツール本体に残ったコードの修正時にツール本体の配布が必要になってしまうので、できる限りアドインの方に移してしまいましょう。せいぜいWorkbook_OpenやxxxButton_Clickなどのイベントトリガーと、その内部でのアドイン呼び出しやSheetからのツール設定値取得程度のコードのみ残すくらいに抑えましょう。
3. アドイン内でのThisWorkbookプロパティ参照が適切か注意する（大抵はActiveWorkbookの方が適切）
複数のExcelブックを取り扱うマクロでは、ツール本体のシートへの参照を確実に取得するためにSet sh = ThisWorkbook.Worksheets("Sheet1")とすることがありますが、このコードをアドインファイルに引っ越しさせるとThisWorkbookプロパティはアドインファイルを指すので、コードの動きが変わってしまいます。ツール本体のWorkbookオブジェクトはActiveWorkbookプロパティを使って取得するように変更しましょう。
4. アドインのバージョンをツール本体から参照できるようにする
ユーザーからの問い合わせの際にアドインのバージョンを報告してもらうなど、ツール本体からアドインのバージョンを確認できる手段を用意したほうがよいです。
まず、アドインにバージョンを記述するために、下記のような__AddinInfo__モジュールをアドイン内に作成し、アドインのバージョンを定数で取得できるようにします。
Option Explicit
Public Const MODULE_VERSION As String = "2.2"
次に、ツール本体側にアドインのバージョンの定数を表示する機能を実装します。自分の場合は、下記のようにツール本体のシート上にアドインのバージョンを表記するようにし、Workbook_Openで自動更新するようにしています。
Option Explicit
Private Sub Workbook_Open()
ThisWorkbook.Worksheets("Tool").Range("module_version").Value = AddinInfo.MODULE_VERSION
End Sub
アドインをGit管理
アドインファイルを作成したらGitHubやAzure DevOps上にGitリポジトリを作成し、アドインファイルをcommitしましょう。README.mdにアドインの使い方やリリースノートも書くようにすると良いです。
また、GitHub/Azure DevOps上でソースコードのDiffを見たりレビューしたりできるように、VBAのソースコードもcommitするとステキです。手前味噌で恐縮ですが、Azure PipelinesやGit ActionsのようなCI/CDの仕組みを使ってVBAソースコードを自動抽出・自動commitするようにすると楽ちんです。
共有フォルダ上にアドインフォルダを作成
共有フォルダ上にアドインフォルダを作る時は、GitHub/Azure DevOps上のリモートリポジトリをgit cloneすればよいです。
cd <共有フォルダ上のアドイン置き場>
git clone <リモートリポジトリのURL>
アドインフォルダを作成した後に、1つやるべきことがあります。それは__アドインファイル(*.xlam)を読み取り専用にする__こと。
アドインファイルは、それを参照するツール本体のExcelブックが開かれた時に、編集ロックがかかってしまいます。編集ロックがかかった状態だとアドインファイルの更新ができなくなってしまうので、ロックがかかるのを防止するために予め読み取り専用にしておきます。
アドインファイルが複数ある場合は、下記のようなPowershellコマンドを実行すればOKです。
Get-ChildItem -File -Recurse <アドインフォルダのパス> -Include "*.xlam" | Set-ItemProperty -Name IsReadOnly -Value $true
逆に、読み取り専用属性を外す時はSet-ItemPropertyに渡す値を$falseにすればOKです。
Get-ChildItem -File -Recurse <アドインフォルダのパス> -Include "*.xlam" | Set-ItemProperty -Name IsReadOnly -Value $false
ツール本体から共有フォルダのアドインを参照させる
ツール本体にアドインを組み込むには、Visual Basic Editorのメニューから[ツール]-[参照]を選択し、参照設定ダイアログで[参照]ボタンからアドインファイルを選択すればOKです。
ファイル選択ダイアログで、右下のファイルタイプのプルダウンから__*.xlam__を指定すれば、アドインファイルを選択できます。
アドインを追加すると、Visual Basic Editorのプロジェクトエクスプローラー上に表示されます。
git pullでアドインの最新化
アドインを最新版にアップデートする際は、共有フォルダ上のアドインフォルダにてgit pullすればよいです。
ただし、git pullの前後に読み取り専用属性の解除・再設定が必要なので、以下のような手順になります。
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
上記手順を毎回タイプするのは煩雑なので、自分は以下のようなスクリプトを作って一括実行してます。
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に戻ります。
回避方法は以下のように「アドインの参照を一度外してツール本体を保存・終了する」というステップを挟むとよいようです。
- アドインの参照を一旦外してツール本体を保存→Excelを終了させる
- ツール本体を再度開き、アドインの参照を変更先のアドインファイルに設定しなおす
毎回上記手順を行うのは煩雑なので、以下のようなBuildスクリプトを作ると楽です。
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 プロジェクト オブジェクト モデルへのアクセスを信頼する__にチェックを入れればよいです。
雑感
ツールのロジック部分をアドイン化するようにしてから、メンテナンスがかなり楽になりました。ただ、まだ数十個近くあるツールのいくつかをアドイン化して運用している状況なので、手作業でのgit pullによるアドイン更新が回っていますが、全ツールをアドイン化した際には、アドイン更新漏れが発生するかもしれません。その際には、Azure PipelinesでSelf-Hostedエージェントを使った自動リリースとかやってみようかなーと思ってます。