【初心者向け】動かして学ぶExcel VBA入門（10）

Excel VBA

こんにちは。ECF Tech担当
Michiharu.Tです。

Excel VBA入門の第10回です。
今回は動的な処理が実現可能なメソッドやプロパティをご紹介します。「動的な処理」とは、Excelシートの状態によって動作が変わる処理のことです。これらの操作を活用することで、より実践的なマクロプログラムを書くことができます。

本連載の目次は下記よりご覧頂けます。

【初心者向け】動かして学ぶExcel VBA入門
Excel VBAを動かしながら学べる連載をお送りしています。講座目次第１回 Excel VBAを始めようExcel VBAとはExcelマクロを動かしてみようExcelマクロの保存第２回 基本操作(1)セルに値を設定セルの削除オブジェクト...
tech.e3factory.com

表のサイズに応じた操作

これまでご紹介してきたマクロは、Excelシート上の表のサイズがあらかじめ決まっている（わかっている）ものに対する操作が基本でした。ですが実際には、表は常に編集され大きさも日々変わるものです。ここでは、表サイズの変化にも対応できる処理をご紹介します。

今回は下のようなExcel表を例にマクロを作成・動作させていきます。

サンプルシート

最終行を探す

.End(xlDown)

RangeオブジェクトのEndプロパティを使用することで、表の最終行を探すことができます。基本的な文法は下のとおりです。

Rangeオブジェクト.End(xlUp)
Rangeオブジェクト.End(xlDown)

実例を示しながら説明します。まずはプログラム例を示します。

Sub searchLastLine()
    Dim lastLine As Integer
    lastLine = Range("A1").End(xlDown).Row
    MsgBox "最終行は" & lastLine & "行目です"
End Sub

実行すると、下のような結果となります。

searchLastLineサンプル

Range("A1").End(xlDown)は、「A1から下にたどって最終行のセル」を表しています。これにより連続したデータの最終行にたどり着きます。その後ろについている.Rowは、その行番号を表しています。例では行番号を変数lastLineに代入し、最後にメッセージボックスで表示しています。

一見すると、このプログラムによって表の最終行を判断できそうですが、下のような一部のセルに値が入っていない表の場合は次のような結果になってしまいます。

searchLastLineエラー

上記の表は7行目が空欄になっています。この場合、連続したデータが6行目で止まってしまいますので、最終行が6行目と判定されてしまいます。

.End(xlUp)

というわけで、改善したプログラムを示します。

Sub searchLastLine2()
    Dim lastLine As Integer
    lastLine = Cells(Rows.Count, 1).End(xlUp).Row
    MsgBox "最終行は" & lastLine & "行目です"
End Sub

実行結果は次のようになり、表の最終行を正しく示していることがわかります。

searchLastLineエラー

ポイントは起点の違いです。Rows.Countはシートの最下行(1048576行目など)を示します。したがって、Cells(Rows.Count, 1)でA列の最下行を起点にしています。.End(xlUp)を使うとセルを上にたどります。起点が空のセルの場合、上にたどりながら最初に値があるセルを見つけます。これによって、表の最終行を見つけることができます。

Endプロパティのポイント

Endプロパティはどのセルを起点にするかで動作が変わります。

RangeやCellsを使って指定した起点が値のあるセル(下図①)の場合は、指定の方向に向かって、値が連続したセルをたどり、最後に値があるセル(下図②)を示します。一方、起点が値のないセル(下図②)の場合は、指定の方向に向かってセルをチェックしながら、最初に値があるセル(下図④)を示します。これらの動作は、Ctrlキーを押しながら方向キーを押すのと同様の動作です。実際にキーボードで動かしてみて、イメージすると良いでしょう。

Endプロパティについて

アクティブセルを起点にする

現在指定しているセル(アクティブセル)を起点にした操作ができるプロパティをご紹介します。

Offsetプロパティ

Offsetは指定したセルから「2つ右」、「1つ下」のような指定ができます。基本文法は次のようになります。

Rnageオブジェクト.Offset(行数, 列数)

プログラム例で説明します。まずは3行下を表すプログラムです。

Sub valueNext3Line()
    Dim val As Variant
    val = ActiveCell.Offset(3, 0).Value
    MsgBox "3つ下は「" & val & "」です"
End Sub

実行結果は次のようになります。

valueNext3Line結果

アクティブセルがA4(バナナ)になっています。.Offset(3, 0)で「3行下、0行右」のセルを表し、.Valueでそのセルの値を示します。よって、バナナの3行下にある「スイカ」が表示されています。

列指定をするプログラムもご紹介します。

Sub valueNext2Column()
    Dim val As Variant
    val = ActiveCell.Offset(0, 2).Value
    MsgBox "2つ右は「" & val & "」です"
End Sub

実行結果は次のようになります。

valueNext3Line結果

アクティブセルはA4です。.Offset(0, 2)は「0行下、2行右」をあらわしますので、バナナの2行右にある「40」が表示されています。

Offsetプロパティに負の数を設定することも可能です。Offset(-1,-2)のように設定すると「1行上、2列左」を表します。

Resizeプロパティ

Resizeプロパティを使うことで、アクティブセルを起点にした範囲指定をすることができます。基本文法は次のとおりです。

Rangeオブジェクト.Resize(行数, 列数)

Rangeオブジェクトが示すセルを左上とし、〇行〇列を範囲指定します。プログラム例を示します。

Sub rangeSetColor()
    ActiveCell.Resize(2, 2).Interior.Color = RGB(255, 255, 0)
End Sub

実行結果は次のようになります。

Resizeサンプル

アクティブセルはB5です。.Resize(2, 2)により、アクティブセルを左上とする2行2列のセルが範囲指定されます。.Interiorは色などの情報を管理するオブジェクトで、そのInteriorオブジェクトのColorプロパティを使って、背景色を設定できます。RGB(255, 255, 0)は色情報を指定するもので、黄色を表します。

(※色情報については、こちらをご参照ください。)

CurrentRegionプロパティ

CurrentRegionプロパティを使うと、アクティブセルがあるエリアを範囲選択することができます。基本文法は次のようになります。

Rangeオブジェクト.CurrentRegion

実際のプログラムで確認してみましょう。今回は下のようなシートを使います。

サンプルシート2

それでは、サンプルプログラムです。

Sub RegionSetColor()
    ActiveCell.CurrentRegion.Interior.Color = RGB(255, 255, 0)
End Sub

実行結果は次のようになります。

CurrentRegionsサンプル

上の実行結果は、D4をアクティブセルとして実行した結果です。ActiveCell.CurrentRegionはアクティブセルが属している領域を表します。その領域全体の背景色を黄色に塗りつぶす。という処理を行っています。

ここでいう領域とは、空白のセルで区切られた表とみなせる範囲。と考えればOKです。今回使用したシートは色付けすると、下のように4つの島のように見えますね。それぞれがRegion(=日本語で「領域」の意味)というわけです。

Regionイメージ

というわけで本日はここまでとさせて頂きたいと思います。最後までお読み頂きありがとうございました。これらのプロパティを活用した練習問題などもご提供する予定です。ひきつづき、よろしくお願いいたします。

タイトルとURLをコピーしました