さて、前項でシート内のセルやセル範囲をRangeオブジェクトに取得して操作することは説明しましたが、今度はシートやブックを越えてRangeオブジェクトを取得する方法を説明します。
前項の「RangeオブジェクトとRangeを返すプロパティ」でシート内のセルやセル範囲を「Rangeオブジェクト」に取得していろいろな形で操作・参照する方法は説明しました。
ですが、
Range("A1")
のように、いきなり「Rangeプロパティ」や「Cellsプロパティ」から書き始める方法では、シートやブックを越えた操作・参照はできません。
本章では、すでに「単一セルの転記操作」などでシートを越えた転記を説明してしまっており、何となく理解されているかも知れませんが、ここでは「Rangeオブジェクト」と同様にオブジェクト変数を用意して操作・参照することをやってみます。
では、まず、マクロを書き込んだ自分のブックのSheet3に前項の、
'***************************************************************************************************
'* 処理名 ：TEST11
'* 機能 ：セル範囲に値をセットするサンプル⑦
'---------------------------------------------------------------------------------------------------
'* 返り値 ：(なし)
'* 引数 ：(なし)
'---------------------------------------------------------------------------------------------------
'* 作成日 ：2004年02月11日
'* 作成者 ：井上 治
'* 更新日 ：2020年01月17日
'* 更新者 ：井上 治
'* 機能説明：
'* 注意事項：シートを明示していないのでアクティブなシートに対して作用します
'***************************************************************************************************
Sub TEST11()
'-----------------------------------------------------------------------------------------------
Dim objR As Range ' 対象セル範囲
Set objR = Range("A1").Offset(1, 1).Resize(5, 4)
objR.Value = "aaa"
End Sub
この記述で値をセットさせてみます。
記述にはワークブックオブジェクト、ワークシートオブジェクトの変数を追加し、「Rangeプロパティ」と同様に、「Workbooksプロパティ」、「Worksheetsプロパティ」からオブジェクトの参照を取得させます。
'***************************************************************************************************
'* 処理名 ：TEST12
'* 機能 ：セル範囲に値をセットするサンプル⑧
'---------------------------------------------------------------------------------------------------
'* 返り値 ：(なし)
'* 引数 ：(なし)
'---------------------------------------------------------------------------------------------------
'* 作成日 ：2004年02月11日
'* 作成者 ：井上 治
'* 更新日 ：2020年01月17日
'* 更新者 ：井上 治
'* 機能説明：
'* 注意事項：シートを明示していないのでアクティブなシートに対して作用します
'***************************************************************************************************
Sub TEST12()
'-----------------------------------------------------------------------------------------------
Dim objWbk As Workbook ' 対象ワークブック
Dim objSh As Worksheet ' 対象ワークシート
Dim objR As Range ' 対象セル範囲
Set objWbk = ThisWorkbook
Set objSh = objWbk.Worksheets("Sheet3")
Set objR = objSh.Range("A1").Offset(1, 1).Resize(5, 4)
objR.Value = "aaa"
End Sub
ここでは、ワークブックの取得には、「ThisWorkbookプロパティ」を使います。「ThisWorkbookプロパティ」であればブック名(ファイル名)がどう変わろうと動作させるマクロ(プロジェクト)が収容されている「自分」のワークブックを取得できます。
一方、マクロが収容されているブックではなく、その時点でアクティブなワークブックを取得したい場合は、「ActiveWorkbookプロパティ」を使います。
この方法で実際の値セットの記述は、
objR.Value = "aaa"
となり、前項のシートやブックを明示していない場合の記述と同じになりますが、オブジェクト変数「objR」に取得されたセル範囲にはシートやブックが明示された状態なので、動作させると、
その時点でアクティブだった「Sheet1」では何も変化はなく、
「Sheet3」に切り替えるとこのように正しく値がセットされていることが判ります。
これが1回だけの処理であれば、途中でブックやシートを一旦オブジェクト変数に取得せずに、
'***************************************************************************************************
'* 処理名 ：TEST13
'* 機能 ：セル範囲に値をセットするサンプル⑨
'---------------------------------------------------------------------------------------------------
'* 返り値 ：(なし)
'* 引数 ：(なし)
'---------------------------------------------------------------------------------------------------
'* 作成日 ：2004年02月11日
'* 作成者 ：井上 治
'* 更新日 ：2020年01月17日
'* 更新者 ：井上 治
'* 機能説明：
'* 注意事項：シートを明示していないのでアクティブなシートに対して作用します
'***************************************************************************************************
Sub TEST13()
'-----------------------------------------------------------------------------------------------
Dim objR As Range ' 対象セル範囲
Set objR = ThisWorkbook.Worksheets("Sheet3").Range("A1").Offset(1, 1).Resize(5, 4)
objR.Value = "aaa"
End Sub
このように1行に連記してしまっても同じ結果になります。
前に説明した「単一セルの転記操作」などの記述ではブックに関する記述が省略されているので、ブックに関する部分ではアクティブなワークブック(ActiveWorkbook)に対して作用します。
ブックを越えた操作の場合のブックの取得は若干難しいものがあります。
たとえば、このページのサンプル画像のブックがマクロを持つ「自分」のブックでない場合は、
Dim objWbk As Workbook ' 対象ワークブック
Set objWbk = Workbooks("Book1")
となりますが、このブックをどこかにそのまま保存させると、
タイトルバーを見てわかるようにブック名に保存時に指定したファイル形式の拡張子が追加されており、コードも、
Dim objWbk As Workbook ' 対象ワークブック
Set objWbk = Workbooks("Book1.xlsm")
としないとエラーになってしまいます。
また、これらの記述は、そのブックがExcel上に開かれていることが前提で、開かれていないと、
このようにエラーとなってしまいます。
であれば、最初から開かれていないことを前提として、フルパスでファイル名を指定してブックを開き、それと同時にブックをオブジェクト変数に取得するようにします。
Dim objWbk As Workbook ' 対象ワークブック
Set objWbk = Workbooks.Open("C:\TEMP\Book1.xlsm")
この場合は、逆に指定フォルダやファイルの実体がなかったり、すでに開かれていると実行時エラーになります。
ほかの人に配布する目的であれば、どこから見ても同じフォルダ名となるような共有フォルダか、それが不可能であれば、マクロを持つブックをそれらのファイルと同じフォルダに収容して起動させることを前提として、
Dim objWbk As Workbook ' 対象ワークブック
Set objWBK = Workbooks.Open(ThisWorkbook.Path & "\Book1.xlsm")
などと記述する方法を採ると良いと思います。