LoginSignup
24
27

More than 3 years have passed since last update.

Visual BasicAdvent Calendar 2020Day 23
@takeruko(さそう たける)

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

Posted at

これは、Visual Basic Advent Calendar 2020とSpreadsheets/Excel Advent Calendar 2020の23日目の記事となります。

はじめに

今の会社に転職して早3年が経ちました。多数のExcelファイルと秘伝のタレ的なマクロツールで業務が回っており、入社当初は「絶対にマクロを駆逐してやる！」とExcel撲滅を心に誓ったものですが、Excelマクロ開発を効率化するライブラリ作っちゃたり、VBAソースコードを簡単にGit管理する仕組みを作っちゃったりしているうちに、益々Excelマクロツールが増えてしまいました。
Excelマクロツールの増加に伴い、問題となるのがツールの管理。ユーザーに配布したツールのバージョンアップは頭痛の種。「バージョンアップしてね」とアナウンスしても、実際にユーザーがツールを使うまでバージョンアップする人はいないので、使う頃にはアナウンスのことなんか忘れており「なんか動かないんですけど」「それ古いバージョン使ってるからや」みたいなやり取りが頻繁に。
あまりにもExcelマクロツールの管理がしんどいので、バージョン管理や配布を効率的にやる方法を編み出してしまいました。

どうやるの？

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

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

Excelマクロのロジック部分をアドイン化

Excelマクロをアドイン化する手順自体は複雑ではありません。ファイル保存時に拡張子を「Excel Add-in (*.xlam)」にするだけです。
image.png

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

1. VBAプロジェクト名をツール名（もしくはツールのアドインであると分かる名前)に変更する。

VBAプロジェクト名は、後述するツール本体からアドインの参照設定を行う際、アドインの名称になります。
image.png

2. ツール本体にコードを極力残さないようにする（特にSheetモジュール）

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

3. アドイン内でのThisWorkbookプロパティ参照が適切か注意する（大抵はActiveWorkbookの方が適切）

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

4. アドインのバージョンをツール本体から参照できるようにする

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

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

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

ThisWorkbook.cls
Option Explicit

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

image.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でバージョン管理する方法

共有フォルダ上にアドインフォルダを作成

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

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

アドインフォルダを作成した後に、1つやるべきことがあります。それはアドインファイル(*.xlam)を読み取り専用にすること。
image.png

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

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

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

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

ツール本体から共有フォルダのアドインを参照させる

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

image.png

ファイル選択ダイアログで、右下のファイルタイプのプルダウンから*.xlamを指定すれば、アドインファイルを選択できます。
image.png

アドインを追加すると、Visual Basic Editorのプロジェクトエクスプローラー上に表示されます。
image.png

git pullでアドインの最新化

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

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

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

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
  2. ツール本体を再度開き、アドインの参照を変更先のアドインファイルに設定しなおす

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

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

雑感

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

24
27
0

Register as a new user and use Qiita more conveniently

  1. You get articles that match your needs
  2. You can efficiently read back useful information
  3. You can use dark theme
What you can do with signing up
Sign upLogin
24
27