[VBA]フォルダ内に存在するExcelにおいて、使用されている機能をカウントするツール「FeatureTotalizer」を作ってみた

発端

新入社員の子が「パソコンが得意です！Excelできます！」ってアピールが激しいらしい。事務をしたいって言ってるみたいだけど、偉い人が求めてるのはただデータを打ち込む人じゃなくて業務改善もできるような人。
意見を求められたけどそれだけの情報じゃどれくらいできるかもわからんな…

— しるる (@sirururun) April 28, 2021

チェックリスト作って、淡々と埋めてもらうのが良いと思うのです。
どこかでレスした記憶があるのですが。

ワークシート関数、ワークシート操作、VBA、VBE操作、など、複数のセクションに分けて、それぞれの要素技術を列挙してチェックつけてもらうと客観的に判断できると思うのですよね。

— W.D. (@WD4096) April 29, 2021

ふむむむ…！やはりチェックリスト！用意してあるとこういう時にすごく役立ちそうですね👀
W.D.さんは用意されてますか？👀

— しるる (@sirururun) April 29, 2021

恥ずかしながら、用意していないのです。
ただ、上長が、他の担当とも作業分担したいと言っているので近日中に整えたいです。

なお、別途、既存VBAに使われている要素技術はまとめ資料を作成中です。
内容は、使ってるワークシート関数、VBAの関数、ステートメント、M言語の一覧。になります。

— W.D. (@WD4096) April 29, 2021

ワークシート関数は既存Excelファイルでの使用頻度順に並べてリストを作ると有効かも知れないですね。
調査用のVBAツールとか作ると、需要ありそうです。
フォルダを指定する事で、その範囲内のExcelファイルの数式を解析して利用数統計を取ってくれる奴とか。

— W.D. (@WD4096) April 29, 2021

連休中の課題にしましょうかね。
書店、Round One、居酒屋が全て休業しているので、出かける用事が皆無になりましたし。

— W.D. (@WD4096) April 29, 2021

以上が、作成した発端です。

集計対象

以下の項目を集計対象としました。
既存Excelに使われている機能を集計し、メンテナンスするのに必要なスキルを列挙するのが目的なので、今回、構文解析は行っていません。

・ワークシート関数
・VBAステートメント
・VBA関数
・VBAオブジェクト
・VBAプロパティ
・VBAメソッド
・M言語
・VBA中に記載されたM言語

集計結果は何に使えるのか

主な利用目的として考えられるのは、既存Excelファイルに使われている機能の把握であり、ツールはそれを前提としたデザインになっています。

使われている機能を把握する事により、業務における学習範囲を絞る事が可能です。
Excelのワークシート関数は現時点で約500個あり、VBAのステートメント、関数、M言語の関数なども含めると、すべての機能を網羅しようと思ったら1500以上の機能を学習する必要があり、それはナンセンスです。
使用する機能を開拓するのは一部のエンジニアで良くて、他の担当者は既存機能に追随する事を目的に学習を進めることは効率的だと考えます。

ツール紹介

以下、メイン画面です。
ツール名は当初別の名前だったのですが、ぐぐってみたら、同じ名前でAddIn作られている方が居て、かつ、意味合い的にもAggregateじゃないな、と、思ったので、こちらの名前（Feature Totalizer）にしました。
使用方法は下図に記載ありますが、「パス指定」「変数名指定」「集計ボタンクリック」の３ステップで完了します。
検査対象を１つのフォルダにまとめてから集計する事をお勧めします。

以下、ワークシート関数のマスターです。
関数名と説明は、すべて参考サイトからの引用です。
ここに記載されていない関数名は、オリジナル関数と見做して集計しません。

以下、VBAのマスターです。
種別には「Solver関数、コレクション、オブジェクト、メソッド、プロパティ、関数、ステートメント」を記載していますが、参考サイトと厳密な1対１対応になっていません。
例えば「ステートメント」は「言語リファレンス＞リファレンス＞キーワードタスク別キーワード」から取得していますが、「ステートメント」という表現は取られていません。
古い話ですが、MSX BASICのテキストにおいて「関数」「ステートメント」と分けて記載されていましたので、その分類に従いました。

以下、M言語のマスターです。
関数名および説明は参考サイトに準拠していますが、1セル1関数になるように行を増やしています。
なお、M言語についてはステートメントに相当するキーワードの探索を行わない仕様としています。

以下、集計結果です。
それぞれファイル名、名称、出現回数（カウント）が分かるように集計しています。VBAのみ、種別も表示するようにしています。
並べ替えは行っていませんので、必要に応じて並べ替えを行うと良いと思います。

集計方法

ざっくり説明すると、「全文字列からキーワード候補の文字列を配列で取り出し、それぞれのマスターに存在する物をカウントする」方法で集計しています。
厳密な構文解析を行っていませんので、想定できていない誤動作は起こりうると考えていますが、当座の目的は果たせると考えています。
集計方法は、それぞれの機能によって微妙に異なります。
ざっくり以下の通りです。

・ワークシート関数
　セルの「Formula」プロパティのうち、1文字目が「=」の文字列から、「(」の直前に記載された文字列をキーワードとして取り出し、マスター有無を確認の上、集計しています。

・VBA
　対象ブックのCodeModuleに含まれる文字列をLinesプロパティで取得、した後、次の操作を行う事でキーワードを取得、マスター有無を確認の上、集計しています。
　・文字列除去
　・コメント除去
　・プロシージャ名除去
　・行継続文字「& _」除去（replaceで置換）
　・ラベル除去（([\w|\.]+: )を除去）
　・複数のスペース「\s」を1個の半角スペースに置換（[\s]：[ \t\r\n\v\f]と等価）
　・左辺値から変数名および「=」除去（([\w|\.]+ = )を除去）
　・特定文字列除去
　　文字列連結文字「&」、半角カッコ「()」、半角中カッコ「{}」、カンマ「,」
　・複数のスペース「\s」を1個の半角スペースに置換（[\s]：[ \t\r\n\v\f]と等価）
　「.」を半角スペースに置換
　・半角スペースを区切り文字としてsplit
　・文字列が無くなるまでループ
　・3個の文字列を半角スペースでつないだ文字がキーワードにあるか確認
　・2個の文字列を半角スペースでつないだ文字がキーワードにあるか確認
　・1個の文字列を半角スペースでつないだ文字がキーワードにあるか確認

・M言語
　ブックのQueriesオブジェクト配下にあるFormulaプロパティから文字列を取得し、ワークシート関数と同様の手順で関数名を取り出し、カウントしています。
　なお、VBAにてM言語を編集している可能性も考慮し、コードモジュールから取り出した文字列についても、ダブルクォートを除去して結合した後にワークシート関数と同様の手順で関数名を取り出しています。