第4章 常用对象 【上】

概述：

你要做什么，作用的对象是什么？

1 与Excel交流，需要熟悉的常用对象

2、 Application对象

2.1 ScreenUpdating 属性

是否保存中间结果

Option ExplicitSub inputTest()    Cells.ClearContents  ' 清除表中所有数据        Range("A1:A10") = 100   '在A1：A10单元格输入数值        MsgBox "刚才在A1：A10输入数值100，你能看到结果吗 ？" '提示框        Range("B1:B10") = 200        MsgBox "刚才在B1：B10输入数值200，你能看到结果吗？"    End Sub

如果想要最后显示结果写入单元格中，可以用 Application.ScreenUpdating = False     

False: 表示关闭屏幕更新，也就是结果不会立即刷新出来，True刚好相反（默认就是True）

2.2、DisplayAlerts属性

取消确认框，提示框，直接操作

Option ExplicitSub delSht() Dim sht As Worksheet    '定义工作表变量  For Each sht In Worksheets    '循环所有工作表     If sht.Name <> ActiveSheet.Name Then  '如果不是活动工作表就是删除            sht.Delete            End If     Next sht End Sub

取消提示警告框 ： Application.DisplayAlerts = False   (这个不显示警告信息，注意慎重用，删除数据不好恢复)  默认为True，记得改完之后改回来

Option ExplicitSub delSht() Dim sht As Worksheet    '定义工作表变量  Application.DisplayAlerts = False  '取消提示框  For Each sht In Worksheets    '循环所有工作表     If sht.Name <> ActiveSheet.Name Then  '如果不是活动工作表就是删除            sht.Delete            End If     Next sht  Application.DisplayAlerts = True  '开启提示框End Sub

2.3、 EnableEvents属性

可以通过设置Application对象的EnableEvents属性来启用或禁用事件

自动写入单元格地址

Option ExplicitPublic Sub Worksheet_SelectionChange(ByVal Target As Range)        Target.Value = Target.Address        End Sub

效果：

如果要禁用事件： Application.EnableEvents = False 

注意禁用之后要开启： Application.EnableEvents = True

Public Sub Worksheet_SelectionChange(ByVal Target As Range)        Application.EnableEvents = False        Target.Value = Target.Address        Application.EnableEvents = TrueEnd Sub

2.4、WorksheetFunction属性

通过WorksheetFunction调用工作表函数

2.5、它的子对象

3、管理工作簿，了解Workbook对象

3.1 Workbook 与 Workbooks

Workbook表示单个对象， Workbooks对象集合

怎么引用单个工作簿

方法一： 利用索引号引用工作簿

语法： Workbooks.Item(3)

简写： Workbooks(3)

方法二：利用工作簿名引用工作簿

语法： Workbooks("Book1")

Workbooks("Book1.xls") 如果保存之后系统显示后缀，必须加上后缀。其它情况可以不用加

3.2 认识Workbook，需要了解的信息

Name: 文件名称

Path : 文件路径

FullName:  Name+Path  (全路径)

Sub testWorkbook()    Range("B2") = ThisWorkbook.Name        Range("B3") = ThisWorkbook.Path        Range("B4") = ThisWorkbook.FullNameEnd Sub

3.3、实际操作，都能做什么

创建一个工作簿文件

Workbooks.Add "路径"    

Sub testWorkbook()   Workbooks.Add      ActiveWorkbook.SaveAs "D:\1.xls"End Sub

其实好像都是当前活动工作簿，另存为达到创建目的。应该还有更好的方法，只是还不知道

打开工作簿

语法：Workbooks.Open  "路径"

例子： Workbooks.Open "D:\1.xls"

激活工作簿

语法：Workbooks("工作簿名称").Activate

Workbooks("Book1").Activate

保存工作簿

ThisWorkbook.Save   （表示保存当前工作簿）

ThisWorkbook.SaveAs Filename :="路径" 

简写：ThisWorkbook.SaveAs "路径"  

还有：ThisWorkbook.SaveCopyAs "路径"   

两者的区别是，saveAs 另外新文件同时打开这个新的文件，SaveCopyAs 它不会打开新的文件，但是它会新增其它工作簿

关闭工作簿

Workbooks.Close  '关闭所有工作簿

Workbooks("Book1").Close   '关闭Book1

如果保存修改值，可以在后面加上True

Workbooks("Book1").Close True

3.4、ThisWorkbook 与ActiveWorkbook

