ExcelマクロのVBAソースコードをAzure DevOpsでバージョン管理する方法

18 min readApr 18, 2020

はじめに

会社でAzure DevOps Servicesを導入しました。PythonやUiPathなどの開発に使おうとしているのですが、職場でも多数存在しているExcelマクロをバージョン管理する方法は無いか？と何気なく試してみたところ、割とうまく動いたので、そのやり方を紹介します。

Excelマクロのバージョン管理の運用イメージ

ExcelマクロをAzure DevOpsでバージョン管理するする場合は、下記のようにマクロ付きExcelファイル(*.xls, *.xlsm, *.xlam)をGit管理するだけの非常に簡単な手順になります。

マクロ付きExcelファイルをGitのリポジトリに追加し、更新をAzure DevOps上のリモートリポジトリにPushする
Azure DevOps上でマクロ付きExcelファイルからVBAソースコードが自動で抽出され、リポジトリにCommitされる
VBAソースコードのDiffを見て、レビューを行う

ネットでよく見かける他のやり方だと、リポジトリにCommitする前にVBAソースコードを抽出するツールを実行する必要があったり、マクロのセキュリティ設定を変更してプログラム的にVBAソースコードを編集する必要があったりします。しかし、これから紹介するやり方では、それらの煩雑な作業は不要です。マクロをパスワードで保護したままでも問題ないです(ブック自体がパスワードで暗号化して保護されている場合は流石に無理ですが)。

技術的な仕組み

Azure DevOpsでExcelマクロを管理するやり方は、下記の仕組みで実現しています。

Pythonのoletoolsパッケージを使って、マクロ付きExcelファイルからVBAソースコードを抽出するツールを作る
Azure Pipelinesを使って、リポジトリのPush時に1. で作成したツールを自動実行し、抽出したVBAソースコードをリポジトリにCommit/PushするYAMLファイルを作成する
マクロ付きExcelファイルを管理するGitリポジトリに2. で作成したYAMLファイルを追加し、Azure DevOps上でリポジトリのPipelineとして設定する

上記1, 2の成果物はGithub上で公開してあるので、マクロ付きExcelファイルのGit管理をすぐにやりたい人は、1, 2を読み飛ばして「3. リポジトリのPipelineを設定」へ進んでください。

1. VBAソースコード抽出ツールの作成

マクロ付きExcelファイルからVBAソースコードを抽出する方法はいくつかありますが、ここではPythonのoletoolsパッケージを使います。oletoolsはマクロマルウェアの検知・解析を主な目的として開発されたPython用オープンソースライブラリであり、以下のメリットがあります。

Microsoft Officeがインストールされていない環境(例えばLinuxサーバー)でも動作
マクロのセキュリティ設定変更が不要
パスワードで保護されたマクロのVBAソースコードでも抽出可能

oletoolsではOfficeファイル中に含まれるVBAプロジェクトのバイナリを直接解析しているため、上記メリットに記載したことができるみたいです。
※パスワードで暗号化・保護されたExcelファイルからは、さすがにソースコード抽出ができないようです。

デメリットとしては、ユーザーフォームの.frxファイル(画面定義のバイナリファイル)は抽出できないという点があります。しかし、バイナリファイルの差分表示してもレビューには使えず意味が無いので、今回の用途では不問にします。

oletoolsのインストール

oletoolsは下記のようにpipでインストールします。

pip install oletools

oletoolsによるVBAソースコード抽出の実装

VBAソースコード抽出のシンプルなPythonコードは下記のとおりです。下記コードは、カレントディレクトリにあるmacro.xlsxからVBAソースコードをすべて抽出し、カレントディレクトリに出力します。

VBAソースコード抽出には、VBA_Parserクラスをoletools.olevbaパッケージからimportして使用します。また、ソースコードの整形(先頭にある”Attribute VBA_”で始まる行の削除、改行コード揃え)のためにfilter_vba関数もimportしておきます。

from oletools.olevba import VBA_Parser, filter_vba

VBA_ParserのコンストラクタにExcelファイルのパスを渡し、detect_vba_macrosメソッドを呼び出した後に、extract_all_macrosメソッドで全クラス・モジュールのVBAソースコードとファイル名を抽出します。

vba_parser = VBA_Parser(workbook_path)
if vba_parser.detect_vba_macros():
    vba_modules = vba_parser.extract_all_macros()
else:
    vba_modules = []

extract_all_macrosメソッドの戻り値は、VBAモジュールの各種属性情報を持つタプルのリストです。タプルの3番目がVBAファイル名(*.cls, *.bas, *.frm)で、4番目がVBAソースコードです。1番目・2番目は特に使わないので捨てます。

extract_all_macrosメソッドから取得したVBAソースコードの先頭行には、VBAエディタには表示されないモジュール・クラスの属性情報が書き込まれていたり、不要な改行コードが混入していて見ずらいので、filter_vba関数で整形しておいたほうが良いです。

