エクセレントな Office の冒険、第 2 部

  • [アーティクル]

David Shank
Microsoft Corporation

February 1, 2001

先月のコラムでは、Excel オブジェクト モデルを使って作業するために、理解しておく必要のある基礎についてお話しました。今月は Range オブジェクトとそのプロパティやメソッドの一部を使った作業の基礎についてお話するつもりです。

Range オブジェクトとは

Excel の Range オブジェクトは、Excel オブジェクト モデルの中で最も優れた動的なオブジェクトで、最も頻繁に使用されます。Range オブジェクトを完全に理解し、Visual Basic for Applications (VBA) プロシージャで効果的に使用する方法を理解すると、プログラムで Excel の能力を応用することが容易になるでしょう。

Excel Range オブジェクトはオブジェクト間で一意なオブジェクトです。 多くの場合、オブジェクトは Excel ユーザー インターフェイスで明確に識別できる対象を持っています。たとえば、Workbook オブジェクトは、.xls ファイルとして認識されます。Worksheet オブジェクトのコレクションは、ユーザー インターフェイス内でタブ付きの独立したシート群として表されます。しかし、Range オブジェクトは違います。範囲が表すものは環境によって異なります。Range オブジェクトは 1 つのセルを表すことも、セルのコレクションを表すこともできます。このオブジェクトは、1 つのオブジェクトになることも、オブジェクトのコレクションになることもあります。1 列、1 行になることも、複数のワークシートにまたがるセルの 3 次元のコレクションになることもあります。さらに、ほかのオブジェクトがオブジェクトとして、そしてオブジェクトのコレクションのメンバとして存在するのと異なり、ブックやワークシート内のすべての Range オブジェクトを含む Ranges コレクションはありません。おそらく、何か作業に使いたいと思うものがあったら、それが Ranges オブジェクトになりうると考えるのが一番簡単です。

Range オブジェクトは Excel 内で基盤となる存在なので、Range オブジェクトを返す多くのプロパティやメソッドが見つかるでしょう。ユーザーは返されたオブジェクトを使用して、カスタム ソリューションでデータを使った作業を行えます。次のセクションでは、Range オブジェクトの基本的な側面と、組み込みのプロパティやメソッドから Range オブジェクトを返す多くの手段を説明します。

Range プロパティを使った作業

多くの異なる環境で、Range オブジェクトを返すために Range プロパティを使用することになるでしょう。Application オブジェクト、Worksheet オブジェクト、および Range オブジェクトのすべてが Range プロパティを持っています。Application オブジェクトの Range プロパティは Worksheet オブジェクトと同じ Range オブジェクトを返します。言い換えれば、Application オブジェクトの Range プロパティはアクティブ ワークシート上の指定したセルまたはセル群への参照を返します。Range オブジェクトの Range プロパティは、Application オブジェクトや Worksheet オブジェクトの Range プロパティとは微妙な違いがあり、この違いを理解しておくことは重要です。次の例を考えてみましょう。


Dim rng1 As Range
Dim rng2 As Range
Dim rng3 As Range

Set rng1 = Application.Range("B5")
Set rng2 = Worksheets("Sheet1").Range("B5")
Set rng3 = rng2.Range("B5")

これら 3 つの Range オブジェクトは、すべて同じセルへの参照を返すわけではありません。この例では、rng1 と rng2 は共にセル B5 への参照を返します。しかし、rng3 はセル C9 への参照を返します。Range オブジェクトの Range プロパティは、指定したセルへの "相対的な" 参照を返すので、この違いが起こります。この場合、指定されたセルは B5 です。従って、B は参照が B5 の右隣の 1 列を指すこと意味します。5 は参照が B5 で指定された行の 5 行下の行を指すことを意味します。この場合、現在行 (5 行目) を含めて 5 行下の行を指します。つまり、Range オブジェクトの Range プロパティは、指定したセルから右方向に n 列、下方向に y 行移動した位置のセルへの参照を返します。

一般的には、まず Range オブジェクトを返すために、Range プロパティを使用します。その後、その Range オブジェクトのプロパティやメソッドを使用して、セルやセルのグループ内のデータを使った作業を行います。Range プロパティを使って作業する方法を示す例をいくつか次の表に示します。

