vba教程(续1)

11、引用命名单元格区域

用名称比用 A1 样式的记号更容易标识单元格区域。如果要命名选定的单元格区域，单击公式栏左端的名称框，键入名称，然后按 ENTER 键。

引用命名单元格区域

下例引用了工作簿“MyBook.xls”中的单元格区域“MyRange”。

Sub FormatRange()

Range("MyBook.xls!MyRange").Font.Italic = True

End Sub

下例在工作簿“Report.xls”中引用了特定于工作表的单元格区域“Sheet1!Sales”。

Sub FormatSales()

Range("[Report.xls]Sheet1!Sales").BorderAround weight:=xlthin

End Sub

如果要选定命名的单元格区域，可用 GoTo 方法，该方法将激活指定区域所在的工作簿和工作表，然后选定该区域。

Sub ClearRange()

Application.Goto Reference:="MyBook.xls!MyRange"

Selection.ClearContents

End Sub

下例显示了在活动工作簿中对上述同一过程的写法。

Sub ClearRange()

Application.Goto Reference:="MyRange"

Selection.ClearContents

End Sub

在命名区域中的单元格上循环

下例用 For Each...Next 循环语句在命名区域中的每一个单元格上循环。如果该区域中的任一单元格的值超过 limit 的值，就将该单元格的颜色改为黄色。

Sub ApplyColor()

Const limit As Integer = 25

For Each c In Range("MyRange")

If c.Value > limit Then

c.Interior.ColorIndex = 27

End If

Next c

End Sub

12、按相对于其他单元格的方式来引用单元格

处理位于相对于其他单元格的某一位置单元格的常用方法是使用 Offset 属性。下例中，将位于活动工作表上活动单元格下一行和右边三列的单元格的内容设置为双下划线。

Sub Underline()

ActiveCell.Offset(1, 3).Font.Underline = xlDouble

End Sub

注意 可用 Offset 属性而不用绝对引用方式来记录宏。指向“工具”菜单内的“录制宏”子菜单，然后单击“使用相对引用”命令即可。

如果要在单元格区域中循环，可在循环中将 Cells 属性和变量共用。下例用从 5 到 100 的值以 5 为步长填充第三列的前 20 个单元格。变量 counter 用作 Cells 属性的行号。

Sub CycleThrough()

Dim counter As Integer

For counter = 1 To 20

Worksheets("Sheet1").Cells(counter, 3).Value = counter * 5

Next counter

End Sub

13、用 Range 对象引用单元格

将对象变量设置为 Range 对象，即可用变量名方便地操纵该单元格区域。

下述过程创建了对象变量 myRange，然后将活动工作簿中工作表“Sheet1”上的单元格区域“A1:D5”赋予该变量。随后的语句用该变量代替该区域对象，以修改该区域的属性。

Sub Random()

Dim myRange As Range

Set myRange = Worksheets("Sheet1").Range("A1:D5")

myRange.Formula = "=RAND()"

myRange.Font.Bold = True

End Sub

14、引用工作表上的所有单元格

如果对工作表应用 Cells 属性时不指定索引，该属性将返回代表工作表上所有单元格的 Range 对象。下述 Sub 过程清除活动工作簿中工作表“Sheet1”上的所有单元格的内容。

Sub ClearSheet()

Worksheets("Sheet1").Cells.ClearContents

End Sub

15、引用若干单元格区域

使用适当的方法可以很容易地同时引用若干单元格区域。可用 Range 和 Union 方法引用任意组合的单元格区域；用 Areas 属性可引用工作表上选定的一组单元格区域。

使用 Range 属性

使用 Range 属性时，在两个或多个引用之间加上逗号，就可以引用多重区域。下例清除了工作表“Sheet1”上三个单元格区域的内容。

Sub ClearRanges()

Worksheets("Sheet1").Range("C5:D9,G9:H16,B14:D18"). _

ClearContents

End Sub

命名区域使得用 Range 属性处理多重区域更为容易。下例可在三个命名区域处于同一工作表时运行。

Sub ClearNamed()

Range("MyRange, YourRange, HisRange").ClearContents

End Sub

使用 Union 方法

用 Union 方法可将多个单元格区域组合到一个 Range 对象中。下例创建了名为 myMultipleRange 的 Range 对象，并将其定义为单元格区域“A1:B2”和“C3:D4”的组合，然后将该组合区域的字体设置为加粗。

