VBAで別のシートを書き換えないために

LWP | VBAで別のシートを書き換えないために

VBAで別のシートを書き換えないために

図解1｜B2は、一つの場所だけを表していない

図1｜セル番地の前に、どのブックのどのシートかを決める。

解説1｜B2は、一つの場所だけを表していない

ストーリー

コイ丸先輩「このマクロはB2へ確認済と書くんだが、今日は別のシートが変わったんだ。」

シャケモン「B2だけだと場所を一つに決められません。まず、どのブックのどのシートに書く処理なのかを確認しましょう。」

全国規模の中華食材卸・淡水社を舞台にした架空の教材例である。対象は受注処理.xlsmの「受注」シートにあるB2。ここでは受注明細の処理全体ではなく、そのセルへ「確認済」と書く小さな処理で参照先を調べる。

入力例

ブック	シート	セル	今回の役割
受注処理.xlsm	受注	B2	書き込みたい場所
受注処理.xlsm	控え	B2	変更しない場所
確認.xlsx	一覧	B2	変更しない場所

Excelでは、ブックの中にシートがあり、その中にセルがある。別のシートにも同じB2という番地が存在するため、セルの住所は「ブック→シート→セル」の組で考える必要がある。

読むポイント

コードがどこへ書くかと、業務としてどこへ書きたいかを先に区別する。以降の試験は、この三つのB2へ異なる初期文字を入れた専用の教材ブックで行う。実際の受注データには実行しない。

図解2｜標準モジュールのRangeは、選択中のシートへ

図2｜同じコードでも、実行時にアクティブなシートが違えば対象が変わる。

解説2｜標準モジュールのRangeは、選択中のシートへ

ストーリー

コイ丸先輩「いつも受注シートを開いてから実行していた。控えを見たあとに押すと、そっちが変わるのか。」

シャケモン「標準モジュールに置いたこのRangeは、実行時のアクティブシートを使っています。」

比較するコード

次は原因を再現するためのコード。受注処理.xlsmの標準モジュールへ置く。Alt＋F11でVBEを開き、対象ブックの［挿入］→［標準モジュール］を使う。

01	`Option Explicit`
02
03	`Public Sub Test_Unqualified()`
04	`Range("B2").Value = "確認済"`
05	`End Sub`

結果の比較

実行直前にアクティブなシート	書き換わるセル
受注処理.xlsmの受注	受注!B2
受注処理.xlsmの控え	控え!B2
確認.xlsxの一覧	一覧!B2

範囲を修飾する親オブジェクトが書かれていないため、画面の選択状態が処理先の決定に使われる。受注シートを選ぶという運用が守られたときだけ、意図した場所へ書けていたことになる。

読むポイント

これは標準モジュールでの例である。ワークシートのコードモジュールでは無修飾Rangeの意味が異なるため、図5で分けて扱う。「Rangeはどこに書いてもActiveSheet」という覚え方はしない。

図解3｜コードのあるブックと、操作中のブックを分ける

図3｜ThisWorkbookとActiveWorkbookは、違う役割の名前である。

解説3｜コードのあるブックと、操作中のブックを分ける

ストーリー

シャケモン「確認.xlsxを操作中でも、コードを持っているブックは受注処理.xlsmのままです。」

コイ丸先輩「今回は、そのコードの入ったブックの受注シートへ書けばよいんだね。」

ThisWorkbookは、実行しているVBAコードを含むブックを表す。ActiveWorkbookは、その時点でアクティブなブックを表す。コードの所有元と画面上の操作対象は、別々に考える。

関係を確かめるコード

図2と同じ標準モジュールへ次の手続きを追加する。確認.xlsxの一覧を選んだ状態で実行し、VBEのイミディエイトウィンドウ（Ctrl＋G）を見る。

01	`Public Sub Test_BookNames()`
02	`Debug.Print ThisWorkbook.Name`
03	`Debug.Print ActiveWorkbook.Name`
04	`End Sub`

この場面の出力は、順に「受注処理.xlsm」「確認.xlsx」となる。ブックを選び直すとActiveWorkbookは変わるが、このコードのThisWorkbookは受注処理.xlsmを指す。

読むポイント

ThisWorkbookへ置き換えれば常に正しい、という規則ではない。PERSONAL.XLSBやアドインにコードを置く場合、コードのあるブックと処理したい業務ブックは通常別になる。外部ブックを開いて処理する設計なら、Workbooks.Openが返すWorkbookを変数へ保持するなど、対象の決め方を明示する。

図解4｜対象を変数へ保持し、親から子へたどる

図4｜後で画面の選択が変わっても、保持した対象を使って書き込む。

解説4｜対象を変数へ保持し、親から子へたどる

ストーリー

コイ丸先輩「受注シートへ必ず書く、という処理の意図をコードにも残したいな。」

シャケモン「ブック、シート、セルを順に変数へ入れれば、どこを使うのかを追えます。」

実行するコード

