シートの選択・指定方法と意味。簡単でも軽視してはダメ

こんにちは、じゅんぱ店長（@junpa33）です。

エクセルVBAでのワークシートの選択・指定方法についてです。

エクセルVBAにおいて、セルを操作しようとした時には必ず、「どこにあるセルか？」つまり「どのシートのセルか？」を指定しないといけません。

シートを指定するVBAコード記述は非常に重要で、特にシート間を跨いでのセルデータのやり取りを行う場合には、指定間違いが致命的なミスに繋がってしまいます。

VBAプログラム作成では、非常に重要な項目になりますので記述方法を含めしっかり覚えておきましょう。

シートの選択と指定の方法

シートを指定する方法は3つあります。

シート名によって指定

シートをそのシート名で指定します。

記述方法は、　Worksheets(“シート名”)　と記述します。

シートを指定する場合には、もちろんそのシート名が存在していないといけません。存在しない場合はエラーになります。

また、

シートを新しく作成した時は、まだシート名は付いていません。何もしなければエクセルが自動でシート名を付けますが、その新しいシートを指定してすぐに利用するためには、シートに名前を付けて新規作成します。

Sub シート名を付ける1()
    Worksheets.Add
    ActiveSheet.Name = "新規シート"
End Sub

2022年11月20日 Excelシートを追加・挿入するAddメソッドの使い方

インデックス番号によって指定

シートの指定方法で ” シートのインデックス番号 ” というものもあります。

インデックス番号でワークシートを指定するには、　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番が削除されるということですが・・・

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」に代わっています。

シートプロパティの変更によって指定

VBAを記述するビジュアルベーシックエディタ(VBE)より、シートのプロパティを表示することが出来ます。

この方法では、「Nameプロパティ項目を変更」と「オブジェクト名を変更」または「両項目の同時変更」が可能です。

ただし、実用場面で判断すると、

オブジェクト名を変更する場合は、そのシート指定の方法を取ることでのコード文全体に対してのデメリットの方が大きいと言えます。何故かというと、

大量のシート数で、よく似たシート名でエクセルBOOKが出来ている場合には良いと思われるかもしれませんが、

VBAコード作成の中では、デフォルトのオブジェクト名を変更しないと、可読性が非常に悪くなる種のものがたくさんあります。

Sheetsは、どうひっくり返っても単なるシートですので、わざわざオブジェクト名を変更する意味が殆どないと言えるでしょう。括弧内の名前表示で確認すればよいわけで、プログラム実行エラーを呼び込むような誤読を誘う変更は出来るだけしない方が吉と言えます。

Name項目を変更する場合

Nameのプロパティ項目で、「Sheet1」から「もくじ」と変更すると、

シート名「Sheet1」が「もくじ」に変更されました。

この変更によるシートの指定の方法の変化は、

Worksheets(“Sheet1”)　から　Worksheets(“もくじ”)　で指定できるようになります。

VBA

Sub シートの指定7()
    Worksheets("もくじ").Select
    Range("B3").Value = "ありがとう"
End Sub

オブジェクト名を変更する場合（オススメしない！）

オブジェクト名を変更すると、元の「Sheet1」は存在ごと（オブジェクトそのもの）が無くなりました。

つまりこの方法を取った場合は、” オブジェクト名「目次」 のシート” が存在しないエクセルBOOKでは、VBAコードを移植して実行することは出来ないということです。（エラーが発生する）

エクセルのシート外観上は先ほどのNameプロパティの変更時と何らの変化もありません。

ただしProjectツリー上は、Sheet1に代わって「目次」がシートオブジェクトとして登録されています。

この変更によるシートの指定の方法の変化は、

Worksheets(“Sheet1”) や Worksheets(“もくじ”)　から　目次　で指定できるようになります。

VBA

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を独習するのに参考書は欠かせません。 参考書選びは自分に合った「相棒」にできるものを選んでいきたいです。

2022年1月9日 エクセルVBAの独習でおすすめ参考書を7冊選ぶ。良書との出会いは大切です

今回の記事はここまでです。   最後までご覧いただき有難うございました。

エクセルVBA最速理解で必要な知識を集めよう！

エクセルVBA業務ツールで日常の業務改善を行いましょう。

＜記事内容についての告知＞

VBAコードの記述記事においては、その記述には細心の注意をしたつもりですが、掲載のVBAコードは動作を保証するものではりません。 あくまでVBAの情報の一例として掲載しています。 掲載のVBAコードのご使用は、自己責任でご判断ください。 万一データ破損等の損害が発生しても当方では責任は負いません。

アンケートでポイ活しよう!!

アンケートに答えれば答えるほど ”使える” ポイントがたまります。

NTTコム サーチ

Dstyle web