Sub MultipleRange()

Dim r1, r2, myMultipleRange As Range

Set r1 = Sheets("Sheet1").Range("A1:B2")

Set r2 = Sheets("Sheet1").Range("C3:D4")

Set myMultipleRange = Union(r1, r2)

myMultipleRange.Font.Bold = True

End Sub

使用 Areas 属性

可用 Areas 属性引用选定的单个单元格区域或多块的选定区域集合。下述过程计算选定区域内的块数目，如果选定区域中有多个块，就显示警告消息。

Sub FindMultiple()

If Selection.Areas.Count > 1 Then

MsgBox "Cannot do this to a multiple selection."

End If

End Sub

16、在单元格区域中循环

使用 Visual Basic 时，经常需要对某一区域内的每个单元格运行同一段语句。为达到这一目的，可将循环语句和标识每个单元格的一个或多个方法组合使用，每循环一次，就对一个单元格执行一次操作。

在单元格区域中循环的一种方法是使用 For...Next 循环语句和 Cells 属性。使用 Cells 属性时，可用循环计数器（或其他变量或表达式）作为单元格编号。下例中，变量 counter 代替了行号。此过程将在单元格区域“C1:C20”中循环，将所有绝对值小于 0.01 的数字都设置为 0（零）。

Sub RoundToZero1()

For counter = 1 To 20

Set curCell = Worksheets("Sheet1").Cells(counter, 3)

If Abs(curCell.Value) < 0.01 Then curCell.Value = 0

Next counter

End Sub

在单元格区域中循环的另一种简便方法是使用 For Each...Next 循环语句和由 Range 方法返回的单元格集合。在每一次循环过程中，Visual Basic 都为下一次循环所用的单元格自动设置一个对象变量。下述过程在单元格区域“A1:D10”中循环，将所有绝对值小于 0.01 的数字都设置为 0（零）。

Sub RoundToZero2()

For Each c In Worksheets("Sheet1").Range("A1:D10").Cells

If Abs(c.Value) < 0.01 Then c.Value = 0

Next

End Sub

如果不知道要循环的单元格区域的确切边界，可用 CurrentRegion 属性返回活动单元格周围的区域。例如，下述过程在工作表上运行时，将在活动单元格周围的区域内循环，将所有绝对值小于 0.01 的数字都设置为 0（零）。

Sub RoundToZero3()

For Each c In ActiveCell.CurrentRegion.Cells

If Abs(c.Value) < 0.01 Then c.Value = 0

Next

End Sub

17、选定和激活单元格

使用 Microsoft Excel 时，通常要先选定单元格或单元格区域，然后执行某一操作，如设置单元格的格式，或在单元格中输入数值。但在 Visual Basic 中，在修改单元格之前，通常不必先选定它们。

例如，如果要用 Visual Basic 在单元格“D6”中输入公式，就不需要先选定单元格“D6”，而只需返回该 Range 对象，然后将该对象的 Formula 属性设置为所需的公式，如下例所示。

Sub EnterFormula()

Worksheets("Sheet1").Range("D6").Formula = "=SUM(D2:D5)"

End Sub

如果要查阅使用其他方法不选定单元格就控制它们的示例，请参阅“如何引用单元格和区域”。

使用 Select 方法和 Selection 属性

Select 方法激活工作表和工作表上的对象；而 Selection 属性返回代表活动工作簿中活动工作表上的当前选定区域的对象。在使用 Selection 属性之前，必须先激活工作簿，用 Select 方法激活或选定工作表，然后再选定单元格区域（或其他对象）。

宏记录器经常创建使用 Select 方法和 Selection 属性的宏。下述 Sub 过程是用宏记录器创建的，该过程演示了如何共用 Select 方法和 Selection 属性。

Sub Macro1()

Sheets("Sheet1").Select

Range("A1").Select

ActiveCell.FormulaR1C1 = "Name"

Range("B1").Select

ActiveCell.FormulaR1C1 = "Address"

Range("A1:B1").Select

Selection.Font.Bold = True

End Sub

下例完成同样的功能，但不用激活或选定工作表或单元格。

Sub Labels()

With Worksheets("Sheet1")

.Range("A1") = "Name"

.Range("B1") = "Address"

.Range("A1:B1").Font.Bold = True

End With

End Sub

