excel vbaの操作をコマンドラインで

はじめに

https://cstmize.hatenablog.jp/entry/2021/12/30/%E5%B9%B4%E6%9C%AB%E5%B9%B4%E5%A7%8B%E6%99%82%E9%96%93%E3%81%8C%E3%81%82%E3%82%8B%E3%81%AE%E3%81%A7excel_vba%E3%82%92%E3%82%84%E3%81%A3%E3%81%A6%E3%81%BF%E3%81%9F%26%E7%92%B0%E5%A2%83%E6%A7%8B%E7%AF%89 の続き。ただ、README的なことを書くつもりはなくて、頑張ったけど妥協したところや、上手く言ったところなどを書く。

output

まだ完成はしてないが、骨格は出来てきているので。

やったこと

数百行程度、1~数個のvbaファイルで済む規模であれば、ほぼほぼvimなどで編集し、コマンドライン上でimport/export/runなどがあまりストレスなくできるようになってきた。コードを書く際はやはりvim(+プラグイン)を使用できたほうが断然便利。

windowsとmacの対応

どちらもexcel vbaのimport/exportとmacroのremove(自分のプロジェクトではunbindと名付けている), macroのrunをWindowsでもMacOSでも同一のコマンドで対処できるようになった。基本的にはmake <action> XLSM=<xlsm basename>のような感じで動く。

WindowsについてはCOM操作ができるので、どうとでもなる(vbacをフォークして、修正したいところを修正しながら作っている)

MacOSの場合は、exportについては前回の記事の通り、olevbaを使用。runについては、runVBMacroというmacでのみ使える関数があるので、それを使用して対処した。JXA(JavaScript Application Scripting)のAPIを使用している形。一方でimportとunbindについては対応するAPIが存在しなかった。ということで最終手段としてはvbaのコードをコアとして、excel applicationの管理自体はJXAに任せるようにした。コマンドラインで実行できるものの、import, unbindする際はexcelのprocessが立ち上がってimportやunbindのfunctionを実行する形になるので微妙に中途半端なコードとなってしまった。もちろん使えるので良いのだけれどね。

MacOSで試してみたこととしては、CreateObject("Excel.Application")相当をMacOSのターミナル内でもなんとかして使えないかどうかというところで、Mono(のvbnc)をインストールしてもダメだったので、この方向性で頑張るのは辞めにした。ただ、excel VBEditorからCreateOjbect("PowerPoint.Appliciation")などは使えるっぽいので、裏技的なことを駆使すれば、コンソールからでもこれらのコードが実行できるのかもしれない。情報がなさすぎるので、優先度はかなり低め。

Macのexcelはプログラムによってはアクセス権のダイアログが頻繁に求められるので、これもなんとかしたい。

Note) ちなみに前回の記事で取り上げたxlwingsはwindowsだとpywin32(COMのpythonバインド)に依存しており、macOSだとapplescript(JXAの仲間)に依存しているので、直接使うことはできなさそう、という結論になった。

runコマンドの追加

macroのrunについてはWindowsではCOM操作、MacOSではJXAで対応できるということでこれは比較的上手く言ったのではないかと思う。また、オプションとして、excel vbaのmacro実行前後でpdfとしてexportすることで両者の差分がexcelのウィンドウ画面を見ることなく確認できるようにした。pdfでexportできれば画像としてconvertするのはなんとでもなる。画像のbefore-afterの形でmacroの処理を可視化できるようにしたのは、人によっては嬉しいかもしれない(が実際はよくわからない)。

また、vimなどでvbaコードを修正して、runコマンドを打てばexcelが自動的に立ち上がって、vimで書いたmacroが自動的に実行されるのは色々コードを書く上で手間がかからず便利になった。この機能を実装したことによって、excel VBEditorはもはやデバッガーでしか使わないようになった。

vbacの修正

vbac ではdirectory単位でimport/exportが行われていた。これをxlsmのbook単位で行えるように修正した。また、directory単位のimport/exportについてはMakefileの機能で補填した。あとは上に挙げたとおり、runコマンドの追加など。

日本語を含んだコードに対応

nkfを使って、Shift-JIS(xlsm側) <-> UTF-8 (editor側)の変換を行っている。コードは https://github.com/knknkn1162/excel_vba_skeleton/blob/aa456a01e43955ceed654b8eb9035e18dccbdc03/Makefile#L102-L109 あたり。VBComonentのexportやimport関数にはencodingの引数がないため。とりあえず文字化け気にしなくてよいのは心理的に楽。

excel vba100本ノック を40本くらい

出題は https://excel-ubara.com/vba100/ にある。アルゴリズム的にはfor eachやloopなどを使えば事足りるレベルだが、excelの機能に付随して多種多様なオブジェクトが出てくるので、一つづつ調べながらやっている。vba自体の言語仕様は頼りない感じだが、excelのアプリケーション以下、ワークシートやセル、オートフィルターなどの機能がどのようなオブジェクト/プロパティモデルで実現されているかを考えながらやっていると、よく出来たアプリケーションなんだな、と思った。(もちろん変な仕様も中にはあるけど)

まだまだたくさんあるので、暫くはこれをやりきるつもり。その際に https://github.com/knknkn1162/excel_vba_skeleton で改良したいことがあればその都度行うというフローになりそう。

git commit/pushをmakefileで簡易化

これはmake pushみたいなふうにすると、add,commit,pushまでシーケンシャルにやってくれるというだけで技術的には大したことはない。ただ、これがあるとgitでの管理がめんどくさくなくなって嬉しい。

neovim+αに環境移行

