VBA100本ノックをPowerShellで解いてみた感想

はじめに

VBA100本ノック：マクロVBAの特訓｜エクセルの神髄 をPowerShellで解いてみた。もともと左の問題集はVBAの練習問題として用意されたものだけど、VBAでは https://github.com/knknkn1162/vba100_knock で解き終わっており、PowershellでもExcelをComとして操作することでExcel操作できるようなので、PowerShellの練習もかねてやってみた。総じて大変だったけど、違う価値観の世界で作られたシェル言語があり、bashよりも広範囲な処理ができる、という学びを得たので結構良かったと思っている。

これです

github.com

感想

  • シェルでこれだけ多彩なことができるのは素晴らしい。bash環境では少し込み入ったことをするとsed, awkを使わざるを得ないが、PowerShellはシェルというよりはプログラミング言語に限りなく近く、型にあった関数を利用してユーザーが欲しい情報を加工して手に入れることができる。違う価値観の世界のシェル言語という印象。Netのライブラリを利用できるし、いざとなればVisualBasicの関数をそのまま呼び出せるので、処理関数の不足について悩むことは全くなかった。Mac環境でも普段使いでPowerShell使っても良いのではないか、と思った。

  • 言語の難易度はそこそこ高い(決して簡単な言語ではない)。foreach-objectやwhere-objectを最大限活用するならばのはなしだけど。

  • VBAとの比較優位性は、gitでのコード管理しやすいとか、関数プログラミングを駆使してコードをかけるので、コードが簡潔で短くて済むなどがあげられる。対してイベント処理やフォーム処理はPowershell単体で処理することはできない。また、VBAと比べて速度はPowershellのほうが遅い。ほかの言語に比べると、mapやfilterなど一般的によく使用される関数は%や?などのaliasが設けられていて、ワンライナーに特化している。特にファイル処理はShellのお家柄で威力が高い。

  • PowerShellは2次元配列の扱いがかなり難しい。とくにforeach-objectを2回入れ子にしている時の振る舞い。Excelだと2次元配列に加工して、結果をワークシートに書き込むような処理が多いので、割と頻出。一番時間を使った。これは後述。

    • 関数型プログラミング的な書き方を好む人にとって高確率で悩む部分(かつ分かりにくい)ということもあり、Excel操作にはPowerShell一択という感じで手放しで推奨することができない(qiitaのポジショントークはあてにならない...)。VBAでforかfor eachを利用して愚直に書きさえすれば問題なかった処理で悩むのは生産的かというと、うーん,,という感想。

  • Count, lengthの仕様も結構罠(ここの記事に詳しい)。int型でも$tmp=78; $tmp.lengthとすると1を返す。この仕様のため、型を配列と勘違いしてしまうところがままあった。

  • 要素数が1のときは配列でなく要素の型になってしまう場合がある。例えば、gm -i (@(1,2,3) | ?{$_ -eq 1})は型がInt32となってしまう。これを防ぐには@()で囲うようにするとよい。さっきの例だと、gm -i @(@(1,2,3) | ?{$_ -eq 1})とすると配列型と認識される。whereなどでfilterされて結果的に配列の要素数が1になった場合とか上記の現象のため、かなりハマった。

  • 文字化けで余計な労力を使うことがたまにある。

  • ExcelのmethodをPowerShellで用いる場合は名前付き引数として引数を渡すことができないため、順番通りに記述する必要がある(ただ多くの場合において記述不要になる。例えば、5つの引数をもつメソッドにおいて、第1,2引数のみ埋めればよい場合は後の3,4,5引数の記述を省略できる。省略できない場合というのは第1,2引数のほかに第5引数も埋める必要がある場合)。引数をデフォルト値として省略したい場合は多々あるがそのばあいは、[System.Reflection.Missing]::Valueという定数を埋める。自分の上記のレポジトリでは$xlnullとして使っています。

  • ComObjectの場合、type名が非常に長くなりがちなので、関数作成時などの[int]$argのようなことはできない。toplevelのappには[Microsoft.Office.Interop.Excel.ApplicationClass]という名前が、Workbookだと[System.__ComObject, mscorlib, Version=4.0.0.0, Culture=neutral, PublicKeyToken=b77a5c5]と指定する必要がある。set-aliasなどを利用して[$booktype]$argみたいなことはできない。

  • 完全に理解できていないところがあるが、functionの戻り値がほかの言語と違う。また、どうも勝手にキャストされる場合があるっぽい。これについては原因がわからず逃げた部分もあるので、また落ち着いたときにやってみようと思う。

2次元配列への対応

断りなければ、$appを[Microsoft.Office.Interop.Excel.ApplicationClass]の変数、$book = $app.Workbooks.Open($path)でえられる関数、$ws=$book.Worksheets(1), $range=$ws.Range("A1")などの変数とします。