选定活动工作表上的单元格

如果用 Select 方法选定单元格，就应注意 Select 方法仅用于活动工作表。从模块中运行 Sub 过程时，必须先在该过程中激活工作表，然后才能用 Select 方法选定单元格区域，否则该方法将失败。例如，下述过程在活动工作簿中将工作表“Sheet1”中的一行复制到工作表“Sheet2”上。

Sub CopyRow()

Worksheets("Sheet1").Rows(1).Copy

Worksheets("Sheet2").Select

Worksheets("Sheet2").Rows(1).Select

Worksheets("Sheet2").Paste

End Sub

激活选定区域内的单元格

可用 Activate 方法激活选定区域内的单元格。即便选定了单元格区域，也只能有一个活动单元格。下述过程选定了一个单元格区域，然后激活该区域内的一个单元格，但并不改变选定区域。

Sub MakeActive()

Worksheets("Sheet1").Activate

Range("A1:D4").Select

Range("B2").Activate

End Sub

18、处理三维区域

如果要处理若干工作表上同一位置的单元格区域，可用 Array 函数同时选定两张或多张工作表。下例设置三维单元格区域的边框格式。

Sub FormatSheets()

Sheets(Array("Sheet2", "Sheet3", "Sheet5")).Select

Range("A1:H1").Select

Selection.Borders(xlBottom).LineStyle = xlDouble

End Sub

下例应用 FillAcrossSheets 方法，在活动工作簿中，将工作表“Sheet2”上单元格区域的格式和数据复制到该工作簿中所有工作表上的相应区域中。

Sub FillAll()

Worksheets("Sheet2").Range("A1:H1") _

.Borders(xlBottom).LineStyle = xlDouble

Worksheets.FillAcrossSheets (Worksheets("Sheet2") _

.Range("A1:H1"))

End Sub

19、处理活动单元格

ActiveCell 属性返回代表活动单元格的 Range 对象。可对活动单元格应用 Range 对象的任意属性和方法，如下例所示。

Sub SetValue()

Worksheets("Sheet1").Activate

ActiveCell.Value = 35

End Sub

注意 只有活动单元格所在的工作表处于活动状态时，才能处理该活动单元格。

移动活动单元格

可用 Activate 方法设置活动单元格。例如，下述过程使单元格“B5”成为活动单元格，然后将其字体设置为加粗。

Sub SetActive()

Worksheets("Sheet1").Activate

Worksheets("Sheet1").Range("B5").Activate

ActiveCell.Font.Bold = True

End Sub

注意 如果要选定单元格区域，可用 Select 方法。如果要使单个单元格成为活动单元格，则要用 Activate 方法。

可用 Offset 属性来移动活动单元格。下述过程在选定区域内的活动单元格中插入文字，然后将活动单元格右移一列，但并不改变选定区域。

Sub MoveActive()

Worksheets("Sheet1").Activate

Range("A1:D10").Select

ActiveCell.Value = "Monthly Totals"

ActiveCell.Offset(0, 1).Activate

End Sub

选择活动单元格周围的单元格

CurrentRegion 属性返回由空白行和空白列所包围的单元格区域。下例中，选定区域扩充到与活动单元格相邻的包含数据的所有单元格，然后用“货币”样式设置扩充后的区域的格式。

Sub Region()

Worksheets("Sheet1").Activate

ActiveCell.CurrentRegion.Select

Selection.Style = "Currency"

End Sub

20、在工作表上使用 ActiveX 控件

本帮助主题包括了有关在工作表或图表上使用 ActiveX 控件的特定信息。有关添加和处理控件的一般信息，请参阅“在文档中使用 ActiveX 控件”和“创建自定义对话框”。

在工作表上处理控件时，必须记住以下几点。

·     除 ActiveX 控件的标准属性之外，在 Microsoft Excel 中还可使用 ActiveX 控件的下列属性：BottomRightCell、LinkedCell、ListFillRange、Placement、PrintObject、TopLeftCell 和 ZOrder。

这些属性可用 ActiveX 控件的名称来返回或设置。下例滚动工作簿窗口，使 CommandButton1 位于窗口的左上角。

Set t = Sheet1.CommandButton1.TopLeftCell

With ActiveWindow

.ScrollRow = t.Row

.ScrollColumn = t.Column

End With

