Share via

Excel 2007 範囲オブジェクトに関する開発者向けガイド

  • [アーティクル]

  

Frank Rice、Microsoft Corporation

2008 年 1 月

適用対象:
   Microsoft Office Excel 2007

要約: Microsoft Office Excel 2007 で、プログラムによってデータ範囲を参照し操作する方法について説明します。範囲を指定したら、範囲にわかりやすい名前を付けたり、数式で使用したり、範囲内に格納されているデータを検証したりすることができます。

目次

概要
範囲に値を挿入する
範囲を参照する
セル範囲を選択する
範囲の集合を作成し、重複範囲をチェックする
名前付き範囲の詳細情報
範囲をコピーする
範囲をループ処理する
範囲内を検索する
結論
追加情報

概要

Microsoft Office Excel 2007 で行う作業のほとんどは、セルに関係します。また、Excel オブジェクト モデルで行うほとんどの操作は、 ** 範囲に関係します。 範囲とは、単一のセル、行、列、連続するセルのブロックを 1 つ以上含むセルの選択範囲、あるいは 3D 範囲を表します。

Range プロパティは、Range オブジェクトを返します。Range プロパティは、次の 2 つの形式をとります。

expression.Range(cell1)
expression.Range(cell1, cell2)

最初の例では、cell1 は単一のセルで構成される範囲を表しています。一方、2 番目の例では、2 つの値によって連続するセルの範囲を示しています。これらの例では、expression が必須要素となります。この要素は、Application オブジェクト、Range オブジェクト、または Worksheet オブジェクトのいずれかである必要があります。 この要素を省略すると、ActiveSheet オブジェクトが想定されます。

2 番目の例では、セルの区切り記号には範囲演算子 (コロン)、共通集合演算子 (スペース)、和集合演算子 (カンマ) を使用できます。

以降のセクションでは、Excel の範囲に対して実行できるさまざまな操作について説明します。ただし、ここで説明される例は、実際に行える操作の一部にすぎません。 範囲オブジェクト モデル メンバの詳細については、「Range Object Members (英語)」を参照してください。

範囲に値を挿入する

範囲内の 1 つのセルに値を挿入するのは簡単です。

Worksheets("Sheet1").Range("A1").Value = 3.14159

同様に、名前が付けられているセルにも、次のように値を追加できます。

pre>ActiveSheet.Range("MyCell").Value = 1

次の例では、一連の範囲のセルに 1 つの値を挿入します。

Worksheets("Sheet1").Range("A1:B10").Value = 1

最初のセクションで説明した Range プロパティの 2 番目の形式を使用すると、次のステートメントでも同じ結果が得られます。

 
Range("A1", "B10") = 1

オブジェクト参照が省略されているため、アクティブ シートが想定されている点に注目してください。また、Range オブジェクトのプロパティが省略されているため、Value プロパティが想定され、値 1 が割り当てられています。

さらに、2 番目の形式を使用すると、次のステートメントで値 XYZ を Sheet2 のセル A1、A3、および A5 に挿入することができます。

Worksheets("Sheet2").Range("A1, A3, A5") = "XYZ"

次の例では、Sheet1 のセル A1 に数式を挿入できます。

Worksheets("Sheet1").Range("A1").Formula = "=10*RAND()"

次の例は、Sheet1 のセル D1:D10 をループ処理します。いずれかのセルに "For Sale" と等しい String 値が挿入されていれば、このコードはその値を文字列 "Sold" に置き換えます。

For Each c in Worksheets("Sheet1").Range("D1:D10")
   If c.Value = "For Sale" Then 
      c.Value = "Sold"
   End If 
Next c

範囲を参照する

範囲を参照する方法は、次のように複数あります。

  • セル番地
  • 他のセルからのオフセット
  • セルの名前
  • 現在の選択範囲

セルの番地を使用して範囲を直接参照する方法はいくつかあります。ActiveSheet オブジェクトを使用する絶対参照、または ActiveCell オブジェクトやその他のオブジェクトを使用する相対参照を使用できます。

他のワークシートの範囲を参照する

範囲の操作では、ほとんどの場合、アクティブなワークシート上にある範囲を参照します。また、アクティブではないブックの範囲も簡単に参照することができます。 たとえば、ActiveSheet の範囲を参照するには、次のように Range プロパティを使用します。この例では、セル B1 ～ B15 が参照されます。

