VBA再入門
セルにブック・シートを指定する(Workbooks,Worksheets,With,Set)

マクロが覚えられないという初心者向けに理屈抜きのやさしい解説
最終更新日:2020-09-26

第17回.セルにブック・シートを指定する(Workbooks,Worksheets,With,Set)


ExcelマクロVBAで自動化する時に、シートが1つという事はむしろ稀でしょう。
多くの場合は複数のブック、複数のシートを扱う事になります。
そうなると、
どのブックのどのシートなのか、
どのシートのどのセルなのか、
これらを指定する必要が出てきます。


RangeやCellsでセルを指定するだけでは、どのブックのどのシートか分かりません・・・
RangeやCellsだけを書いた場合にはアクティブブックのアクティブシートのセルになります。
つまり、手前に見えているブックの選択されているシートのセルという事です。

アクティブブックのアクティブシート以外のセルを指定するには、
・ブックの指定
・シートの指定

これらを明確に指定します。
これらの書き方を順に解説します。

ブックの書き方

複数のブックを開いている時に、特定のブックを指定するには、

Workbooks("ブック名")
ブック名は拡張子も含めて指定します。
空白文字・全角半角文字等も含めて正確に指定します。
大文字小文字は区別されませんが、実際の文字に合わせて指定するようにします。
Workbooks("Book1.xlsm")
このように書きます。

Workbooks(インデックス)
インデックスは1から始まる数値です。
ブックを開いた順番になります。
Workbooks(2)
このように書きます。
主に全ブックに対する処理を書くときに使います、それ以外ではあまり使いません。

ActiveWorkbook
選択されているブック(これをアクティブブックと言います)です。
見た目で、一番手前のブックになります。
特にブック指定しなければActiveWorkbookになります。

ThisWorkbook
実行しているマクロVBAが書かれているブックです。
つまり、マクロを書いているブックです。

シートの書き方

ブックには複数のシートがあります。
その複数のシートから特定のシートを指定するVBAの書き方です。

Worksheets("シート名")
シート名は空白文字・全角半角文字等も含めて正確に指定します。
大文字小文字は区別されませんが、実際の文字に合わせて指定するようにします。
Worksheets("Sheet1")
このように書きます。

Worksheets(インデックス)
インデックスは1から始まる数値です。
ブックの左からの順番で、一番左のシートが1、次が2となります。
Worksheets(2)
このように書きます。
主に全シートに対する処理を書くときに使います、それ以外ではあまり使いません。

ActiveSheet
選択されているシート(アクティブシートと言います)です。
特にシート指定しなければActiveSheetになります。

セルにブック・シートを指定する方法

セルにブック・シートを指定する時の書き方は何通りかあります。

・直接指定
・Withを使う
・Setを使う


以下で順に説明していきます。

直接指定

Workbooks("Book1.xlsm").Worksheets("Sheet1").Range("A1") = "値"
Book1のSheet1のA1セルに"値"を入れる
ThisWorkbook.Worksheets("Sheet1").Range("A1") = "値"
マクロの書かれているブックのSheet1のA1セルに"値"を入れる
ActiveWorkbook.Worksheets("Sheet1").Range("A1") = "値"
Worksheets("Sheet1").Range("A1") = "値"
選択されているブックのSheet1のA1セルに"値"を入れる
上記どちらでも同じになります。
ActiveWorkbook.ActiveSheet.Range("A1") = "値"
ActiveSheet.Range("A1") = "値"
Range("A1") = "値"
選択されているブックの選択されているシートのA1セルに"値"を入れる
上記いずれでも同じになります。

Withを使う

全てのセル(RangeやCells)にブック・シートを指定するのは面倒です。
Withを使う事で一括指定できるようになります。

With ブックやシート
  'ここに書くセルは、.(ドット)から書き始める事で、
  'その前にWithの後に書いたブックやシートを省略することができます。
End With


Withを使わずに書くと
Excel マクロ VBA サンプルコード
↓Withを使うと、以下のように書けます
Excel マクロ VBA サンプルコード
↓Withはネスト(入れ子)出来ます
Excel マクロ VBA サンプルコード

それぞれ、実際に書いてみましょう。
Withの行を書いたら、Enter2回で先にEnd Withを書き、Tabでインデントを付けてから内部を書くようにして下さい。

Setを使う

Withで指定できるのは、1つのブック、1つのシートだけです。
マクロVBAでは複数のブック、複数のシートを扱う事が多いので、
Withで指定するブック・シート以外の指定が面倒になります。
Setを使う事で短い名称で簡単に指定できるようになります。

Dim 変数 As データ型
Set 変数 = ブックやシート
データ型は、WorkbookやWorksheetになります。
データ型(WorkbookやWorksheet)は省略可能です。
Dim 変数
これだけでも動作に問題はありません。
データ型はVariantになり、何でも入れられます。