for _, _, filename, content in vba_modules:
  filtered_content = filter_vba(content)
  with open(filename, 'w', encoding='utf-8') as f:
    f.write(filtered_content)

日本語文字を含むソースコードの抽出

先程のサンプルコードで十分使えそうなのですが、困ったことにソースコード中の日本語文字が文字化けしてしまいます。原因は、Excelファイル中のVBAプロジェクトに書き込まれているVBAソースコードの文字コード情報にあるらしく、oletoolsがソースコードをShift-JISではなくUTF-8でエンコーディングしてしまうためのようです。VBA_Parserのコンストラクタ引数で、VBAソースコードのエンコーディングを指定できそうにも見えるのですが、Excelファイルからソースコード抽出時のエンコーディングには使われていないようです。

oletoolsのソースコードを解析してみたところ、VBAプロジェクトを表すVBA_ProjectクラスのcodecプロパティがVBAソースコード抽出時のエンコーディングに使われるらしいので、extract_all_marosメソッドの中身を参考にして、日本語文字を含むソースコードを文字化けせずに抽出することに成功しました。

上記日本語文字化け回避以外に、ソースコードの出力先指定や複数Excelファイル対応などの機能を付けてツール化したものをGitHubに公開しておきます。

2. Azure Pipelines用YAMLファイル作成

Azure PipelinesはCI/CD用のサービスで、Azure DevOps上のリモートリポジトリへのcommit/pushをトリガーにして、ビルドやテスト、デプロイの自動化ができます。今回は、Azure Pipelinesと前章で作成したVBAソースコード抽出ツールを組み合わせて、Azure DevOps上にマクロ付きExcelファイルをcommit/push→自動でVBAソースコードをGitリポジトリに展開してくれる、という仕組みを構築します。

Azure Pipelinesで実行するのはざっくり下記4ステップです。

Build環境の構築
commit/pushされたリポジトリのcheckout
VBAソースコード抽出ツールのセットアップ・実行
抽出したVBAソースコードをcommit/push

上記ステップをAzure Piplinesで実行するためのYAMLは下記のようになります（※このYAMLファイルは、マクロ付きExcelファイルを管理するリポジトリに追加します）。

以下、ポイントを解説します。

2.1. Build環境の構築

まず、VBAソースコード抽出ツールを実行するためのBuild環境を構築します。Azure PipelinesではAgent Poolという仕組みでBuild環境のためのvmImage(仮想マシンのイメージ)が予め何種類か用意されているので、使用するvmImage名をpoolキーワードとvmImageキーワードで指定します。

以下の例では、Ubuntu Linuxの最新版を選択しています。vmImageに指定可能なvｍImageは公式リファレンスに書いてあります。

pool:
  vmImage: 'ubuntu-latest'

VBAソースコード抽出ツールはPython 3で書かれているので、Build環境で実行するPythonのバージョン指定が必要です。Azure Pipelinesでは頻繁に実行される処理がビルトインタスクとして予め用意されており、Pythonのバージョン指定は以下の様にUsePythonVersionを使用します。

- task: UsePythonVersion@0
  inputs:
    versionSpec: '3.8'

2.2. commit/pushされたリポジトリのcheckout

リポジトリへのcommit/pushをトリガーにしてPipelineを実行すると、commitされたリポジトリがBuild環境にcheckoutされるのですが、checkoutしたリポジトリに対してgit commit/pushするためには追加の設定が必要らしいです。Azure PipelinesのGitコマンド実行に関するドキュメントによると、Pipelineで実行するscriptにシステムトークンへのアクセスを許可したり、作業後にローカルリポジトリへの変更やgit configへの変更を消すために、下記のような設定が必要です。

- checkout: self
  submodules: true
  persistCredentials: true
  clean: true

また、Gitにcommitするユーザー名とメールアドレスを指定するために、下記設定を行います。Azure Pipelinesでは事前定義された変数でcommit/pushを行ったユーザー情報を取得できるので、ユーザーのメールアドレスをBuild.RequestedForEmail、ユーザー名をBuild.RequestedForで指定すればOKです。

- script: |
    git config --global user.email $(Build.RequestedForEmail)
    git config --global user.name $(Build.RequestedFor)

なお、Azure PipelineのBuildジョブのgit checkoutでは、なぜかbranchではなく、branchが指すcommitをcheckoutしている(detached HEAD状態になる)ため、そのままではcommit/pushされたbranchに対して変更をcommitできません。そのため、branchをcheckoutし直してあげる必要があります。

commit/pushされたbranch名は、Build.SourceBranchという変数から取得できます。この変数で取得できる文字列は、

refs/heads/<branch名>

となっているので、下記のように先頭のrefs/heads/ を除去してあげれば、git checkoutできます。

- script: |
    export branchname=$(echo $(Build.SourceBranch) \
      | sed s@refs/heads/@@)
    git checkout $branchname