2次元配列の保持

  • 次の例のように一次元 -> 二次元に膨らませたい場合がある。この場合、,@($_.Name ~~~)のように最初にカンマが必要。また、配列を明示するために式全体を@()とくくっておくのが無難。 Powershellでは、カンマがないと自動的に1次元にflattenされてしまう。
$mat = ls -File $bdir |`
    %{,@($_.Name, $_.LastAccessTime, $_.Length)}
  • より厄介で見落としがちなミスとしては、下の例のように一見すると配列に見えない場合でも(ResizeはEnumerableを返す関数なので)%{,$_.Resize(1,$cols)}のように先頭にカンマをつけないと$arrが二次元配列になってくれない。%{,@($_.Resize(1,$cols))}として明示的に@()をくくる書き方でも大丈夫。
$arr = $rng.Columns(1).Cells |`
    ?{$_.Offset(0,3).Font.Color -eq $color} |`
    %{,$_.Resize(1,$cols)}

セルから別のセルへの代入(コピー)

  • これはRangeクラスのCopyメソッドをそのまま使えばよい。第一引数はdestinationでこれは始点のセルのみでよい(resizeを使う必要なし)
  • [void]をつけないとconsole上にTrueと表示される。
[void]$ws.Range("A1").CurrentRegion.Copy($ws2.Range("A1"))

配列からn行1列のセル代入

  • headerをセルに代入するときなどによく使う
  • $arrは事前に型変換しておく。(e.g [string[]], [int[]]など)
  • resizeがないとA2セルのみ値が格納される
$ws.Range("A2").Resize($arr.length, 1) = [string[]]$arr

配列から1行n列のセル代入

  • transpose を使う
  • $arrは事前に型変換しておく。(e.g [string[]], [int[]]など)
$ws.Range("A2").Resize(1,$arr.length) = $app.WorksheetFunction.transpose([string[]]$arr)

二次元配列からn行m列のセル代入

  • 配列の大きさが同じでもそのままの形でセルに代入できないことが多い。あるあるパターンとしては、foreach-objectのなかで配列を作成し、全体が2次元になった場合。なぜうまくいかないかというと、このように作成した配列はジャグ配列arr[i][j]なのに対して、二次元セルの代入時に要求されるのは二次元配列arr[i,j]だから。要するに型変換で代入が失敗する1。

  • 上の問題を回避するためにはWorksheetFunctionのtransposeを二回重ねる(ほかにベストプラクティスありそうだけど)。transpose関数はいい感じにObject[]型(ジャグ配列)をObject[,]型(二次元配列)にキャストしてくれるのでこれを援用している。ちなみにそうして作られたcast2dという関数は配列を返すのだが、その際はreturn ,(...)のようにカンマをつけないといけない。

  • resizeで正確なセルサイズを指定する必要がある

function cast2d($app, $arr2) {
    return ,($app.WorkSheetFunction.transpose($app.WorkSheetFunction.transpose($arr2)))
}

function Run-Macro($app, $book) {
    // ...
    $mat = ls -File $bdir |`
        %{,@($_.Name, $_.LastAccessTime, $_.Length)}
    // ..
    $ws.Range("A2").Resize($mat.length, 3) = cast2d $app $mat
  • ただし、transposeされた後の配列は1-indexedになっているので、cast2dで得られた二次元配列をさらに加工するなどの処理を行う場合は注意すること。(Powershellでは通常もちろん0-indexedで配列が生成される)

その他

次にやること

PowerShellについてはある程度学習できていったん区切りとしたい。Excelとの絡みでいうと、Google SpreadSheetではどのように書けるのだろう、という興味がある。実は

github.com

でTypeScriptで少し取り組み始めている。気になっていることとしてはExcelでの得意領域とSpreadSheetでの領域は結構違うということ。例えば、Excelの内部に閉じた処理の場合は(Excelの機能のほうが豊富なので)Excel優位だが、Excel Applicationの外に出たとたんVBAでは扱いにくくなる。SpreadSheetはほかのサービスと連携できたり、常に最新のバージョンに更新されるとかウェブに特化した処理をかけるとかが得意そう。なので、VBAの問題をGoogleSpreadSheet上で解くのは価値があることなのかは疑問がある。まぁとりあえず比較のために実装してから考えるのでもよいかなと思う。

言語的な比較でいうと、Powershellは省略記法が使えたりしてワンライナーでもかなりのことができてしまう奴だけど、文法の癖が結構強いラディカルな奴、TypeScriptは文法的にそれほどひねくれたところがなくリッチな文法体系なので型操作含めてあっさりできてしまうが、型がすべてを支配しているので時には融通が利かない場合がある奴という具合？ あとはExcelはTypeScriptの利用に関してまだまだ発展途上なので、GoogleSpreadSheetのTypeScriptとの単純比較は難しいかな。

  1. ちなみに前者と後者の配列で微妙にメソッドが違う。例えば、ジャグ配列の1次元目の配列の長さはarr.lengthで取得できるが、後者の二次元配列の場合の取得方法はarr.getLength(0)となる。↩