学习笔记

3.EXCEL VBA 常用对象

1.需要熟悉的常用对象

Excel中的对象总是层次分明地组织在一起,一个对象可以包含其他对象,这种对象排列模式称为对象模型,Excel中的所有对象都可以在对象模型中找到。

适用于 Visual Basic for Applications (VBA) 的 Excel 对象模型 | Microsoft Learn

https://learn.microsoft.com/zh-cn/office/vba/api/overview/excel/object-model

VBA通过操作不同的对象来控制Excel,作为一个Excel用户,每天都在重复打开、关闭工作簿、输入、清除单元格内容的操作,这些操作都是在操作对象

退出Excel程序:操作Application对象

新建工作簿:操作Workbook对象

删除工作表:操作Worksheet对象

设置边框:操作Range对象

设置字体:操作Font对象

实际上,VBA程序就是用代码记录下来的一个或一组操作,例如想在Sheet1工作表的A1单元格输入数值100,完整的代码为:

Sub test()
Application.Worksheets("Sheet1").Range("A1").Value = 100
End Sub

无论是用动作还是用代码,都是在操作对象,所以,编写VBA程序,就是利用VBA语句引用对象并有目的的操作它。

应该记住哪些对象

并不用记住所有的对象,只需要熟悉它的结构和组成,记住经常的即可,有必要时可以查询AI语言模型。

常用的对象有:

对象代表示例
Application整个Excel应用程序Application.Quit
Workbook整个工作簿Workbooks.Add
Worksheet工作表Worksheets(“Sheet1”).Active
Range一个单元格 或 一片单元格区域Range(“A1″).Value=”Hello”
Cell单个单元格Cells(1,1).Value=Hello

Cells是Worksheet对象的一个属性,返回一个Range对象,代表工作表的所有单元格,可以用来访问单个单元格或单元格区域

在VBA中,我们通常使用Cells属性来访问单个单元格,Cells允许我们使用行号和列好来直接访问单元格,Cells可以轻松地用于循环和动态引用,Excel VBA中没有直接的”Cell”对象,而Cells是一个广泛使用的属性。

2.最顶层的Application对象

最顶端的Application对象是起点、它代表Excel程序本身,Excel里所有对象都以它为起点,实际编程时,会经常用到它的属性与方法

通过Application对象,可以

控制Excel的外观和行为

访问所有打开的工作簿

执行全局操作,如计算或打印

设置Excel的各种选项

ScreenUpdating属性

ScreenUpdating属性的默认值为True,如果设置为False,Excel会暂停屏幕更新,不会将计算结果显示到屏幕上,直到重新设置为True或代码执行完毕

据介绍,当有大量操作时,关闭屏幕更新可以提高代码执行熟读,执行多个连续的视觉变化时,关闭屏幕更新可以防止屏幕闪烁,如果不希望用户看到中间步骤,只想展示最终结果,也可以关闭屏幕更新

以下示例比较了开启和关闭ScreenUpdating时,在1000个单元格中填入数值所需的时间(实测关闭时也可能耗时更久?)

Sub DemoScreenUpdating()
    Dim i As Long
    Dim startTime As Double
    Dim endTime As Double
    
    ' 使用ScreenUpdating = True
    startTime = Timer
    Application.ScreenUpdating = True
    For i = 1 To 1000
        Cells(i, 1).Value = i
    Next i
    endTime = Timer
    MsgBox "With ScreenUpdating On: " & (endTime - startTime) & " seconds"
    
    ' 清除单元格
    Range("A1:A1000").Clear
    
    ' 使用ScreenUpdating = False
    startTime = Timer
    Application.ScreenUpdating = False
    For i = 1 To 1000
        Cells(i, 1).Value = i
    Next i
    Application.ScreenUpdating = True
    endTime = Timer
    MsgBox "With ScreenUpdating Off: " & (endTime - startTime) & " seconds"
End Sub
注意事项

始终记得在代码结束时将ScreenUpdating设置会True,否则Excel将保持屏幕冻结状态

如果代码出错并中断执行,ScreenUpdating可能保持关闭状态,可以在立即窗口中通过命令【Application.ScreenUpdating = True】来恢复。

DisplayAlerts属性

Application对象的DisplayAlerts属性决定在程序运行过程中是否显示警告信息,默认值为True,如果设置为False,Excel会自动选择默认操作,不弹出警告。

关闭DisplayAlerts可能导致意外操作,使用时要小心,处理重要数据时,还是尽量保留警告信息,始终记得在代码结束时重新开启DisplayAlerts。

尝试删除除活动工作表外所有其他工作表

Sub DelSht()
    ' 声明一个Worksheet类型的变量sht,用于在循环中引用每个工作表
    Dim sht As Worksheet
    
    ' 使用For Each循环遍历工作簿中的所有工作表
    For Each sht In Worksheets
        ' 检查当前工作表(sht)的名称是否与活动工作表的名称不同
        If sht.Name <> ActiveSheet.Name Then
            ' 如果不同,则删除该工作表
            sht.Delete
        End If
    ' 移动到下一个工作表
    Next
End Sub

程序运行后会出现,每次删除工作表时,都会出现一个确认对话框,点击删除后才会执行删除操作

如果想要取消警告对话框,只需要在开头将DisplayAlerts属性设置为False,代码结束后再重新设为True即可

Sub DelSht()
    Dim sht As Worksheet
    Application.DisplayAlerts = False	'关闭确认对话框
    For Each sht In Worksheets
        If sht.Name <> ActiveSheet.Name Then
            sht.Delete
        End If
    Next
    Application.DisplayAlerts = True	'重新开启确认对话框
End Sub

EnableEvents属性

可以通过Application对象的EnableEvents属性来启用或禁用事件,“事件”是能被Excel认识的一个操作动作,比如打开保存关闭工作簿、更改工作表、选择单元格、选择图表元素等都是“事件”。

用户可以编写不同的代码来相应这些事件,当触发某个事件时,自动执行特定代码。

EnableEvents是一个控制是否启用事件处理的属性,通过True/False来启用或关闭

EnableEvents当设置为True时,Excel能正常相应各种事件(如工作表变化、单元格选择等),当设置为False时,Excel会暂时停止相应这些事件

EnableEvents的作用:

防止事件循环:某些情况下,代码可能会触发事件,而这些事件又会再次运行代码,造成无限循环,禁用事件可以防止这种情况。

某哦写情况下,希望阻止某些事件触发,也可以使用,以便更好地控制代码的执行流程。

自动写入单元格地址

当选中一个单元格时,自动在单元格中写入该单元格的地址

Private Sub Worksheet_SelectionChange(ByVal Target As Range)
    Target.Value = Target.Address
    '返回的是绝对引用的地址,带有$号
End Sub
'Target变量代表用户当前选中的单元格

如果不希望写入的地址为绝对引用格式,也就是不希望单元格地址带有$号,代码可以是:

Private Sub Worksheet_SelectionChange(ByVal Target As Range)
    If Target.Cells.Count = 1 Then
        Target.Value = Target.Address(RowAbsolute:=False, ColumnAbsolute:=False)
    End If
