VBA技術解説
エクセルVBAでのシート指定方法

ExcelマクロVBAの問題点と解決策、VBAの技術的解説
公開日:2018-09-08 最終更新日:2021-09-04

エクセルVBAでのシート指定方法


ExceのマクロVBAにおいてシート指定は必須になりますが、
シートの指定方法は何種類かあり、それぞれの特徴があります。


シートの指定方法には、以下の3通りがあります。

✅シートのインデックスで番号
✅シートの名称で指定
✅シートのオブジェクト名で指定

シート指定方法ごとに使い方を簡単に説明しながら、
それぞれの主な使用場面やメリット、デメリット等を解説します。


シートのインデックス番号で指定

シートのインデックス番号(順番の数値)で指定する方法になります。

シートのインデックスは、シートの左からの順番通りになります。
ただし、非表示シートも含んでいますので注意してください。
左から、1,2,3・・・の順になります。

マクロ VBA 画像


インデックスを定数値で指定

定数値を直接指定します。

Worksheets(1).Range("A1") = "test1"

手動でシートの順番を入れ替えてしまうと別のシートになってしまうので、
VBAにおいて、このような使い方をするのはかなり限定的になります。

あるシートを先頭に移動する場合
Worksheets("Sheet2").Move Before:=Worksheets(1)

このようにWorksheets(1)の前に移動させることで実現します。

一番後ろに移動する場合
Worksheets("Sheet2").Move After:=Worksheets(Sheets.Count)

Sheets.Countがブック内の全シート数になります。

実際に存在しないシートのインデックスを指定した場合はエラーとなります。

マクロ VBA シートの指定


変数で指定

変数を使って指定します。

Dim i As Long
For i = 1 To Worksheets.Count
  Worksheets(i).Range("A1") = "test1"
Next

全シートに対する処理をする場合の定番一つになります。

練習問題26(全シート処理とハイパーリンク)
・マクロVBA練習問題 ・シンキングタイム ・マクロVBA練習問題回答へ
VBAこれだけは覚えておきたい必須基本例文10
・1.最終行まで処理 ・2.後ろから逆順ループ ・3.コレクション処理 ・4.分岐処理 ・5.セルのコピー ・6.VBA関数とワークシート関数 ・7.オブジェクト変数とWith ・8.ブックを開く・閉じる ・9.ファイル一覧 ・10.テキスト読み書き ・VBA必須基本例文10を覚えた後は


インデックス指定を使う場面は主に以下になります。
・先頭シートを指定
・最終シートを指定
・全シートの処理



シートの名称で指定

シートの名称で指定する方法になります。
マクロVBAにおいては、最も多く使われる方法になります。

定数値で指定

定数値を直接指定します。

Worksheets("Sheet1").Range("A1") = "test2"

一般的で最も良く書かれているVBAコードになります。
VBAの記述が長くなってしまうので、Withステートメントやオブジェクト変数を使うようにします。

With Worksheets("Sheet1")
  .Range("A1") = "test1"
  .Range("A2") = "test2"
End With

Dim sht As Worksheet
Set sht = Worksheets("Sheet1")
sht.Range("A1") = "test1"
sht.Range("A2") = "test2"

第51回.Withステートメント
・Withの構文 ・Withを使った時と使わない時の比較 ・Withの使用例 ・Withのネスト ・Withを使ったときに気を付けるべき書き方 ・Withの使いどころ ・サイト内の参考ページ
第52回.オブジェクト変数とSetステートメント
・オブジェクト変数 ・個有のオブジェクト型とは ・Setステートメント ・Setステートメントの使用例 ・WithとSetの使い分け方 ・Setステートメントの実践的な使い方 ・Is演算子によるオブジェクトの比較 ・最後に


Constで指定

プロシージャー内の複数個所でシート指定が必要であったり、複数フロシージャーで同じシートを指定する場合、
使う都度に指定するのは、メンテナンス性が悪くなります。
シート名を変更したい時に、VBAコードの何か所も変更しなければならなくなるからです。

こうような場合は、PublicのConst宣言で指定しておくことで、一か所で管理できます。

Public Const sht1 = "Sheet1"

Sub sample()
  Worksheets(sht1).Range("A1") = "test2"
End Sub

シート名を変更する場合は、Constの値を変更するだけで完了できるようになります。


シート関数