·     当 ActiveX 控件处于激活状态时，将禁用某些 Microsoft Excel Visual Basic 方法和属性。例如，当某一控件激活时，就不能使用 Sort 方法，故下述按钮单击事件处理过程中的代码将失败（因为用户单击按钮后，该按钮就处于激活状态）。

·          Private Sub CommandButton1.Click

·          Range("a1:a10").Sort Key1:=Range("a1")

End Sub

可通过先激活工作表上的其他元素的方法来绕过这种问题。例如，下列代码可对单元格区域排序：

Private Sub CommandButton1.Click

Range("a1").Activate

Range("a1:a10").Sort Key1:=Range("a1")

CommandButton1.Activate

End Sub

·     当用户通过双击鼠标来编辑内嵌在其他应用程序的文档中的 Microsoft Excel 工作簿时，该工作簿上的控件将不会正常工作。如果用户是通过用右键单击工作簿，然后选中快捷菜单上的“打开”命令来编辑工作簿的话，工作簿上的控件就能正常工作了。

·     用 Microsoft Excel 5.0/95 工作簿文件格式保存 Microsoft Excel 工作簿时，将丢失 ActiveX 控件信息。

·     在工作表上 ActiveX 控件的事件处理过程中，Me 关键字所指向的是工作表，而非控件。

用 Visual Basic 添加控件

在 Microsoft Excel 中，用 OLEObjects 集合中的 OLEObject 对象代表 ActiveX 控件（所有的 OLEObject 对象也包含在 Shapes 集合中）。如果要用编程的方式向工作表添加 ActiveX 控件，可用 OLEObjects 集合的 Add 方法。下例向第一张工作表添加命令按钮。

Worksheets(1).OLEObjects.Add "Forms.CommandButton.1", _

Left:=10, Top:=10, Height:=20, Width:=100

通过 Visual Basic 使用控件属性

绝大多数情况下，可在 Visual Basic 代码中用名称引用 ActiveX 控件。下例修改了控件“CommandButton1”的标题。

Sheet1.CommandButton1.Caption = "Run"

请注意，当在控件所在的工作表的类模块之外使用控件的名称时，必须用工作表的名称限定该控件的名称。

如果要修改在 Visual Basic 代码中所用的控件的名称，可先选定该控件，然后在“属性”窗口中设置控件的“（名称）”属性。

因为 ActiveX 控件也可用 OLEObjects 集合中的 OLEObject 对象代表，所以也可用该集合中的对象来设置控件的属性。下例设置了控件“CommandButton1”的左边位置。

Worksheets(1).OLEObjects("CommandButton1").Left = 10

那些不属于 OLEObject 对象的属性的控件属性，可通过由 Object 属性返回的实际控件对象来设置。下例设置了控件“CommandButton1”的标题。

Worksheets(1).OLEObjects("CommandButton1"). _

Object.Caption = "run me"

因为所有的 OLE 对象也是 Shapes 集合的成员，所以也可用该集合设置若干控件的属性。下例对齐第一张工作表上的所有控件的左边框。

For Each s In Worksheets(1).Shapes

If s.Type = msoOLEControlObject Then s.Left = 10

Next

使用 Shapes 和 OLEObjects 集合的控件名称

工作表上的 ActiveX 控件具有两个名称：其一是包含该控件的形状的名称，当查看工作表时，可在“Name”框中看到此名称；其二是控件的代码名称，在“属性”窗口的“（名称）”框右边的单元格中可看到此名称。第一次向工作表中添加控件时，形状名称和代码名称是相同的。但是，如果更改其中之一（形状名称或代码名称），另一个名称并不会自动随之更改。

对于控件的事件过程，其名称中所使用的是控件的代码名称。但是，当从工作表的 Shapes 或 OLEObjects 集合中返回控件时，必须使用形状名称（而不是代码名称），以便按名称引用控件。例如，假设向工作表中添加了一个复选框，其默认形状名称和默认代码名称都是“CheckBox1”。如果此后在“属性”窗口的“（名称）”旁键入了“chkFinished”，从而将控件的代码名称更改为“chkFinished”，则在事件过程名称中必须使用 chkFinished，而仍然应使用 CheckBox1 从 Shapes 或 OLEObject 集合中返回控件，如下例所示。

Private Sub chkFinished_Click()

ActiveSheet.OLEObjects("CheckBox1").Object.Value = 1

End Sub