End Sub

什么是禁用事件

禁用事件就是执行操作后不让事件发生,如果禁用了事件,Sub与End Sub之间的代码并不会运行,在VBA中,可以设置Application对象的EnableEvents的属性为False来禁用事件

Application.EnableEvents = False

WorksheetFunction属性

VBA里有许多内置函数可以使用,但在实际应用中,并不是所有问题都能找到合适的函数解决。

WorksheetFunction属性是Application对象的一个属性,它提供了对Excel内置工作表函数的访问,也就是可以在VBA中使用熟悉的Excel函数

基本语法是【Application.Worksheet.函数名(参数1,参数2,…)】

需要注意的是:如果VBA里的函数与EXCEL中的函数同名,那就只能使用VBA中的函数,不能再通过application.WorksheetFunction

案例:使用VLOOKUP函数

Sub vl()
    Dim result As Variant
    result = Application.WorksheetFunction.VLookup("苹果", Range("A1:B10"), 2, False)
    MsgBox "查找结果是:" & result
End Sub

案例:使用IF函数

Sub IfExample()
    Dim score As Integer
    Dim result As String
    
    score = 85
    result = Application.WorksheetFunction.If(score >= 60, "及格", "不及格")
    MsgBox "结果是: " & result
End Sub

案例:统计指定单元格区域内,值大于1000的单元格个数

Sub CountTest()
    Dim mycount As Integer, rng As Range
    For Each rng In Range("A1:A100")
        If rng.Value > 1000 Then mycount = mycount + 1
    Next
    MsgBox "大于1000个个数为" & mycount
End Sub

给Excel梳妆打扮

可以设置Application对象的某些属性来更改Excel界面,如果你不想看见Excel的某个组件,还可以将其隐藏

功能区Ribbon

Application.DisplayFullScreen = True  ' 全屏模式,隐藏功能区
Application.DisplayFullScreen = False ' 退出全屏模式

Application.ExecuteExcel4Macro "SHOW.TOOLBAR(""Ribbon"",False)" ' 隐藏功能区
Application.ExecuteExcel4Macro "SHOW.TOOLBAR(""Ribbon"",True)"  ' 显示功能区

工作表标签

Application.DisplayWorkbookTabs = False ' 隐藏工作表标签
Application.DisplayWorkbookTabs = True  ' 显示工作表标签

滚动条

ActiveWindow.DisplayHorizontalScrollBar = False ' 隐藏水平滚动条
ActiveWindow.DisplayVerticalScrollBar = False   ' 隐藏垂直滚动条

ActiveWindow.DisplayHorizontalScrollBar = True  ' 显示水平滚动条
ActiveWindow.DisplayVerticalScrollBar = True    ' 显示垂直滚动条

网格线

ActiveWindow.DisplayGridlines = False ' 隐藏网格线
ActiveWindow.DisplayGridlines = True  ' 显示网格线

行列标题

ActiveWindow.DisplayHeadings = False ' 隐藏行列标题
ActiveWindow.DisplayHeadings = True  ' 显示行列标题

窗口大小和位置

Application.WindowState = xlMaximized ' 最大化窗口
Application.WindowState = xlMinimized ' 最小化窗口
Application.WindowState = xlNormal    ' 恢复正常大小

' 自定义窗口位置和大小
With Application.WindowState
    .Top = 100
    .Left = 100
    .Width = 800
    .Height = 600
End With

警告和消息

Application.DisplayAlerts = False ' 禁用警告消息
Application.DisplayAlerts = True  ' 启用警告消息

屏幕更新

Application.ScreenUpdating = False ' 禁用屏幕更新,提高宏的运行速度
' 执行一些操作
Application.ScreenUpdating = True  ' 重新启用屏幕更新

自定义菜单栏和工具栏

' 添加自定义菜单
Dim newMenu As CommandBarControl
Set newMenu = Application.CommandBars("Worksheet Menu Bar").Controls.Add(Type:=msoControlPopup, Temporary:=True)
newMenu.Caption = "自定义菜单"

' 添加菜单项
Dim menuItem As CommandBarButton
Set menuItem = newMenu.Controls.Add(Type:=msoControlButton)
menuItem.Caption = "新功能"
menuItem.OnAction = "NewFunction"  ' NewFunction 是一个自定义的 Sub 过程

她和她的孩子们

把对象模型这本家谱打开,Application是家族的起点,开枝散叶,不同的孩子住在不同的地方

可以通过引用Application对象的属性返回不同的子对象

引用对象必须把每一级的对象名称写清楚,如:
Application.Workbooks(“Book1”).Worksheets(“Sheet1”).Range(“A1”)

但对于某些特殊的对象却不需要那么严谨,比如想在当前选中的单元格里输入数值300,因为“选中的单元格”是一个特殊的对象,所以代码可以写为【Application.Selection.Value=300】,甚至可以省略‘Appliction’,直接写【Selection.Value=300】

除了Selection,还可以使用其他属性来引用某些特殊对象,如:

属性说明
ActiveCell当前活动单元格
ActiveChart当前活动工作簿中的活动图表
ActiveSheet当前活动工作簿中的活动工作表
ActiveWindow当前活动窗口
ActiveWorkbook当前活动工作簿
Charts当前活动工作簿中所有图表工作表
Selection当前活动工作簿中所有选中的对象
Sheets当前活动工作簿中所有Sheet对象,包括普通工作表、图表工作表、Ms Excel 4.0宏工作包和Ms Excel 5.0对话框工作表
Worksheets当前活动工作簿中所有Worksheet对象(普通工作表)
Workbooks当前所有打开的工作簿
对象/属性描述示例代码
ApplicationExcel应用程序的顶级对象Application.Caption = “我的Excel”
Workbooks所有打开的工作簿的集合Set wb = Application.Workbooks(“Book1.xlsx”)
ActiveWorkbook当前活动的工作簿Set activeWB = Application.ActiveWorkbook
Worksheets工作簿中所有工作表的集合Set ws = Application.Worksheets(“Sheet1”)
ActiveSheet当前活动的工作表Set activeWS = Application.ActiveSheet
Range单元格或单元格区域Set rng = Application.Range(“A1:B10”)
Selection当前选中的单元格范围Set sel = Application.Selection
Charts工作簿中所有图表的集合Set cht = Application.Charts(“Chart1”)
Windows所有打开的Excel窗口的集合Set win = Application.Windows(1)
DisplayFullScreen控制全屏模式Application.DisplayFullScreen = True
DisplayFormulaBar控制公式栏的显示Application.DisplayFormulaBar = False
StatusBar控制状态栏的显示和内容Application.StatusBar = “处理中…”
ScreenUpdating控制屏幕更新Application.ScreenUpdating = False
DisplayAlerts控制警告消息的显示Application.DisplayAlerts = False
Caption设置Excel窗口的标题Application.Caption = “自定义Excel”

3.管理工作簿,了解Workbook对象

Workbook与Workbooks

Workbook代表一个工作簿