Range("B1:B15").Select

同様に、アクティブでないワークシート上の同じ範囲のセルを参照するには、そのワークシートのインデックスまたは名前を指定します。

Worksheets(2). Range("B1:B15").Select

ただし、範囲に一意の名前がある場合は、ワークシートを指定しなくても、範囲を参照できます。

Range("SalesTax").Select

このテクニックは、どの名前付き範囲が含まれているワークシートが配置されているかに関係なく使用できます。

範囲内の個々のセルを参照する

範囲内のセルを参照する方法は、すべてのブックでセルを参照する方法と同じです。たとえば、範囲として B4:E10 のセルを指定している場合、この範囲内のセル B4 を参照するには、Range オブジェクトで A1 を指定します。 また、この範囲内のセル D8 を参照するには、Range オブジェクトで C5 を指定します。

オフセットを使用して範囲内のセルを参照する

Offset プロパティは Range オブジェクトを返します。このプロパティを使用すると、他のセルとの位置関係を使用して目的のセルを参照できます。つまり、ある場所から目的のセルまでの列数と行数を指定することによって、セルを参照できます。 構文は、次のとおりです。

object.Offset(rowOffSet, columnOffset)

正の数を指定すると、フォーカスは下および右に移動します。負の数を指定すると、フォーカスは上および左に移動します。 ゼロは、現在のセルを参照します。 たとえば、開始位置としてセル E5 を指定するとします。

最初のステートメントでは、E5 から 1 行下に移動し、セル E6 に 1 が挿入されます。

ActiveCell.Offset(1,0) = 1

次のステートメントでは、E5 から 1 列右に移動し、セル F5 に 1 が挿入されます。

ActiveCell.Offset(0,1) = 1

また、次のステートメントでは、E5 から 3 列左に移動し、セル B5 に 1 が挿入されます。

ActiveCell.Offset(0,-3) = 1

次の例では、Offset プロパティを使用して、移動平均を計算します。

Sub MovingAvg()
    Dim rng As Range
    Dim lngRow As Long

    Set rng = Range("B1:B3")
    
    For lngRow = 3 To 12
        Cells(lngRow, "C").Value = WorksheetFunction.Sum(rng) / 3
        Set rng = rng.Offset(1, 0)
    Next lngRow
    
End Sub

このコードは、B 列の最初の 3 つの値を範囲として設定し、それらの値の合計を 3 で割った平均値をセル C3 に挿入します。次に、Offset プロパティは B 列内でこの範囲を 1 行下に移動し、範囲 B2:B4 の値の平均値を計算します。 この平均値は、セル C4 に挿入されます。 このプロセスは、セル B12 まで繰り返されます。

名前付き範囲のセルを参照する

名前付き範囲とは、一意の名前を持ち、イベントを公開し、データにバインドできる範囲です。Excel では、定義済みの範囲の名前は Names コレクションに格納されます。このコレクションは、Workbook オブジェクトのプロパティです。 名前付き範囲は、Excel の強力なツールとなります。これにより、単一のセルやセルの範囲にわかりやすい名前を割り当てることができます。 たとえば、セル C1 に SalesTax という名前を割り当てると、通常 C1 を使用して行う操作を、次のように SalesTax という名前を使用して行うことができます。

Total = Range("A1") * Range("SalesTax")

名前付き範囲については、後で詳しく説明します。

セル範囲を選択する

アクティブな範囲を参照できる ActiveRange オブジェクトはありませんが、さまざまな方法で範囲を操作することができます。たとえば、Select メソッドを使用すれば、セルの範囲を選択できます。範囲が選択されたら、Selection オブジェクトを使用して、セルの選択範囲に対するアクションを制御できます。

たとえば、範囲の値をクリップボードにコピーし、新しいワークシートに挿入することができます。

Range("D5").Activate
ActiveCell.CurrentRegion.Select
Selection.Copy
Sheets.Add After:=Sheets(Sheets.Count)
Sheets(Sheets.Count).Name = "Sample"
Sheets("Sample").Select
Range("D5").Activate
ActiveSheet.Paste

