如何：在代码中引用工作表范围

Range 类的使用非常灵活。有时 Range 对象是单个对象，有时它表示一个对象集合。尽管 Range 对象常常指代单个对象，它却拥有 Item 和 Count 成员，因而有时很难确切把握 Range 对象的用法。

注意   下面有几个实例检索一个范围的 Address 属性。此属性返回一个字符串，该字符串包含范围坐标的表示形式，其格式可以是以下几种格式中的一种：“$A$1”（位置 A1 的单元格）、“$1”（工作表的第一行）和“$A$1:$C$5”（由 A1 和 C5 限定的矩形内的所有单元格组成的范围）。“$”指示绝对坐标，而不是相对坐标。使用 Address 属性是确定检索到的范围的精确位置的最简单方法。

以下过程使 Range 对象引用单个单元格或一组单元格。每个实例都采用了以下设置代码：

' Visual Basic
Dim ws As Excel.Worksheet = _
    DirectCast(ThisWorkbook.Worksheets(1), Excel.Worksheet)
Dim rng, rng1, rng2 As Excel.Range

// C#
Excel.Worksheet ws = (Excel.Worksheet)ThisWorkbook.Worksheets[1]; 
Excel.Range rng,rng1,rng2;

可以使用以下任一方法引用特定的范围。

引用工作表中的特定范围

• 引用 Application 对象的 ActiveCell 属性：

  ' Visual Basic
  rng = ThisApplication.ActiveCell
  
  // C#
  rng = ThisApplication.ActiveCell;

• 使用对象的 Range 属性可指定一个范围：

  ' Visual Basic
  rng = ws.Range("A1")
  rng = ws.Range("A1:B12")
  
  // C#
  rng = ws.get_Range("A1",Type.Missing);
  rng = ws.get_Range("A1:B12",Type.Missing);

• 使用工作表的 Cells 属性可指定单个行和列的值：

  ' Visual Basic
  ' The Cells collection returns an Object.
  ' Convert it to a Range object explicitly.
  rng = DirectCast(ws.Cells(1, 1), Excel.Range)
  
  // C#
  // The Cells collection returns an Object.
  // Convert it to a Range object explicitly.
  rng = (Excel.Range)ws.Cells[1,1];

• 指定一个范围的“角”；也可直接引用范围的 Cells、Rows 或 Columns 属性；这几种情况下该属性都返回一个范围：

  ' Visual Basic
  rng = ws.Range("A1", "C5")
  rng = ws.Range("A1", "C5").Cells
  rng = ws.Range("A1", "C5").Rows
  rng = ws.Range("A1", "C5").Columns
  
  // C#
  rng = ws.get_Range("A1", "C5"); 
  rng = ws.get_Range("A1", "C5").Cells; 
  rng = ws.get_Range("A1", "C5").Rows; 
  rng = ws.get_Range("A1", "C5").Columns;

• 引用命名范围：

  ' Visual Basic
  rng = ThisApplication.Range("SomeRangeName")
  
  // C#
  rng = ThisApplication.get_Range("SomeRangeName", Type.Missing);

• 引用特定的行或列或者由行和列构成的范围；请注意，这些 Rows 和 Columns 属性分别返回一个 Object，如果将 Option Strict 设置为 On，则需要进行转换：

  ' Visual Basic
  rng = DirectCast(ws.Rows(1), Excel.Range)
  rng = DirectCast(ws.Rows("1:3"), Excel.Range)
  rng = DirectCast(ws.Columns("B:E"), Excel.Range)
  
  // C#
  rng = (Excel.Range)ws.Rows[1,Type.Missing];
  rng = (Excel.Range)ws.Rows["1:3",Type.Missing];
  rng = (Excel.Range)ws.Columns["B:E",Type.Missing];

• 使用 Application 对象的 Selection 属性返回与选定的单元格相对应的范围：

  ' Visual Basic
  System.Diagnostics.Debug.WriteLine(ThisApplication.Selection.Address)
  
  // C#
  System.Diagnostics.Debug.WriteLine(((Excel.Range)
      ThisApplication.Selection).get_Address(
      Type.Missing, Type.Missing, Excel.XlReferenceStyle.xlA1, 
      Type.Missing, Type.Missing)); 

• 创建一个包含两个范围的并集的范围（在引号中指定这两个范围，之间以逗号分隔）：

  ' Visual Basic
  rng = ThisApplication.Range("A1:D4, F2:G5")
  ' You can also use the Application object's Union
  ' method to retrieve the intersection of two ranges:
  rng1 = ThisApplication.Range("A1:D4")
  rng2 = ThisApplication.Range("F2:G5")
  rng = ThisApplication.Union(rng1, rng2)
  
  // C#
  rng = ThisApplication.get_Range("A1:D4","F2:G5"); 
  // You can also use the Application object's Union 
  // method to retreive the intersection of two ranges: 
  
  rng1 = ThisApplication.get_Range("A1","D4"); 
  rng2 = ThisApplication.get_Range("F2","G5"); 
  rng = ThisApplication.Union(rng1, rng2, Type.Missing, Type.Missing,
      Type.Missing, Type.Missing, Type.Missing, Type.Missing, 
      Type.Missing, Type.Missing, Type.Missing, Type.Missing, 
      Type.Missing, Type.Missing, Type.Missing, Type.Missing, 
      Type.Missing, Type.Missing, Type.Missing, Type.Missing, 
      Type.Missing, Type.Missing, Type.Missing, Type.Missing, 
      Type.Missing, Type.Missing, Type.Missing, Type.Missing, 
      Type.Missing, Type.Missing);

