【究極サバイバル】上司がExcel/VBAでシステム開発を迫ってきたら

あなたの置かれている状況は

• Excelとそれを動かす言語VBAで何かを登録したり表示したりしろと迫られています。他の道具は使えません。
• Excelとは何でしょう。触ったことがないなら幸せです。今事切れれば幸福度の損切りができます。簡単に言うとgoogle spreadsheetの劣化コピーです。spreadshitです。
• あなたのポケットには運良くgitが入っているかもしれません。しかしdiffは取れません。.xlsm(マクロ付きExcelファイル)はzipされたxml、バイナリなので容易に差分比較できません。気持ちを切り替えて 「株価一覧_20200414_01_罫線調整.xlsm」みたいなのを手動管理する手間が省けたことを喜びましょう。
• 他の被害者と協力し1つの.xlsmを編集するのは一見良さげですが危険な行為です。mergeもクソもないですよバイナリなんですから。ただし、「モジュール」を「エクスポート」するとテキストが吐かれます。それをマージするというなら現実的です。
• ただし、モジュールをすべて吐き出す機能はありません。「vba 一括エクスポート」でググって実現したとしましょう。それでも致命的なのは「シートの状態」などは容易にエクスポートできないという点です。
• 被害者達は「Excelシートの状態」と「VBAで書かれたコード」の2つを管理しなければ生き残れません。絶望的なのは「シートの状態」がこの上なく管理しにくいという事実です。普通の開発で言ったらhtmlファイルがなんかひと塊になってて中身がうんこ色になっている状態ですね。

Excelというジャングル

をちょっと覗いてみましょう。深淵もまた覗いているのだ。

シート

なんとやら.xlsxをダブルクリックすると内部的にはworkbookが開きます。
下のタブにsheet1と書かれているでしょう。
マス目が並んでいてA列の1行目が選択されているかもしれません。
Excelは workbook > sheet > セル(rangeやcell) という階層からなります。

それぞれのオブジェクトに名前がついています。workbook はファイル名。sheet は下のタブ名と連動。ここがミソなんですが、セル は"B2"など座標で指定する方法と、名付けてしまう方法があります。名付けられたセルは上部の「数式」タブからしか一覧参照・編集ができません。見つけにくいです。

コード

次はVBAコードを書く場所に着目してみます。

• 各シート ... sheet1にfooというpublic関数を書いたらsheet1.foo()が呼べるわけです。
  Meという修飾子はsheet1オブジェクトを示します。Me.Range("A1")でそのシートのセルが参照できるのでそういうのを利用したい関数を書けばいいんじゃないでしょうか。
• 標準モジュール ... シートに依存しない共通した処理をかけます。グローバル定数とかも。ただし、グローバル変数を宣言しても破棄されるかもしれん保証はしないという噂を聞きました。聞かなかったことにしました。
• クラスモジュール ... クラスみたいなものをかけます。20年前の最新技術です。

ということで、VBAでセルを指定する方法は複数あります。いくつか例をあげると

• sheet1.Range("銘柄コード入力欄")
• Me.Range("A1")
• .Cells(1, 1)

SheetオブジェクトのRange関数で座標を指定するというのが基本だと思います。
セルに名付けてセル名参照か座標参照かは統一したほうがいいんじゃないでしょうか。

どう切り拓くか

さて、「シート」が少し厄介なhtmlファイル、「コード」が20年継ぎ足し継ぎ足しで使ってる秘伝の言語ということがわかりまし 上司「その日の室温・湿度と我が社の株価・日経平均(これだけ自動取得)を手入力して保存するシステムを作ってくれ」 この必要火急なシステムを作りましょう。

想定: 弊社の「室温及び株価管理部」のユーザ 1人か2人

「室温・湿度記録.xlsx」と「株価・日経平均記録.xlsx」の2ファイルを成果物として出すことにしましょう。小規模なので「シート」に記録してしまいましょう。ユーザの寿命か株価の寿命が来るまでのデータは1列で保存できます。数えてませんが少なくとも200行はあるので。
1ファイルにしたほうがUXが良い気がしますが、構成管理担当のエクスペリエンスを上げたいので分けます。絶対に共通する処理がありますが分けます。ファイルは開発者分に分割しましょう。1人1ファイルを担当します。「室温・湿度記録.xlsx」を先に作ってもらって、それをコピペして後者のファイルを作ります。これは石器時代によくみられる開発手法です。いいんだよExcelなんて尖った石がついたモップの柄なんだから。
もう一人開発者が来たら日経平均をスクレイピングする部分を書かせます。この処理は「シート」にあまり影響を与えないので平和に分業できます。できないかもしれません。来なかったら日経平均は19000で固定します。

想定: 全国50工場の「室温及び株価管理部」

ここでやっと役立つTipsです。.xltmというファイルがあります。これはテンプレファイルというやつです。普通に開くと「ファイル名1.xlsm」が生成されます。ユーザに汚されても、もとの.xltmは変わりません。配布するときに役立ちますね。
しかし、各工場のローカルな.xlsmファイルにデータを保存されたら保守運用の人がかわいそうですが雇用創出のために放っておきます。
本当はDBに保存したいですよね。各工場にIDを振って。
SQLを処理するサーバにクエリだして結果をテキストで受け、それをオブジェクトに変換するという手法があります。VBAの仕様上、結果を表すテキストが大きすぎるとオーバーフローしたりします。絶対ベストプラクティスじゃないです。限りなくワーストに近いはずです。しかしそこに正解はない。ExcelがDBへの保存というものを見据えていないから。

開発における注意点

• 全「シート」にロックをかけて入力させたい「セル」のロックを外すと入力欄だけ編集可能になる
• 一応パスワードかけとかないと秩序が崩壊する
• 1シートのロックに1秒くらいかかることがある
• ページの遷移的なものは各種ボタンやクリックイベントなどを利用する
• ボタンには2種類あるので統一したほうがいい
• ここらへんの細かいノウハウはあまり世に出ていないし出してはいけない
• .Range("A1") => 文字列とか数値
• .Range("A1:B1") => 配列。型エラー出うるけどコンパイル時に弾かれない？
• エラーメッセージが2種類くらいしかない気がします。例外処理やらエラーログ出力やらはプロジェクトで統一した方法を使いたいですね。

学び

今回の開発で異なる2つの「シート」がそれぞれViewとModelを担当し、「コード」がPresenterを担うMVP開発がExcel/VBAという閉じていて枯れた環境において実現できるとわかりました。