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

vbasheetspecifyeyecatch

エクセルVBAのコード作りで、シートの指定はイロハのイの字と言われます。

本当にこれが完璧ならマクロ実行エラーの多くが解決するでしょう。

そんなシートの選択と指定を説明します。

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

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

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

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

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

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

vbasheetspecifyp016

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

  • シート名によって指定
  • インデックス番号によって指定
  • シートプロパティの変更によって指定

シート名によって指定

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

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

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

また、

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

VBA
Sub シート名を付ける1()
    Worksheets.Add
    ActiveSheet.Name = "新規シート"
End Sub
vbasheetnamep000
新しいシートに名前を付ける
vbaaddeyecatch Excelシートを追加・挿入するAddメソッドの使い方

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

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

インデックス番号でワークシートを指定するには、 WorkSheets(番号) と表記します。

シートのインデックス番号とは、シートタブの左より順に割り振られる番号です。注意する点は、シートそれぞれの固有の番号でないということです。

インデックス番号の特徴を検証する

シートを10枚作成します。シート名は自動で割り振られます。

vbasheetnamep003
シートを10枚作成

インデックス番号は左から順に割り振られることになります。

メッセージボックスで、1番、3番、5番、8番のシート名を表示します。

VBA
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
vbasheetnamep004
インデックス番号でのシート名

シート名とインデックス番号が一見、一致しています。

矢印下001

では、Sheet2とSheet5とSheet8の3枚を削除します。

つまりインデックス番号2番と5番と8番が削除されるということですが・・・

vbasheetnamep005
10枚中3枚シートを削除

コード文の「Sname8 = Worksheets(8).Name」はコメントアウトしています。(このコードを実行すると、シート枚数が足らないので、エラーストップしてしまいます。)

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

vbasheetnamep006
インデックス番号でのシート名

つまりインデックス番号は、単純に、1番が始まりで左よりシート枚数を数えた番号ということです。

決して各シートと紐づいたID番号ではありません!

そしてその指定方法は、 WorkSheets(番号) と記述します。

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

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

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

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

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

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

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

特にActiveX系のコントロールを使用する場合は問題です。例えば、「クリアのコマンドボタン」と「印刷のコマンドボタン」がデフォルトのまま「CommandButton1」と「CommandButton2」なんてコード文上で意味を判読せよなんて不可能なくらい大変です。

さらに「クリアシート」が作成されていて ” クリア.cells(1.1) ” と、書いてあった場合に クリアシートのセルA1か、セルA1を消去なのか、完全に混乱してしまうでしょう。

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

vbasheetspecifyp009
シートのプロパティ

Name項目を変更する場合

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

vbasheetspecifyp010
「もくじ」に変更
矢印下001

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

vbasheetspecifyp011
Sheet1がもくじに変更

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

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

VBA
Sub シートの指定7()
    Worksheets("もくじ").Select
    Range("B3").Value = "ありがとう"
End Sub
vbasheetspecifyp012
セルB3に値が表示された

オブジェクト名を変更する場合(オススメしない!)

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

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

vbasheetspecifyp013
オブジェクト名を目次に変更
矢印下001

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

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

vbasheetspecifyp014.
ツリー上の変化

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

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

VBA
Sub シートの指定8()
    目次.Select
    Range("B5").Value = "ありがとうさん"
End Sub

では、先ほどのWorksheets(“もくじ”)での指定方法はもう使えないのかというとそんなことはありません。

Sub シートの指定7() を実行しても正常にコード処理されています。

vbasheetspecifyp015
「シートの指定8」でも「シートの指定7」でも機能する

シートの指定はこんなに大切

vbasheetspecifyp017

シートを指定することは、VBAの中での基本で、かつ、超重要な作業です。

間違った操作や結論にならないように、「何処のシートの・・・」はいつも細かくチェックしておく必要があります。

例えば、このような会話がかみ合わないケースにも似ています。

悩める君
悩める君

