最後に残った事務員マクロ
みなさ～ん！「マクロの書ける事務員さん」です！
いつもお世話になっております！
事務員さんからは「この作業なんとか楽にならない？」と頼られ、
IT課からの叱られにビビりながらマクロを配布する身です。
マクロを綺麗にしよう！
「直せないし拡張性がないから」
一般的に事務員さんの作ったマクロは嫌われ、
実務経験にはならず、
「闇マクロ」と呼ばれ、
未来もないワケですね！！
ならば作りましょう。綺麗なマクロを。
なので私は、
「直せるように、拡張性を持たせて、壊れにくい」マクロを作ろうと思っています！
その集大成のようなマクロを派遣満了前に何とか完成させたので、
今日はそのマクロについて喋ろうと思います。
「事務員さんがやってるおおよその集計業務汎用代替自動化ツール」
事務員さんがやってることと言えば、
- オートフィルタで絞り込んだものの件数をどっかに転記する
- オートフィルタで絞り込んだときの特定列の合計値をどっかに転記する
- どっかに転記したそれらを合計したり減算したりする
- どっかのセルのデータをそのまま転記する
- 特定表の特定項目(支店名など)でlookupしたものを転記する
みたいなことじゃないですか？
その辺をWS上で設定してそれを取得して実行するマクロを作りました！！
構造
モジュール数はおおよそ30、そのうち10が標準モジュールで残りがクラスモジュールです。
図にするとこんな感じ！
(内容説明したらAIくんが作ってくれました ありがと～～～！！そのうち勉強して自作したいですね)
OOPやデザインパターンを使ったなんかすごいマクロ！
まずVBAは所謂手続き型の「やってることをそのままコードにする」というのが一般的です。
でも別に実はそうじゃなくてもいい！！
ので、例えばこんな感じ。
Sub RunTool()
Dim mgr As Manager_Workbook
Dim tasks As Collection
On Error GoTo ErrorHandler
With New Guard_Performance
Set mgr = New Manager_Workbook
If Not mgr.Guard.ValidateRules() Then GoTo ExitProc
Set tasks = LoadConfigAndTasks()
Dim Processor As Svc_BatchProcessor
Set Processor = New Svc_BatchProcessor
Processor.Run tasks, mgr
End With
'以下略…
ほとんど実処理は委譲しちゃって、メインプロセスはな～んにも知りません。
え～？
どっから設定取ってくるの？どうやって？
その設定が何で？どこから？どうするの？
な～んにも知りません。無知です。
何故OOPなのか？
VBAは「グローバル変数の嵐」「数千行のIf文」になりがちです。
それを防ぐために、今回は 「SOLID原則」 を意識してみました！
単一責任の原則を重視し、
「データを持ってくる人」
「計算する人」
「結果を書き出す人」
それぞれを完全に分離しています。
これにより、例えば「ExcelからではなくAccessからデータを取る」という変更があっても、
修正はIDataSource_Accessを追加するだけで済んで、
集計ロジックや出力ロジックには触らなくってオッケーなんですよ！
ちなみにVBAではモジュールのフォルダ分けなんかがないので、
これをあんまり重視しすぎると逆に分かりにくくなります。
なので、実はこれを徹底しすぎないのも実はポイント。
例えば私がファクトリとして使用しているM_DataFactoryは
クラスの生成だけでなくVariantの返却の実行指示も行っています。
ここは完全に責任外のことをやっていて単一責任の原則的には良くないんですが、
実行を指示するだけのクラスを作るとかやってると
モジュール一覧が大変なことになるのであえて分け切ってません。
密結合（互いの依存が強くてどこかを直すとどこかが壊れる）を避け、
疎結合（各機能がそれぞれ独立している）にすることで、
実はこの時代ではよくあるAIに指示を出してコードを作るってときも、明確に「このクラスだけを直して」と言えるメリットがあるんです！
例えば手続き型の大きなマクロを作っていると、
「ここだけ直してね」「他のところは触らないで」
ってものすご～くおねがいをしても、AIって結構要らんとこ触るじゃないですか！？
その癖直してほしかったところは直ってなかったりするし……
アレを防ぐためにも結構しっかりモジュールやプロシージャを分割するのって有効なんですよ！
あんまり貼る文字数多くなるとAIも読むの大変ですからね。
人間がいらんとこ触らないようにして、うっかり触っても問題を広げにくくするモノですから、
AIにもそれは有効ってワケです。
ちなみにちょっと専門的な話をぺろっとすると、
FactoryパターンやStrategyパターンを集計ロジックの拡張性を持たせるために採用しています。
また、拡張に失敗しても既存部分にダメージがないように、というのも大きいですね。
アレです、「閉じてて開いてる」ってやつです。
ここで皆さんにひとつお土産を配ります！
クラスGuard_Performanceはめちゃめちゃオススメなのでここで全文お渡しします。
最悪今日はこれだけ持って帰って！
'ClassModule Guard_Performance
Option Explicit
Private m_OldScreenUpdating As Boolean
Private m_OldCalculation As xlCalculation
Private m_OldEnableEvents As Boolean
Private m_OldDisplayAlerts As Boolean
Private Sub Class_Initialize()
m_OldScreenUpdating = Application.ScreenUpdating
m_OldCalculation = Application.Calculation
m_OldEnableEvents = Application.EnableEvents
m_OldDisplayAlerts = Application.DisplayAlerts
Application.ScreenUpdating = False
Application.Calculation = xlCalculationManual
Application.EnableEvents = False
Application.DisplayAlerts = False
Application.StatusBar = "処理中..."
End Sub
Private Sub Class_Terminate()
Application.ScreenUpdating = m_OldScreenUpdating
Application.Calculation = m_OldCalculation
Application.EnableEvents = m_OldEnableEvents
Application.DisplayAlerts = m_OldDisplayAlerts
Application.StatusBar = ""
End Sub
これ！すごいいいので！！オススメ！！
使い方はさっきのメインプロセスみたいに
With New Guard_PerformanceとEnd Withで処理範囲をくくるだけ！
そうするとWithに入るときにこのクラスオブジェクトがNewされて、高速化してくれて、
Withから出るとクラスオブジェクトが破棄されるので設定が元に戻ります！うれし！
ちなみにこれ専門的に言うなら「RAII」とかに近いらしいですね。
これはひらたーく言うとよく処理の途中で抜けた時にScreenUpdatingがFalseのままになっちゃって、
え～ん！Excelこわれちゃった！みたいになる事務員さんがいると思いますが、
これはそれを可能な限り防ぐ構成です。うれし～！
例えばOn Error Gotoで終了処理に飛んだ時に、
あ～！うっかりScreenUpdatingとか戻し忘れちゃった！みたいなのを防ぎます。
基本的にはGotoだったとしてもWithブロックを出ればクラスオブジェクトは破棄されて元に戻るし、
プロシージャから出るときにもクラスオブジェクトは破棄されますからね。
しかもこれ拡張性もあり、例えばコンストラクタで秒数取得して、
デストラクタで処理xx秒でした～みたいな出力をさせる機能を追加したりも出来ます。
メインプロセスは全然触らずに！お得！
ただ時々エラーから強制終了しちゃったりEnd SubとかせずEndすると戻らなかったりすることがあるので、
結局は「え！？Excel壊れた！？」って言われないためにも
Sub Reset_Performance()
Application.ScreenUpdating = True
Application.Calculation = xlCalculationAutomatic
Application.EnableEvents = True
Application.DisplayAlerts = True
Application.StatusBar = ""
End Sub
とか作って「なんか変な時に押すボタン」として置いとくのが丸いみたいな悲しき定めは正直あります。
まあ結局「なんか困ったときに押すボタン」の存在自体が事務員さんなどには安心を与えますからね。
これを押せば直るのかも知れないという安心。一種のプラセボの面もあるワケです。
何故こんな厄介な構造をしているのか
メンテナンス性と拡張性、再利用性を限りなく高めるために必要だったからです。
例えば普通の事務員さんが書くマクロやマクロの記録で作られたような手続き型のマクロ、
ちょっと修正や変更がしたくなったら、
上から下までぜ～～～んぶ一回読んで、
修正によって変更が必要な部分をぜ～～～～んぶ確かめて、
その上で修正しないといけません。
しかも、それでいてその上でも思わぬ変数同士がつながっていたりしてぶっ壊れたりすることもあります。
そうなると結局、毎回毎回似たような機能を持つマクロを何個も何個も作り直すことになります。
それが速くて確実だからです。
でもイヤじゃないですか！
毎回毎回、同じような処理書いて、同じような変数で受け渡しして、
それを毎回毎回書くの！
イヤじゃないですか！？
そのために今回の構造があるのです！
メインプロセスは処理の上での流れしか知りません。
例えば元データがどこにあるのかも知りません。
どんな形かも知りません。
元データからどうやって欲しいデータを取ってくるのかも知りません。
その処理をどう行っているのかも知りません。
つまり、中身が多少差し変わっても問題ないんですよ！すご～い！
他にも例えば今このマクロで使っているIDataSource_xxという名前のクラスは、
- セル範囲の取得
- テーブルの取得
- PVTの元データの取得
以上のことをそれぞれVariant配列で返却する構造なんですが、
ここに例えば「DAOで取得したRecordSetを返却する機能をつけよう！」みたいなことも可能です。
結局次の集計をしているICalculation_xxというクラスはVariant配列を取得してそこから集計をする、
という構造なので、与えられるものがVariant配列でさえあれば文句は言いません。
私はあまり業務で使わなかったのでつけなかったCSVの取得なんかもさらっと付けられちゃいますよ。うれしいですね～！
つまりそれぞれの処理単位が
「これを受け取って」
「これを返却する」
その約束さえ守ればつつがなく処理を継続することが出来るのです。
ちなみによく世のVBA×OOP講座で「継承」の話が出ますが、
実はVBAには正式な継承は存在しないので、
IcalculationやIDataSourceはインターフェースであり、
共通処理を持っているみたいなことはありません。
例えばこんな感じ。
' IDataSource クラス（インターフェース）の中身のサンプル
Public Function GetData(task as Task_DTO) As Variant
End Function
ここで保証するのは 「GetData」って命令と「Task_DTO(条件データ)」をくれればデータをVariantで取ってきますよ！
ってことだけです。
ただしそれ以上のことは全く意味がないかと言えばそういうワケでもなく、
例えば「こういう名前のこういう引数を受けてこういうモノを返すメソッドを持つ」ことを保証した上で
同じ「Icalculation型」や「IDataSource型」のオブジェクトに入れられるようになるので、
修正するときに影響が外に出なくなるのでうれしいのです。
外から見たら同じ見た目で中身が違うだけなので、多少書き換わっても外に影響が出ません。
例えるならばメインプロセスを社長さんとすると、
「近くにいた人にこの書類が欲しいってお願いしたら、
誰かは分からないがとりあえず社員証付けた人が呼ばれてきて、
『じゃあおねがい』って言えば働いて結果の資料をくれる」
みたいな状態になります。
社員証ついてて身元は保証されてるし、
日本語の「じゃあおねがい」が通じるのでそれを言うだけです。
この人がどの部署の誰かは分からなくてオッケーです。
この辺りはそのうちもうちょっと詳しく話をしたいですね。
ただ今日は「VBAには正式な継承がない」ことだけ覚えて帰ってください。
インターネットには嘘と方便がいっぱいです。
結構継承はこうやる！って言いながら委譲してるだけのサンプルコード載せてる方いますからね……
そのうちそれを詳らかにしたいですね～。
ちなみにエラーハンドラは専用モジュールが一括管理して、
内容によってMsgboxではこうしたらいいんじゃない？みたいなメッセージを出し、
(例:この名前のファイルないかも？とかこのセルの指定おかしいかも？とか！)
WSまたはtxtに管理者用エラーログを出力しています。
(Err.Descriptionとかの硬いやつですね)
事務員さんはVBEが出ると怖くて泣いちゃうので出来る限りエラーが出てもVBEが出ないようにするのは大事です。
事務員さんに嬉しいあの機能！
設定変更する度にVBAをいじらないといけないマクロを作るとどうなりますか？
そう！いちいち作成者に返ってくるんですよ！！マクロが！最早関係ないのに！！
なので、設定用WSを使用して設定を切り替えて、全くVBAを触らなくてもよい構造にしました。
例えばフィルタロジックを使うときの設定をするとします。
(実際にはすべての設定を一枚のWSでやっている都合上ちょっと構造は違うのですが説明用なのでイメージです)
| 出力先 | 取得元WBパス | 取得元WB名 | 取得元WS名 | 条件列1 | 条件1 |
|---|---|---|---|---|---|
| A1 | C:test | testWB1.xlsx | test1 | 商品名 | りんご* |
| A2 | D:test2/test | testWB2.xlsx | test2 | 支店名 | #Branch_Short# |
| A3 | C:test | testWB1.xlsx | test3 | 区分 | 100~300,!150 |
こんな感じの表があり、これをマクロで読み込んで使っています。
出力先WB/WS情報はまた別のWBの表から取得しており、
複数のWB/WSの同じセルに同じ内容を吐き出すイメージです。
ちなみにこの設定シートもマクロで作成できるのですが、
例えば取得元WBパス～取得元WS名のセルをダブルクリックするとファイル選択ダイアログが出る機能なんかも付けています。
事務員さんはパス調べてくるのも苦手ですからね。
事務員さんがパスとファイル名の間に\が必要なんてことを知っているワケもないので、
フルパスの作成はFSOのBuildPathで実行しています。
勿論FSO使うならファイルの存在確認もしてますよ。FSO、便利！
そしてここからポイントなのですが、
このフィルタ処理自体を独自のロジックで実行しているため、
オートフィルタの代替と言いつつ実はオートフィルタより賢いです。
あいまい検索対応
「りんご*」という条件が入力されていますが、これは「りんごから始まるもの」という意味です。
つまり「りんごジュース」も「りんごジャム」も引っかけます。
ついでに「りんご」自体も引っかけます。
逆に「青森産りんご」は引っかかりません。
これは内部では比較を=ではなくLikeで実行するために可能になっています。
プレースホルダ対応
このプレースホルダはWS名を取得しフィルタ条件として使用します。
そしてBranch_Shortという名前の通り、WS名が「xx支店」である時「xx」のみを使用します。
例えば東京支店なら東京、大阪支店なら大阪。
そしてそのプレースホルダの設定もWS側から実行しています。
#Branch_で始まるならShortのとき「支店」を抜きなさい、とか、
#Office_で始まるならShortのとき「営業所」と「出張所」を抜きなさい、とかね。
DTOにDictionaryの形で設定を保持してプレースホルダが来たかをExistsで判定して、
来たらWS名取得してShortならいらないところ落としてフィルタ条件として使うだけです。
範囲対応/除外対応
範囲指定と除外設定が出来ます。ちなみに複数条件は,で区切る形にしています。
それぞれ特定の記号を含むかを文字列で判定しているだけです。
複数あるかどうか判定して、複数あると思えば ,でSplitして配列にして順番に見ていくだけ。
判定ロジックはそれぞれFunctionで持って、順番に判定していく形です。
WBのI/O(開いたり閉じたり)を最低限に！
上記の表の処理、実は上から順番に実行するワケではありません。
なんと自動でマクロ側がお！1行目と3行目同じWB使ってデータ取得するなら、
これを一緒にやっちゃえばWB開いたり閉じたりする回数が減らせるな！
判断してくれます。
うれし～！
ちなみにこれはただScripting.DictionaryでWBフルパスをAddして、
内部に更にフィルタ条件をCollectionで保持していくだけなのでお手軽です。
実行するときKeyのLoopの中でCollectionのループすれば勝手にファイルごとに処理が出来ます。
最後に改めて、どうしてこうなったのか
「マクロの書ける事務員さん」は、まあ勿論IT課などの詳しい方からすれば困った存在であるとは思うのですが、
結構他の「事務職の人たち」にはアテにされている節があります。
だけど、私はもう派遣満了なので、これ以上マクロを作ってあげることが出来ません。
保守してあげることも出来ません。
それならば、自分でマクロを作ることが出来るツールを、
可能な限り自分で直せる/拡張できるような形で作ってあげることが、
私の最後に返せる優しさなのかもしれないなあと思ったワケです。
ここまでガチガチのデザインパターンとOOPで作ってしまえば、
きっと最近の賢いAIならある程度の構造が理解できるでしょう。
それならきっと直して拡張して使っていくことが出来るはずです。
つまりね。
このマクロは「事務員さんの仕事を代替するツール」でもあり、
「マクロの書ける事務員さんの仕事を代替するツール」でもあるんですよ！
これできっともうおしまい。
なんだか寂しいですね。
ちなみにこれを書いた事務員さんはどうなったのか
なんと！正社員のエンジニアに転職成功しました！！
わ～～い！！！
私来月から「マクロの書けるエンジニアさん」です！うれし～～！
とは言いつつ、肩書だけで最初はお勉強からですけどね。
でもいっぱいお勉強出来たらうれしいなあ。今までず～っと技術的対話はAIとだけでしたからね～！
でもまだまだ事務員さん向けのアレコレを書いていきたいと思っているので、
今月中は「マクロの書ける事務員さん」として、
来月からは「マクロの書ける元事務員さん」としてよろしくおねがいします～！
それでは～！