シート名を変更してしまうと、
例えば、"Sheet1"を"Sheet11"と変更してしまうと、
Worksheets("Sheet1).Range("A1") = "test1"
このVBAはエラーとなってしまいます。

マクロ VBA シートの指定

そこで、シート名を自由に変更したい場合の方法を考えます。

シートの順番を入れ替えても、シート名を変更しても、
とにかく、目的のシートのシート名を取得できるようにします。

シート名を管理するためのシートを追加して、
例えば、
「シート指定」というシートを追加して、A1セルに、

=RIGHT(CELL("filename",Sheet1!A1),LEN(CELL("filename",Sheet1!A1))-FIND("]",CELL("filename",Sheet1!A1)))
このように入れると、A1セルには、
Sheet1
と表示されます。
そこで、このセルの値を使ってシート指定します。

Dim sht As String
sht = Worksheets("シート指定").Range("A1")
Worksheets(sht).Range("A1") = "test"

「シート指定」シートは、VBAでしか使用する必要が無いので、非表示にしておけば良いでしょう。
この方法であれば、シートを削除しない限りVBAの変更はしなくて良くなります。


シートのオブジェクト名で指定

シートのオブジェクト名で指定する方法になります。
オブジェクト名は、VBEで変更することが出来ます。

マクロ VBA サンプル画像
プロジェクト ウィンドウ
Sheet1(Sheet1)
オブジェクト名(シート名)
括弧の中が、通常使用しているシート名です。

初期のオブジェクト名は、このようにSheet1から順に番号が振られています。
このオブジェクト名は、VBEもしくはVBAからしか変更できませんので、
通常のユーザーが間違って変更してしまう事はありません。

プロパティのNameがシート名になります。
シート名を変更してもオブジェクト名に影響はありません。
Sheet1.Range("A1") = "test"
このように、オブジェクト名を直接記述するだけになります。

VBA記述時にわかり易く読みやすいように、オブジェクト名は適切な名前にしておきましょう。
obj○○○
sht○○○
このように接頭子を付けておくと管理しやすいと思います。

オブジェクト名で指定すると、
ActiveXコントロールをシートに追加した時は、コントロールが入力候補に表示されます。
以下は、Sheet1にActiveXのチェックボックスを追加してある場合です。

VBA マクロ 画像

この方法の場合、
シート位置やシート名称に左右されないという利点があります。
オブジェクト名はVBEからでなければ変更できないので、間違って変更してしまう事もないでしょう。

ただし、シートが都度増減したりコピーしたりする場合には、この方法は使えません。
また、他ブック(マクロ記述しているブック以外のブック)に対しては使えません。
固定的なThisWorkbookのシートにおいてのみ使う方法になります。


ブックの保護

VBAでシートを指定している限り、対象のシートが削除されてしまうとVBAはエラーとなります。
これは避けて通れません。

これに対応するには、
マクロの先頭で、必要なシートが揃っているかどうかを確認するようにすれば良いでしょうが、
さすがに、少々面倒な気がしてしまうと思います。
もしそこまでする必要があるのであれば、その前にブックの保護をしておくことをお勧めします。

マクロ VBA シート指定

ただしブックを保護してしまうとVBAで臨時にシートを追加することも出来なくなってしまいます。
そのような場合は、
マクロ内で、ブックの保護を解除してからシートを挿入し、最後にまたブック保護しておく必要があります。

ThisWorkbook.Unprotect
Worksheets.Add
ThisWorkbook.Protect

第68回.シートの保護、ブックの保護(Protect)
・シートの保護 ・シートの保護解除 ・シート保護・解除の実践例 ・ブックの保護 ・ブックの保護解除 ・最後に


VBAでのシート指定方法

VBAにおいてシートの指定は必ず必要になるもので、避けては通れないものです。
しかし、このシート指定は意外とVBA記述が面倒なものです。

使い方にあわせて、シートの指定方法を検討してみるとVBA記述がかなり楽になる場合も多いと思います。




同じテーマ「マクロVBA技術解説」の記事

Colorプロパティの設定値一覧(カラー定数、XlRgbColor列挙)
VBAを定型文で覚えよう
VBAこれだけは覚えておきたい必須基本例文10
エクセルVBAでのシート指定方法
文字列結合&でコンパイルエラーになる理由
手動計算時の注意点と再計算方法
VBAの用語について:ステートメントとは
オブジェクト変数とは何か
VBAの小数以下の演算誤差について
スピルでVBAの何が変わったか
CharactersプロパティとCharactersオブジェクト


新着記事NEW ・・・新着記事一覧を見る

VBA10大躓きポイント(初心者が躓きやすいポイント)|VBA技術解説(2024-03-05)
テンキーのスクリーンキーボード作成|ユーザーフォーム入門(2024-02-26)
無効な前方参照か、コンパイルされていない種類への参照です。|エクセル雑感(2024-02-17)
初級脱出10問パック|VBA練習問題(2024-01-24)
累計を求める数式あれこれ|エクセル関数応用(2024-01-22)
複数の文字列を検索して置換するSUBSTITUTE|エクセル入門(2024-01-03)
いくつかの数式の計算中にリソース不足になりました。|エクセル雑感(2023-12-28)
VBAでクリップボードへ文字列を送信・取得する3つの方法|VBA技術解説(2023-12-07)
難しい数式とは何か?|エクセル雑感(2023-12-07)
スピらない スピル数式 スピらせる|エクセル雑感(2023-12-06)


アクセスランキング ・・・ ランキング一覧を見る

1.最終行の取得(End,Rows.Count)|VBA入門
2.RangeとCellsの使い方|VBA入門
3.セルのコピー&値の貼り付け(PasteSpecial)|VBA入門
4.繰り返し処理(For Next)|VBA入門
5.変数宣言のDimとデータ型|VBA入門
6.ブックを閉じる・保存(Close,Save,SaveAs)|VBA入門
7.並べ替え(Sort)|VBA入門
8.条件分岐(IF)|VBA入門
9.セルのクリア(Clear,ClearContents)|VBA入門
10.マクロとは?VBAとは?VBAでできること|VBA入門




このサイトがお役に立ちましたら「シェア」「Bookmark」をお願いいたします。


記述には細心の注意をしたつもりですが、
間違いやご指摘がありましたら、「お問い合わせ」からお知らせいただけると幸いです。
掲載のVBAコードは動作を保証するものではなく、あくまでVBA学習のサンプルとして掲載しています。
掲載のVBAコードは自己責任でご使用ください。万一データ破損等の損害が発生しても責任は負いません。



このサイトがお役に立ちましたら「シェア」「Bookmark」をお願いいたします。
本文下部へ