ThisWorkbook是程序所在工作簿的引用，ActiveWorkbook是对活动工作簿的引用

4、操作工作表，认识worksheet对象

4.1 认识Worksheet对象

Worksheets是集合，Worksheet单个对象  这个和工作簿类似

引用工作簿第一张表

Worksheets.Item(1)

Worksheets(1)    (通过索引)

Worksheets("Sheet1") 

4.2、操作工作表

新建工作表：

语法： Worksheets.Add  

指定位置新增工作表

Worksheets.Add before:=Worksheets(1)  在第一张工作表前插入一张新工作表

Worksheets.Add after:=Worksheets(1)   在第一张工作表后插入一张新工作表

Worksheets.Add Count:=3    在活动工作表前插入3张工作表

Worksheets.Add before:=Worksheets(Worksheets.Count), Count:=2 在最后一张工作表前插入两张工作表

更改工作表标签名称

Worksheets(2).Name = "工资表"      '更改第二张工作表的标签名称为“工资表”

ActiveSheet.Name = "工资表"  当前活动工作名称修改为“工资表”  

Worksheets.Add(before:=Worksheets(1)).Name="工资表"     ’ 在第一张工作表前面插入名称为”工资表”的工作表

删除工作表

Worksheets("Sheet1").Delete   '删除Sheet1工作表

激活工作表

Worksheets(1).Activate      激活第一张工作表

Worksheets(1).Select   激活第一张工作表

当工作表隐藏时，调用它的Select会出错，用Activate方法不用同时选中多张工作表，但用Select方法可以同时选中未隐藏的多张工作表

复制工作表

Worksheets("工资表").copy before:= Worksheets("出勤登记表")    '复制工资表到出勤登记表前面

简单：Worksheets("工资表").Copy  

移动工作表

这个与复制工作表类似。只要把Copy改为Move

Worksheets("工资表").Move before:=Worksheets ("出勤登记表")     '将“工资表”移动到 “出勤登记表前面”

Worksheets("工资表").Move  表示移动新的工作簿中。

隐藏或显示工作表

Worksheets("工资表").Visible = False

Worksheets("工资表").Visible= xlSheetHidden

Worksheets("工资表").Visible = 0

以上三句都是隐藏

Worksheets("工资表").Visible = xlSheetVeryHidden

Worksheets("工资表").Visible = 2    

以上两句都是隐藏，但是这个必须通过VBA代码取消隐藏或属性窗口

显示工作表

Worksheets("工资表").Visible = True

Worksheets("工资表").Visible=xlSheetVisible

Worksheets("工资表").Visible=1

Worksheets("工资表").Visible = -1

获取工作表的数量

Worksheets.Count

4.3、Sheets 与 Worksheets

Sheets:包含Worksheets,还有其它类型表，例如图表

Worksheets:只是普通工作表集合

5、核心，至关主要的Range对象

5.1、 多种方法引用Range对象

Worksheets("Sheet1").Range("A1").Value = 50  '将工作表Sheet1中单元格A1的值为50

如果不写就是当前活动工作表

Range("A1:A10").Value=200    '在活动工作表A1：A10输入数值为200

参数可以字符串， 当然你可以为特定区域设置名称。

Range ("名称") .Value = 100   '为你设置名称区域单元格设置值为100

引用不连续的多个区域

Range("A1:A10, A4:E6,C3:D9").select       '选中单元格区域（三个区域），分割为逗号

若是取交集，也就是重叠区域，分割符为空格

Range("B1:B10 A4:D6").Select     '选中多个单元格区域的交集

包围所有区域的最小方块

Range("B6:B10", "D2:D8")  '注意逗号隔开， 选中区域为B2:D10

Worksheet(或Range) 对象的Cells属性

ActiveSheet.Cells(3,4).Value = 20  "在第3行与第4列的相交的单元格输入20"

ActiveSheet.Cells(3,“D”).Value = 20  "在第3行与第D列的相交的单元格输入20"

Range("B3:F9").Cells(2,3) = 100   相对坐标也就是 D4单元格

Range(Cells(1,1), Cells(10,5)).Select     选中活动工作表的A1：E10单元格