Workbooks表示当前打开的所有工作簿,即工作簿集合

引用工作簿,就是指明工作簿的位置及名称

引用工作簿的方法有两种

方法1:利用索引号引用工作簿

第一个打开的工作簿,索引号是1,第二个打开的工作簿,索引号是2,以此类推

如果要引用Workbooks集合里的第三个Workbook,可以使用代码:

Workbooks.Items(3)

也可以省略Item,直接简写为:Workbooks(5)

方法2:利用工作簿名引用工作簿

如果不能确定索引号,使用工作簿的名称引用会更准确一些

对于已经保存的工作簿,通常需要包含文件扩展名,例如【Workbooks(“销售报表.xlsx”)】

对于新建未保存的工作簿,这个工作簿没有扩展名,Excel会自动为其分配一个临时的名称,如“Book1”、“工作簿1”等,引用时不需要加扩展名,例如【Workbooks(“工作簿1”)】

认识Workbook,需要了解的信息

Workbook对象有几个重要的属性,可以帮助我们获取Excel文件的信息:

Name属性:获取文件名(包括扩展名)

Path属性:获取文件所在的路径(不包括文件名)

FullName属性:获取文件的完整路径(包括路径和文件名)

Sub GetWorkbookInfo()
    Dim wb As Workbook
    Set wb = ThisWorkbook ' 使用当前工作簿
    
    ' 获取文件名
    MsgBox "文件名: " & wb.Name
    
    ' 获取文件路径
    MsgBox "文件路径: " & wb.Path
    
    ' 获取完整文件名(包括路径)
    MsgBox "完整文件名: " & wb.FullName
End Sub
常用属性表
属性名描述示例
Name返回工作簿的名称Debug.Print ActiveWorkbook.Name
Path返回工作簿的路径Debug.Print ActiveWorkbook.Path
FullName返回工作簿的完整路径和文件名Debug.Print ActiveWorkbook.FullName
Sheets返回工作簿中所有工作表的集合Debug.Print ActiveWorkbook.Sheets.Count
Worksheets返回工作簿中所有工作表的集合
Debug.Print ActiveWorkbook.Worksheets.Count(输出当前工作簿中工作表的数量)
ActiveSheet返回活动工作表Debug.Print ActiveWorkbook.ActiveSheet.Name
FileFormat返回或设置文件格式Debug.Print ActiveWorkbook.FileFormat
ReadOnly返回工作簿是否为只读Debug.Print ActiveWorkbook.ReadOnly
Saved返回工作簿自上次保存后是否有更改Debug.Print ActiveWorkbook.Saved
常用方法表
方法名描述示例
Save保存工作簿ActiveWorkbook.Save
SaveAs以指定名称保存工作簿ActiveWorkbook.SaveAs Filename:=”C:\NewFile.xlsx”
Close关闭工作簿ActiveWorkbook.Close SaveChanges:=True
Activate激活工作簿Workbooks(“Book1.xlsx”).Activate
Protect保护工作簿结构和窗口ActiveWorkbook.Protect Password:=”123″
Unprotect解除工作簿保护ActiveWorkbook.Unprotect Password:=”123″
AddWorksheet添加新工作表ActiveWorkbook.Worksheets.Add
DeleteWorksheet删除指定工作表ActiveWorkbook.Worksheets(“Sheet1”).Delete
OpenLinks更新工作簿中的链接ActiveWorkbook.UpdateLinks
PrintOut打印工作簿ActiveWorkbook.PrintOut

实际操作,都能做什么

创建一个工作簿文件

【Workbooks.Add】不带任何参数(也不需要括号),将创建1个新的空白工作簿

Add方法也可以设置参数,比如设置文件路径

Workbooks.Add “C:\新建文件夹\1.xls”

路径参数没有括号,如果后面加上路径参数,含义是创建一个新的工作簿,并尝试使用指定路径的文件作为模板

还可以通过参数指定新建工作簿中包含的工作类型

Workbooks.Add xlWBATChart ‘参数告诉VBA,新建的工作簿包含1张图表工作表

EXCEL有4种类型的工作表,可以右键sheet标签,然后选择【插入】,在弹出的对话框里能看到,除了正常的工作表,还有图表、MS EXCEL 4.0宏工作表和MS EXCEL 5.0对话框工作表

如果想让新建的工作簿包含指定类型的工作表,可以使用:

xlWBATWorksheet:普通工作表

xlWBATChart:图表工作表

xlWBATExcel4MacroSheet:MS Excel 4.0宏工作表

xlWBATExcel4IntlMacroSheet:MS Excel 4.0国际宏工作表

打开工作簿

打开Excel文件可以使用Workbooks的Open方法

Workbooks.Open Filename:=”文件路径”

参数名称可以不写,代码可以简写为:Workbooks.Open “文件路径”

除了Filename参数,Open方法还有14个参数,让用户决定以何种方式打开指定文件

参数名数据类型说明默认值
FilenameString要打开的文件的名称必需参数
UpdateLinksVariant指定如何更新链接xlUpdateLinksUserSetting
ReadOnlyVariant是否以只读方式打开文件FALSE
FormatVariant文件格式(用于非Excel文件)
PasswordString打开受保护文件的密码
WriteResPasswordString写保护密码
IgnoreReadOnlyRecommendedVariant是否忽略”推荐以只读方式打开”的设置FALSE
OriginVariant文件的来源(用于文本文件)xlWindows
DelimiterVariant分隔符(用于文本文件)
EditableVariant是否可编辑(用于查询表)TRUE
NotifyVariant文件被修改时是否通知FALSE
ConverterVariant文件转换器的索引号
AddToMruVariant是否将文件添加到最近使用列表FALSE
LocalVariant是否以本地语言打开文件FALSE
CorruptLoadVariant如何处理损坏的文件xlNormalLoad
激活工作簿

打开了多个工作簿文件,但同一时间只能有一个窗口是活动的,

调用Workbooks对象的Activate方法可以激活一个工作簿

Workbooks(“工作簿名称”).Activate

如果工作簿已保存,工作簿名称需要加上扩展名,新建未保存的工作簿只需要名称

保存工作簿

调用Workbooks对象的Save方法可以保存工作簿

ThisWorkbook.Save

如果想将文件另存为一个新的文件,或者是第一次保存一个新建的工作簿,就用SaveAs方法

ThisWorkbook.SaveAs Filename:=”文件路径”

使用SaveAs方法将工作簿另存为新文件后,将自动关闭原文件,打开新文件,

如果希望继续保留原文件不打开新文件,可以使用SaveCopyAs方法

ThisWorkbook.SaveCopyAs Filename:=”文件路径”

关闭工作簿

关闭所有打开的工作簿:

Workbooks.Close

如果想关闭指定的某个工作簿文件,代码为:

Workbooks(“工作簿名称”).Close

如果工作簿被更改过而没有保存,关闭Excel工作簿前会有弹窗提示,如果不想显示该对话框,可以给Close方法设置参数

Workbooks(“工作簿名称”).Close savechanges:=True ‘关闭并保存修改