説明 Range プロパティの例
Sheet1 のセル A1 の値に 100 を設定します Worksheets("Sheet1").Range("A1").Value = 100
アクティブ ワークシートのセル グループに値を設定します Range("B2:B14").Value = 10000
アクティブ ワークシートのセル B15 の数式を設定します Range("B15").Formula = "=Sum(B2:B14)"
フォントを太字に設定します Range("B15").Font.Bold = True
フォントの色を緑に設定します Range("B15").Font.Color = RGB(0, 255, 0)
1 つのセルを参照するオブジェクト変数を設定します Set rngCurrent = Range("A1")
セル グループを参照するオブジェクト変数を設定します Set rngCurrent = Range("A1:L1")
名前付きセル範囲内のすべてのセルに書式を設定します Range("YTDSalesTotals").Font.Bold = True
オブジェクト変数を名前付きセル範囲に設定します Set rngCurrent = Range("NovemberReturns")
Employees ワークシートで使用済みのすべてのセル (後述) を表すオブジェクト変数を設定します Set rngCurrent = Worksheets("Employees").UsedRange
アクティブ セルを囲む関連セルのグループを表すオブジェクト変数を設定します Set rngCurrent = ActiveCell.CurrentRegion
アクティブ ワークシートの先頭の 3 列を表すオブジェクト変数を設定します Set rngCurrent = Range("A:C")
アクティブ ワークシートの 3、5、7、 9 行目を表すオブジェクト変数を設定します Set rngCurrent = Range("3:3, 5:5, 7:7, 9:9")
アクティブ シートの連続していない複数のセル グループを表すオブジェクト変数を設定します Set rngCurrent = Range("A1:C4, D6:G12, I2:L7")
指定したセル グループ (B5:B10) 内のすべてのセルの内容を、書式設定を残したまま削除します Range("B5", "B10").ClearContents

例からも分かるように、Range プロパティの Cell 引数には、A1 形式の文字列参照または現在のブック内の名前付きセル範囲を表す文字列を指定できます。

また、Excel オブジェクト モデルのほかのメソッドの引数に Range オブジェクトを返すために、Range プロパティを使うこともできます。この方法で Range プロパティを使用する場合は、Range プロパティを適用する Worksheet オブジェクトを完全修飾名で指定していることを確認してください。Excel メソッドの引数で Range プロパティへの参照を完全修飾名で行わないことは、範囲に関連したコードがエラーになる最も一般的な原因の 1 つです。

アクティブ セルまたは現在の選択範囲を使った作業

ActiveCell プロパティは、現在のアクティブ セルを表す Range オブジェクトを返します。セルが 1 つだけ選択されているときは、ActiveCell プロパティはその 1 つのセルだけを表す Range オブジェクトを返します。複数のセルが選択されているときは、ActiveCell プロパティは現在の選択範囲内でアクティブなセルを 1 つ表します。セルまたはセル グループが選択されているときは、Selection プロパティが現在の選択範囲内のすべてのセルを表す Range オブジェクトを返します。

ActiveCell プロパティと Selection プロパティの関係を理解するために、ユーザーがセル A1 をクリックし、その後セル F1 までドラッグすることにより、セル A1 からセル F1 まで選択する場合を考えます。この場合、ActiveCell プロパティはセル A1 を表す Range オブジェクトを返します。Selection プロパティはセル A1 から F1 までを表す Range オブジェクトを返します。

Excel のユーザー インターフェイスを使って作業する場合、一般的にまずセルまたはセル グループを選択します。その後、1 つのセルに値を入力したり、セル グループを書式化するような、選択したセルまたはセル群での操作を実行します。VBA でセルを使った作業を行う場合は、セルまたはセル グループで操作を実行する前に、選択を行う必要はありません。代わりに、作業するセルまたはセル群を表す Range オブジェクトを返す必要があるだけです。たとえば、ユーザー インターフェイスを使ってセル A1 に値 "January" を入力するには、まずセル A1 を選択し、その後 Januaryとキー入力します。次の例はこれと同じ操作を VBA で行っています。


ActiveSheet.Range("A1").Value = "January"

この方法で Range オブジェクトを使って作業するために VBA を使用すると、現在ワークシート上で選択されているセルの選択が変更されることはありません。ただし、VBA コードでもユーザーがユーザー インターフェイスを使って作業するのと同じ方法でセルを操作できます。それには、まずセルまたはセル範囲を選択するために、Range オブジェクトの Select メソッドを使用します。次に、Range オブジェクトの Activate メソッドを使って、現在の選択範囲内の 1 つのセルをアクティブにします。たとえば、次のコードはセル A1 から A6 までを選択し、セル A3 をアクティブ セルにしています。


With ActiveSheet
    .Range("A1:A6").Select
    .Range("A3").Activate
End With

Select メソッドを使って複数のセルを選択すると、参照されているセル群の先頭のセルがアクティブ セルになります。たとえば上記の例では、Select メソッドが実行された後はセル A1 から A6 までが選択されたことになりますが、ActiveCell プロパティはセル A1 への参照を返します。コードの次の行で Activate メソッドが実行された後は、セル A1 から A6 までは選択されたままですが、ActiveCell プロパティはセル A3 への参照を返します。次の例で、ActiveCell プロパティまたは Selection プロパティを使って Range オブジェクトを返す方法を示しています。


