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

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

第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 変数
これ だけでも動作に問題はありません。

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でも複数ブック・複数シートを扱う必要があります。ここまでのVBAでは、あくまでアクティブブックのアクティブシートを対象としてきました。

第37回.ブック・シートの指定
ここまでのVBA入門では、アクティブブックのアクティブシートだけを扱ってきました。アクティブブックのアクティブシートとは、一番手前に表示されているブックの選択しているシートで、通常、手作業で操作しているシートの事になります。手作業では、アクティブブックのアクティブシートしか扱えませんが、(作業グループで複数のシートに同時に操作するのは別の話として) アク…

第51回.Withステートメント
Withステートメントを使う事で、Withに指定したオブジェクトに対してオブジェクト名を再度記述することなく、プロパティやメソッドを記述することができます。文章で例えて言えば、主語を一度書いたら、その後は主語を省略するような書き方になります。

第52回.オブジェクト変数とSetステートメント
変数のデータ型の説明において、Object…オブジェクト型 というのがあった事を覚えているでしょうか。数値や文字ではなく、オブジェクトを入れる変数がオブジェクト変数です。オブジェクトと言っても、いろいろなものがあります。



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

第14回.セルの書式を設定する(NumberFormatLocal,Font,Barders,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 ・・・新着記事一覧を見る

VBAで写真の撮影日時や音楽動画の長さを取得する|VBA技術解説(1月10日)
ツイッターで出されたVBAのお題をやってみた|エクセル雑感(1月13日)
イベントプロシージャーの共通化(Enter,Exit)|ユーザーフォーム入門(1月13日)
Rangeオブジェクトの論理演算(差集合と排他的論理和)|VBA技術解説(1月10日)
イベントプロシージャーの共通化|ユーザーフォーム入門(1月7日)
コントロールの動的作成|ユーザーフォーム入門(1月6日)
Evaluateメソッド(文字列の数式を実行します)|VBA技術解説(1月5日)
エクスポート(PDF/XPS)|VBA入門(1月2日)
分析関数(OVER句,WINDOW句)|SQL入門(12月25日)
取得行数を限定するLIMIT句|SQL入門(12月21日)


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

1.最終行の取得(End,Rows.Count)|VBA入門
2.RangeとCellsの使い方|VBA入門
3.セルのコピー&値の貼り付け(PasteSpecial)|VBA入門
4.マクロって何?VBAって何?|VBA入門
5.Range以外の指定方法(Cells,Rows,Columns)|VBA入門
6.変数宣言のDimとデータ型|VBA入門
7.繰り返し処理(For Next)|VBA入門
8.セルに文字を入れるとは(Range,Value)|VBA入門
9.空白セルを正しく判定する方法(IsEmpty,IsError,HasFormula)|VBA技術解説
10.徹底解説(VLOOKUP,MATCH,INDEX,OFFSET)|エクセル関数超技




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


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



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