1.需要熟悉的常用对象
Excel中的对象总是层次分明地组织在一起,一个对象可以包含其他对象,这种对象排列模式称为对象模型,Excel中的所有对象都可以在对象模型中找到。
适用于 Visual Basic for Applications (VBA) 的 Excel 对象模型 | Microsoft Learn
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 | 当前所有打开的工作簿 |
对象/属性 | 描述 | 示例代码 |
Application | Excel应用程序的顶级对象 | 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个参数,让用户决定以何种方式打开指定文件
参数名 | 数据类型 | 说明 | 默认值 |
Filename | String | 要打开的文件的名称 | 必需参数 |
UpdateLinks | Variant | 指定如何更新链接 | xlUpdateLinksUserSetting |
ReadOnly | Variant | 是否以只读方式打开文件 | FALSE |
Format | Variant | 文件格式(用于非Excel文件) | – |
Password | String | 打开受保护文件的密码 | – |
WriteResPassword | String | 写保护密码 | – |
IgnoreReadOnlyRecommended | Variant | 是否忽略”推荐以只读方式打开”的设置 | FALSE |
Origin | Variant | 文件的来源(用于文本文件) | xlWindows |
Delimiter | Variant | 分隔符(用于文本文件) | – |
Editable | Variant | 是否可编辑(用于查询表) | TRUE |
Notify | Variant | 文件被修改时是否通知 | FALSE |
Converter | Variant | 文件转换器的索引号 | – |
AddToMru | Variant | 是否将文件添加到最近使用列表 | FALSE |
Local | Variant | 是否以本地语言打开文件 | FALSE |
CorruptLoad | Variant | 如何处理损坏的文件 | 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 常用对象”