Dim rngActiveRange As Excel.Range

' Selection プロパティから返される Range オブジェクト。
Set rngActiveRange = Selection
Call PrintRangeInfo(rngActiveRange)
' ActiveCell プロパティから返される Range オブジェクト。
Set rngActiveRange = ActiveCell
Call PrintRangeInfo(rngActiveRange)

上記の例で呼び出されている PrintRangeInfo カスタム プロシージャは、このプロシージャへの引数で渡された Range オブジェクトに含まれるセルまたはセル群に関する情報を出力します。


Sub PrintRangeInfo(rngCurrent As Excel.Range)
    Dim rngTemp         As Excel.Range
    Dim strValue        As String
    Dim strRangeName    As String
    Dim strAddress      As String
    Dim strFormula      As String
    
    On Error Resume Next
    
    strRangeName = rngCurrent.Name.Name _
        & " (" & rngCurrent.Name.RefersTo & ")"
    strAddress = rngCurrent.Address
    
    For Each rngTemp In rngCurrent.Cells
        If IsEmpty(rngTemp) Then
            strValue = strValue & "Cell(" & rngTemp.Address _
                & ") Is empty." & vbCrLf
        Else
            strValue = strValue & "Cell(" & rngTemp.Address _
                & ") = " & rngTemp.Value _
                & " Formula " & rngTemp.Formula & vbCrLf
        End If
    Next rngTemp
    Debug.Print IIf(Len(strRangeName) > 0, "Range Name = " _
        & strRangeName, "Range not named") & vbCrLf & "Address = " _
        & strAddress & vbCrLf & strValue
    Debug.Print "**********************************"
    Debug.Print
    If Err > 0 Then Err = 0
End Sub
セルおよびセル グループを使った作業

セル範囲に対して作業するためにコードを記述する必要があることがよくあります。ところが、コードを記述している時点では、その範囲に関する情報がない場合があります。たとえば、範囲のサイズや位置、または別のセルに関連しているセルの位置を知らないこともあるかもしれません。サイズが分からないセル範囲を使って作業するために、CurrentRegion プロパティと UsedRange プロパティを使用できます。セル位置がわからないほかのセルに関連しているセルを使って作業するために、Offset プロパティを使用できます。

Range オブジェクトの CurrentRegion プロパティは、空白行と空白列の組み合わせ、またはワークシートの境界で区切られる範囲を表す Range オブジェクト (ただし空白行と空白列は含みません) を返します。たとえば、セル D3 から E12 までの内部にデータのテーブルを持ち、D3 がフォーカスを持つ場合、CurrentRegion プロパティはセル D3 から E12 までを表す Range オブジェクトを返します。

CurrentRegion プロパティは、1 つのワークシート上の多数の範囲を返すことができます。このプロパティは、関連するセル グループのサイズを知る必要がある場合の操作に役に立ちますが、確実に知ることができるのはグループ内のセルまたはセル群の位置だけです。たとえば、アクティブ セルがセルのテーブルの内部にある場合、次のコード行を使用して、テーブル全体の書式を適用できます。


ActiveCell.CurrentRegion.AutoFormat xlRangeAutoFormatAccounting4

また、セルのコレクションを返すために CurrentRegion プロパティを使用することもできます。たとえば、次のように使用します。


Dim rngCurrentCell As Excel.Range

For Each rngCurrentCell In ActiveCell.CurrentRegion.Cells
    ' 個別のセルを使った作業をここで行います。
Next rngCurrentCell

各 Worksheet オブジェクトは、ワークシートの使用されている領域を表す Range オブジェクトを返す UsedRange プロパティを持っています。UsedRange プロパティはワークシート上でデータを持っているセルの左上隅と右下隅で表される領域を示します。この場合、その 2 つのセルで表す領域内に含まれるすべてのセルがデータを持っているとは限りません。たとえば、2 つのセル A1 と G55 だけに入力されているワークシートがあるとすると、そのワークシートの UsedRange プロパティは A1 から G55 までのすべてのセルを含む Range オブジェクトを返します。

ワークシート上で指定した種類のすべてのセルを表す Range オブジェクトを返すには UsedRange プロパティと SpecialCells メソッドを組み合わせて使用します。たとえば、次のコードは数式を持つアクティブ シートのすべてのセルを含む Range オブジェクトを返します。


Dim rngFormulas As Excel.Range
Set rngFormulas = ActiveSheet.UsedRange.SpecialCells(xlCellTypeFormulas)

Cells プロパティを使用して、ワークシート上のあるセル範囲をループするか、行と列を数値で表現して範囲を参照することができます。Cells プロパティは、ワークシート内のすべてのセルまたは指定したセルを表す Range オブジェクトを返します。1 つのセルを使って作業する場合は、Cells プロパティが返す Range オブジェクトの Item プロパティを使い、作業する特定のセルのインデックスを指定します。Item プロパティは、1 つのセルの行インデックスまたは行と列のインデックスを指定する引数を受け取ります。

