はじめに

excel vbaをやっている。IDEから文法から色々古く不便な部分も多いのだけれど、excelは四半世紀たっても未だに使われているアプリケーションだ。使われているとはいえ、エンジニアにとっては、excelは触りたくない対象だと思う。データの加工やgraph化であればpythonで事足りると。

それは一方では正しいと思うのだけど、Embededな環境でツールを使い作業してきた身としては、不便だったら便利にできないものか、というのと、自動化して便利にする手段は非エンジニアにとっては思いつくのが難しい、ということで、じゃあプログラミング一通りできる人がexcel vbaを再入門したらどのように環境を整えるのか、ということをやってみた。

環境

mac OS + Microsoft excel for mac(2019)

やったこと

excel vbaを抽出する環境をdockerで作成
gitによるexcel vbaのソースコードのバージョン管理(極力楽ができるように工夫した)

やってないこと

エディター選定
excel vbaのテスト(なにをもってテストできたとするのかが難しい)
大規模開発の際の自動化

まだまだ不足はあるけど、バージョン管理がストレスなくできれば半分片付いたようなものなので。その他はもうちょっとexcel vbaに習熟してからかな〜

バージョン管理の手順

成果物は

https://github.com/knknkn1162/vba_extractor (docker image作成レポジトリ)
https://github.com/knknkn1162/excel_vba_playground ( vba file群のレポジトリ)

でgit clone https://github.com/knknkn1162/excel_vba_playgroundしてmakeすることで変更点のあるものだけをcommit, レポジトリにpushできるようにした。なので、

.xlsmファイルのvba ソースコードを編集
make

でvba ソースコードのバージョン管理ができるようになった。1番は相変わらずexcel付属のIDEを使用する点で不便だが、2番で快適になった。もう少しやったことを詳しく書くと、

https://github.com/knknkn1162/vba_extractorレポジトリにdockerfileを配置し、circleCIによって、docker imageを自動的にdockerhubにプッシュされるようにした
1で作成したimageを用いて、.xlsmからexcel vbaコードを抽出できるようにした。ただし、コマンドがやや長いので、makefileを作成して抽出 -> git commit, pushをワンコマンドでできるようにした。ちなみに、複数のxlsmファイルにも対応した。

以下、作業過程の詳細。

vbaコードの抽出

xlsmはバイナリファイルなので、流石にそのままgithubのレポジトリに置くことはできない。excel vbaのソースコードは.xlsmファイルの内部で圧縮された状態¹なので、コードの部分を取り出す必要がある。調べてみると、olevbaというのがあるので、これを用いる。ただし、そのまま使用するとソースコードのうち、日本語部分が文字化けすることがわかった。普通のプログラミング言語だと、日本語の対処は本質的でないことが多いので、後回しで考えることが多いが、excelの場合、通常セルやシート名に日本語が入っている場合が多いので、ソースコードにも日本語が含まれている場合が非常に多い(e.g. Worksheets("サンプル")など)。そういうわけで早めに対応したほうがよさそうと判断しolevba.pyを読む²。

調査の結果、excel vbaを.xlsmファイルから抽出するときに、cp1252でエンコードしていることがわかった。vba ソースコードの文字コードは'shift_jis'となっているようなので、これが文字化けの原因のようだ。Pythonではmodule内の関数はモンキーパッチできない(よね? クラス内のメソッドはできるけど)ので、結局forkして以下のように修正することで対応した。VBA_Parserクラスのextract_macrosメソッドを用いることと、抽出したコードで出てくるAttributeはいらないのでfilter_vba関数で取り除くことに注意してpython スクリプトを書けばいい。

これを含む環境をdocker imageとして作成したのがhttps://github.com/knknkn1162/excel_vba_playground となる。内部ではcircleCIで都度docker imageを作成しdockerhubにpushしているので、docker push knknkn1162/vba_extractorとして、docker runコマンドを叩けば、vbaのファイルが作成されるようにした。ついでにcircleCIで日本語を含むxlsmファイルに対して、文字化けせずにソースコードを抽出できるかどうかのテストも書いた -> https://github.com/knknkn1162/vba_extractor/blob/0b927782052b90c9143105d6f96cc612f1934c6a/.circleci/config.yml#L33-L39。

.vbaコードのバージョン管理

上記で与えられたxlsmに対して、.vbaのコードを抽出することができた。ただ、docker run -it -v $(pwd):/code --rm knknkn1162/vba_extractor -- /code/hoge.xlsmを毎回打つのは長いし、excelのIDEでコードが書き終わった状態なので、都度git add -p -> git commit -> git pushと分かりきったコマンドを打つのも面倒くさい。ということでMakefileを作成してmakeコマンドをうちさえすれば、諸々修正分がレポジトリにpushされるようにしてみた。一応工夫した所は以下の点：

複数のxlsmファイルの修正に対してもmakeコマンドの1コマンドで済むようにする
作業中のexcelファイルには~$sample.xlsmみたいな感じのファイルが生成されるようなので、その対策
xlsmファイルが複数のdirectoryにまたがっている場合でもmakeコマンドの1コマンドで済むようにする

ディレクトリ階層は変わるかもしれないがおおよそ次の通り:

.
├── Makefile <= 2. 1が済んだら`make`
├── README.md
├── excelvba1 <= 3. 2の実行でコードが抽出される(Module1.basがそれ) -> レポジトリにpush
│   ├── ex001
│   │   ├── Module1.bas
│   │   ├── Sheet1.bas
│   │   └── ThisWorkbook.bas
│   └── ex001.xlsm<= 1. vbaコードを編集
└── excelvba9
    ├── ex001
    │   ├── Module1.bas
    │   ├── Sheet1.bas
    │   └── ThisWorkbook.bas
    ├── ex001.xlsm
    ├── ex002
    │   ├── Module1.bas
    │   ├── Sheet1.bas
    │   └── ThisWorkbook.bas
    └── ex002.xlsm <= 1. vbaコードを編集

vbaのコードを書いてみる

https://github.com/knknkn1162/excel_vba_playground では https://excel-ubara.com/ で出題されている練習問題をいくつか解いている。まだ単純だが、もうちょっと複雑なデモでも書ければ良いかな、と思っている。

次にできそうなこと

1番は相変わらずexcel付属のIDEを使用する点で不便

これはvbaファイルをvimなどで編集し、実行するときはexec_xlsm oo.vba --target xx.xlsmみたいなコマンド叩くことでexcelが立ち上がってoo.vbaで指定したvbaマクロが実行するようにできそうな感じがする。もちろん、xx.xlsmにはなんらかのデータがsheetに入っている前提。ただし、vbaをxlsmにエキスポートする手段があんまりない(Pythonのxlwingsが有力?) もしかしたら文字化けなどでハマるかもしれない。

あとはweb上の連携だけど、これはGASのほうがふさわしいかな。