受注処理.xlsmの標準モジュールへ追加する。先頭のOption Explicitは図2ですでに書いていれば追加不要。


    Dim xOrderBook As Workbook
    Set xOrderBook = ThisWorkbook

    Dim xOrderSheet As Worksheet
    Set xOrderSheet = xOrderBook.Worksheets("受注")

    Dim xStatusCell As Range
    Set xStatusCell = xOrderSheet.Range("B2")

    xStatusCell.Value = "確認済"
End Sub

01	`Public Sub Test_Qualified()`
02	`Dim xOrderBook As Workbook`
03	`Set xOrderBook = ThisWorkbook`
04
05	`Dim xOrderSheet As Worksheet`
06	`Set xOrderSheet = xOrderBook.Worksheets("受注")`
07
08	`Dim xStatusCell As Range`
09	`Set xStatusCell = xOrderSheet.Range("B2")`
10
11	`xStatusCell.Value = "確認済"`
12	`End Sub`

結果の読み方

Setはオブジェクトへの参照を変数へ入れる文。ここではブックのコピーやシートの複製を作っているわけではない。xOrderBookから受注シートを取り出し、そのシートからB2を取り出す、という所属関係がコードに表れている。

確認.xlsxを操作中でも、書き込み先は受注処理.xlsmの受注!B2になる。対象を参照できる状態なら、書き込むためだけのSelectやActivateは不要である。

読むポイント

「受注」というシートが存在しなければエラーになる。処理先が見つからないときに、別のシートを代わりに選んで続ける設計にはしない。

図解5｜内側のCellsにも、同じ親を付ける

図5｜外側のRangeだけでなく、範囲の端点を作るCellsの所属も確認する。

解説5｜内側のCellsにも、同じ親を付ける

ストーリー

コイ丸先輩「B2からB4へ広げたいときも、Rangeの前にシートを書けばよいのかな。」

シャケモン「Cellsで端点を指定するなら、Cellsにも同じ親が必要です。Withの中では、先頭のドットがその指定になります。」

実行するコード

次は標準モジュールへ追加する別の試験。対象は受注!B2:B4へ広がるので、その三セルが変わってよい教材ブックで試す。

01	`Public Sub Test_Block()`
02	`With ThisWorkbook.Worksheets("受注")`
03	`.Range(.Cells(2, 2), .Cells(4, 2)).Value = "確認済"`
04	`End With`
05	`End Sub`

Cellsの引数は行番号、列番号の順。Cells(2, 2)がB2、Cells(4, 2)がB4であり、Rangeがその間を含む長方形の範囲を作る。三つのドットにより、Rangeと両端点がすべて同じ受注シートへ結び付く。

コードを置く場所による違い

置く場所	無修飾のRange("B2")が指すもの
標準モジュール	アクティブシートのB2
受注シートのコードモジュール	その受注シートのB2（Me.Range）

ワークシートモジュールのMeは、そのモジュールを持つシートである。そこでActiveSheet.Range("B2")と明示すれば、アクティブシートのB2を参照する。コードを移す場合には、この違いも確認する。

読むポイント

外側だけを受注シートで修飾し、内側のCellsが別シートを指すと、エラーなどの原因になる。内側も外側も同じ所属にそろえて読む。

図解6｜選択を変えて、変更されたセルを確かめる

図6｜一度成功しただけで終えず、元の問題が起きる条件で確かめる。

解説6｜選択を変えて、変更されたセルを確かめる

ストーリー

コイ丸先輩「控えを開いていても、別ブックを見ていても、受注のB2だけが変わったね。」

シャケモン「選択状態に頼っていた部分をなくせたことが確認できます。変更しないセルも一緒に見ておきましょう。」

入力例と試し方

各試験の前に、受注!B2を「受注_未処理」、控え!B2を「控え_保持」、確認.xlsxの一覧!B2を「一覧_保持」へ戻す。受注処理.xlsmのTest_Qualifiedを、次の三条件で実行する。図5のTest_Blockとは分けて試す。

結果の確認

実行直前の選択	受注!B2	控え!B2	一覧!B2
受注シート	確認済	控え_保持	一覧_保持
控えシート	確認済	控え_保持	一覧_保持
確認.xlsxの一覧	確認済	控え_保持	一覧_保持

確認したいのは、エラーが出ないことだけではない。意図した対象が変わり、対象外が変わらないことを、初期値と比較する。変更先を調べる際は、ブック名、シート名、セル番地を組み合わせて記録する。

読むポイント

既存マクロを直すときも、まず処理先を選ぶ規則を読み取り、ブック→シート→セルの参照を追う。ThisWorkbookへの機械的な置換や、実行前に特定のシートを選ばせるだけの対応で済ませず、業務上の対象とコードの参照先を一致させる。

出典

Microsoft Learn：ThisWorkbook ／ Application.Range ／ Worksheet.Range。2026-11-03確認。ブック・会社・業務場面は教材用の架空例。