今回の記事では、Excel VBAを学び始めたばかりの人を対象に、Excel VBAにおいて、まず理解しておかないといけない「セルを参照するやり方」を解説していきます。
また、セルを参照する処理の説明に伴い、ワークシートを指定するやり方や、VBEの標準モジュールとその他のモジュールとの違いなど、併せて知っておいてほしいVBAの基礎知識も紹介していきます。
そもそもVBAはどこに書けばいいの？
当記事ではExcel VBAを新しく覚える人も対象にしているため、まずはVBAの書き始める手順から説明しておきます。
「それは知っているよ」という方は読み飛ばしてください。
また、VBAの処理をどこに書くかによって、セルを参照する場合の書き方も変わるため、それも併せて説明していきます。
マクロ有効ブック(xlsm形式)で保存
ExcelファイルにVBAを登録するには、通常のExcelブック形式のファイルではなく、マクロ有効ブック形式のファイルに保存し直す必要があります。
VBAを登録したいExcelファイルを作成、又は開き、画面上部のメニュー内の「ファイル」→「名前を付けて保存」を選択し、ファイル形式に「マクロ有効ブック」を選択して「保存」を押します。
今後はこのファイルに対して処理を記述します。
VBE(Visual Basic Editor)を起動してプロシージャを作成
まずはVBEを起動します。
VBEはExcelやWordの機能として利用できるVBAを書いたり管理するための画面です。
Excelの場合は、画面上部メニューの「開発」を選択し、「Visual Basic」をクリックします。
また、もし上部のメニューに「開発」が無ければ、上部メニュー内の「ファイル」→「オプション」→「リボンのユーザー設定」で「開発」を表示対象に含めてください。
VBEが起動すると、以下のような画面が表示されます。
今回は「標準モジュール」を新しく作成して、そのなかにVBAで処理を書きます。
よって、画面左側の「プロジェクト」枠内の「Microsoft Excel Object」を右クリックします。
すると、以下のように右クリックメニューが表示されます。
「挿入」→「標準モジュール」をクリックします。
すると、プロジェクト枠内に「標準モジュール」が作成され、その下の階層に「Module1」が作成されます。
その「Module1」をダブルクリックすると、画面左側にエディタ―ウィンドウが表示されます。
今回の記事ではそこにVBAのプログラムを書いていきます。
「Sheel1」と「ThisWorkBook」とは何か？
プロジェクト枠内に最初から存在していた項目に「Sheet1」と「ThisWorkBook」があります。
これは何でしょうか？
まず「Sheet1」ですが、これは「Sheetモジュール」です。
Excelファイル内に存在しているワークシートと対になってシートモジュールも存在します。
今回の記事で使用しているサンプルのExcelファイルでは、Excelファイルを新しく作成した直後の状態のファイルを使用しているため、その状態から存在しているシート名の「Sheet1」と紐付いているシートモジュールなので、同様にシートモジュール名も「Sheet1」になっています。
このSheetモジュールにもVBAの処理を作成できます。
一般的には、特定のシートでセルをクリックされたことを検出して処理を動かしたい場合や、特定のセルの値が変更されたことをトリガーとして特定の処理を動かしたいといったイベント系の処理を使用する場合に使用しますが、今回は使用しません。
また、「ThisWorkBook」という名前のモジュールもあります。
これは「Bookモジュール」です。
このExcelファイル自体に紐付いています。
このモジュールでも同様にVBAを書くことができます。
このモジュールは、例えばExcelファイル(自分自身)を開いた時の処理や、保存したとき、閉じるときなどのブックに対するイベントを取得して処理を動かしたい場合に使用します。
当記事ではこちらも使用しません。
Excel VBAで処理を作成する場合、基本的に「標準モジュールを作成して処理を記述する」という認識で結構です。
Excel VBAでセルを参照する記述例
当項では、Excel VBAでシート内のセルを参照して値を取得するプログラムの書き方を複数パターンで紹介し、個々のパターンの解説も併せて実施していきます。
RangeやCellsでセルを参照する
Excel VBAでセルを参照する場合は、RangeやCellsを使用するのが一般的です。
具体的にはWorkSheetオブジェクトのRangeプロパティやCellsプロパティがRangeオブジェクトを戻り値として返します。
そのRangeオブジェクトを参照することで、セルの値などの取得ができる仕組みです。
このCellsとRangeの大まかな使い方は以下です。
'Range([セルの位置"A1"などを入れる])
Range("A1")
'Cells([行番号],[列番号])
Cells(1,1)
Rangeのよくある使い方は、”A1″や”C5″などのExcelシート内の座標を文字列で指定する使い方です。
他にもセルの範囲を指定したり、色々な書式に対応していますが、当記事では割愛します。
Cellsは行番号と列番号をそれぞれ指定してシート内の位置を指定します。
この行番号や列番号は数値型の変数を使用することもできるため、ループ処理などでも使用します。
まずこの記事では、シート内のセルを参照するには、RangeかCellsを使うとだけ覚えておいてください。
シート名を指定せずセルを参照する書き方
当項では、シート名を指定せずにセルを参照する書き方を紹介します。
例えば以下の様な書き方です。
Sub Test01()
MsgBox Cells(1, 1)
MsgBox Range("A1")
End Sub
上記のプログラムを標準モジュール内に書いて、入力カーソルが上記プログラムのどこかにある状態で、VBE画面上部の「実行」→「Sub/ユーザーフォームの実行」を選択すると、プログラムが実行されます。
また、入力カーソルが上記プログラムのどこかにある状態で、キーボードの「F5キー」を押下しても実行できます。
そうすると、以下のようにSheet1のA1のセルの値がメッセージボックスで表示されます。
一見正常に動作するため、上記のような書き方で問題無いかのように思えますが、シート名の指定をせずにCellsやRangeを書いた場合、どのシートのセルを参照するかが動的に変わります。
具体的にはどのモジュールに処理を書いたかによって挙動が変わります。
その違いは以下です。
| モジュール種類 | シートを指定しない場合の挙動 |
|---|---|
| 標準モジュール | 実行時にアクティブなシートを参照する |
| Sheetモジュール | そのモジュールに紐付くシートを参照する |
| ThisWorkBookモジュール | 実行時にアクティブなシートを参照する |
「実行時にアクティブなシート」とは、VBAでシート名を指定せずにCellsやRangeを呼び出した時点で、Excel内で選択されているシートです。
Sheetモジュールに書く場合は、必ずそのシート内のセルを参照しますが、それ以外のモジュールでは、その直前の処理やシート選択状態によって、意図しないシートのセルを参照する恐れがあります。
よって、Excel VBAでセルの参照する場合は、必ずシート名も明示的に指定するようにしましょう。
シート名を指定してセルを参照する書き方
当項では、シート名を指定してセルを参照する書き方を紹介します。
まず、シートを指定する場合は、「Workbook.Worksheetsプロパティ」を使用します。
WorkbookオブジェクトのWorksheetsプロパティでシートの一覧を取得したり、シートを名前やインデックスで指定することで、特定のシートを指定することができます。
また、このプロパティはSheetsオブジェクトを返します。
まずは書き方の例を紹介します。
Worksheetsプロパティでシート名を指定する場合
Sub Test02()
'シート名を指定
MsgBox Worksheets("Sheet1").Range("A1")
MsgBox Worksheets("Sheet1").Cells(1, 1)
End Sub
上記の例では、シート名を指定して、そのシートの特定のセルを取得しています。
尚、上記の例では、シート名を指定する構文を以下のように記述しています。
Worksheets("Sheet1")
この記述はブックの指定を省略した記述です。
省略しない場合は、以下のような記述になります。
ThisWorkbook.Worksheets("Sheet1")
ブックの指定を省略した場合は、「Application.ThisWorkbookプロパティ」が使用されます。
この「Application.ThisWorkbookプロパティ」は、現在VBAを実行しているWorkbookオブジェクト(自分自身のブック)を返します。
ブックを指定することに対して疑問に思うかもしれませんが、例えば同時に複数のExcelファイルを開いている状態で、VBAを実行しているブックではなく、ことなるブックのシートを参照したい場合などは、特定のブック名を指定する必要があります。
ただ、通常は自分自身のブック内のシートに対して処理をすることが大半であり、シートを指定する度に「ThisWorkbook」まで指定することは面倒です。
よって、上記の書き方の例のように、
Worksheets("Sheet1").Range("A1")
ブックを指定せず、直接シート名を指定する書き方で結構です。
また、シート名ではなく、内部的に振られているシート番号(インデックス)を指定することもできます。
シート番号を指定する書き方の例は以下です。
Worksheetsプロパティでシート番号(インデックス)を指定する場合
Sub Test02()
'インデックスが1のシートを指定
MsgBox Worksheets(1).Range("A1")
MsgBox Worksheets(1).Cells(1, 1)
End Sub
インデックスは1から始まり、1はExcel画面下部のシート名のタブで一番左の位置のシートを指します。
その一つ右側のシートは2、その次のシートは3といった採番ルールです。
ブック内の全シートをインデックスで指定してセルを取得する例
前項ではシート名で指定してシート内のセルを参照する方法と、シートのインデックスを指定してシート内のセルを参照する書き方を紹介しました。
尚、インデックスを指定する書き方の場合は変数と組み合わせて使用するケースが多いです。
例えば、対象のブック内の全シートを対象にして、シートごとに処理を実装したい場合などは以下のような使い方もできます。
Sub Test03()
Dim i As Long
For i = 1 To Worksheets.Count
MsgBox Worksheets(i).Range("A1")
Next
End Sub
上記の書き方では、変数：i をインデックスとして使用してシートを指定しています。
対象のブック内のシート数は「Worksheets.Count」で取得できます。
繰り返し処理の構文である「For」の繰り返す回数を全シート数と定義したうえで、先頭のシートから順にA1の位置にあるセルを参照する処理になります。
ブック内の全シートをコレクションで指定してセルを取得する例
当項で使用している「Workbook.Worksheetsプロパティ」の中身はコレクションです。
コレクションとは、データや値、オブジェクトを複数格納できる特別な入れ物です。
コレクションについての詳しい説明は省略しますが、上のサンプルプログラムのように全シートに対して繰り返し処理を行う場合は、その「コレクション」の仕組みを利用した書き方もあります。
Sub Test04()
Dim item As Worksheet
For Each item In Worksheets
MsgBox item.Range("A1")
Next
End Sub
【推奨】シートオブジェクトを作成してセルを指定する書き方
前項までの書き方の例では、セルを参照するたびにシート名まで指定することになり、セルを参照する処理が多い場合は実装が面倒です。
また、参照先のセルがあるシート名を変える場合は、セルを参照しているVBAの記述をすべて1行ずつ書き換えることが必要になりますが、それにより修正漏れを発生させるなどの不具合の原因にもなります。
よって、シート内のセルを参照する場合、まず参照対象のセルがあるシートをオブジェクト変数として生成したうえで、処理を実装することをおススメします。
具体的な書き方は以下です。
Sub Test05()
Dim objWorkSheet As Worksheet
Set objWorkSheet = Worksheets("Sheet1")
MsgBox objWorkSheet.Range("A1")
Set objWorkSheet = Nothing
End Sub
上記の例では、最初に「objWorkSheet」と言う名前の「Worksheet型」のオブジェクト変数を宣言しています。
その変数に対して、「Worksheets(“Sheet1”)」で取得したWorksheetオブジェクトを代入しています。
また、変数への代入は、オブジェクト変数への代入のため「Set」を使用しています。
以降はそのオブジェクト変数を使用してセルへの参照処理を実装しています。
このように実装することで、もし参照先セルのシート名が変更になる場合なども、「Worksheets(“Sheet1”)」を書き換えるだけで済みます。
また、オブジェクト変数生成後の処理の記述も文字数が減りすっきりして読みやすくなります。
よって、VBAでセルを参照する場合、上記のように、オブジェクト変数を生成して処理を実装する書き方を強く推奨します。
セルを参照するその他の書き方例
前項では、Excel VBAでセルを参照する場合の基本となる書き方を解説しました。
当項では、それらを踏まえて、更に異なるケースでセルを参照する場合の書き方の例を紹介していきます。
特定の列にあるすべてのセルを参照する書き方
Excel VBAの実務でよくある実装では、特定の列を上から順に参照して、すべての値を取得するといったものがあります。
その場合、繰り返し処理を使いながらセルを参照していきます。
よくある実装例ですが、例えばシートのA1を始点として、同列の始点以降のセルをすべて参照する場合の実装例を以下で紹介します。
Sub Test06()
Dim objWorkSheet As Worksheet
Dim i As Long
Dim LastRow As Long
Set objWorkSheet = Worksheets("Sheet1")
LastRow = objWorkSheet.Range("A1").End(xlDown).Row
For i = 1 To LastRow
Debug.Print objWorkSheet.Cells(i, 1)
Next
Set objWorkSheet = Nothing
End Sub
この実装例では、以下のコードでデータが存在する最終行を取得しています。
Range("A1").End(xlDown).Row
特定のシート内のセルを選択した後に、Ctrlキーを押しながら下矢印キーを押すと、選択セルがその列で値のある最終行に移動するのですが、それをVBA上で実行したのが上記の記述です。
最終行が非常に簡単に取得できるのですが、この書き方で実装する場合は注意点があります。
もし1000行のデータが存在しているシートがあり、そのシートのセル：A100の値が空白だった場合、上記のコードでは99を返してきます。
本当に取りたいのは1000です。
よって、当実装例を利用して実装する場合は、必ずすべてのセルに値が存在していることを担保する必要があります。
もし空白のセルが存在することが明白であれば、先にシート自体の最終行を取得して、その最終行から、その上の空白ではないセルの位置を取得して最終行を判別する方法もあります。
シート自体の最終行(現在のExcelのバージョンでは1048576行目)に移動したうえで、Ctrlを押しながら上矢印キーを押す操作と同じです。
この場合は、最終行を取得する処理として以下のように書きます。
Cells(Rows.Count, 1).End(xlUp).Row
「Worksheet.Rowsプロパティ」ではそのワークシートのすべての行を表す「Rangeオブジェクト」を返します。
この「Rangeオブジェクト」では「Range.Countプロパティ」を持ち、ここでシート自体の総行数を取得できます。
セルを参照するまでの書き方としては以下です。
Sub Test07()
Dim objWorkSheet As Worksheet
Dim i As Long
Dim LastRow As Long
Set objWorkSheet = Worksheets("Sheet1")
LastRow = objWorkSheet.Cells(objWorkSheet.Rows.Count, 1).End(xlUp).Row
For i = 1 To LastRow
Debug.Print objWorkSheet.Cells(i, 1)
Next
Set objWorkSheet = Nothing
End Sub
このように最終行を取得すれば、取得範囲の行に空白があっても最終行を取得できますが、そもそもExcelはRDBではないため、入力者が自由に値を入れたり行を消すことができます。
そのため、Excelでデータの最終行を取得する行為自体が不確かなものだと認識しておいていただく必要があります。
特定の行にあるすべてのセルを参照する書き方
前項では特定の列を上から順に参照する処理を紹介しましたが、当項では特定の行を左から右に移動しながらセルを参照する処理を紹介します。
前項では最終行を取得して繰り返し処理を行いましたが、今回の処理では最終列を取得します。
この処理も最終行を取得する処理と同様に、Ctrlキー＋右矢印キーを押下する操作と同じ動きになります。
Range("A1").End(xlToRight).Column
セルA1を始点として、値のある最終列の位置を取得します。
セルを参照するまでの書き方としては以下です。
Sub Test08()
Dim objWorkSheet As Worksheet
Dim i As Long
Dim LastCol As Long
Set objWorkSheet = Worksheets("Sheet1")
LastCol = objWorkSheet.Range("A1").End(xlToRight).Column
For i = 1 To LastCol
Debug.Print objWorkSheet.Cells(1, i)
Next
Set objWorkSheet = Nothing
End Sub
最後に
今回の記事では、Excel VBAでシート内のセルを参照する方法を解説しました。
当記事のなかで大事なポイントは以下です。
- シート名を必ず明示的に指定すること
- シートはオブジェクト変数化して操作すること
- 複数のセルを参照する場合はループ処理を使うこと
これらを理解しておけば、Excel内のセルを参照する処理で困ることはないかと思います。
今回も読んでいただきましてありがとうございました。
それでは皆さまごきげんよう！