この例では、ActiveCell オブジェクトの CurrentRegion プロパティを使用して、範囲 (この場合はセル D1) を選択し、その範囲の内容をクリップボードにコピーします。最後のワークシートの後ろに新しいシートが追加され、セルの内容がコピーされます。

セルの選択範囲に対しては、フォントを太字にするなど、他にも多様な操作を実行できます。

Selection.Font.Bold = True

これと同じ操作は、次のステートメントでも実行できます。

Range("C5:C20").Font.Bold = True

CurrentRegion プロパティに加えて、Range オブジェクトの End メソッドも使用できます。このメソッドでは、範囲をどの方向に拡張するかを指定できます。列挙子は、範囲を拡張する方向を示します。 End メソッドによって返される Range オブジェクトは、元の範囲を含む領域の終端にあるセルを表します。 End メソッドを使用することは、End + ↑、End + ↓、End + ←、End + → キーを押すことに相当します。 次の例では、セル B4 を含む領域の列 B の一番上にあるセルが選択されます。

Range("B4").End(xlUp).Select

次の例では、セル B4 を含む領域の行 4 の一番右にあるセルが選択されます。

Range("B4").End(xlToRight).Select

次の例では、セル B4 から、データが存在する行 4 の最後のセルまで、選択範囲が拡張されます。

Worksheets("Sheet1").Activate 
Range("B4", Range("B4").End(xlToRight)).Select

次のサンプルでは、End メソッドと方向の列挙子を使用して、列の値を合計します。たとえば、値を持つ列がセル B2 から G11 の範囲で存在する場合、セル B1 をアクティブなセルとしてマクロを実行し、値を持つ各列の一番上に合計値を挿入することができます。

Sub SumBelow()
    Dim rng As Range

    ' Sum cells below active cell & copy the totals across.

    With ActiveCell
        Set rng = Range(.Offset(1), .Offset(1).End(xlDown))
        .Formula = "=SUM(" & _
                   rng.Address(RowAbsolute:=False, ColumnAbsolute:=False) & ")"
        .Copy Destination:=Range(.Cells(1), .Offset(1).End(xlToRight).Offset(-1))
    End With

End Sub

範囲の集合を作成し、重複範囲をチェックする

Union メソッド (Union(range1, range2, ...)) を使用すると、連続するセルのブロックを複数含む範囲の集合を作成できます。次の例では、範囲 A1:B2 と範囲 C3:D4 の集合を 1 つのオブジェクトとして定義し、新たに定義された範囲を選択します。

Dim rng1 As Range, rng2 As Range, myMultiRanges As Range
Worksheets("Sheet1").Activate
Set rng1 = Range("A1:B2")
Set rng2 = Range("C3:D4")
Set myMultiRanges = Union(rng1, rng2)
myMultiRanges.Select

Intersect メソッド (Intersect(range1, range2)) は、複数の範囲が直角に交差して重複する範囲を表す Range オブジェクトを返します。範囲が重複しない場合は、Nothing キーワードが返されます。

次の例では、Sheet1 にある、名前が付けられた 2 つの範囲 rng1 と rng2 の重複範囲が選択されます。重複する箇所がない場合は、メッセージが表示されます。

Worksheets("Sheet1").Activate 
Set interSect = Application.Intersect(Range("rng1"), Range("rng2")) 
If interSect Is Nothing Then 
   MsgBox "The ranges do not intersect." 
Else 
   interSect.Select 
End If

名前付き範囲の詳細情報

前述のように範囲に名前を付けることにより、一連の範囲のセルにわかりやすい名前を定義することができます。範囲の名前には、文字、数字、アンダースコアを含めることができますが、スペースや特殊な句読文字は使用できません。 最大文字数は、255 文字です。 名前付き範囲は、セルのブロックを参照する方法として推奨されています。 たとえば、Range("D1:D15") を使用するよりも Range("Sales") を使用するほうが、わかりやすくなります。

名前付き範囲を定義および検出する

Names コレクションの Add メソッドを使用すると、名前およびその名前によってポイントされるセル範囲を指定して、ブック内に名前付き範囲を作成できます。

ThisWorkbook.Names.Add Name:="Home_Sales", RefersTo:="=$A$1:$E$15", Visible:=True

