エクセルVBAのコード作りで、シートの指定はイロハのイの字と言われます。
本当にこれが完璧ならマクロ実行エラーの多くが解決するでしょう。
そんなシートの選択と指定を説明します。
こんにちは、じゅんぱ店長(@junpa33)です。
エクセルVBAでのワークシートの選択・指定方法についてです。
エクセルVBAにおいて、セルを操作しようとした時には必ず、「どこにあるセルか?」つまり「どのシートのセルか?」を指定しないといけません。
シートを指定するVBAコード記述は非常に重要で、特にシート間を跨いでのセルデータのやり取りを行う場合には、指定間違いが致命的なミスに繋がってしまいます。
VBAプログラム作成では、非常に重要な項目になりますので記述方法を含めしっかり覚えておきましょう。
- シートの知識
- シートの存在を確認する2種類のコードと実務での例題
- シートクリアーを目的のメソッド別にVBA最速理解
- シートのコピーを最速に理解!VBAコードで異なる結果
- Excelシートを追加・挿入するAddメソッドの使い方
- シートの選択・指定方法とその意味。簡単でも軽視してはダメ
- エクセルシートの名付け・変更と取得そして一覧化するコード
- ワークシートのブック内移動と新規ブックへの移転を分かりやすく
- エクセルシートの並び順が思い通り!シート名一覧で自由自在
- Excelシートを保護する。 記述方法とプロテクトされない操作
- エクセルシートを削除する。Deleteの使い方と注意ポイント
- エクセルシートの非表示コードをパスワード管理するVBA
- ワークシートを変数化する3つの手法 オブジェクト変数など
コンテンツ
シートの選択と指定の方法
シートを指定する方法は3つあります。
- シート名によって指定
- インデックス番号によって指定
- シートプロパティの変更によって指定
シート名によって指定
シートをそのシート名で指定します。
記述方法は、 Worksheets(“シート名”) と記述します。
シートを指定する場合には、もちろんそのシート名が存在していないといけません。存在しない場合はエラーになります。
また、
シートを新しく作成した時は、まだシート名は付いていません。何もしなければエクセルが自動でシート名を付けますが、その新しいシートを指定してすぐに利用するためには、シートに名前を付けて新規作成します。
Sub シート名を付ける1()
Worksheets.Add
ActiveSheet.Name = "新規シート"
End Sub
インデックス番号によって指定
シートの指定方法で ” シートのインデックス番号 ” というものもあります。
インデックス番号でワークシートを指定するには、 WorkSheets(番号) と表記します。
シートのインデックス番号とは、シートタブの左より順に割り振られる番号です。注意する点は、シートそれぞれの固有の番号でないということです。
インデックス番号の特徴を検証する
シートを10枚作成します。シート名は自動で割り振られます。
インデックス番号は左から順に割り振られることになります。
メッセージボックスで、1番、3番、5番、8番のシート名を表示します。
Sub シートのインデックス番号で調査1()
Dim Sname1, Sname3, Sname5, Sname8 As String
Sname1 = Worksheets(1).Name
Sname3 = Worksheets(3).Name
Sname5 = Worksheets(5).Name
Sname8 = Worksheets(8).Name
MsgBox "Sname1= " & Sname1 & vbCrLf & "Sname3= " & _
Sname3 & vbCrLf & "Sname5= " & Sname5 & vbCrLf & _
"Sname8= " & Sname8
End Sub
シート名とインデックス番号が一見、一致しています。
では、Sheet2とSheet5とSheet8の3枚を削除します。
つまりインデックス番号2番と5番と8番が削除されるということですが・・・
コード文の「Sname8 = Worksheets(8).Name」はコメントアウトしています。(このコードを実行すると、シート枚数が足らないので、エラーストップしてしまいます。)
Sub シートのインデックス番号調査2()
Dim Sname1, Sname3, Sname5, Sname8 As String
Sname1 = Worksheets(1).Name
Sname3 = Worksheets(3).Name
Sname5 = Worksheets(5).Name
' Sname8 = Worksheets(8).Name
MsgBox "Sname1= " & Sname1 & vbCrLf & "Sname3= " & _
Sname3 & vbCrLf & "Sname5= " & Sname5 & vbCrLf & _
"Sname8= " & Sname8
End Sub
インデックス番号3番は「Sheet4」に、5番は「Sheet7」に代わっています。
つまりインデックス番号は、単純に、1番が始まりで左よりシート枚数を数えた番号ということです。
決して各シートと紐づいたID番号ではありません!
そしてその指定方法は、 WorkSheets(番号) と記述します。
シートプロパティの変更によって指定
VBAを記述するビジュアルベーシックエディタ(VBE)より、シートのプロパティを表示することが出来ます。
この方法では、「Nameプロパティ項目を変更」と「オブジェクト名を変更」または「両項目の同時変更」が可能です。
ただし、実用場面で判断すると、
オブジェクト名を変更する場合は、そのシート指定の方法を取ることでのコード文全体に対してのデメリットの方が大きいと言えます。何故かというと、
大量のシート数で、よく似たシート名でエクセルBOOKが出来ている場合には良いと思われるかもしれませんが、
VBAコード作成の中では、デフォルトのオブジェクト名を変更しないと、可読性が非常に悪くなる種のものがたくさんあります。
特にActiveX系のコントロールを使用する場合は問題です。例えば、「クリアのコマンドボタン」と「印刷のコマンドボタン」がデフォルトのまま「CommandButton1」と「CommandButton2」なんてコード文上で意味を判読せよなんて不可能なくらい大変です。
さらに「クリアシート」が作成されていて ” クリア.cells(1.1) ” と、書いてあった場合に クリアシートのセルA1か、セルA1を消去なのか、完全に混乱してしまうでしょう。
Sheetsは、どうひっくり返っても単なるシートですので、わざわざオブジェクト名を変更する意味が殆どないと言えるでしょう。括弧内の名前表示で確認すればよいわけで、プログラム実行エラーを呼び込むような誤読を誘う変更は出来るだけしない方が吉と言えます。
Name項目を変更する場合
Nameのプロパティ項目で、「Sheet1」から「もくじ」と変更すると、
シート名「Sheet1」が「もくじ」に変更されました。
この変更によるシートの指定の方法の変化は、
Worksheets(“Sheet1”) から Worksheets(“もくじ”) で指定できるようになります。
Sub シートの指定7()
Worksheets("もくじ").Select
Range("B3").Value = "ありがとう"
End Sub
オブジェクト名を変更する場合(オススメしない!)
オブジェクト名を変更すると、元の「Sheet1」は存在ごと(オブジェクトそのもの)が無くなりました。
つまりこの方法を取った場合は、” オブジェクト名「目次」 のシート” が存在しないエクセルBOOKでは、VBAコードを移植して実行することは出来ないということです。(エラーが発生する)
エクセルのシート外観上は先ほどのNameプロパティの変更時と何らの変化もありません。
ただしProjectツリー上は、Sheet1に代わって「目次」がシートオブジェクトとして登録されています。
この変更によるシートの指定の方法の変化は、
Worksheets(“Sheet1”) や Worksheets(“もくじ”) から 目次 で指定できるようになります。
Sub シートの指定8()
目次.Select
Range("B5").Value = "ありがとうさん"
End Sub
では、先ほどのWorksheets(“もくじ”)での指定方法はもう使えないのかというとそんなことはありません。
Sub シートの指定7() を実行しても正常にコード処理されています。
シートの指定はこんなに大切
シートを指定することは、VBAの中での基本で、かつ、超重要な作業です。
間違った操作や結論にならないように、「何処のシートの・・・」はいつも細かくチェックしておく必要があります。
例えば、このような会話がかみ合わないケースにも似ています。
もう一人、11時に太田さんと道頓堀の橋で待ち合わせだよ。遅れているのかな?
もう来るんじゃない。太田さんは市内でしょ。
違う違う、高槻市の人だよ!
市内の人かと思ったよ
最初にどこから来るか言わないと、どれくらいで着くか分からないよね。
簡単に言うとこんな感じですが、この程度では済まなくなるケースも多いです。
シート指定の基本的な意味
Sub シートの指定1()
Range("B3").Value = "お疲れ様"
End Sub
「コード記述上では、 ” どのシート を指定する” とは書かれてはいない」という場合があります。
でも、コードを実行すれば、問題なくエラーストップなしに実行されます。
このコード記述は、シートを指定しなくてもよいのではなくて、
シートの指定が省略されている場合は、今まさに開いている ” アクティブなシートを指定している ” 、ということなのです。つまり、次のコードと同じ意味になります。
説明用に書いただけで、「 Activesheet.Select 」とは実務で書いたことも、見たこともありませんが・・・
Sub シートの指定2()
ActiveSheet.Select
Range("B3").Value = "お疲れ様"
End Sub
なので、Sheet2のシートのセルB3に値を表示する場合は、「Sheet2」シートを指定することになります。
Sub シートの指定3()
Worksheets("Sheet2").Select
Range("B3").Value = "お疲れ様"
End Sub
基本、シートの指定とは、「操作を行う対象(セルなど)はどのシートにあるのか」を指定することです。
このシートの指定を怠ると例えば、
バラバラに散らばったデータを一まとめにするような一覧表を作る場合などは、本来は、” どのシートのセルのデータ ” かを一つづつ指定しておくべきところを、違ったシートの全く関係のない値を引っ張ってくることにもなりかねません。
他のシートから値を取得する時は要注意
エクセルVBAのプログラムコード作成でよくある内容ですが、作業中のシートとは別のシートからセルの値を取得することも頻繁です。
その都度参照するシートが変わってしまう場合は、今、どのシートにフォーカスがあるのかを常に意識しておくことが必要です。
4つのシートから値を取ります
アクティブシート(Sheet1)に他シートセルからコピーした値をペーストして表示させます。
Sheet2のセルA1、Sheet3のセルA1、Sheet4のセルA1、をそれぞれコピーしてA3、C3、D3セルにそれぞれ貼り付けます。
この場合は、Sheet1が常にアクティブシートになっています。
Sub シートの指定4()
Range("B3").Value = "お疲れ様"
Worksheets("Sheet2").Range("A1").Copy Destination:=Range("A3")
Worksheets("Sheet3").Range("A1").Copy Destination:=Range("C3")
Worksheets("Sheet4").Range("A1").Copy Destination:=Range("D3")
End Sub
実行結果として、アクティブシートSheet1にはこのように表示されます。
では、一見同じように見えるこのVBAコードではどうでしょうか?
Sub シートの指定5()
Range("B3").Value = "お疲れ様"
Worksheets("Sheet2").Select
Range("A1").Copy Destination:=Range("A3")
Worksheets("Sheet3").Range("A1").Copy Destination:=Range("C3")
Worksheets("Sheet4").Range("A1").Copy Destination:=Range("D3")
End Sub
Sheet1ではなくSheet2にフォーカスが移動して意図とは違う結果になりました。
Sheet2のセルA1の値を取得するためにSheet2を選択したため、アクティブシートがSheet1から変更されてしまいました。
今回のケース、このように別のシートを選択した場合は、アクティブシートを元に戻すコードを記述しておくことが必要になります。
Sub シートの指定6()
Range("B3").Value = "お疲れ様"
Worksheets("Sheet2").Select
Range("A1").Copy Destination:=Worksheets("Sheet1").Range("A3")
Worksheets("Sheet1").Select
Worksheets("Sheet3").Range("A1").Copy Destination:=Range("C3")
Worksheets("Sheet4").Range("A1").Copy Destination:=Range("D3")
End Sub
まとめ
シートの指定方法をごく単純化して説明してきました。
プログラム作成での本番のコード組み立てでは、もっと複雑にフォーカス状態が絡み合ってきます。
アクティブ状態を変えずに他のシートを参照してセルデータを取得する記述や、シートを変更して行っていく操作やしっかり見ておかないと、何処にフォーカスが移っているのかが分からなくなることもあります。
そんな時は、このプロシージャーを実行するのが近道です。
Sub アクティブシート表示()
MsgBox ActiveSheet.Name
End Sub
エクセルVBAを独習するのに参考書は欠かせません。 参考書選びは自分に合った「相棒」にできるものを選んでいきたいです。
エクセルVBAの独習でおすすめ参考書を7冊選ぶ。良書との出会いは大切です今回の記事はここまでです。 最後までご覧いただき有難うございました。
<記事内容についての告知>
VBAコードの記述記事においては、その記述には細心の注意をしたつもりですが、掲載のVBAコードは動作を保証するものではりません。 あくまでVBAの情報の一例として掲載しています。 掲載のVBAコードのご使用は、自己責任でご判断ください。 万一データ破損等の損害が発生しても当方では責任は負いません。