如果参数为False,则不保存直接关闭,

代码可以简写为Workbooks(“工作簿名称”).Close True/False

ThisWorkbook与ActiveWorkbook

同是Application对象属性,同是返回Workbook对象,但二者并不是等同的。

ThisWorkbook是对程序所在工作簿的引用,

ActiveWorkbook是对活动工作簿的引用

Sub t()
    Workbooks.Add
    MsgBox "代码所在工作簿为:" & ThisWorkbook.Name
    MsgBox "当前活动工作簿为:" & ActiveWorkbook.Name
    ActiveWorkbook.Close savechanges:=False
End Sub

4.操作工作表,认识Worksheet对象

认识Worksheet对象

Worksheet对象代表一张普通工作表

worksheets对象是多个Worksheet对象的集合,包含指定工作簿中所有的Worksheet对象

可以使用工作表的索引号或标签名称引用它

Worksheets.Item(1) ‘引用工作簿里的第一张工作表

Worksheets(1) ‘引用工作簿里的第一张工作表

Worksheets(“工作表名称”) ‘通过名称引用工作表

除此之外,还可以使用工作表的代码名称来引用工作表,在工程资源管理器或属性窗口,

工作表的代码名称通常是Sheet1、Sheet2这样的格式,而平时的工作表名称实际上是该工作表的标签名称,可以随意修改,

代码名称

使用代码名称引用工作表,只需直接写代码名称,比如在“Sheet1”的A1单元格输入10,代码为:

Sheet1.Range(“A1”)=100

查看工作表的代码名称,可以直接读取它的CodeName属性,如果读取活动工作表的代码名称,代码为:

msgbox ActiveSheet.Codename

操作工作表

新建工作表

使用Worksheets对象的Add方法,如果不带任何参数,将在活动工作表前新建一张工作表

worksheets.Add ‘插入一张新工作表

可以用参数给新建的工作表指定位置:

Worksheets.Add before:=Worksheets(1) ‘在当前工作簿的第一张工作表前添加一个新的工作表

如果想要在指定工作表后面插入,可以将before换成after

还可以同时插入多张工作表

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

编写程序时,可以同时使用多个参数

Worksheets.Add after:=Worksheets(1), Count:=6 ‘在当前工作簿的第一个工作表之后添加6个新的工作簿

更改工作表标签名称

可以通过Name属性来修改工作表的标签名称

Worksheets(2).Name=”测试” ‘将第二张工作表的标签名称改为指定内容

如果是新建的工作表,可以在程序中更改(也就是新建工作表后,趁着新工作表时活动工作表,修改活动工作表的标签名)

Sub t()
Worksheets.Add before:=Worksheets(1)
ActiveSheet.Name = "测试sssssssss"
End Sub

另一种代码格式:

Sub t()
Worksheets.Add(before:=Worksheets(1)).Name = "NEW"
End Sub

如果同时添加多张工作表,即Count参数值大于1时,并不能使用一句代码同时命名

Add方法有哪些参数

在代码窗口中,写完对象的方法名称后按空格,VBE会显示该方法的所有参数

删除工作表

使用Worksheet对象的Delete方法可以删除指定的工作表

Worksheets(“Sheet1”).Delete ‘删除Sheet1工作表

激活工作表

激活工作表就是将处于不活动状态的工作表设为活动工作表,可以使用Activate方法和Select方法

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

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

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

Activate 方法:

直接激活指定的工作表,使其成为活动工作表。

不影响工作表的选择状态。

可以在不可见的工作簿中使用。

通常更快,因为它只改变活动状态。

Select 方法:

选择指定的工作表,并使其成为活动工作表。

可以用于选择多个工作表(按住 Ctrl 键选择多个工作表时的效果)。

只能在当前活动工作簿中使用。

可能会稍微慢一些,因为它不仅改变活动状态,还改变选择状态。

复制工作表

使用Copy方法可以复制工作表

Worksheets(“工作表标签名”).Copy before:=Worksheets(“另一张工作表名”)

‘将指定工作表移动到另一张工作表前面,如果想要移到后面,可以将before换成after

如果不使用参数,默认将工作表复制到新的工作簿中(标签名保持不变)

Worksheets(“工作表标签名”).Copy

隐藏或显示工作表

可以设置工作表单Visible属性显示或隐藏该工作表

在编辑器的左边,就能找到工作表的属性框,提供1种显示属性和2种隐藏属性

xlVisible (值为-1):

这是默认值。

表示工作表在Excel窗口中是可见的。

用户可以直接查看和操作这个工作表。

xlSheetHidden (值为0):

表示工作表被隐藏。

用户无法直接看到这个工作表,但可以通过Excel的”取消隐藏工作表”功能重新显示它。

在VBA代码中仍然可以访问和操作这个工作表。

xlSheetVeryHidden (值为2):

表示工作表被深度隐藏。

这种隐藏级别比xlSheetHidden更高。

用户无法通过Excel的普通界面操作来显示这个工作表。

只能通过VBA代码来显示或操作这个工作表。

Sub ManageSheetVisibility()
    ' 隐藏工作表
    Worksheets("Sheet1").Visible = xlSheetHidden
    
    ' 深度隐藏工作表
    Worksheets("Sheet2").Visible = xlSheetVeryHidden
    
    ' 显示工作表
    Worksheets("Sheet3").Visible = xlVisible
End Sub

无论何种方式隐藏了工作表,想用代码显示它,可以用以下任意代码:
Worksheets(“标签”).Visible=True

Worksheets(“标签”).Visible=xlVisible

Worksheets(“标签”).Visible=1

Worksheets(“标签”).Visible=-1

获取工作表的数目

想知道当前工作簿中有几张工作表,可以读取Worksheets的Count属性值

MsgBox Worksheets.Count

Sheets与Worksheets

Sheets与Worksheets代表两种不同的集合,Excel里一共有4种不同类型的工作表,Sheets表示工作簿里所有类型的工作表的集合,而Worksheets仅表示普通工作表的集合

Excel中的4种工作表类型:

a) 普通工作表(Worksheet)

b) 图表工作表(Chart sheet)

c) 宏工作表(Macro sheet,在新版Excel中已不常用)

d) 对话框工作表(Dialog sheet,在新版Excel中已不常用)

Sheets与Worksheets集合里的对象都有标签名称(Name),代码名称(CodeName)、索引号(index)等属性,也都有Add、Delete、Copy和Move等方法,设置属性或调用方法的操作类似。

Sheets集合包含更多类型的工作表,所以其包含的方法和属性会比Worksheets更多。

至关重要的Range对象

Range对象代表工作表中的单元格或单元格区域,包含在Worksheets对象中

多种方法引用Range对象

操作单元格,需要先引用单元格

如果将某商品的销售数量100保存到活动工作簿中Sheet1工作表的A1单元格,代码为:
Worksheets(“Sheet1”).Range(“A1”).Value=100

Worksheet(或Range)对象的Range属性
Sub test()
Range("A1:A10").Value = 200 '参数是表示单元格地址的字符串