Range("A1", "E10").Select 或 Range (Range("A1"), Range("E10)).Select    选中活动工作表的A1：E10单元格

ActiveSheet.Cells(2).Value=200      活动工作表的第2个单元格输入200

注意编号顺序： 从左往右， 从上到下的顺序编号

Range("B3:F9").Cells(8).Value = 100   在B3：F9的第8个单元格输入100

当序号超过区域最大编号，会扩展新行，列数不变

ActiveSheet.Cells.select       选中活动工作表中的所有单元格

Range("B3:F9").Cells.select   选中活动工作表中的B3：F10单元格区域

更加简短的快捷方式

[B2]        B2 单元格

[A1:D10]     A1:D10单元格

[A1:A10, C1:C10,D1:E10]   三个区域的并集

[B1:B10 A5:D5]   两个单元格区域的交集，也就是重叠部分

[n]     名称为n代表的单元格

注意：只限于常量，不能用于变量

5.2、还可以怎样得到单元格

引用整行

ActiveSheet.Rows("3:3").Select    选中活动工作表的第3行

ActiveSheet.Rows("3:5").Select    选中活动工作表第3行到第5行

ActiveSheet.Rows(3).Select   选中活动工作表第3行

ActiveSheet.Rows.Select    选中工作表的所有行

Rows("3:10").Rows("1:1").Select   选中第3行到第10行区域中的第1行

引用整列

ActiveSheet.Columns("F:F")   选中活动工作表中的F列

ActiveSheet.Columns(6)     选中活动工作表的第6列

ActiveSheet.Columns     选中工作表中的所有列

Columns("B:G").Columns("B:B").Select    选中B:G列区域中的第2列

Application 对象的Union方法

也就是将多个区域当成一区域对待

Application.Union(Range("A1:A10"), Range("D1:D5")).Select

Range对象的Offset属性

Range("A1").offset(2,3).Value = 500  (偏移，向下移动2行（如果是负数就相反方向），向右移动3列（如果是负数就相反方向） 也就是D3单元格赋值为500)

Range对象的Resize属性

Range("B2").Resize(5,4).Select    将B2单元格扩大为B2：E6 （5行4列）

Range("B2:E6").Resize(2,1).Select    将B2：E6单元格缩小为B2：B3    （两行一列）

Worksheet对象的UsedRange属性

ActiveSheet.UsedRange.Select      选中活动工作表中已经使用的单元格区域  

Range对象的CurrentRegion属性

CurrentRegion属性返回当前区域，即以空行和空列的组合为边界的区域

Range("B5").CurrentRegion.Select

Range对象的End属性

返回当前区域结尾处的单元格

Range("C5").End(xlUp).Select    最上面一个单元格

xlToLeft 向左移动

xlToRight 向右移动

xlUp    向上移动

xlDown 向下移动

ActiveSheet.Range("A65536").End(xlUp).Offset(1,0).Value=100   在A列最后一行增加值为100 ，如果第一个出现A1没有值就会有问题

改变这一状况，需要判断是否第一个是否有值

Sub testWorkbook()    Dim c As Range          Set c = ActiveSheet.Range("A65536").End(xlUp)          If c.Value <> "" Then    '不为空表示当前单元格有值需要偏移下一个                Set c = c.Offset(1, 0)          End If          c.Value = 100End Sub

Sub UsedTest()    Dim xrow As Long          '使用的行数加一,这个有可能A列不是行数最多，这个有问题的    xrow = ActiveSheet.UsedRange.Rows.Count + 1        Cells(xrow, "A").Value = 100    End SubSub currTest()    Dim xrow As Long        xrow = Range("A1").CurrentRegion.Rows.Count + 1        Cells(xrow, "A") = 100End Sub

5.3、操作单元格，还需要了解什么

单元格里的内容，Value属性

Range("A1:B2").Value = "abc"  在A1：B2输入abc

单元格个数，Count一下就知道

Range("B4:F10").Count   计算 B4：F10的单元格式的数量

ActiveSheet.UsedRange.Rows.Count   求活动工作表中已使用的行数

ActiveSheet.UsedRange.Columns.Count  求活动工作表已使用的列数

单元格地址，Address属性

Selection.Address 选中区域的地址

5.4、 亲密接触，操作单元格

选中单元格，Activate与Select方法

ActiveSheet.Range("A1:B10").Select

ActiveSheet.Range("A1:B10").Activate

选择性清除单元命令清除它们

Range("A1").Clear    清除所有

Range("A1").ClearComments  清除单元格批注

Range("A1").ClearContents  清除单元内容

Range("A1").ClearFormats  清除单元格式

复制单元格区域

Range("A1").Select   '选中A1单元格Selection.Copy       '复制选中单元格Range("C1").Select   '选中C1单元格ActiveSheet.Paste    '粘贴动作

简写：

Range("A1").Copy   Range("C1")

或：Range("A1").Copy Destination:=Range("C1")

WorkSheets("Sheet1").Range("A1:A10").Copy WorkSheets("Sheet2").Range("B1:B10")   将工作表sheet1 的A1:A10 复制到sheet2的B1：B10

如果不确定单元格区域大小，指定目标区域最左上角单元格

Range("A1").CurrentRegion.Copy   Range("G1")

复制数值，不复制格式

Sub Rng_Copy_1()

Range("A1:D10").Copy    

Range("F1:I10").PasteSpecial Paste:=xlPasteValues

End Sub

或者

Sub Rng_Copy_2()

Range("F1:I10").Value = Range("A1:D10").Value

End Sub

剪切单元格

Sub RngCut()

Range("A1:E5").Cut Destination:=Range("G1")   ' 把A1:E5剪切到G1:K5

Range("A6:E10").Cut Range("G6")       ' 把A6：E10剪切到G6：K10

End Sub

删除单元格

Range("B5").Delete Shift:=xlToLeft    删除B5单元格，删除后右侧单元格左移

Range("B5").Delete Shift:=xlUp           删除B5单元格，删除后下方单元格上移

Range("B5") .EntireRow.Delete          删除B5单元格所在的行

Range("B5").EntireColumn.Delete      删除B5单元格所在的列

Range("B5").Delete   相当于 Range("B5").Delete Shift:=xlUp

6、不止这些，其他常见的对象

6.1、 名称、就是名字

Excel中定义的名称就是给单元格区域（或数值常量、公式）取的名字

ActiveWorkbook.Names.Add Name:="date", RefersToR1C1:="=Sheet1!R5C[-2]"

R5C[-2] : "R"后面的数字代表行号，“C” 后面的数字代表列号。指定行与指定列相交的单元格

C[-2] 中的[]是什么？

[] 表示相对引用，没有加称之为相对引用

R5表示工作表中的第5行，C[-2]表示活动单元格左边的第二列

R[2]C[3] ： 对活动单元格下方的第2行与右面第3列相交的单元格的引用。

R2C3: 对工作表第2行与第3行相交的单元格的引用

可以使用A1样式的引用

ActiveWorkbook.Names.Add Name:="date" , RefersTo:="=Sheet1!$B$4" (不加$就是相对引用，反之就是绝对引用)

定义名称，更简单的方式

Range("A1:C10").Name = "date"

怎样引用名称

ActiveWorkbook.Names("date").Name = "姓名"      '更改名称名

ActiveWorkbook.Names("date").RefersTo  = "点点滴滴"     '更改名称的值

Sub d()    Dim i As Integer, mx As Integer    mx = ActiveWorkbook.Names.Count           '统计一共有多少个名称        For i = 1 To mx        ActiveWorkbook.Names(i).Visible = False      '隐藏名称    Next iEnd Sub

6.2、单元格批注，Comment对象

给单元格添加批注

Sub d()  Range("B5").AddComment Text:="VBA用的666"End Sub

怎么知道单元格是否有批注

Range("B5").Comment Is Nothing 判断B5是否存在批注

Sub d()   If Range("B5").Comment Is Nothing Then        MsgBox "B5单元格没有批注！"       Else        MsgBox "B5单元格中有批注了"       End IfEnd Sub

Range("B5").Comment.Text = "更改过的批注"     更改批注的内容

Range("B5").Comment.Visible = False    隐藏批注

Range("B5").Comment.Delete                 删除批注

6.3、 给单元格化妆

Sub d() ' 字体设置   With Range("A1:L1").Font          .Name = "宋体"                   '字体为宋体       .Size = 12                       '字体大小       .Color = RGB(255, 0, 0)          '字体颜色为红色       .Bold = True                     '加粗       .Italic = True                   '斜体       .Underline = xlUnderlineStyleDouble  '双下划线          End With      '给单元格添加底纹   Range("A1:L1").Interior.Color = RGB(255, 255, 0) '添加黄色底纹      '给表格设置边框      With Range("A1").CurrentRegion.Borders         .LineStyle = xlContinuous       '设置单线边框      .Color = RGB(0, 0, 255)         '设置边框的颜色      .Weight = xlHairline            '设置边框线条样式   End With      '如果不知其他设置采用宏进行录制然后修改End Sub