• 创建一个表示两个范围的交集的范围（在引号中指定这两个范围，之间以逗号分隔）：

  ' Visual Basic
  rng = ThisApplication.Range("A1:D16 B2:F14")
  ' You can also use the Application object's Intersect
  ' method to retrieve the intersection of two ranges:
  rng1 = ThisApplication.Range("A1:D16")
  rng2 = ThisApplication.Range("B2:F14")
  rng = ThisApplication.Intersect(rng1, rng2)
  
  // C#
  rng = ThisApplication.get_Range("A1:D16","B2:F14"); 
  // You can also use the Application object's Intersect 
  // method to retrieve the intersection of two ranges: 
  rng1 = ThisApplication.get_Range("A1","D16"); 
  rng2 = ThisApplication.get_Range("B2","F14"); 
  rng = ThisApplication.Intersect(rng1, rng2, Type.Missing, Type.Missing,
      Type.Missing, Type.Missing, Type.Missing, Type.Missing, 
      Type.Missing, Type.Missing, Type.Missing, Type.Missing, 
      Type.Missing, Type.Missing, Type.Missing, Type.Missing, 
      Type.Missing, Type.Missing, Type.Missing, Type.Missing, 
      Type.Missing, Type.Missing, Type.Missing, Type.Missing, 
      Type.Missing, Type.Missing, Type.Missing, Type.Missing, 
      Type.Missing, Type.Missing);

• 使用范围的 Offset 属性可检索相对于原始范围的范围；以下实例将内容添加到位于第 1 行第 1 列的单元格下方的范围：

  ' Visual Basic
  rng = DirectCast(ws.Cells(1, 1), Excel.Range)
  
  Dim i As Integer
  For i = 1 To 5
      rng.Offset(i, 0).Value = i.ToString
  Next
  
  // C#
  rng = (Excel.Range)ws.Cells[1,1]; 
  
  for(int i = 1; i <= 5; i++) 
      rng.get_Offset(i,0).Value2 = i.ToString();

• 使用范围的 CurrentRegion 属性可检索代表当前区域的范围，当前区域指的是由最近的空行和空列所界定的区域：

  ' Visual Basic
  System.Diagnostics.Debug.WriteLine( _
      ThisApplication.Range("C3").CurrentRegion.Address())
  
  // C#
  System.Diagnostics.Debug.WriteLine(ThisApplication.get_Range(
      "C3", Type.Missing).CurrentRegion.get_Address(
      Type.Missing, Type.Missing,
      Excel.XlReferenceStyle.xlA1, Type.Missing, Type.Missing));

• 使用范围的 Areas 属性可检索一组范围，每个范围对应于该范围内容中的一个区域：以下代码要求工作表中有一个名为“rangeTest”的范围：

  ' Visual Basic
  rng = ThisApplication.Range("rangeTest")
  Dim i As Integer
  For i = 1 To rng.Areas.Count
      System.Diagnostics.Debug.WriteLine(rng.Areas(i).Address)
  Next
  
  // C#
  rng = ThisApplication.get_Range("rangeTest",Type.Missing); 
  for(int i = 1; i <= rng.Areas.Count; i++) 
      System.Diagnostics.Debug.WriteLine(
          rng.Areas[i].get_Address(Type.Missing,
          Type.Missing, Excel.XlReferenceStyle.xlA1, Type.Missing, 
          Type.Missing));

• 使用 End 属性以及 XlDirection 枚举中的值 (xlUp、xlToRight、xlToLeft、xlDown)，可检索代表该区域末尾处的单元格的范围（如同按下了枚举值所描述的键一样）：

  ' Visual Studio
  With ThisApplication.Selection
      System.Diagnostics.Debug.WriteLine(.End(
          Excel.XlDirection.xlToRight).Address)
      System.Diagnostics.Debug.WriteLine(.End(
          Excel.XlDirection.xlToLeft).Address)
      System.Diagnostics.Debug.WriteLine(.End(
          Excel.XlDirection.xlUp).Address)
      System.Diagnostics.Debug.WriteLine(.End(
          Excel.XlDirection.xlDown).Address)
  End With
  
  // C#
  rng = (Excel.Range)ThisApplication.Selection; 
  System.Diagnostics.Debug.WriteLine(rng.get_End(
      Excel.XlDirection.xlToRight).get_Address(
      Type.Missing, Type.Missing, Excel.XlReferenceStyle.xlA1, 
      Type.Missing, Type.Missing)); 
  System.Diagnostics.Debug.WriteLine(rng.get_End(
      Excel.XlDirection.xlToLeft).get_Address(
      Type.Missing, Type.Missing, Excel.XlReferenceStyle.xlA1, 
      Type.Missing, Type.Missing)); 
  System.Diagnostics.Debug.WriteLine(rng.get_End(
      Excel.XlDirection.xlUp).get_Address(
      Type.Missing, Type.Missing, Excel.XlReferenceStyle.xlA1, 
      Type.Missing, Type.Missing)); 
  System.Diagnostics.Debug.WriteLine(rng.get_End(
      Excel.XlDirection.xlDown).get_Address(
      Type.Missing, Type.Missing, Excel.XlReferenceStyle.xlA1, 
      Type.Missing, Type.Missing));

• 使用 EntireRow 或 EntireColumn 属性引用包含指定范围的行或列。以下代码要求工作表中有一个名为“rangeTest”的范围：

  ' Visual Basic
  rng = ThisApplication.Range("rangeTest")
  System.Diagnostics.Debug.WriteLine(rng.Areas(2).EntireRow.Address)
  
  // C#
  rng = ThisApplication.get_Range("rangeTest",Type.Missing); 
  System.Diagnostics.Debug.WriteLine(rng.Areas[2].EntireRow.get_Address(
      Type.Missing, Type.Missing, Excel.XlReferenceStyle.xlA1,
      Type.Missing, Type.Missing));