在VBA中,一切操作都以对象为基础,操作Excel就是操作这些对象,所以理解和熟悉对象对于VBA编程非常重要,常用的对象有以下10类:
Application 代表整个Excel程序
Workbook 代表Excel工作薄对象
Worksheet 代表工作表对象
Window 代表窗口对象
Range 代表单元格对象
Shape 代表嵌入工作表中的图形对象
Name 代表名称对象
Chart 代表图表对象
WorksheetFunction 代表工作表函数对象
Comment 代表单元格中的批注对象
例1:控制是否显示警告消息
用Application.DisplayAlerts属性,设置为 False 可以在执行某些可能会触发警告的操作时不显示警告对话框。
Application.DisplayAlerts = False
Application.DisplayAlerts = True
例2:控制屏幕更新
用Application.ScreenUpdating属性,关闭屏幕更新可以提高代码运行速度,尤其是在进行大量数据操作时。
Application.ScreenUpdating = False
Application.ScreenUpdating = True
例3:执行计算。
用Application.Calculate方法,重新计算所有打开的工作簿中的公式。
Application.Calculate '重新计算所有打开的工作簿中的公式
例4:退出Excel
用Application.Quit方法,退出Excel
Application.Quit '退出 Excel
在 VBA 中,Window对象代表 Excel 应用程序中的窗口。通过Window对象,可以对窗口进行操作,如激活、关闭、调整大小等。以下是一些常用的Window对象的属性和方法:
Activate 方法:将窗口提到 z-次序的最前面,使其成为活动窗口。
ActivateNext 方法:激活指定窗口,并将其移到窗口 z-次序的末尾。
ActivatePrevious 方法:激活指定窗口,然后激活窗口 z-次序末尾的窗口。
Close 方法:关闭对象。
LargeScroll 方法:按页滚动窗口内容。
NewWindow 方法:新建一个窗口或者创建指定窗口的副本。
PointsToScreenPixelsX 方法:将横向度量值由以点(文档坐标)为单位转换为以屏幕像素(屏幕坐标)为单位,以Long值的形式返回转换的度量值。
PointsToScreenPixelsY 方法:将纵向度量值由以点(文档坐标)为单位转换为以屏幕像素(屏幕坐标)为单位,以Long值的形式返回转换的度量值。
PrintOut 方法:打印对象。
PrintPreview 方法:按对象打印后的外观效果显示对象的预览。
RangeFromPoint 方法:返回位于指定的一对屏幕坐标处的形状或Range对象,如果没有形状位于指定的坐标处,则此方法返回Nothing。
ScrollIntoView 方法:滚动文档窗口,使指定矩形区域的内容显示在文档窗口或窗格的左上角或右下角(具体取决于_Start_参数的值)。
ScrollWorkbookTabs 方法:滚动工作簿窗口下方的工作表标签,本方法不改变该工作簿中的活动工作表。
SmallScroll 方法:按行或按列滚动窗口内容。
例1:Windows.Activate方法激活指定工作簿
Sub ActivateSpecificWindow()
'如果有多个打开的工作簿窗口,假设存在名为“财务分析.xlsx”的工作簿窗口,通过这段代码就可以将其激活并置于前端。
Windows("财务分析.xlsx").Activate
End Sub
例2:ActiveWindow.LargeScroll 方法按页滚动窗口内容
Sub ScrollWindow()
ActiveWindow.LargeScroll Down:=1 '向下滚动一页
ActiveWindow.LargeScroll Up:=1 '向上滚动一页
End Sub
例3:将窗口向下滚动 10 行
Sub scrdown()
ActiveWindow.SmallScroll Down:=10
'将窗口向下10行
End Sub
例4:将窗口向右滚动 5 列
Sub scrright()
ActiveWindow.SmallScroll ToRight:=5
'将窗口向右5列
End Sub
在 VBA 中,Range
对象是用于操作 Excel 工作表中单元格范围的重要对象。使用频率相当高
例1:激活单个单元格
Sub range对象()
'下面为range对象的方法
Range("a1").Activate
'激活单个单元格
End Sub
例2:批量添加批注
用Range.AddComment方法为区域添加批注。如下代码:
Sub rngcommt()
Dim rng As Range
'定义一个名称为rng类型为range
Set rng = Range("a1:c3")
For Each a In rng
'循环单元格对象
a.AddComment ("公众号Excel公式与技巧")
Next a
End Sub
Sub 删除批注()
Range("A1:C3").ClearComments
End Sub
例3:Range.AutoFill 方法自动填充数据
语法:Range.AutoFill(Destination, Type)
-
Destination:要填充到的目标范围。
-
Type:指定填充类型,可选值包括 xlFillDefault(默认填充方式)、xlFillCopy(复制填充)、xlFillSeries(序列填充)等。
以下是一个示例,演示如何使用 AutoFill 方法进行默认填充:
Sub AutoFillExample()
Range("A1").Value = 1
Range("A2").Value = 2
Range("A1:A2").AutoFill Destination:=Range("A1:A10")
'先在A1和A2输入1和2,然后填充
End Sub
首先在 A1 和 A2 单元格中分别输入 1 和 2,然后使用 AutoFill 方法将这两个单元格的值填充到 A1:A10 范围。
Sub SeriesAutoFillExample()
Range("A1").Value = 1
Range("A1:A1").AutoFill Destination:=Range("A1:A10"), Type:=xlFillSeries
'先输入1,然后按序列填充
End Sub
例4:Range.AutoFilter 方法对指定的单元格区域应用筛选。
语法:
Range.AutoFilter(Field, Criteria1, Operator, Criteria2, VisibleDropDown)
-
Field:要筛选的列序号(从 1 开始)。
-
Criteria1:筛选条件 1。
-
Operator:筛选操作符,例如 xlAnd(与)、xlOr(或)等。
-
Criteria2:筛选条件 2(当使用 Operator 时需要)。
-
VisibleDropDown:是否显示筛选下拉箭头,默认为 True。
如下代码,对第一列中值大于 50 的数据进行筛选:
Sub ApplyAutoFilter()
Range("B1:B27").AutoFilter Field:=1, Criteria1:=">50"
'筛选第1列大于50的数据
End Sub
例5:Range.AutoFit 方法自动调整指定单元格区域的列宽或行高,以适应其中的内容。
如下代码,自动调整 A1:B10 区域的列宽,使其适应单元格中的内容;
Sub AutoFitColumnsAndRows()
Range("A1:B10").Columns.AutoFit '自动调整列宽
Range("A1:B10").Rows.AutoFit '自动调整行高
End Sub
更多Range对象的方法如下
Activate 激活单个单元格,该单元格必须处于当前选定区域内。要选择单元格区域,请使用 Select 方法。
AddComment 为区域添加批注。
AdvancedFilter 基于条件区域从列表中筛选或复制数据。如果初始选定区域为单个单元格,则使用单元格的当前区域。
AllocateChanges 在基于 OLAP 数据源的区域中的所有已编辑的单元格上执行回写操作。
ApplyNames 将名称应用于指定区域中的单元格。
ApplyOutlineStyles 对指定区域应用分级显示样式。
AutoComplete 从列表中返回一个记忆式键入匹配项。如果没有相应的记忆式键入匹配项,或者在列表中有不止一个与已键入字符串相匹配的项,此方法将返回一空字符串。
AutoFill 对指定区域中的单元格执行自动填充。
AutoFilter 使用“自动筛选”筛选一个列表。
AutoFit 更改区域中的列宽或行高以达到最佳匹配。
AutoOutline 自动为指定区域创建分级显示。如果区域为单个单元格,Microsoft Excel 将创建整个工作表的分级显示。新分级显示将取代所有的分级显示。
BorderAround 向单元格区域添加边框,并设置该新边框的 Color、LineStyle 和 Weight 属性。Variant。
Calculate 计算所有打开的工作簿、工作簿中的某张特定工作表或工作表指定区域中的单元格,如下表所示。
CalculateRowMajorOrder 计算指定的单元格区域。
CheckSpelling 检查对象的拼写。
Clear 清除整个对象。
ClearComments 清除指定区域的所有单元格批注。
ClearContents 清除指定区域的公式。
ClearFormats 清除对象的格式设置。
ClearHyperlinks 删除指定区域中的所有超链接。
ClearNotes 清除指定区域中所有单元格的批注和语音批注。
ClearOutline 清除指定区域的分级显示。
ColumnDifferences 返回一个 Range 对象,该对象代表各列中其内容与比较单元格不同的单元格。
Consolidate 将多个工作表中多个区域的数据合并计算至单个工作表上的单个区域。Variant 类型。
Copy 将单元格区域复制到指定的区域或剪贴板中。
CopyFromRecordset 将 ADO 或 DAO Recordset 对象中的内容复制到工作表,从指定区域的左上角开始。如果 Recordset 对象包含具有 OLE 对象的字段,则该方法无效。
CopyPicture 将所选对象作为图片复制到剪贴板。Variant。
CreateNames 在指定区域中依据工作表中的文本标签创建名称。
Cut 将对象剪切到剪贴板,或者将其粘贴到指定的目的地。
DataSeries 在指定区域内创建数据系列。Variant 类型。
Delete 删除对象。
DialogBox 显示由 Microsoft Excel 4.0 宏工作表上的对话框定义表所定义的对话框。返回选定控件的编号,或者当用户单击“取消”按钮时返回 False。
Dirty 下一次重新计算发生时指定要重新计算的区域。
DiscardChanges 放弃对该区域的已编辑的单元格的所有更改。
EditionOptions 您查询的是 Macintosh 专用的 Visual Basic 关键词。有关该关键词的帮助信息,请查阅 Microsoft Office Macintosh 版的语言参考帮助。
ExportAsFixedFormat 导出为指定格式的文件。
FillDown 从指定区域的顶部单元格开始向下填充,直至该区域的底部。区域中首行单元格的内容和格式将复制到区域中其他行内。
FillLeft 从指定区域的最右边单元格开始向左填充。区域中最右列单元格的内容和格式将复制到区域中其他列内。
FillRight 从指定区域的最左边单元格开始向右填充。区域中最左列单元格的内容和格式将复制到区域中其他列内。
FillUp 从指定区域的底部单元格开始向上填充,直至该区域的顶部。区域中尾行单元格的内容和格式将复制到区域中其他行内。
Find 在区域中查找特定信息。
FindNext 继续由 Find 方法开始的搜索。查找匹配相同条件的下一个单元格,并返回表示该单元格的 Range 对象。该操作不影响选定内容和活动单元格。
FindPrevious 继续由 Find 方法开始的搜索。查找匹配相同条件的上一个单元格,并返回代表该单元格的 Range 对象。该操作不影响选定内容和活动单元格。
FunctionWizard 对指定区域左上角单元格启动“函数向导”。
Group 当 Range 对象代表某个数据透视表字段的数据区域中的单个单元格时,Group 方法在该字段中执行基于数字或日期的分组。
Insert 在工作表或宏表中插入一个单元格或单元格区域,其他单元格相应移位以腾出空间。
InsertIndent 向指定的区域添加缩进量。
Justify 调整区域内的文字,使之均衡地填充该区域。
ListNames 从指定区域的第一个单元格位置开始,将所有未隐藏的名称的列表粘贴到工作表上。
Merge 由指定的 Range 对象创建合并单元格。
NavigateArrow 定位追踪箭头,此箭头指定引用单元格、从属单元格或错误源单元格。选定引用单元格、从属单元格或错误源单元格并返回一个 Range 对象,该对象代表新选定区域。本方法应用于没有可见追踪箭头的单元格时将出错。
NoteText 返回或设置与区域左上角单元格相关联的单元格注释。String 类型,可读写。单元格注释已经替换为区域注释。有关详细信息,请参阅 Comment 对象。
Parse 分列区域内的数据并将这些数据分散放置于若干单元格中。将区域内容分配于多个相邻接的列中;该区域只能包含一列。
PasteSpecial 将 Range 从剪贴板粘贴到指定的区域中。
PrintOut 打印对象。
PrintPreview 按对象打印后的外观效果显示对象的预览。
RemoveDuplicates 从值区域中删除重复的值。
RemoveSubtotal 删除列表中的分类汇总。
Replace 返回 Boolean,它表示指定区域内单元格中的字符。使用此方法并不会更改选定区域或活动单元格。
RowDifferences 返回一个 Range 对象,该对象代表各行中其内容与比较单元格不同的所有单元格。
Run 在该处运行 Microsoft Excel 宏。区域必须位于宏表上。
Select 选择对象。
SetPhonetic 为指定区域中的所有单元格创建 Phonetic 对象。
Show 滚动当前活动窗口中的内容以将指定区域移到视图中。此区域必须由活动文档中的单个单元格组成。
ShowDependents 绘制从指定区域指向直接从属单元格的追踪箭头。
ShowErrors 绘制通过从属单元格树而指向错误源单元格的追踪箭头,并返回包含该单元格的区域。
ShowPrecedents 绘制从指定区域指向直接引用单元格的追踪箭头。
Sort 对值区域进行排序。
SortSpecial 使用东亚排序方法对区域或数据透视表进行排序;或者如果区域中只包含一个单元格,则对活动区域使用本方法。例如,日文排序方法是按照假名音节表进行排序的。
Speak 按行或列的顺序朗读单元格区域。
SpecialCells 返回一个 Range 对象,该对象代表与指定类型和值匹配的所有单元格。
SubscribeTo 您查询的是 Macintosh 专用的 Visual Basic 关键词。有关该关键词的帮助信息,请查阅 Microsoft Office Macintosh 版的语言参考帮助。
Subtotal 创建指定区域或当前区域(如果该区域为单个单元格时)的分类汇总。
Table 依据输入值和工作表上定义的公式创建模拟运算表。
TextToColumns 将包含文本的一列单元格分解为若干列。
Ungroup 在分级显示中对一个区域进行升级(即降低其分级显示的级别)。指定区域必须是行或列,或者行区域或列区域。如果指定区域在数据透视表中,本方法将对该区域内的项取消分组。
UnMerge 将合并区域分解为独立的单元格。
单一的对象和同类别的多个对象分别定义为对象与对象集合,对象集合表示多个复数的意思,常用的对象集合如下:
Workbooks 工作簿集合
Worksheets 工作表集合
Comments 批注集合
Cells 单元格集合
Shapes 图形对象集合
例1:关闭所有有打开的工作簿
Sub 关闭所有工作簿()
Workbooks.Close
'用Workbooks.Close方法关闭所有工作簿
End Sub
例2:统计工作表的数量
Sub 计算工作表数量()
MsgBox Worksheets.Count
'用对话框和Worksheets.Count属性统计工作表的数量
End Sub
例3:显示第1个批注
Sub 显示第1个批注()
MsgBox ActiveSheet.Comments(1).Text
'用对话框显示当前活动工作表的第1个批注
End Sub
例4:Cells的用法
Cells 是一个用于引用单元格的对象。它可以通过指定行号和列号来访问工作表中的单个单元格。以下是关于 Cells 对象的一些基本用法:
-
引用单个单元格:通过指定行号和列号,例如 Cells(1, 1) 表示引用第一行第一列的单元格,即 A1 单元格。
-
遍历单元格范围:可以使用循环结合 Cells 对象来遍历一定范围内的单元格。例如,使用两个嵌套的循环可以遍历整个工作表的单元格。
-
赋值和读取单元格值:可以使用 Cells 对象来赋值给单元格或读取单元格的值。例如,Cells(1, 1).Value = 10 将 10 赋值给 A1 单元格。
-
使用变量作为行号和列号:行号和列号可以是变量,以便在运行时动态地确定要访问的单元格。
-
结合其他对象使用:Cells 对象可以与 Range 对象结合使用,例如 Range(Cells(1, 1), Cells(10, 10)) 表示引用 A1 到 J10 的单元格范围。
如下代码,使用两个嵌套的循环遍历了 10 行 5 列的单元格,并将每个单元格的值设置为其行号和列号的乘积。
Sub Example()
Dim i As Integer, j As Integer
'遍历第一行到第十行,第一列到第五列的单元格
For i = 1 To 10
For j = 1 To 5
Cells(i, j).Value = i * j '单元格赋值
Next j
Next i
End Sub
可以理解为先循环里面的 j = 1 to 5,在循环i,放进Cells中当行和列。
技术交流,软件开发,欢迎微信沟通: