ここ数日、マクロ・VBAの記事をアップしてきましたが、この記事で紹介したマクロは、実は大きな問題を抱えてました。
今日はその解決策を考えてみたいと思います。
Contents
このマクロの問題の所在
このマクロの問題点はこの２つです。
- 開いているシートで実行される。
- 行の追加があった場合対応できない。
それではそれぞれ見てみましょう。
開いているシートで実行される問題
マクロを実行するシートはSheet2ですが、例えばSheet1を選択してマクロを実行すると、Sheet1上で実行してしまいます。
これについては、シートコピーすればそのままマクロのコピー先のシートで実行されるので問題ないかもしれませんが、やはり元シートのみで処理するように固定したいところです。
行の追加に対応できない問題
こちらの方が致命的です。列を追加してマクロを実行すると、追加列に対しては処理されません。（これは困った。。。）
VBAを見ればわかるのですが、セルの範囲選択を固定してしまっているので、行の追加に対応できないのです。
Range以下で(“F3:F9”)や(“D3:E9”)となっているところです。
これにより列の追加に対応できなくなっているところが問題なのです。
では、解決策を見ていきましょう。考えられる方法は２つです。
マクロを使った方法
開いているシートで処理される問題
そもそも開いているシートでマクロが実行されるのは、「特に指定が無い場合、今開いてるシートで実行する」というルールがあるからです。
であれば、最初にマクロを実行するシートを指定すればいいのです。
例えばそのシートが「Sheet2」の場合の指示はこうです。
指示：「Sheet2」を選択する。
VBA: Sheets(“Sheet2”).select
これをマクロ開始宣言直後に記述するだけでOKです。
これだけで処理するシートが固定できない問題は回避できます。
マクロの実行でシートを移動したのが分かると思います。
行追加に対応できない問題
これはCtrl+↓みたいなことをVBAで記述できれば解決できそうですよね。
VBAでは例えばCells(1,1).End(xldown)とすればCtrl+↓と同じ効果を得られるのですが、間に空白セルがある場合や、最終行が合計の場合などはさらに記述しないといけないなど、なかなか手が出せるものではありません。
以上より、マクロによる解決法はあるにはあるがお勧めではないという結論になります。
実は、もっと簡単に解決できる方法があったのです。それを次で解説します。
テーブルを使った解決法
実はテーブル機能を使うと今回の問題は簡単に解決できます。
ちなみに、テーブル機能とは、選択範囲をデータベースとして識別する機能です。
これによりテーブル自体にテーブル名をつけることができ、また各列に列タイトルをつけることができるのです。
これが何を意味するかというと、VBAで範囲を選択するときに、テーブル名＋列タイトルで指定することができるということです。
そして、行が増えてもテーブルである以上列単位で指定することができる、と言うことです。上の2つの問題動画とこの動画を見比べてください。また列が増えても簡単に対応できるのが分かると思います。
テーブルの設定方法
テーブル設定の仕方は簡単です。
（１）範囲を選択して、（２）挿入タブからテーブルを押して、（３）確認してOK
これだけです。
あと、テーブルに集計行を付け足したい場合はテーブルデザインから集計行にチェックを入れます。
ちなみにテーブル名は、テーブル範囲のセルにカーソルを置いて、テーブルツールのデザインタブを選択し、画面左上の「テーブル名：」で変更することができます。
テーブル機能を使った時のVBA
注目すべきはマーカーの箇所3点です。
- Sheets(“sheet3”).select
- コピー元の範囲を（”サンプル[月末残高]”）でテーブル列を選択する。
- 削除する範囲を（”サンプル[[増加]:[減少]]”）でテーブル列を選択する。
なお、この時、集計行は含まれないのでご安心ください。
ここにテーブルの列選択の書き方をまとめておきます。
- １列のみ：”テーブル名[列タイトル]”
- ２列のみ：”テーブル名[[列タイトル１]:[列タイトル2]]”
※ちなみに、シートが違うと、そのシートにテーブルが無いのでエラーになるので、テーブル機能で処理する場合は、必ずテーブルのあるシートを指定します。
まとめ
シートが違うとエラーになる問題と行追加に対応できない問題の対応策を書いてみました。
ここで大事なことはこれだと思います。
- マクロの問題はマクロで解決しなくていい。
- 使える機能は何でも使えばいいし、テーブル機能とマクロ機能は相性がいい。
- 自分で作りこむより、既にある機能を組み合わせた方が都合がいいこともある。
こんなところではないでしょうか。
最後までお読みいただきありがとうございました！
