ExcelマクロVBA再入門 | 第17回.セルにブック・シートを指定する(Workbooks,Worksheets,With,Set) | マクロが覚えられないという初心者向けに理屈抜きのやさしい解説



最終更新日:2016-03-29

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


ExcelマクロVBAで自動化する時に、シートが1つという事はむしろ稀で、

多くの場合は複数のブック、複数のシートを扱う事になります、

そこで、どのブックのどのシートのセルかを指定する必要が出てきます。


RangeやCellsでセルを指定するだけでは、どのブックのどのシートか分からない・・・
RangeやCellsだけを書いた場合にはアクティブブックのアクティブシートのセルになります。

つまり、手前に見えているブックの選択されているシートのセルという事です。

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

まずは、この書き方から覚えます。

■ブックの書き方

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

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

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

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

ThisWorkbook
実行しているマクロ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を使う


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

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




↓Withを使うと、以下のように書けます。



↓Withはネスト(入れ子)出来ます。



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


■Setを使う

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

Dim 変数 As データ型
Set 変数 = ブックやシート

※データ型は、WorkbookやWorksheetになります。
  データ型(WorkbookやWorksheet)は省略可能です。
  Dim 変数
  だけでも動作に問題はありません。



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

つまり、
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列に入れる場合



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

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





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

第16回.エクセルの機能を上手に使う
第18回.シートをコピー・挿入・削除する(Worksheets,Copy,Add,Delete)
第19回.ブックを開く・閉じる・保存する(Workbooks,Open,Close,Save,SaveAs)
第20回.全てのシートに同じ事をする(For〜Worksheets.Count)
第21回.ファイル一覧を取得する(Do〜LoopとDir関数)
第22回.複数ブックよりデータを集める
第23回.複数のプロシージャーを連続で動かす(Callステートメント)

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

SUMIFの間違いによるパフォーマンスの低下について|エクセル関数超技(6月17日)
条件式のいろいろな書き方:TrueとFalseの判定とは|ExcelマクロVBA技術解説(6月15日)
空白セルを正しく判定する方法2|ExcelマクロVBA技術解説(5月6日)
フルパスをディレクトリ、ファイル名、拡張子に分ける|ExcelマクロVBA技術解説(4月15日)
テキストボックスの各種イベント|Excelユーザーフォーム入門(4月9日)
フォルダ(サブフォルダも全て)削除する、Optionでファイルのみ削除|ExcelマクロVBAサンプル集(4月4日)
最後の空白(や指定文字)以降の文字を取り出す|エクセル関数超技(3月26日)
先頭の数値、最後の数値を取り出す|エクセル関数超技(3月26日)
Excelファイルを開かずにシート名をチェック|ExcelマクロVBAサンプル集(3月23日)
数式の参照しているセルを取得する|ExcelマクロVBAサンプル集(3月18日)

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

1.最終行の取得(End,Rows.Count)|ExcelマクロVBA入門
2.RangeとCellsの使い方|ExcelマクロVBA入門
3.Range以外の指定方法(Cells,Rows,Columns)|ExcelマクロVBA入門
4.変数とデータ型(Dim)|ExcelマクロVBA入門
5.セルのコピー&値の貼り付け(PasteSpecial)|ExcelマクロVBA入門
6.定数と型宣言文字(Const)|ExcelマクロVBA入門
7.徹底解説(VLOOKUP,MATCH,INDEX,OFFSET)|エクセル関数超技
8.マクロって何?VBAって何?|ExcelマクロVBA入門
9.CSVの読み込み方法|ExcelマクロVBAサンプル集
10.ひらがな⇔カタカナの変換|エクセル基本操作



  • >
  • >
  • >
  • セルにブック・シートを指定する(Workbooks,Worksheets,With,Set)

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


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

    ↑ PAGE TOP