Dim n As String
n = "B1:B10"
Range(n) = 600
End Sub

如果单元格已经被定义为名称,参数还可以是表示名称名的字符串或字符串变量

“名称”通过【公式-定义名称】来创建,可以将一个单元格区域定义为名称,这样引用这个区域不需要再选择单元格地址

Range(“名称”).Value = 值

如果要引用多个不连续的区域,可以在各区域间添加逗号(注意双引号依然只有一组)

Range(“A1:A10,B2:f20”).Value = 值

如果想要引用交叉区域(公共区域),可以在多个区域间添加空格

Range(“A7:F7 D2:D13”).Value = 1000

交叉区域值得是两个单元格区域中重叠的部分,这部分重叠的部分将会选中

还可以使用两个参数,来引用两个区域形成的矩形区域,也就是根据两片单元格区域的左上角与右下角形成的矩形区域,这时候Range对象需要接受两个参数

Range(“A7:F7”, “D2:D13”).Value = 1000

Worksheet(或Range)对象的Cells属性

这是引用Range对象的另一种形式,返回指定工作表或单元格区域中指定行与列交叉的单元格

指定第几行第几列单元格(第二个参数指定第几列,也可以换成是列的字母列表(需要加上引号))

ActiveSheet.Cells(3, 6).Value = 20 ‘将第3行第6列单元格的值设为20

第二个参数改为列的字母形式的列标

ActiveSheet.Cells(3, “F”).Value = 233 ‘将第3行第6列单元格的值设为20

如果引用的是Range对象的Cells属性,将返回指定单元格区域中指定行与列相交的单元格

也就是说在Range对象内部的第几列第几行单元格

Range(“B3:F9”).Cells(2, 1) = 111111 ‘将B3:F9作为一个表格,在这个表格内第二行第一列的值进行修改

同样的第二个参数也可以换成字母列表,但此时字母的列表也是基于Range小表哥而不是工作表

Range(“B3:F9”).Cells(2, “C”) = 值 ‘这里的C列指的是Range小表格中的第三列

Cells属性还可以用作Range对象属性的参数,也就是可以指定第几行第几列代表一个单元格地址

Range(Cells(1, 1), Cells(10, 5)).Select ‘选中工作表区域A1到E10(第十行第五列的单元格是E10)

Cells可以只使用1个参数,代表引用表格中第几个单元格(Excel会按照从左到右,从上倒下的顺序,没每个单元格分配唯一的序号,在Worksheet中,第二行第一个单元格需要已经是16385)

ActiveSheet.Cells(2) = “222” ‘在活动工作表的第2个单元格输入200

如果引用的是Range对象的Cells属性,索引号的范围为1到这个单元格区域包含的单元格的个数

索引号也可以大于单元格区域里的单元格个数,这时候,可以更改Range对象范围外的单元格(会自动基于Range表格的形状进行延伸)

Range(“B2:E6”).Cells(26).Value = 999999 ‘

如果不使用任何参数,Cells属性将返回指定对象中所有的单元格

ActiveSheet.Cells.Select ‘选中整张工作表(用作Range对象时会选中整个Range对象)

用Range属性和Cells属性引用单元格的区别是什么

使用Cells只能引用1个单元格,不能引用多个单元格

1.语法差异

Range("A1")              ' 单个单元格
Range("A1:B5")           ' 单元格区域
Range("A1,C3,E5")        ' 不连续的单元格

Cells(1, 1)              ' A1 单元格
Cells(1, "A")            ' A1 单元格
Cells(5)                 ' 第5个单元格(A5)

2.灵活性

Range属性:

可以直接引用单个单元格、连续区域或不连续区域。

支持使用命名区域。

可以使用字母数字组合(如”A1″)来指定单元格。

Cells属性:

主要用于引用单个单元格。

可以使用数字索引,更适合在循环中使用。

不能直接引用不连续的区域。

3.动态引用

Range属性:

适合使用字符串变量构建单元格引用。

Dim cellRef As String
cellRef = "A" & someNumber
Range(cellRef).Value = "Dynamic!"

Cells属性:

更适合使用数值变量动态引用单元格。

Dim row As Integer, col As Integer
row = 5: col = 3
Cells(row, col).Value = "Dynamic!"
更简短的快捷方式

如果想引用某个单元格或单元格区域,可以直接将单元格地址写在中括号中,如果单元格或单元格区域已被定义为名称,也可以直接把名称写在中括号里

[](中括号)是Application对象的Evaluate方法的简写形式,这种简写形式非常适合引用一个固定的Range对象。但是因为不能在方括号中使用变量,所以这种引用方式缺少灵活性

[A1] = "hello"
[A1:D10] = "TEST"
[a1:a10,c1:c10,e1:e10] = "hello"
[b1:b10 a5:d5] = 0  '两组区域之间的交叉区域
[n] = 0 '名称n代表的单元格

还可以怎样得到单元格

引用整行

Rows返回ActiveSheet中所有行的集合

ActiveSheet.Rows(“3:5”).Select ‘选中第三行到第五行

Rows(“3:5”) ‘选中第三行到第五行

Rows(2).Select ‘选中第二行

Rows(5).RowHeight=20 ‘设置第五行的高度

Rows(3).Clear ‘清除第三行的内容

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

如果引用的是Range对象的Rows属性,则返回Range对象区域里的指定行

Rows(“3:10”).Rows(“1:1”).Select ‘选中区域内的第一行

引用整列

引用整列时参数可以是列标,也可以是索引号

ActiveSheet.Columns(“D:E”).Select ‘选中D列至E列

Columns(“D:E”).Select ‘选中D列至E列

Columns(6).Select ‘选中第6列

Columns.Select ‘选中所有列

Columns(“C:G”).Columns(“B:B”).Select ‘Range对象中的第二列

Application对象的Union方法

可以将多个不连续的区域合并成一个单一的Range对象,同时可以对其进行操作

Sub test()
'同时选中两个单元格区域
Application.Union(Range("A1:A10"), Range("D1:D5")).Select
'Application.可以省略不写
'参数为多个Range对象,至少2个,最多30个,参数之间用英文逗号隔开

End Sub
Range对象的Offset属性

它允许我们相对于当前单元格或范围移动到其他单元格或范围,

它有两个参数,在参数为正数的情况下,第一个参数指定向下移动几格,第二个参数指定向右移动几格

参数可以是负数,负数表示反方向移动

Range(“A1”).offset(2,3).value=500 ‘从A1单元格开始,向下移动2行,向右移动3列,然后在那个单元格中填入数值500。

Range对象的Resize属性

可以改变现有范围的大小,扩大或缩小指定的单元格区域,得到一个新的单元格区域

基于给定范围的左上角单元格,重新定义一个新的范围,指定新的行数和列数。

它有2个参数,第一个参数指定新区域的行数,第二个参数指定新区域的列数

Range(“B2:H9”).Resize(2, 3).Select ‘只保留2行3列,基于左上角第一个单元格

Worksheet对象的UsedRange属性

返回一个Range对象,代表工作表中已使用的单元格区域,这个区域包含了所有的非空白单元格,以及曾经包含过数据但现在可能为空的单元格

它总是一个矩形区域,左上角总是从A1单元格,它包含所有曾经使用过的单元格,即使这些单元格现在可能是空的

ActiveSheet.UsedRange.Select ‘实际测试,只会选中有内容的单元格,不会从A1单元格开始,以前使用过但被清除的单元格,也不会选中,会选中一个矩形区域,从第一个有内容的单元格开始,到最后一个有内容的单元格结束

Range对象的CurrentRegion属性

CurrentRegion属性返回一个Range对象代表当前区域,“当前区域”是指包含指定单元格的连续数据区域,由空白行或空白列包围。

它返回一个矩形区域,以指定单元格为起点,向外扩展直到遇到空白行或列,它不包括完全空白的行或列。

Current对象放回挡圈区域,即以空行或空列的组合为边界的区域

相当于选中指定单元格后,按ALT+A,自动扩选周围的连续数据单元格区域,

Range(“C7”).CurrentRegion.Select

Range对象的End属性

End属性返回当前区域结尾处的单元格,等同于在源单元格按按【CTRL+上下左右方向键】得到的单元格

Range(“C7”).End(xlToRight).Select

四个方向的参数:

xlUp: 向上

xlDown: 向下

xlToLeft: 向左

xlToRight: 向右

什么时候会用到End属性?

工作表中记录的行数随时都在变化,应该把新的记录写入工作表的第五行还是第十行?可以用Range对象的End属性来解决这个问题

ActiveSheet.Range(“A6”).End(xlUp).Offset(1, 0).Value = “Test” ‘查找指定单元格 指定方向 的 最后1个连续单元格,然后通过offset向下偏移1个单元格,然后写入内容

操作单元格,还需要了解什么

单元格里的内容,Value属性

如果单元格是一个瓶子,Value就是装在瓶子里的东西,输入内容,修改数据,浙西额都是在设置Range对象的Value属性。

Value属性用于获取或设置单元格的内容,它可以处理文本、数字、日期等多种类型的数据。

Range("A1:B2").Value="abc"	'修改单元格内容

Dim CellContent As Variant
CellContent = Range("A1").Value	'读取单元格内容

Range("B1").Value = Range("A1").Value	'复制单元格内容给另一个单元格

Range("A1:B1")="ABC"	
'修改指定单元格的内容,因为Value是Range对象的默认属性,在给区域赋值时可以省略
'但为了保证程序运行过程中不出现意外,建议养成保留Value属性的习惯
单元格个数,Count一下就知道

Range对象的Count属性返回指定的单元格区域中包含的单元格个数(会统计包括空单元格的所有单元格,数量等于 行数*列数)

Sub test()
    Dim mycount As Integer
    mycount = Range("A1:F2").Count
    MsgBox mycount
End Sub

如果想知道某个区域的行数或列数,代码为:

ActiveSheet.UsedRange.Rows.Count	' 获取当前活动工作表中已使用区域的总行数
ActiveSheet.UsedRange.Columns.Count	' 获取当前活动工作表中已使用区域的总列数
单元格地址,Address属性

想知道某个单元格的地址,可以读取它的Address属性

MsgBox “当前选中的单元格为” & Selection.Address

‘返回带有$号的单元格地址格式,可以是单个单元格的地址,也可以是一片区域

操作单元格

选中单元格,Activate与Select方法

选中活动工作表的A1:B10单元格,代码可以是:

ActiveSheet.Range(“A1:B10”).Select

也可以是:

ActiveSheet.Range(“A1:F1”).Activate

但Select与Activate两种方法存在一些区别

选择性清除单元格

【开始选项卡-编辑-清除】可以清除单元格全部内容,也可以选择性的清除 格式/内容/超链接/批注

VBA也同样提供这些功能

Range(“A1”).Clear ‘清除全部内容

Range(“A1”).ClearContents ‘清除内容(保留格式)

Range(“A1”).ClearFormats ‘仅清除格式(保留内容)

Range(“A1”).Hyperlinks.Delete ‘清除超链接

Range(“A1”).ClearComments ‘清除批注

还可以组合清除

Sub ClearContentsAndFormats()

With Range(“A1:A10”)

.ClearContents

.ClearFormats

End With

End Sub

复制单元格区域

Range(“B1”).Copy Destination:=Range(“E1”) ‘将B1单元格的内容(包括格式)复制粘贴到E1单元格

Range(“B1”).Copy Range(“E1”) ‘省略Destination名称的简写方式

如果要跨工作表复制粘贴,代码需要加上【Worksheets(“工作表名称”).】,比如:

Worksheets(“Sheet1”).Range(“A1:A10”).copy worksheets(“Sheet2”).Range(“B1:B10”)

如果要复制的单元格区域不能确定大小,可以只指定一个单元格作为粘贴的起始位置:

Worksheets("Sheet1").Range("A1:A10").Copy Worksheets("Sheet2").Range("B1")

如果只想粘贴纯数值,即只复制内容但不复制格式,代码可以是这样的:

Range("A1:A10").copy
Range("F1:F10").PasteSpecial Paste:=xlPasteValues

或者更简单的写法:

Range("B1:B10").value = Range("A1:A10").value
剪切单元格

调用Range对象的Cut方法可以剪切单元格,格式与copy方法一致

Range(“A1:A10”).Cut Range(“F1”)

删除单元格

调用Range对象的Delete方法可以删除单元格,同手动删除单元格一样,用VBA删除单元格后,也有4个删除选项

VBA删除时不会弹出对话框,但可以在代码中指定

  • 4个参数分别为:
    xlShiftToLeft (值为-4159):将右侧单元格左移
  • xlShiftUp (值为-4162):将下方单元格上移
  • xlShiftToRight (值为-4161):将左侧单元格右移(不常用)
  • xlShiftDown (值为-4121):将上方单元格下移(不常用)

删除单元格的代码格式:

Range(“要删除的单元格或区域”).Delete Sheft=删除选项(无双引号)

比如:Range(“A1”).Delete Shift:=xlShiftToLeft

如果不在代码中指定删除选项,那么默认的选项激素是删除单元格后下方单元格上移:Range(“A1”).Delete

5.其他常见的对象

名称,Names集合

名称,就是名字

Excel中定义的名称就是给单元格区域(或数值常量、公式)取的名字。一个自定义的名称就是一个Name对象,Names是工作簿中定义的所有名称的集合

基本语法:

ActiveWorkbook.Names.Add Name:="名称", RefersTo:="=引用"

为单元格区域创建名称:

Sub CreateRangeName()
    ActiveWorkbook.Names.Add Name:="SalesData", RefersTo:="=Sheet1!$A$1:$D$10"
End Sub

为常量创建名称:

Sub CreateConstantName()
    ActiveWorkbook.Names.Add Name:="TaxRate", RefersTo:="=0.2"
End Sub

为公式创建名称:

Sub CreateFormulaName()
    ActiveWorkbook.Names.Add Name:="TotalSales", RefersTo:="=SUM(Sheet1!$B$2:$B$10)"
End Sub

删除名称:

Sub DeleteName()
    ActiveWorkbook.Names("SalesData").Delete
End Sub

修改名称引用:

Sub ModifyNameReference()
    ActiveWorkbook.Names("SalesData").RefersTo = "=Sheet1!$A$1:$D$20"
End Sub

检查名称是否存在:

Sub CheckNameExists()
    Dim nm As Name
    On Error Resume Next
    Set nm = ActiveWorkbook.Names("SalesData")
    On Error GoTo 0
    
    If Not nm Is Nothing Then
        MsgBox "名称'SalesData'存在"
    Else
        MsgBox "名称'SalesData'不存在"
    End If
End Sub

定义名称,更简单的方式:

Range(“A1:B30″).Name=”名称”

单元格批注,Comment对象

单元格批注用于对单元格做注释或说明,批注本身并不影响单元格内的数值,也不参与或影响计算

在Excel里,一个批注就是一个Comment对象,Comments是工作簿中所有Comment对象的集合

给单元格添加批注

Range(“A1″).AddComment Text:=”批注的内容”

如果单元格已经存在批注,再添加批注时会报错,

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

if Range(“A1”).Comment Is Nothing Then

msgbox “A1单元格没有批注”

Else

msgbox “A1单元格存在批注”

End If

还可以这样操作批注:

Range(“A1″).Comment.Text Text:=”批注的内容2” ‘更改批注的内容

Range(“A1”).Comment.VIsible=False ‘隐藏批注

Range(“A1”).Comment.Delete ‘删除批注

给单元格化妆

设置字体,Font对象
Sub test()
    With Range("A1:A10").Font
        ' 设置字体为微软雅黑
        .Name = "微软雅黑"
        ' 设置字体大小为20磅
        .Size = 20
        ' 应用之前定义的红色
        .Color = RGB(255, 0, 0)
        ' 设置字体为粗体
        .Bold = True
        ' 设置字体为斜体
        .Italic = True
        ' 添加单下划线
        .Underline = xlUnderlineStyleSingle
    End With
End Sub

FONT对象的其他属性

With rng.Font
        ' 1. 字体名称
        .Name = "Arial"
        
        ' 2. 字体大小
        .Size = 12
        
        ' 3. 字体颜色
        .Color = RGB(0, 0, 255)  ' 蓝色
        
        ' 4. 粗体
        .Bold = True
        
        ' 5. 斜体
        .Italic = True
        
        ' 6. 下划线
        .Underline = xlUnderlineStyleSingle
        
        ' 7. 删除线
        .Strikethrough = True
        
        ' 8. 上标
        .Superscript = False
        
        ' 9. 下标
        .Subscript = False
        
        ' 10. 字符间距
        .Spacing = 1.5  ' 1.5 磅
        
        ' 11. 文本阴影
        .Shadow = True
        
        ' 12. 轮廓
        .OutlineFont = False
        
        ' 13. 主题颜色
        .ThemeColor = xlThemeColorAccent1
        
        ' 14. 主题字体
        .ThemeFont = xlThemeFontMinor
        
        ' 15. 字体样式(仅适用于某些字体)
        .FontStyle = "Bold Italic"
    End With
给单元格添加底纹(设置单元格背景色)

Range(“A1:A10”).Interior.Color = RGB(255, 255, 0)

给表格设置边框
Sub test()
    With Range("A1").CurrentRegion.Borders
    '自动扩选单元格A1的表格区域,并且操作边框属性
    .LineStyle = xlContinuous   '设置单线边框
    .Color = RGB(0, 0, 255) '设置边框颜色
    .Weight = xlHairline    '设置边框线条样式
    End With
End Sub

如果想用代码却不知道代码该怎么写,可以手动操作,用宏录制器录下他,就能看见代码的写法了

6.典型的技巧与示例

创建一个工作簿,并将其保存到指定的文件夹

Sub 快速创建并保存工作簿()
    ' 创建新工作簿
    Dim wb As Workbook
    Set wb = Workbooks.Add
    
    ' 设置保存路径和文件名
    Dim savePath As String
    savePath = "C:\Users\YourUsername\Documents\新工作簿.xlsx"
    
    ' 保存工作簿
    wb.SaveAs Filename:=savePath
    
    ' 显示消息
    MsgBox "工作簿已保存到: " & savePath
End Sub

判断工作簿是否打开

打开的工作簿有很多,想知道名为“成绩表”的工作簿是否打开,代码可以这样写

Function 是否打开成绩表() As Boolean
    Dim wb As Workbook
    
    ' 遍历所有打开的工作簿
    For Each wb In Workbooks
        ' 检查工作簿名称是否为"成绩表"
        If wb.Name = "成绩表.xlsx" Then
            是否打开成绩表 = True
            Exit Function
        End If
    Next wb
    
    ' 如果没有找到,返回False
    是否打开成绩表 = False
End Function

Sub 检查成绩表是否打开()
    If 是否打开成绩表() Then
        MsgBox "成绩表已经打开。"
    Else
        MsgBox "成绩表未打开。"
    End If
End Sub

判断工作簿是否存在

文件夹中存了许多工作簿文件,想知道指定文件.xls是否存在

Function 文件是否存在(文件路径 As String) As Boolean
    ' 使用Dir函数检查文件是否存在
    文件是否存在 = (Dir(文件路径) <> "")
End Function

Sub 检查工作簿是否存在()
    Dim 文件路径 As String
    Dim 文件夹路径 As String
    Dim 文件名 As String
    
    ' 设置文件夹路径(请根据实际情况修改)
    文件夹路径 = "C:\Users\YourUsername\Documents\"
    
    ' 设置要查找的文件名
    文件名 = "指定文件.xls"
    
    ' 组合完整的文件路径
    文件路径 = 文件夹路径 & 文件名
    
    ' 检查文件是否存在并显示结果
    If 文件是否存在(文件路径) Then
        MsgBox "文件 '" & 文件名 & "' 存在于指定文件夹中。", vbInformation
    Else
        MsgBox "文件 '" & 文件名 & "' 不存在于指定文件夹中。", vbExclamation
    End If
End Sub

向未打开的工作簿中录入数据

Sub 向关闭工作簿录入数据()
    Dim 文件路径 As String
    Dim 目标工作簿 As Workbook
    Dim 目标工作表 As Worksheet
    Dim 行号 As Long
    
    ' 设置目标工作簿的文件路径
    文件路径 = "C:\Users\YourUsername\Documents\目标工作簿.xlsx"
    
    ' 打开工作簿但不显示
    Set 目标工作簿 = Workbooks.Open(文件路径, UpdateLinks:=False, ReadOnly:=False)
    
    ' 选择要操作的工作表(假设是第一个工作表)
    Set 目标工作表 = 目标工作簿.Worksheets(1)
    
    ' 找到最后一行的下一行
    行号 = 目标工作表.Cells(目标工作表.Rows.Count, "A").End(xlUp).Row + 1
    
    ' 录入数据(这里只是一个示例,您可以根据需要修改)
    目标工作表.Cells(行号, 1).Value = "新数据"
    目标工作表.Cells(行号, 2).Value = Date
    目标工作表.Cells(行号, 3).Value = Time
    
    ' 保存并关闭工作簿
    目标工作簿.Save
    目标工作簿.Close
    
    ' 显示完成信息
    MsgBox "数据已成功录入到工作簿中。", vbInformation