同様に、ワークシート内に名前付き範囲が存在するかどうかを確認するには、次の例を使用します。このステートメントは、名前付き範囲が見つかれば True を、見つからなければ False を返します。

Function NameExists(MyName As String) As Boolean
   On Error Resume Next
   NameExists = Len(ThisWorkbook.Names(MyName).Name) <> 0
End Function

予約されている範囲の名前

Excel が使用するため予約されている範囲名があります。カスタム範囲にこれらの名前を付けることはできません。予約されている名前には、Consolidate_Area、Print_Titles、Auto_Open、Recorder、Auto_Close、Data_Form、Extract、Auto_Activate、Database、Auto_Deactivate、Criteria、Sheet_Title、および Print_Area などがあります。

これらの名前を使用して独自のカスタム範囲を定義することはできませんが、これらの名前を使用して操作を行うことはできます。たとえば、Print_Area という名前を使用すると、印刷するセル範囲を指定できます。 さらに、Print_Area 範囲では、A1 形式、R1C1 形式、および範囲名の選択肢を使用して、セルの範囲を設定することができます。 次の例では、印刷するセル範囲が A1:F15 に設定されます。

Sheet1.Names.Add "Print_Area", Sheet1.Range("A1:F15")

次のステートメントを使用すると、印刷領域から範囲を除外することができます。

Sheet1.Names("Print_Area").Delete

名前付き範囲を削除する

Names コレクションの Delete メソッドを使用すると、名前付き範囲を削除できます。

ThisWorkbook.Names("Home_Sales ").Delete

範囲をコピーする

以降のセクションでは、セルや範囲をコピーする方法について説明します。

セル範囲をコピーする

セル範囲をある場所から別の場所にコピーするには、次のような方法があります。

Range("A1").Copy Range("B1")

この操作では、コピー元およびコピー先がアクティブなワークシート上にあると想定されます。コピーを行う前に必ずしも範囲を選択しておく必要はありません。 範囲を選択する場合は、そのワークシートはアクティブでなければなりません。 ワークシートをアクティブにするには、Worksheets コレクションの Activate メソッドを使用します。

アクティブでないワークシートに範囲をコピーするには、範囲の参照を修飾する必要があります。

Workbooks("File.xls").Sheets("Sheet1").Range("A1").Copy Workbooks("File.xls").Sheets("Sheet2").Range("A1")

この同じ構文を使用して、複数のセルから成る範囲を移動することもできます。

Range("C2:E10").Copy Range("D2")

この例では、コピー先の場所は、ワークシートの左上のセルによって示されています。

**メモ:   ** Copy コマンドの代わりに Cut コマンドを使用すると、範囲の移動を選択できます。

不定サイズの範囲をコピーする

多くの場合、コピーまたは移動する範囲の正確なサイズはわかりません。たとえば、週ごとの在庫リストを管理している場合、行の数はその週の売上に応じて変化します。 このような状況に対応するには、CurrentRegion プロパティを使用します。 このプロパティは、特定のセルとその周囲にあるセルを表す Range オブジェクトを返します。

通常、CurrentRegion プロパティは、1 つ以上の空白行や空白列、またはワークシートの境界で区切られる長方形のセル ブロック (空白行と空白列は含まれない) で構成されます。たとえば、セル D2 ～ E15 にデータが挿入されている表があり、フォーカスがセル D2 にある場合、CurrentRegion プロパティはセル D2 ～ E15 の範囲を表す Range オブジェクトを返します。

次の例は、Sheet1 のセル A1 を中心とするセル範囲をコピーし、Sheet2 の対応する範囲に貼り付けます。

Range("A1").CurrentRegion.Copy Sheets("Sheet2").Range("A1")

範囲と配列間でデータをコピーする

範囲内の個々のセルの値には、簡単にアクセスすることができます。これは、ワークシートと Microsoft Visual Basic for Applications (VBA) 間のデータ移動が簡単であるのと同じです。バリアント データ型を作成し、そのバリアント型に範囲を割り当てるだけです。 これで、通常の配列要素にアクセスするのと同じように、個々のセルの値にアクセスできます。 次の例では、範囲 B1:B10 をバリアント型にコピーした後、1 つのセルの値を表示します。

Dim varA As Variant
Set varA = Range("B1:B10")
MsgBox (varA(3))

