社内のマクロ付きファイルは、何本ありますか?マクロの棚卸しを395ファイルで試した手順

社内のマクロ付きファイルは、どう数えれば本数が分かりますか?

拡張子で拾い、中にVBAがあるかを確かめ、同じ中身をハッシュで束ねる、の3段で数えます。拡張子だけで数えると、マクロのないブックや同じファイルの複製まで1本に数えてしまいます。数えた後の進め方はExcelマクロの引き継ぎにまとめています。

ハッシュは、ファイルの中身から計算する指紋のような値です。1バイトでも違えば別の値になり、同じ中身なら名前や場所が違っても同じ値になります。3段で見るものを表にしました。

.xlsxは外して構いません。Microsoftは .xlsxを「VBAのコードを保存できない」形式と説明しています。

架空の共有フォルダで数えると、何本が何本に束なりましたか?

マクロの守りが作った架空の共有フォルダ395ファイルでは、拡張子で拾ったのが139本でした。そのうち中にVBAがあったのは81本です。81本はファイルのハッシュで33本、コードのハッシュで4つに束なりました。

フォルダは部署と個人の12か所に分けました。.xlsx 232本、開いている人がいると残る「~$」で始まるファイル4本、CSV 20本も混ぜています。VBAの本体は、Excelに付いてくるアドイン4つから借りて架空のブックに入れました。そこから「- コピー」「_old」のような名前の複製と、シートの値だけ違う版違いを作っています。

拡張子ごとに見ると、.xlsmでも69本中13本はVBAがありませんでした。マクロのない新しいブックを .xlsmで保存すると、手元のExcelはVBAの本体を書きませんでした。

洗い出しのスクリプトは、どう動かしますか?

PowerShellで一覧とSHA-256をCSVに書き出します。そのCSVをPythonで読み、VBAの有無を確かめます。どちらもファイルを読むだけでExcelを起動しないので、マクロは1行も動きません。

一覧を作る部分の要点は次のとおりです。名前が「~$」で始まるファイルは、開いている人がいるときにできる所有者ファイルなので外します。

$exts = '.xlsm','.xlsb','.xlam','.xls','.xla','.xltm'
Get-ChildItem -LiteralPath $Root -Recurse -File |
  Where-Object { $exts -contains $_.Extension.ToLower() -and $_.Name -notlike '~$*' } |
  ForEach-Object { [pscustomobject]@{ Path = $_.FullName; LastWrite = $_.LastWriteTime
    Sha256 = (Get-FileHash -LiteralPath $_.FullName).Hash } } |
  Export-Csv macro_files.csv -NoTypeInformation -Encoding UTF8

Pythonの側は、ファイルの先頭が「PK」ならZIPとして開き、xl/vbaProject.binを探します。古い形式ならolefileで _VBA_PROJECT_CURを探します。

手元のノートPC(Windows 11、Ryzen 7 7735U)では、一覧とハッシュが1回目23.2秒でした。2回目以降は2.6秒前後です。VBAの有無の確認は0.6〜0.7秒、olevbaでのコードの取り出しは81本で約16秒でした。測ったのはローカルのSSDで、ファイルサーバーの上では測っていません。

同じマクロの複製と版違いは、どう束ねますか?

まずファイル全体のハッシュで複製を束ね、次に取り出したコードのハッシュで版違いを束ねます。ファイルのハッシュはシートの値が1つ違えば別になるので、版違いはコードの側で見ます。

架空のフォルダでは、VBAのある81本がファイルのハッシュで33本になりました。いちばん大きいコードの束は35本で、中身の違うファイルは14本でした。拡張子は .xls・.xlsb・.xlsmにまたがっています。

途中に罠が1つありました。vbaProjectの塊そのもののハッシュで束ねると、4つではなく10に分かれます。Excelで別の形式に保存し直したファイルは、コードが同じでも塊のバイト列が変わっていたためです。版違いを束ねるときは、olevbaでコードの本文を取り出してから比べます。

共有フォルダの外に、数え漏れやすいマクロはありますか?

あります。いちばん漏れやすいのは個人用マクロブック(Personal.xlsb)です。Microsoftの説明では、各PCのXLSTARTというフォルダにある隠しブックです。Excelを開くたびに裏で開きます。

共有フォルダをいくら数えても出てこないので、毎日の作業で使っている人のPCを1台ずつ見ます。

メールの添付やブラウザから保存したファイルにも気を付けます。こうしたファイルにはMark of the Webという印(ZoneId)が付きます。ZoneIdが3のファイルのマクロは、既定でブロックされます(Mark of the Web とは)。

架空のフォルダで印を付けた13本のうち、VBAがあったのは5本です。一覧のCSVにZoneIdの列を足しておくと、開いたときに止まる候補が分かります。

ほかに、拡張子 .xlaの古いアドインと、.xltmのテンプレートもVBAを持てます。スクリプトの拾う拡張子にはこの2つも入れました。

数えた後、どのマクロから手を付けますか?

コードの束ごとに、最後に更新された日と、使っている人を書き出し、止まると業務が止まる束から手を付けます。本数の多い束が大事とは限らず、複製が多いだけのこともあります。

私が棚卸しの結果を読むときは、束ごとにいちばん新しいファイルを1本選びます。それを使っている人に「月に何回、何のために開くか」を聞きます。答えが出ない束は、使われていない候補として印を付けます。束の代表を1本に決めたら、そのファイルについて引き継ぎ書のひな形の12項目を埋めていきます。

数には限界もあります。今回のフォルダは架空で、本数の割合は作り方で決まり、実際の社内で何割が複製かは数えてみないと分かりません。複製は元の更新日時のまま残ることがあり、今回の複製も元の日時を写して作りました。使っているかどうかは、日付ではなく人に聞いて確かめます。

別のPCで開いたときに止まる原因の1つは、参照設定が見つかりませんのエラーです。棚卸しの表に、参照を持つブックを足しておくと追えます。

このサイトで相談するには?

マクロの守りでは、共有フォルダの洗い出しから、束ごとの処理の図と引き継ぎ書までをお手伝いします。スクリプトは御社のPCの中で動かし、ファイルの中身を外に出しません。

何をどう頼むか決まっていなくても大丈夫です。御社のやり方に合わせて、進め方を一緒に考えます。準備は要りません。無料で相談するからお送りください。