現在はwindows terminal + neovim(+いくつかのプラグイン)で整備してコードを書いている。WSL上にneovim入れたのとdotfilesはすでに用意していたので、極端に大きな差分はなかった。ただ、すべてWSLで作業が完結するかというと全然そうではなく、今回はexcelを操作するのでその際はpowershell上で作業するなどしている。powershellはchocolateyが便利で主要なコマンドは大体chocolateyを通じてインストールできる。今回のプロジェクトのmakefileはpowershellのコードとbashのコードが少し散らかってしまっているのでなんとかしたい所。

やるかもと考えている所

現状、excel vba100本ノック + powershellを少しずつしらべる、という方向性で考えているが、思いついたものが手頃な規模であればそちらを優先するかもしれない。

excel vba100本ノックのつづきをやる

これはやりきろうと思う。githubのぞいても完遂したレポジトリを見なかったので。macOSとwindowsで同じように動作することを目標とする。

外部のコードを読み込めるようなコマンドの追加

というのも https://github.com/VBA-tools のようなやつを見ていると、便利そうなものがあるので、何らかの形でこのライブラリが必要になったときを(自動的に)importできるようにしたい。現状、makefileはすこし窮屈なので、拡張実装すると思う。

google app scripts(GAS) にも対応させる

GASはまだ殆どやっていないのでなんともだが、excel vbaとGASを同じ枠組み(同じcommand)で操作できるとしたら嬉しいかも?と思って。実際には棲み分けがなされていそうだし、gasのコード管理でもっと洗練されたものがあるかもしれない。なにより実務上需要があるのか?というのは気になるが、検討する価値はあるかもしれない。これはやるとしたら調査から初めないといけない。

競プロ90問を解く

https://github.com/E869120/kyopro_educational_90 をexcel(vba)で解く話のことをいっている。

コマンドライン上でrunできるようになったので、拡張としてinitやtestを追加したり、時間計測の仕組みを組み込むなどして、オフラインでもテストデータの入ったxlsmさえあれば、あとはmacroのみでアタックできるようにするのは面白そうかもしれないと思った。テストデータ一つはSheetに対応すれば良さげ。

またexcel vbaはエンジニアと非エンジニアの境界線の技術だと思うので、わざわざexcel vbaでアルゴリズムを組むことで両者の橋渡しができるとしたら結構面白いことだと思った。あとはcellによる操作が中心である関係上、模範解答とは少し違う方向性で実装できるのかもしれない。(例えば、WorksheetFunction(処理が非常に早い)やオートフィルターなど。) また、色などで視覚化しやすいのはexcelのメリットなので、アルゴリズム実装によってより動作が視覚化されやすく、面白いかもしれない。また、アルゴリズムといいつつ、大体の参加者はC++で言語をfixせざるを得ないのが気になっているので、多様性の観点からやるのも面白いかもしれない。

懸念点としてはライブラリが不足していることと言語仕様があまりリッチでないことかなぁ。速度も気になる(とはいえコンパイル言語なので、チューニングすればPythonよりは勝つでしょう)。レベル的にはexcelで取り組むにはオーバースペックな問題が多いので、どうかなというのも思っている。

cmakeへの移行

現状makeでコマンドを書いているが、cmakeのfind_program相当のものがほしいと言うのと、依存関係が汚くなっているのでcmakeでの移行できれいにしたい、という思いがある。また、今回はWindowsとmacOSのマルチプラットフォームなツール開発なので、cmakeのほうが相性良いのではないかと思っている。ただ、cmake入れるほどの規模感にはなっていないので少し躊躇している。また、cmakeは色々難しいから、実際に移行するとどこかでかなり嵌りそうな部分が出てくるに違いないと思う。

powershellの体系的な勉強

これはexcel vbaとは関係ないが、bashの世界観とpowershellのそれとはかなり異なっているのが興味をそそられた。bashは文字列(とspace)主体で組まれるが、powershellはオブジェクトを中心とした世界観のようだ。オブジェクトなので、メソッドやプロパティが脇についてくる形だが、foreachで回すとhoge.methodのように連結できてよりプログラミングの要素が強い。また、.NETとの接続もできるようなので、powershellの守備範囲はかなり広そうに見える。bashはbashでワンライナーが(慣れれば)心地よく感じるので良いが、シェルスクリプトくらいの大きさになると徐々につらみがある。powershellの場合はjavascriptのように書けそうな感じがするので、コードの一貫性という意味ではbashよりも優位にみえる。 また、SIGINTやSIGTERMなどに対する両者の違いもあるようだ。これはshellの違いというよりはOSの違いだが、linuxではinterruptとして扱う(のでtrapコマンドを使う)のに対して、windowsではexceptionとして扱うので、try..finallyで処理するなど。他にもOSの違いに起因するシェルスクリプトの書き方の違いがありそうなので、powershellの世界観を知ってプログラミング観を広げるのは良い気がしている。

具体的にはbashでかける処理とpowershellでかける処理の表などを作るなどすれば為になりそう。xargsに対応するpowershellのコードが何かとかは現状あんまり良くわかっていないんだよね。

やらないこと

JXAの調査

macにしかないアプリケーションで、かつJXAでしか自動化できず、かつそのニーズがあるかどうか、というとかなり狭い適用範囲の技術と思うのでいいや。

MacOSにおけるCOM(or 代替技術)の調査

現状動く形にはなっているので、めちゃくちゃ暇ならやる。Mac版excelのアクセス権問題はやるかもだが、excel100本ノックを優先させる。

ほかのプログラミング言語の習得

最近は書ければ何でもよいのでは? という気持ちになっているので、ほんとうに必要に迫られたときしかしない。Rustも暫くは多分しない。

他に思いついたのがあれば追記するかも。