もう一人、11時に太田さんと道頓堀の橋で待ち合わせだよ。遅れているのかな?

うささん
うささん

もう来るんじゃない。太田さんは市内でしょ。

悩める君
悩める君

違う違う、高槻市の人だよ!

うささん
うささん

市内の人かと思ったよ

ねこ氏
ねこ氏

最初にどこから来るか言わないと、どれくらいで着くか分からないよね。

簡単に言うとこんな感じですが、この程度では済まなくなるケースも多いです。

シート指定の基本的な意味

VBA
Sub シートの指定1()
    Range("B3").Value = "お疲れ様"
End Sub

「コード記述上では、 ” どのシート を指定する” とは書かれてはいない」という場合があります。

でも、コードを実行すれば、問題なくエラーストップなしに実行されます。

vbasheetspecifyp001
シートの指定「アクティブシート」

このコード記述は、シートを指定しなくてもよいのではなくて、

シートの指定が省略されている場合は、今まさに開いている ” アクティブなシートを指定している ” 、ということなのです。つまり、次のコードと同じ意味になります。

説明用に書いただけで、「 Activesheet.Select 」とは実務で書いたことも、見たこともありませんが・・・

VBA
Sub シートの指定2()
    ActiveSheet.Select
    Range("B3").Value = "お疲れ様"
End Sub

なので、Sheet2のシートのセルB3に値を表示する場合は、「Sheet2」シートを指定することになります。

VBA
Sub シートの指定3()
    Worksheets("Sheet2").Select
    Range("B3").Value = "お疲れ様"
End Sub
vbasheetspecifyp002
シートの指定「Sheet2」

基本、シートの指定とは、「操作を行う対象(セルなど)はどのシートにあるのか」を指定することです。

このシートの指定を怠ると例えば、

バラバラに散らばったデータを一まとめにするような一覧表を作る場合などは、本来は、” どのシートのセルのデータ ” かを一つづつ指定しておくべきところを、違ったシートの全く関係のない値を引っ張ってくることにもなりかねません。

他のシートから値を取得する時は要注意

エクセルVBAのプログラムコード作成でよくある内容ですが、作業中のシートとは別のシートからセルの値を取得することも頻繁です。

その都度参照するシートが変わってしまう場合は、今、どのシートにフォーカスがあるのかを常に意識しておくことが必要です。

4つのシートから値を取ります

アクティブシート(Sheet1)に他シートセルからコピーした値をペーストして表示させます。

vbasheetspecifyp003
アクティブシート
vbasheetspecifyp004
Sheet2
vbasheetspecifyp005
Sheet3
vbasheetspecifyp006
Sheet4

Sheet2のセルA1、Sheet3のセルA1、Sheet4のセルA1、をそれぞれコピーしてA3、C3、D3セルにそれぞれ貼り付けます。

この場合は、Sheet1が常にアクティブシートになっています。

VBA
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にはこのように表示されます。

vbasheetspecifyp007
VBAコードの実行結果

では、一見同じように見えるこのVBAコードではどうでしょうか?

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から変更されてしまいました。

vbasheetspecifyp008
コードミス例1

今回のケース、このように別のシートを選択した場合は、アクティブシートを元に戻すコードを記述しておくことが必要になります。

VBA
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

まとめ

vbasheetspecifyp018

シートの指定方法をごく単純化して説明してきました。

プログラム作成での本番のコード組み立てでは、もっと複雑にフォーカス状態が絡み合ってきます。

アクティブ状態を変えずに他のシートを参照してセルデータを取得する記述や、シートを変更して行っていく操作やしっかり見ておかないと、何処にフォーカスが移っているのかが分からなくなることもあります。

そんな時は、このプロシージャーを実行するのが近道です。

VBA
Sub アクティブシート表示()
    MsgBox ActiveSheet.Name
End Sub

エクセルVBAを独習するのに参考書は欠かせません。 参考書選びは自分に合った「相棒」にできるものを選んでいきたいです。

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

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

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

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

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