2.3. VBAソースコード抽出ツールのセットアップ・実行

VBAソースコード抽出ツール(以降、extract_vba_source.py)はGitHub上に置いてあるので、git cloneしてダウンロードするだけでOKです。なお、このツールはpipenvでパッケージ管理をしているので、pipenvもインストールしておきます。

- script: |
    python -m pip install pip --upgrade
    pip install pipenv
    git clone https://github.com/takeruko/extract_vba_source.git

extract_vba_source.pyには実行時パラメーターとしてマクロ付きExcelファイルのディレクトリパスと、VBAソースコード出力先ディレクトリパスが必要なので、変数として定義しておきます。

variables:
  TARGET_DIR: '.'
  VBA_DIR: 'vba-src'

あとは、Pipfileのパスを環境変数$PIPENV_PIPFILEで指定し(Pipfileがカレントディレクトリ以外の場所にある時に必要)、pipenv installしてからextract_vba_source.pyを実行すれば、VBAソースコードが抽出されます。

- script: |
    export PIPENV_PIPFILE=$(pwd)/extract_vba_source/Pipfile
    pipenv install
    pipenv run python ./extract_vba_source/extract_vba_source.py \
                      --dest $(VBA_DIR) \
                      --src-encoding='shift_jis' \
                      --out-encoding='utf8' \
                      --recursive \
                      $(TARGET_DIR)

2.4. 抽出したVBAソースコードをcommit/push

抽出したVBAソースコードをgit addしてcommit/pushしますが、このpushをトリガーにしてPipelineが再び動いてしまわないように、commit時のコメントに[skip ci]と入れましょう(コメントへの入れ方は他にも色々あるようです)。

- script: |
    git add $(VBA_DIR)
    git commit -m "Extracted VBA source files. [skip ci]"
    git push origin $branchname

3. リポジトリのPipelineを設定

Pipelineの設定は、下記3ステップです。

マクロ付きExcelファイルのリポジトリにPipeline用のYAMLを追加し、Azure DevOpsにpushする
VBAソースコード自動抽出のジョブをPipelineに登録する
Build Serviceアカウントに、リポジトリへのcommit権限を付与する

3.1. Pipeline用のYAMLをリポジトリに追加する

前章「2. Azure Pipelines用YAMLファイル作成」で作成したYAML(GitHubからダウンロードしてもOK)をマクロ付きExcelファイルのリポジトリに追加し、Azure DevOpsにpushします。

3.2. VBAソースコード自動抽出のジョブをPipelineに登録する

左メニューのPipelinesからPipelinesを選択し、Create Pipelineボタンをクリックします。

「Where is your code?」でAzure Repos Gitを選択します。

「Select a repository」でマクロ付きExcelファイルのリポジトリ（下記例ではvba_with_git_sample）を選択します。

「Configure your pipeline」でExisting Azure Pipelines YAML fileを選択します。

「Select an existing YAML file」でリポジトリに追加したYAMLファイルのパスを指定し、Continueボタンをクリックします。

「Review your pipeline YAML」で、指定したYAMLの内容が表示されます。右上にRunボタンが表示されていますが、まだ設定が不足しているので押してはいけません（押すとPipelineの実行中にエラーが発生する）。

一旦、Runボタン右の矢印をクリックしてSaveを選択します。

3.3. Build Serviceアカウントに、リポジトリへのcommit権限を付与する

左メニューの最下部にあるProject settingsをクリックします。

「Project Settings」の「Repos」からRepositoriesを選択し、下記のようにBuild Serviceアカウントにリポジトリへのcommit権限を付与します。

マクロ付きExcelファイルのリポジトリを選択
Build Serviceアカウント（<プロジェクト名> Build Service という名前）を選択
ContributeをAllowに設定

以上でPipelineの設定は完了です。

試しに実行してみましょう。左メニューのPipelinesからPipelinesを選択し、作成したPipelineを選択します。

「Run pipeline」でRunボタンをクリックします。

Pipelineが実行されます。実行中のJobをクリックすると、実行ログを参照できます。

VBAソースコードの抽出とリポジトリへのcommitが無事に成功したようです。

リポジトリを見ると、vba-srcディレクトリが新しく作成されており、ExcelマクロのVBAソースコードを参照することが可能になっています。

実際の運用

Pipelineの設定が済んで後は、マクロ付きExcelファイルの更新をAzure DevOpsにpushするたびに、Pipelineが自動実行し、VBAソースコードを再抽出してくれます。

VBAソースコードの更新前後のdiffを見ることもできます。

まとめ

Azure DevOpsでマクロ付きExcelファイルをバージョン管理する方法を紹介しました。自分の職場ではExcelマクロのツールが多数存在していたので、この方法を導入したことで、ツールのバージョン管理が非常に効率的かつ安全になったと感じています。Excelマクロのバージョン管理で苦労している方々は是非とも導入してみてください。