**メモ:   **範囲は常に、2 次元以上の多次元配列を返します。1 つの次元は行を、もう 1 つの次元は列を表し、範囲に含まれる列の数によってはさらに次元数が増えます。

範囲をループ処理する

範囲の値をループ処理し、その範囲内の 1 つ以上のセルに対してアクションを実行するという作業は、開発者が頻繁に行う作業の 1 つです。これを実行する最も簡単な方法は、次の例のように For...Next ループ形式を使用する方法です。 この例では、セルの名前付き範囲がループ処理され、セルの値に基づいてフォントの色が設定されます。フォントの色は、Range オブジェクトの Cell プロパティを使用し、行と列の値を指定して設定されています。

Sub ColorCells()
    Dim rngSales As Range
    Dim lngRow As Long, lngColumn As Long

    Set rngSales = Range("SalesData")
    
    For lngRow = 1 To rngSales.Rows.Count
    
        For lngColumn = 1 To rngSales.Columns.Count
        
            If rngSales.Cells(lngRow, lngColumn).Value < 100 Then
                rngSales.Cells(lngRow, lngColumn).Font.ColorIndex = 3
            Else
                rngSales.Cells(lngRow, lngColumn).Font.ColorIndex = 1
            End If
            
        Next lngColumn
        
    Next lngRow

End Sub

より効率的に同じ結果を得るには、次の例のように単一パラメータの範囲参照を使用します。

Sub ColorCells2()
    Dim rngSales As Range
    Dim lngCell As Long

    Set rngSales = Range("SalesData")
    
    For lngCell = 1 To rngSales.Count
    
        If rngSales(lngCell).Value < 100 Then
            rngSales(lngCell).Font.ColorIndex = 3
        Else
            rngSales(lngCell).Font.ColorIndex = 1
        End If
        
    Next lCell

End Sub

次の例は、セル範囲をループ処理し、特定の値を超えるセルに太字を適用する別の方法を示しています。

Sub BoldRows()
    Dim rngRow As Range

    ' Bold rows where total over 1000.
    For Each rngRow In Range("Data").Rows
    
        If rngRow.Cells(1).Value > 1000 Then
            rngRow.Font.Bold = True
        Else
            rngRow.Font.Bold = False
        End If
        
    Next rngRow
End Sub

範囲内を検索する

ユーザーが頻繁に行うもう 1 つの一般的な作業は、データ範囲で値を検索することです。次の例では、値の範囲内で文字列 "Jun" が検索されます。文字列が見つかった場合は、その値と関連付けられているデータ行がコピーされ、他の場所に貼り付けられます。見つからない場合は、ユーザーにメッセージが表示されます。 Find メソッドに関連付けられているパラメータによって、検索対象や検索範囲が指定されていることに注目してください。

Sub FindIt()
    Dim rng As Range

    ' Find data and use Resize property to copy range.
    Set rng = Range("A1:A12").Find(What:="Jun", _
                                   LookAt:=xlWhole, LookIn:=xlValues)
    If rng Is Nothing Then
        MsgBox "Data not found"
        Exit Sub
    Else
        rng.Resize(1, 3).Copy Destination:=Range("G1")
    End If
    
End Sub

次の例では、ブックのすべてのワークシートが検索され、不連続な複数の範囲内でゼロ以外の値がリセットされます。

Sub ResetRangeValues()
    Dim wSheet As Worksheet
    Dim myRng As Range
    Dim allSheets As Sheets
    Dim cel As Range
    Set allSheets = Worksheets
    For Each wSheet In allSheets
        Set myRng = wSheet.Range("A1:A5, B6:B10, C1:C5, D4:D10")
        For Each cel In myRng
            If Not cel.HasFormula And cel.Value <> 0 Then
                cel.Value = 0
           End If
        Next cel
    Next wSht

End Sub

結論

この記事では、範囲の参照方法および操作方法について学びました。範囲内の個々のセルを参照することも可能ですが、Range オブジェクト内から個々のセルを呼び出すこともできるというのが、この記事の主なコンセプトです。 範囲を指定した後で、範囲にわかりやすい名前を付け、数式で使用したり、その範囲に格納されているデータを検証したりできます。

追加情報

範囲に関する詳細な情報は、次の場所で参照できます。