こんにちは。Excel開発15年、セルネッツの竹本です。
今回は「知らないと損、VBAシート選択、名前を使っちゃダメ」について解説をします。
名前とは、VBAでシート操作をする際のシート名のことです。
3つの方法を紹介します。
Aはバツ。マクロ記録をそのまま部分修正をしたものです。
Bもバツ。Set命令で入力と出力を格納するものです。
Cはマル。セルネッツ流と呼んでいますが、オブジェクト名を活用するやり方です。
処理の概要として、3枚のシートがあります。
桜井商店、佐々木商事、高井戸駅前不動産。3つの顧客ごとのシートから、未処理データを「出荷一覧リスト」というシートに持ってきます。
3枚の顧客シートのサンプルデータを見てみます。
まず桜井商店。5件中G列の処理の欄が済みになってないものを持ってくればOKです。
次が佐々木商事。一番上のものです。
最後、3枚目のシートが下の2件だとします。
ここで解説したいのは、持ってくるやり方というより、シート名をどうやって操作しているかのところだけです。実際にやってみます。
まずデータ転記、Aのほうです。これを押します。終わりました。
3枚のシートの中から未処理のものだけを持ってきました。
いったん初期化をしてBをやります。Bも結果は同じです。
実際にAとBを見てみましょう。
Aをダブルクリックして見たところですが、桜井商店シートを選択して、持っていきたいところを選択して、セレクションコピーで再び出荷一覧リストをセレクトし、コピーしたい先を選択し、アクティブシートペーストです。この6行を書いています。
3回終わったらクリップボードを開放して、ホームポジションに戻ったら終わりました。
ここまでがAのボタンです。
次はBのボタンです。
Bのボタンはそこまでのマクロ記録に加え、記述をよく見ると、4枚のシートを使用する場合、そのシートに取り扱えるオブジェクト名を作成、オブジェクト名にシートを格納します。オブジェクト変数を用意して格納します。
出荷一覧リスト、桜井商店、佐々木商事、高井戸駅前不動産という、固定のシート名称を取り扱うのではなく、オブジェクト変数に入れて、そのあとやっていることは一緒です。
ここがダブルシート1になったり、1を選択して出力先を指定していますが、ここが2になったり3になったり4になったりします。
先ほどとの違いは、Set命令でオブジェクト変数を使用していることです。ここで変数の宣言をして使用したあとは、Nothingでオブジェクトを開放する必要があります。
この2つの方法はセルネッツ流ではバツにしています。
では推奨のやり方について紹介します。
やっていることは同じです。いったん初期化をし、Cのボタンをやってみます。
処理の結果はまったく一緒です。
注目していただきたいのですが、仕込みをしています。21、22、23というように、シートに名前をつけています。
この名前をプログラムではなく、Excelのシートそのものに命名してしまうやり方です。
今は21、どこで設定するのかというと、21であればここです。VBEのプロパティウィンドウのオブジェクト名、ここをシート1とかシート2とか、作った順番にシートの番号が決まっています。
このやり方ではシート名をそのまま固定で操作している場所はひとつもありません。21とか、01とか、なにをどこにというやり方ができます。
この使い方だけを覚えていただければ良いと思います。
ではまとめです。
左がシート名をそのまま使った場合、右がオブジェクト名を設定した場合です。
対比をしているのでわかりやすいと思います。意図した順番通りに並べることができます。
セルネッツが開発にあたって必要なシートに命名したものでないものがあとから追加されてきた場合は、この2枚のシートはあとから足されたシートだとわかります。
つまり「O_」「数字二桁」のものが開発のほうで意図してつけた命名になります。
左側を見ていただくと、そういった処理が行われていないので、シートが物理的にこの順番になっていたとしても、高井戸駅前不動産から始まって、その次に請求書が来たり、こういった実際の物理的なExcelブックのシートの並びとは違った状態になってしまいます。
というふうに、ここの並びと物理的なシートの並び方が一致することで、開発においてもストレスのない検証・チェックができるので心がけてください。
最後のまとめです。
重要なことは、まず1番、入力と出力が一目瞭然であることです。「どのシートからどのシートになにをするのか」と読み解けることが重要です。
たとえば物理的な名前でやった場合、プログラムで書く、大量シートでこれをやると混乱のもとになります。
一方で番号づけをした場合、このシートのこのあたりはほぼ関係ないので、ここだけに着目することができるので効率は上がり、間違いも少なくなります。
つまり、シート名をそのまま使っちゃダメということになります。
特定のワークシートをSet命令でオブジェクト変数に格納した例を先ほどお見せしましたが、その必要がないことがほとんどです。
ですので、物理的なシート名ではなく、固定で使用できるならオブジェクト名を活用するようにしましょう。
メリットとしては記述を減らせるので、お客様とのやりとり・検証をするときにドキュメントもやさしくシンプルになりますし、齟齬、理解誤りが回避できます。
もうひとつ、シート名が物理的に変わったとしても、ここにスペースが入ってしまうなど、実行時エラーを回避することができます。
先頭番号が付与されていないシートに関してはこの2つです。
ユーザーが作成したシートであろうということが判断・手がかりになります。
そしてSet命令を使わないということは、Nothingをする必要もないので、無駄がありません。
今回は「知らないと損、VBAシート選択、シート操作に関しては名前を使わなければならないとき以外は使わないようにしてください」という内容でした。
解説は以上となります。毎週金曜日の夜9時に投稿しています。ありがとうございました。
タケモ塾では、今後も皆さんのVBA学習に役立つコンテンツを作成してまいります。
ブログ記事、Youtubeチャンネルのご質問・ご感想・ご要望などお気軽にお問合せください。
お問合せはこちらから ✉
タケモ塾運営：株式会社セルネッツ
