「おすすめ出来ないマクロの記録」の具体的事例
エクセルにはマクロの記録という機能があります。
簡単に言うと「ユーザーの作業を記録してくれる」機能です。
マクロの記録というぐらいなのでマクロなのですが実は多用禁物の機能です。
マクロの記録とは
マクロの記録とはどんな機能ですか？という方向けに以下リンク先で動画を使って説明しています。
ご覧ください。
加えてこの機能を使って開発業務を行うのは絶対にやめた方が良いです。
しかし規制は出来ないので世の中にはマクロの記録で作られたデータが存在してしまいます。
- なぜマクロの記録はダメなのか
- なぜマクロの記録を使って業務を行うのはダメなのか
- マクロの記録の有効な使い方とは
これらを検証するために記事を用意しました。まずは上記3つを検証します。
続いて事例をもとに解説していきます。
事例が必要ない方は途中で離脱出来る構成で書いています。
なぜマクロの記録はダメなのか
そもそも何でマクロの記録はダメなのでしょうか。
色々あるのですがシンプルにこれです。
具体的に言うと簡単な単発操作ならコピー出来ますが都度データの量が違う様な仕事には向きません。
もう１つダメな理由を挙げます。自動で生成されるコードは無駄が多いです。
このコードを覚えて勉強する事はおおすすめしません。
マクロの記録で生成されたコードを覚えても実質勉強の積み上げにならないという事です。
これが「そのままでは使い物にならない」という事です。
なぜマクロの記録を使って業務を行うのはダメなのか
これも簡単な話です。「使い物にならないから」です。
マクロの記録は作業の再現です。という事は繰り返し処理も条件分岐も出来ません。
コードが書ける人は1から1000行目まで・・・という様なコードが書けます。
これから起こることを作業に組み込むことが可能です。
しかしマクロの記録でこれをやるには一回は1000行目まで作業しないといけません。
毎回1000行目までの作業じゃない時はどうしましょうか。
1000行目以上の範囲の仕事をするにはまた手作業で記録をとらないといけません。
こうなると「もはや手作業よりキツい手作業」です。
マクロの記録は条件分岐も出来ません。
「もし○○なら・・・」というような処理は思考が入ってますので当然出来ません。
全てまとめるとマクロの記録では業務を再現する事は出来ません。
必ずと言っていいレベルで欠陥が生まれます。
マクロの記録の有効な使い方とは
単発作業のコードを調べる時だけ使いましょう。
加えて調べて出てくるコードは無駄が多いコードです。
自力で無駄を削ることが出来る人だけに許される作業です。
これが出来ない人はマクロの記録は使うべきではないです。
ここは 有効な使い方とは というテーマですが実質「使わないでください」というのが回答です。
マクロの記録を辞書代わりに有効に使えるのは一定の経験がある人だけです。
一例を紹介
この2つのコードは上と下で同じ作業をしています。
簡単な作業ですがこれだけでも文字数が随分違います。
'マクロの記録で書かれたコードです
Range("K6").Select
ActiveCell.FormulaR1C1 = "おはようございます"
Range("K6").Select
Selection.Copy
Range("K7").Select
ActiveSheet.Paste
'自作のコードです
Range("K6") = "おはようございます"
Range("K6").Copy Range("K7")- セルK6におはようございますと記入
- セルK6の値をコピー
- セルK7に貼り付け
色々あるのですが分かりやすいところで行くと Select が無駄なコードです。
都度オブジェクトを掴むので実行速度としては遅いです。
遅いのですが仕事が単発なので遅さを気にすることすら難しいです。
例えば以下のようなコードが2つあったとします。
- 0.1秒で完了する自作のコード
- 0.3秒で完了するマクロの記録で生成されたコード
この2つは同じ作業をする為のコードだと想定しましょう。
0.1秒の仕事を1000個積み上げると100秒です。
0.3秒の仕事は1000個積み上げると300秒。
1つの作業同士で比べると0.2秒の差ですが積み重ねると200秒の差になります。
マクロの記録で書かれたコードは積み重ねるほどうまく機能しないという事が分かります。
反復作業では大きな差が出ます
コードが自作出来る人は複数の作業の集合体を構築出来ます。
- マクロの記録で1000行分の作業を記録したコード
- マクロを自作出来る人が書いた1000行分の作業をループ処理で対応するコード
この2つは速度と作業量に雲泥の差が表れます。
実際は自作したコードで1000行分の仕事を実行した場合内容によりますが100秒もかかりません。
実行時間はおそらく数秒です。コードを書くのも数分で終わる事でしょう。
一方でマクロの記録でコードを用意するとコードを用意するだけで数時間かかる事でしょう。
実行時間は分かりません。
マクロの記録で1000行分の仕事を記録なんて効率が悪い事やった事がないからどうなるのかは不明です。
（多分コードの行数が多すぎてエラーになります）
加えてマクロの記録で用意されたコードは反復性の無いコードしか生成出来ません。
かなりキツい話ですね。
マクロの記録では根本的にコードの作りが違う
だからといってマクロの記録からSelectだけ消せば良いというわけではないです。
速度が上がって使えるコードになるのかというとそうではないです。
マクロの記録とコードを自作するのは根本的に作業のとらえ方が違うので別の考え方が必要です。
結論としてはマクロの記録を使って良いのは一部の人たちだけです。
先程例で提示した6行のコードから2行のコードに書き換えが出来る人だけです。
繰り返しますがネットの情報や書籍、動画等ではコードを自作する時の書き方しか紹介していません。
マクロの記録でコードを覚えると必ず行き詰まります。すぐ止めましょう。
ここまでの情報を踏まえて次から事例を見ていきましょう。
事例までは必要ないという方はここで終了となります。
他にも記事を用意しています。ご覧ください。
マクロの記録で用意されたコードのNG例を紹介
ここからは事例の紹介です。マクロの記録の悪いところが出ていた例でしたので記事にしました。今回の問題はマクロの記録だけではないのですがとても良い事例でしたので掲載した次第です。
長文で書いているうえに参考になる事も無いのでアコーディオンに収納する事にしました。
興味がある方は読んでください。（途中で離脱OKです）
数日前に「マクロ付のデータが重くて動かない。何とかならないか」という問い合わせをいただきました。
この段階では何が原因か分かりません。まず思いつくのはマクロがデータの容量を圧迫していることはまずないので他に原因があります。調べてみましょう。
そのうえでメンテナンスが必要な要素を洗い出し対策を考えたうえでデータを作り変える事にします。
NG例のデータを改善するための結論
このデータは一見マクロの記録に関係ない様に見えるのですが実は関係しています。マクロの記録を止める事がこの案件の解決につながります。
先に結論をお伝えしておきます。マクロ付のデータでなくてもエクセルが重くなった時はまずこれを考えてみましょう。
データが重たくなる要素を排除しましょう
結論は抽象的です。具体的に何をしたら良いのか。以下3つが守れるようにデータをチェックしましょう。
・関数を書いて値を待つようなシートは作らない
・不要なデータ、シート、プロシージャは持たない
・照合するためのデータは別で持つ
実はこの中の2つはマクロの記録を止める事で同時に達成されます。どんなプロセスを辿って結論が導き出されたのか見ていきましょう。
現状調査
今のデータがどうなっているのか棚卸を行います。
データの外観
早速エクセルのアイコンから突っ込みどころ有です。この時点で指摘があります。データが .xls でした。.xlsm を使いたいところです。詳細はこちらの記事をご確認ください。
次に容量ですね。重たいとは言われてましたがプロパティを見ると想像を超えてきました。作業開始前のデータ容量は50Mぐらいありましたよ。
「中のデータをメンテナンスしたくても動かない・・・」とのことでしたが「ですよね」と思ってしまいました。
勿論必要なデータが居るとは思うのですが不要なデータも居るんだろうなと感じました。ここもデータを開いてから選別していこうと思います。
データを開いてみる
ワークブックを開いてみると色んなものが居ましたよ。
データの使い方
伝える自信ないので図にしてみました。最初の画像がデータの流れです。次の画像でデータの流れに書いてある番号に対する説明をしています。
自力で読み解くと以下の様な建付けでデータが用意されていました。
| 番号 | 作業内容 |
|---|---|
| 1 | ①にインプットデータを貼り付けます。データを削除するマクロ有。 |
| 2 | 関数だらけの②のシートは①のデータから値を読み取ります。同時に⑨シートから値を読み取ります。③のシートに値を飛ばすマクロが用意されています。 |
| 3 | マクロの記録で作ったコード＋手作業を繰り返し不要なデータを捨てます。 |
| 4 | ④は③のデータと外部からの追加データを足す為のシートです。 |
| 5 | 関数だらけの⑤のシートは④から値を取得しています。行列を整えて⑦のシートにマクロで情報を飛ばします。 |
| 6 | ⑦のシートからテキストデータを生成するマクロが起動しtxtファイルが生成されます。 |
・・・色々言いたい事はありますが現状はこうなので仕方ないです。この流れで作業が進められている様です。
例えば私が社外の人だったら本当はここまで用意して「こんなデータなんですけど何とかなりませんか」じゃないと仕事を頼めないのですがおそらく仕事が止まってしまうので自分でやっています。
関数が並んだデータ量の多いシート
②、⑤のシートは一面に関数が敷き詰められています。
各シート共にひとつ前のシートに値が居たらその値を持ってくる・・・という様にどれだけの量のデータが来ても値を取得出来る様にする為に至るところに関数が書いてあるという仕様です。
この2枚のシートがかなりのデータ量です。真っ先に消す方向で検討したいところです。
シートとマクロとの関係
①、②、③、⑤、⑦のシートにボタンに仕込んだマクロが居るのですが・・・⑦以外はマクロの記録で作成したものです。見た感じ⑦だけは違う人が書いたコードだと思います。
ポイントは③です。マクロの記録で用意されたコードが仕込んである複数のボタンが用意されておりボタン間の作業を手作業でつなぐという仕様です。
ボタンや作業部位付近には一応説明が書いてありますが聞かないと分かりにくい仕様です。これは間違いのもとになりそうな要素でした。
仕様に重大な問題を抱えています
都度インプットデータの量が変るのでデータの最終行が変わります。にも関わらずマクロの記録で用意されたコードではフィルターが最終行まで入り切ってない等良くない仕様になっています。
この様にマクロの記録で作ったマクロでは作業範囲をカバー出来ずインプットデータに対応しきれていません。
加えてマクロの記録を使った作業の間を手作業でつないでいるという半自動的な仕様も含まれていますので当然ミスが起こる可能性があります。
これはいつ問題が起きてもおかしくない状態です。といいますか期待されている作業は出来てないので問題が起きるデータで仕事をしています。
この仕様のマクロで作業が行われている事に非常に驚きました。
やはりマクロの記録はコードを少し調べる程度なら使用OKですがマクロの記録を主体とした作業を構築するのはデータ範囲をカバー出来ないのでやめるべきです。
条件分岐や繰り返し処理も出来ません。実質マクロの記録ではコードは成立しないと言っても過言ではありません。
細かい話ですがデータの削除ボタンが足りてないのも気になります。「消すのは手動かい」と思ってしまいます。
データを用意するのも消すのも合わせて考えてこそ効率ＵＰに繋がるので入力されたデータを選択して消すという作業もマクロを用意したいです。
作業用のエクセルはDB用のシートも所持している
⑨のデータは②のシートを埋める際の照合用として扱われるデータです。数千行×数列の情報ですがDBを兼ねている情報は作業シートと一緒に居ない方が良いと思います。
作業中はDBがメンテナンス出来なくなるしファイル自体の容量もUPします。
VBEにも問題が山積
標準モジュールが7つありました。一応どのボタンに対応したマクロなのかは書かれていますが全部展開しないとどこにどのマクロが居るのか分かりません。
マクロの記録で用意されたので不要なコードがものすごくたくさん書いてあり見難いし良く分からない作業のコードも残っています。
使っていないプロシージャもたくさん残っています。私はどのプロシージャが生きているかは判断は出来ますが他に誰がメンテナンスしても良い様に不要なプロシージャを消しておくのはほぼ必須です。
なぜこんな分かりにくい仕様になってしまったのか
マクロが書けないけどなんとか作業を簡単にしようと考えた結果シートの一面に関数を配置したりマクロの記録主体で作業をするような仕様になってしまったと予測します。
マクロの記録が主体なので作業が十分ではないです。関数が書かれたシートや一旦データを置くだけのシートを作ってマクロの記録では対応出来ない作業範囲をカバーしています。
データ容量が重いのはこのようなシートが居る事が原因なのですが根本的な解決を目指すのであれマクロを自作出来る様になり作業の繋がりをもったコードを用意する必要があります。
何とかしようと頑張っている感は見て取れるのですが自分たちでより複雑にしている様に感じました。データ自体は早々に改善が必要です。
問題点を整理する
では問題点と対策をまとめます。
| 番号 | 問題 | 対策 |
|---|---|---|
| 1 | 不要なシートが居る | 消す DB用のシートは別シートに分ける事を提案 |
| 2 | クリアボタンが足りなてない | 必要な場所に設置する |
| 3 | 関数を敷き詰めたシートが重い | 自作のマクロに置き換える |
| 4 | マクロの記録＋手作業が分かりにくい | 一連の作業を自作のマクロに置き換える |
| 5 | 不要な標準モジュールが放置されている | 消す |
| 6 | 不要なプロシージャが放置されている | 消す |
| 7 | データの拡張子が .xls | 拡張子を .xlsm に変更 |
簡単に言うと「各データを整理して不要なものは捨ててマクロの記録を適切なコードに置き換える」という作業をします。拡張子も変えます。
具体的に何をしたのか
不要だと思われるものは捨てて分けた方が良いと思われるデータは分ける様提案するのですがマクロだけは少し説明する必要がありますので解説します。
簡単に言うと関数のシートやデータを一時受けしている様なシートを極力削除してマクロに置き換えました。
関数だらけのシートやデータの一時置きシートも削除しました。
マクロ3はマクロの記録で作られたものではないのでもとからあるマクロを採用しました。
配列を用いる事でデータの置き場問題を解決させています。
結果
データが重たくて作業が出来ないという問題に対してデータを軽くしたのですがやった事はマクロの記録を止めてVBAを使ってコードを自作したという事に尽きます。
50Mだったデータは280KBまで容量を減らすことが出来たので作業性が上がった
なぜデータ量を減らせたのかは以下作業を行ったからです。
- マクロの記録で書かれたコードを書き換えた
- 容量の重たい関数が書かれたシートを無くした
- 不要なモジュール、プロシージャを削除した
- データを.xlsmに変更した
加えて作業性もＵＰしました。
・データのクリアボタンを適切に配置
1点だけ未達でした。不要なシートは無くしましたがDBとして使っているシートの切り分けが出来ていません。データの置き場所は各部署毎にアクセス権があるので私では作業出来ませんでした。
シートを分けた際はマクロを書き換える必要がありますが全部含めて一旦返そうと思います。
同じ理由で追加データもマクロ側から取りに行こうと思えば出来るのですがアクセス権がなく作業出来ないのでやっていません。もう少しやれる事はありますが当初に比べれば格段に進歩しました。
課題はありますが予定した対策の90%以上は達成出来ました。50Mのデータが1M以下になりデータもサクサク動きますので一定の成果はあったと思っています。
しかし私としては「データを軽く出来た」という考え方ではなく「マクロの記録で書かれたコードを止めてコードを自作し余分なデータを整理した結果データが軽くなっただけ」だと思っています。
まとめ
今回はマクロの記録がダメな理由を紹介しました。
続いてNGデータの例としてあるデータを紹介しています。
マクロの記録が組み込まれている業務を作り変える事により作業性をあげるというケースです。
「マクロの記録」を多用して業務を組むと不必要なデータやシートを持つことになります。
やってはいけない事です。難しいと思ってもコードを自作出来る様に努力をしましょう。
コードの書き方や手順はネット上に溢れています。参考になるものもあるはずです。
分からない時は教えている人達に聞けば良いです。
勉強する気がある方は以下記事を参考にしてください。