Item プロパティは Range オブジェクトの既定のプロパティなので、明示的に参照する必要はありません。たとえば、次の Set ステートメントは共に Sheet1 のセル B5 への参照を返します。


Dim rng1 As Excel.Range
Dim rng2 As Excel.Range

Set rng1 = Worksheet("Sheet1").Cells.Item(5, 2)
Set rng2 = Worksheet("Sheet1").Cells(5, 2)

Item プロパティの行インデックスと列インデックス引数は、指定した範囲内の先頭のセルから始めて相対的に決定した個別のセルへの参照を返します。たとえば、次のメッセージ ボックスは G11 が指定した Range オブジェクトの先頭セルなので、G11 を表示します。


MsgBox Range("G11:M30").Cells(1,1).Address

次のプロシージャは、Cells プロパティを使って、指定した範囲内のすべてのセルをループする方法を示します。OutOfBounds プロシージャは指定した範囲の値よりも大きいか小さい値を検索し、その条件で見つかった値を持つ各セルのフォントの色を変更します。


Function OutOfBounds(rngToCheck As Excel.Range, _
         lngLowValue As Long, _
         lngHighValue As Long, _
         Optional lngHighlightColor As Long = 255) As Boolean
    ' このプロシージャは、範囲内のセルのコレクション全体を繰り返し
    ' 処理するために Cells プロパティを使用する方法を示します。
    ' rngTocheck 範囲内のセルごとに、セルの値が数値で、その値
    ' が lngLowValue と lngHighValue で指定する値の範囲外の場
    ' 合に、セル フォントの色を lngHighlightColor の値 (既定は赤) 
    ' に変更します
    Dim rngTemp           As Excel.Range
    Dim lngRowCounter     As Long
    Dim lngColCounter     As Long

    ' 範囲のパラメータを検証します。
    If lngLowValue > lngHighValue Then
         Err.Raise vbObjectError + 512 + 1, _
            "OutOfBounds Procedure", _
            "Invalid bounds parameters submitted: " _
            & "Low value must be lower than high value."
        Exit Function
    End If

    ' セルを繰り返し調べ、値が範囲パラメータの外部の値かどうか
    ' を判断します。範囲外の場合は値を強調表示します。
    For lngRowCounter = 1 To rngToCheck.Rows.Count
        For lngColCounter = 1 To rngToCheck.Columns.Count
            Set rngTemp = rngToCheck.Cells(lngRowCounter, _
                 lngColCounter)
            If IsNumeric(rngTemp.Value) Then
                If rngTemp.Value < lngLowValue Or _
                    rngTemp.Value > lngHighValue Then
                    rngTemp.Font.Color = lngHighlightColor
                    OutOfBounds = True
                End If
            End If
        Next lngColCounter
    Next lngRowCounter
End Function

また、For Each ... Next ステートメントを使用して、Cells プロパティが返す範囲をループすることもできます。OutOfBounds プロシージャでセルの範囲をループするために次のコードを使用することもできます。


' セルを繰り返し調べ、値が範囲パラメータの外部の値かどうか
' を判断します。範囲外の場合は値を強調表示します。
For Each rngTemp in rngToCheck.Cells
    If IsNumeric(rngTemp.Value) Then
        If rngTemp.Value < lngLowValue Or _
            rngTemp.Value > lngHighValue Then
            rngTemp.Font.Color = lngHighlightColor
            OutOfBounds = True
        End If
    End If
Next rngTemp

Offset プロパティを使用して、指定した Range オブジェクトと同じサイズを持つ Range オブジェクトを返すことができますが、この場合指定した範囲に対してオフセットが加算されます。たとえば、Offset プロパティを使用して、アクティブ セルを基準に計算した値を持つ新しい Range オブジェクトをアクティブ セルに隣接して作成できます。

Offset プロパティは、作業に使う必要のあるの確かなアドレスが分からないときに便利ですが、作業に使う必要のあるほかのセルに "関連して" 位置付けられるセルの位置は知っておく必要があります。たとえば、カスタム ソリューションで、アクティブ セルの左隣の 2 つのセルの平均値をアクティブ セルに代入するようなコマンド バー ボタンを用意するような場合もあるでしょう。


ActiveCell.Value = (ActiveCell.Offset(0, -2) + ActiveCell.Offset(0, -1)/2)

詳細情報

Excel を使った作業の詳細については、次の資料をご覧ください。

David Shank は、Office チームのプログラマ/ライターであり、主に開発者向け文書を専門としています。うわさによると、彼は Redmond 東の山頂に住んでおり、今でも北西部に住んでいる数少ない北西部出身者の 1 人であるとのことです。