列を追加するとマクロの集計はずれる?シート名など10通りの変更をExcelで試した

シート名や列を変えると、マクロはどう壊れますか?

壊れ方は「止まる」と「止まらずに違う答えを出す」の2つに分かれ、気づきにくいのは後者です。架空の受注集計マクロに10通りの変更をすると、止まるが5つ、違う答えが4つでした。正しく動いたのは、日付の表示を変えた1つだけです。読み解きから直すまでの全体は、Excelマクロの引き継ぎにまとめています。

試したのは、マクロの守りで書いた64行のマクロです。架空の受注の明細60行(うち10月分46行)を得意先ごとに足し、消費税を付けて別のシートに書きます。

シート名・ブック名・列の番号・設定のセルは直書きし、得意先マスタは絶対パスで開く、古いマクロによくある書き方です。Excel 16.0(ビルド20430、64bit)をCOMから動かし、Pythonで計算した正解と突き合わせました。

列を挿入すると、なぜエラーなしで集計がずれるのですか?

マクロが列を番号で読んでいると、挿入した列のぶんだけ、読む中身が右隣の列にずれるからです。番号の指す先に数字が入っている限り、マクロは何も言わずに足し続けます。

実験のマクロは、4列目を数量、6列目を金額として読みます。得意先コードの右に「得意先名」の列を挿入すると、4列目は品番に、6列目は単価に変わりました。品番は5けたの数字なので、数量の合計は1,258のはずが2,218,855になり、税込の合計は91,564円と出ました。正しい金額の約3.5%です。

見出しの上にタイトル行と空行を足したときは0円でした。マクロは表の範囲をCurrentRegionで決めています。Microsoftの説明では、これは空の行と列で囲まれた範囲です。空行で区切られ、範囲がA1の1行だけになりました。

設定シートの税率の上に「軽減税率」の行を足したときも0円です。税率のセルには8%が、集計する月のセルには0.1が入り、月が1件も合いませんでした。

マクロが止まるときは、どんな文面が出ますか?

止まったときの文面は、原因をそのまま教えてくれるとは限りません。実験では、シート名の変更とファイル名の変更が、どちらも「インデックスが有効範囲にありません」(エラー9)でした。マクロが「受注」というシートと「受注集計.xlsm」というブックを、名前で探しているためです。

フォルダごと移したときは、得意先マスタの元のパスが文面に出ました。

分かりにくかったのは、PtrSafeのないDeclare文を1行足したときです。外から呼ぶと「マクロ ‘…’ を実行できません。このブックでマクロが使用できないか…」と出ました。セキュリティの設定を疑いたくなる文面です。

Microsoftの互換性の説明によると、PtrSafeのないDeclare文は、64bitのOfficeではコンパイルエラーです。同じ文にPtrSafeを付けると、マクロは正しく動きました。.xlsxで保存し直したブックは、開き直すと標準モジュールが0個で、マクロは残っていませんでした。

違う答えに気づくには、どうすればよいですか?

正しい答えが分かっている月で、マクロの合計と手で数えた合計を突き合わせるのが確実です。表の形を変えたら、変えた日のうちに1回だけ試します。

0円なら気づけますが、列の挿入の91,564円のように、それらしい数字が出ると見逃しやすくなります。確かめるのは次の3つです。

1. 件数が明細の行数と合うか(実験の正解は10月分46件)
2. 合計が、フィルターをかけて手で足した値と合うか
3. 得意先の数と名前が、いつもの月と比べて欠けていないか

セルの結合は、件数の確かめで見つかります。実験では結合したセルの2行目以降が空として読み飛ばされ、残った各得意先の先頭行は9月分でした。

表の形を変えても壊れにくいマクロにするには?

列は見出しの文字で探し、シートはコード名で指す書き方に直すと、10通りのうち9つで正しい合計が出ました。止まったのは、マクロ自体が消える.xlsxの保存だけです。

直した版は78行で、同じ集計をします。直したのは次の7か所です。

• シートはコード名で書く(シート名を変えてもコード名は変わらないとMicrosoftは説明しています)
• 列は見出しの「数量」「金額」を探して番号を決める
• 最終行はEnd(xlUp)で数え、見出しの行も「受注日」を探して決める
• 結合したセルは、結合範囲の左上の値を読む
• 税率と集計する月は、名前を付けたセルから読む
• 得意先マスタはThisWorkbook.Pathから開き、ブックはThisWorkbookで指す
• Declare文は#If VBA7で書き分けてPtrSafeを付ける

要点は、列の番号を見出しから決める数行です。

Set f = ws.Range("A1:J20").Find("受注日", LookAt:=xlWhole)
hdr = f.Row
colQty = 列番号(ws, hdr, "数量")   ' 中は Application.Match で見出しを探す
colAmt = 列番号(ws, hdr, "金額")
lastRow = ws.Cells(ws.Rows.Count, colDate).End(xlUp).Row
code = CStr(ws.Cells(r, colCode).MergeArea.Cells(1, 1).Value)

この実験の結果は、どのマクロにも当てはまりますか?

当てはまるのは、同じ書き方をしている行です。壊れ方は変更の種類よりも、マクロがその場所をどう指しているかで決まります。

実験のマクロは、CurrentRegionで最終行を数えていました。最終行を別の書き方で数えるマクロなら、同じ変更でも結果は変わりえます。同じマクロでも、表の右端に列を足したときは正しく動き、品番が英字入りのときは列の挿入で止まりました。

再現の手順は、macro_breaks.py(直した版は –fixed)にまとめています。データは乱数の種20261016で作った架空のもので、ボタンから動かしたときの画面の文面は確かめていません。手元のマクロでは、直書きの行を数えてから表を変えるのが安全です。どのシートを名前で読み書きしているかの数え方は、呼び出し関係とシートの読み書きの図の記事にあります。

止まったときに出る番号の読み方は、実行時エラー1004・9・13・91の読み方にあります。

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

表を直したら合計がおかしくなったマクロも、マクロの守りで、どの行が位置を直書きしているかを一緒に見ます。何をどう頼むか決まっていなくても大丈夫です。御社のやり方に合わせて、進め方を一緒に考えます。

準備は要りません。無料で相談するからお送りください。返信は最短当日です。