End Sub

隐藏活动工作表外的所有工作表

Sub 隐藏其他工作表()
    Dim ws As Worksheet
    Dim activeWS As Worksheet
    
    ' 存储当前活动工作表
    Set activeWS = ActiveSheet
    
    ' 遍历工作簿中的所有工作表
    For Each ws In ThisWorkbook.Worksheets
        ' 如果不是当前活动工作表,则隐藏
        If ws.Name <> activeWS.Name Then
            ws.Visible = xlSheetHidden
        End If
    Next ws
    
    ' 显示操作完成的消息
    MsgBox "除了活动工作表 '" & activeWS.Name & "' 外,其他所有工作表已被隐藏。", vbInformation
End Sub

批量新建工作表

Sub 批量新建工作表()
    Dim i As Integer
    Dim 工作表数量 As Integer
    Dim 基础名称 As String
    Dim 新工作表 As Worksheet
    
    ' 询问用户要创建的工作表数量
    工作表数量 = InputBox("请输入要创建的工作表数量:", "批量新建工作表", "5")
    
    ' 验证输入
    If 工作表数量 <= 0 Then
        MsgBox "请输入一个大于0的数字。", vbExclamation
        Exit Sub
    End If
    
    ' 询问用户工作表的基础名称
    基础名称 = InputBox("请输入工作表的基础名称:", "批量新建工作表", "Sheet")
    
    ' 关闭屏幕更新以提高速度
    Application.ScreenUpdating = False
    
    ' 创建工作表
    For i = 1 To 工作表数量
        ' 创建新工作表
        Set 新工作表 = ThisWorkbook.Worksheets.Add(After:=ThisWorkbook.Sheets(ThisWorkbook.Sheets.Count))
        
        ' 命名新工作表
        新工作表.Name = 基础名称 & i
    Next i
    
    ' 恢复屏幕更新
    Application.ScreenUpdating = True
    
    ' 显示完成消息
    MsgBox "已成功创建 " & 工作表数量 & " 个新工作表。", vbInformation
End Sub

将工作表保存为新工作簿

工作簿里有许多工作表,如果想把每个工作表保存为一份单独的文件

Sub 将工作表保存为单独工作簿()
    Dim ws As Worksheet
    Dim 新工作簿 As Workbook
    Dim 保存路径 As String
    Dim 原工作簿 As Workbook
    
    ' 设置保存路径(请根据需要修改)
    保存路径 = ThisWorkbook.Path & "\导出的工作表\"
    
    ' 检查并创建保存目录
    If Dir(保存路径, vbDirectory) = "" Then
        MkDir 保存路径
    End If
    
    ' 存储原始工作簿
    Set 原工作簿 = ThisWorkbook
    
    ' 关闭屏幕更新以提高速度
    Application.ScreenUpdating = False
    Application.DisplayAlerts = False
    
    ' 遍历每个工作表
    For Each ws In 原工作簿.Worksheets
        ' 创建新工作簿
        Set 新工作簿 = Workbooks.Add
        
        ' 删除新工作簿中的默认工作表
        Application.DisplayAlerts = False
        For Each sht In 新工作簿.Worksheets
            sht.Delete
        Next sht
        Application.DisplayAlerts = True
        
        ' 复制工作表到新工作簿
        ws.Copy Before:=新工作簿.Sheets(1)
        
        ' 保存新工作簿
        新工作簿.SaveAs Filename:=保存路径 & ws.Name & ".xlsx", FileFormat:=xlOpenXMLWorkbook
        
        ' 关闭新工作簿
        新工作簿.Close SaveChanges:=False
    Next ws
    
    ' 恢复屏幕更新和警告
    Application.ScreenUpdating = True
    Application.DisplayAlerts = True
    
    ' 显示完成消息
    MsgBox "所有工作表已被保存为单独的工作簿。" & vbNewLine & _
           "保存路径:" & 保存路径, vbInformation
End Sub

为工作表建立目录

工作簿中有许多工作表,想要为工作表建立一个目录,可以用这个程序:

Sub 创建工作表目录()
    Dim ws As Worksheet
    Dim 目录工作表 As Worksheet
    Dim 行号 As Long
    Dim 超链接地址 As String
    
    ' 检查是否已存在名为"目录"的工作表
    On Error Resume Next
    Set 目录工作表 = ThisWorkbook.Worksheets("目录")
    On Error GoTo 0
    
    ' 如果"目录"工作表不存在,则创建
    If 目录工作表 Is Nothing Then
        Set 目录工作表 = ThisWorkbook.Worksheets.Add(Before:=ThisWorkbook.Worksheets(1))
        目录工作表.Name = "目录"
    Else
        ' 如果已存在,则清除内容
        目录工作表.Cells.Clear
    End If
    
    ' 设置目录标题
    With 目录工作表.Cells(1, 1)
        .Value = "工作表目录"
        .Font.Bold = True
        .Font.Size = 14
    End With
    
    ' 添加列标题
    目录工作表.Cells(2, 1).Value = "序号"
    目录工作表.Cells(2, 2).Value = "工作表名称"
    目录工作表.Cells(2, 3).Value = "备注"
    
    ' 设置列宽
    目录工作表.Columns("A").ColumnWidth = 6
    目录工作表.Columns("B").ColumnWidth = 30
    目录工作表.Columns("C").ColumnWidth = 40
    
    ' 初始化行号
    行号 = 3
    
    ' 遍历所有工作表并添加到目录
    For Each ws In ThisWorkbook.Worksheets
        If ws.Name <> 目录工作表.Name Then
            ' 添加序号
            目录工作表.Cells(行号, 1).Value = 行号 - 2
            
            ' 创建工作表超链接
            超链接地址 = "'" & ws.Name & "'!A1"
            目录工作表.Hyperlinks.Add Anchor:=目录工作表.Cells(行号, 2), Address:="", SubAddress:=超链接地址, TextToDisplay:=ws.Name
            
            行号 = 行号 + 1
        End If
    Next ws
    
    ' 应用表格样式
    With 目录工作表.Range("A2:C" & 行号 - 1)
        .Borders.LineStyle = xlContinuous
        .Interior.Color = RGB(240, 240, 240)
    End With
    
    ' 冻结首行
    目录工作表.Rows("2:2").Select
    ActiveWindow.FreezePanes = True
    
    ' 选中A1单元格
    目录工作表.Range("A1").Select
    
    ' 显示完成消息
    MsgBox "工作表目录已创建完成。", vbInformation
End Sub

1 thought on “3.EXCEL VBA 常用对象”

发表回复