课题_Excel_VBA_编程教程(完整版)excel高级教程

Excel_VBA_编程教程(完整版)excel高级教程VBA - Excel编程概念之:【单元格和区域】一、如何引用单元格和区域

使用Visual Basic 的普通任务是指定单元格或单元格区域，然后对该单元格或单元格区域进行一些操作，如输入公式或更改格式。通常用一条语句就能完成操作，该语句可标识单元格，还可更改某个属性或应用某个方法。

在Visual Basic 中，Range 对象既可表示单个单元格，也可表示单元格区域。下列主题说明了标识和处理Range 对象最常用的方法。

用A1 样式记号引用单元格和单元格区域

可使用Range 属性来引用A1 引用样式中的单元格或单元格区域。下述子程序将单元格区域A1:D5 的字体设置为加粗。

Sub FormatRange()

Workbooks("Book1").Sheets("Sheet1").Range("A1:D5") _

.Font.Bold = True

End Sub

下表演示了使用Range 属性的一些A1 样式引用。

引用含义

Range("A1") 单元格A1

Range("A1:B5") 从单元格A1 到单元格B5 的区域

Range("C5:D9,G9:H16") 多块选定区域

Range("A:A") A 列

Range("1:1") 第一行

Range("A:C") 从A 列到C 列的区域

Range("1:5") 从第一行到第五行的区域

Range("1:1,3:3,8:8") 第1、3 和8 行

Range("A:A,C:C,F:F") A 、C 和F 列

用编号引用单元格

通过使用行列编号，可用Cells 属性来引用单个单元格。该属性返回代表单个单元格的Range 对象。下例中，Cells(6,1) 返回Sheet1 上的单元格A6，然后将Value 属性设置为10。

Sub EnterValue()

Worksheets("Sheet1").Cells(6, 1).Value = 10

End Sub

因为可用变量替代编号，所以Cells 属性非常适合于在单元格区域中循环，如下例所示。

Sub CycleThrough()

Dim Counter As Integer

For Counter = 1 To 20

Worksheets("Sheet1").Cells(Counter, 3).Value = Counter Next Counter

End Sub

注意如果要同时更改某一单元格区域中所有单元格的属性或对其应用方法，可使用Range 属性。有关详细信息，请参阅用A1 样式记号引用单元格。

引用行和列

可用Rows 属性或Columns 属性来处理整行或整列。这两个属性返回代表单元格区域的Range 对象。下例中，用Rows(1) 返回Sheet1 上的第一行，然后将单元格区域的Font 对象的Bold 属性设置为True。

Sub RowBold()

Worksheets("Sheet1").Rows(1).Font.Bold = True

End Sub

下表举例说明了使用Rows 和Columns 属性的一些行和列的引用。

引用含义

Rows(1) 第一行

Rows 工作表上所有的行

Columns(1) 第一列

Columns("A") 第一列

Columns 工作表上所有的列

若要同时处理若干行或列，请创建一个对象变量并使用Union 方法，将对Rows 属性或Columns 属性的多个调用组合起来。下例将活动工作簿中第一张工作表上的第一行、第三行和第五行的字体设置为加粗。

Sub SeveralRows()

Worksheets("Sheet1").Activate

Dim myUnion As Range

Set myUnion = Union(Rows(1), Rows(3), Rows(5))

myUnion.Font.Bold = True

End Sub

用快捷记号引用单元格

可用方括号将A1 引用样式或命名区域括起来，作为Range 属性的快捷方式。这样就不必键入单词“Range”或使用引号，如下例所示。

Sub ClearRange()

Worksheets("Sheet1").[A1:B5].ClearContents

End Sub

Sub SetValue()

[MyRange].Value = 30

End Sub

引用命名区域

用名称比用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

相对于其他单元格来引用单元格

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

Sub Underline()

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

End Sub

注意可录制使用Offset 属性（而不是绝对引用）的宏。在“工具”菜单上，指向“宏”，再单击“录制新宏”，然后单击“确定”，再单击录制宏工具栏上的“相对引用”按钮。

若要在单元格区域中循环，请在循环中将变量与Cells 属性一起使用。下例以 5 为步长，用 5 到100 之间的值填充第三列的前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

用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

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

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

Sub ClearSheet()

Worksheets("Sheet1").Cells.ClearContents

End Sub

引用多个单元格区域

使用适当的方法可以很容易地同时引用多个单元格区域。可用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

VBA - Excel编程概念之:【单元格和区域】二、在单元格区域中循环

在单元格区域中循环

使用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

VBA - Excel编程概念之:【单元格和区域】三、选定和激活单元格

选定和激活单元格

使用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

VBA - Excel编程概念之:【单元格和区域】四、处理三维区域

如果要处理若干工作表上相同位置的单元格区域，可用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

VBA - Excel编程概念之:【单元格和区域】五、处理活动单元格

处理活动单元格

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