Excel マクロ VBA サンプルコード

Workbooks("Book1.xlsm")に短縮名称(別名)のwbを付けたと考えて下さい。
Workbooks("Book1.xlsm").Worksheets("Sheet1")に短縮名称(別名)のwsを付けたと考えて下さい。

つまり、
Setで変数に入れることで、そのブックやシートを変数で指定できるという事です。
Setで変数に入れた後は、
Workbooks("Book1.xlsm")をwbに、
Workbooks("Book1.xlsm").Worksheets("Sheet1")をwsに書き換えられるようになるという事です。

SetとWithを組み合わせて使う

マクロVBAを書くときに、何をSetで何をWithで、と悩んでも仕方ありません。
悩まないように、以下のように使いましょう。

・全てのブック、全てのシートをSetで変数に設定
・一番よく使うシートをWithで指定


Book1のSheet1のA1セルの値を、Book2のSheet1のA列の最終行までB列に入れる場合

Excel マクロ VBA サンプルコード

練習と思って上の画像を見ながら書いてみて下さい。

この場合は、WorkbookにSetを使う必要はあまり感じませんが、
どうしようかと悩むくらいなら全部Setで変数に入れてしまいましょう。
もちろん、ブックが1つならブックの指定を省略して構いません。

ExcelマクロVBA入門の対応ページ

第27回.ブック・シートの選択(Select,Activate)
・アクティブ ・ブックをアクティブにする ・シートの選択、シートのアクティブ ・複数シートの選択(作業グループ化) ・複数ブックを開いている時のシート選択 ・VBAでの色々なシート指定方法

第37回.ブック・シートの指定
・マクロVBAでのブック・シート指定の具体例 ・マクロVBAでのブック・シート指定の必要性 ・VBAでの色々なシート指定方法

第51回.Withステートメント
・Withの構文 ・Withを使った時と使わない時の比較 ・Withの使用例 ・Withのネスト ・Withを使ったときに気を付けるべき書き方 ・Withの使いどころ ・サイト内の参考ページ

第52回.オブジェクト変数とSetステートメント
・オブジェクト変数 ・個有のオブジェクト型とは ・Setステートメント ・Setステートメントの使用例 ・WithとSetの使い分け方 ・Setステートメントの実践的な使い方 ・Is演算子によるオブジェクトの比較 ・最後に



同じテーマ「マクロVBA再入門」の記事

第14回.セルの書式を設定する(NumberFormatLocal,Font,Borders,Interior)
第15回.手作業で出来なければマクロは書けない
第16回.エクセルの機能を上手に使う
第17回.セルにブック・シートを指定する(Workbooks,Worksheets,With,Set)
第18回.シートをコピー・挿入・削除する(Worksheets,Copy,Add,Delete)
第19回.ブックを開く・閉じる・保存する(Workbooks,Open,Close,Save,SaveAs)
第20回.全てのシートに同じ事をする(For~Worksheets.Count)
第21回.ファイル一覧を取得する(Do~LoopとDir関数)
第22回.複数ブックよりデータを集める
第23回.複数のプロシージャーを連続で動かす(Callステートメント)
第24回.マクロの呪文を追加してボタンに登録(ScreenUpdating)


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

構成比を合計しても100%にならないと言われた…|ツイッター出題回答 (2022-09-01)
一覧から複数条件(部分一致、範囲)に合致するデータを抽出する|ツイッター出題回答 (2022-08-30)
縦横スピルしないXLOOKUP代替(MATCH+INDEX,FILTER,CHOOSEROWS)|エクセル入門(2022-08-27)
IF関数の論理式で比較演算子を省略したCOUNT系関数を書くのは|ツイッター出題回答 (2022-08-23)
LAMBDA以降の新関数の使用例|エクセル入門(2022-08-22)
数珠順列(配置に条件付き)を全て出力する|ツイッター出題回答 (2022-08-20)
日付時刻のマイナス表示に対応する方法|ツイッター出題回答 (2022-08-17)
LAMBDA以降の新関数について|エクセル入門(2022-08-16)
条件付きの最大値と中央値("A"が2文字の条件)|ツイッター出題回答 (2022-08-14)
VBAマクロと操作対象データの分離について|ツイッター出題回答 (2022-08-11)


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

1.最終行の取得(End,Rows.Count)|VBA入門
2.RangeとCellsの使い方|VBA入門
3.変数宣言のDimとデータ型|VBA入門
4.繰り返し処理(For Next)|VBA入門
5.セルのコピー&値の貼り付け(PasteSpecial)|VBA入門
6.Excelショートカットキー一覧|Excelリファレンス
7.並べ替え(Sort)|VBA入門
8.マクロって何?VBAって何?|VBA入門
9.Range以外の指定方法(Cells,Rows,Columns)|VBA入門
10.エクセルVBAでのシート指定方法|VBA技術解説




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


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



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