Option Explicit
’ 这行指令表示 变量需要显示声明 ,一般用在文件开头。
一、操作单元格对象
注释
Sub test()
'弹出对话框
' "'"注释 单引号
Rem 另一种注释 -- Remark
MsgBox "hello world"
End Sub
VBA思想 是处理某个对象
VBA 基础语法
对象.方法
worksheets 当前工作簿中所有工作表对象
worksheets.Add : 新建工作表
Sub AddSheet()
Worksheets.Add
End Sub
对象.属性
' Range("A1").Value = "Create By"
' Range("A2").Value = #6/20/2020# --日期 时间
在单元格中填值
Sub FillInValue()
Range("A1").Value = "Create By"
Range("A2").Value = #6/20/2020# '--日期 时间
Range("B1").Value = Environ("username") 'Environ 表示电脑环境 此处表示获取用户名
Range("B2").Value = Date '日期
Range("B3").Value = Time '时间
End Sub
格式化单元格
Sub FormatRange()
Range("A1").Interior.Color = vbYellow '单元格背景颜色设置为黄色
Range("A2").Interior.Color = vbYellow
End Sub
代码的保存 选用 Excel启用宏的工作簿(.xlsm)传统Excel 工作簿(.xlsx)不支持代码保存。
’ 代码的执行
- 选择当前工作表 点击运行 代码逻辑将作用到工作表中
- 在表格中绘制一个按钮右击选择 指定宏 选择一个运行的宏,当点击该按钮时会调用运行这个宏代码
逐语句运行 调试 F9 添加断点 F8 单步执行
运行VBA代码的常用方式
1.为程序指定快捷键
2.使用按钮控件
3.使用自定义图形
4 使用工具栏
5.使用Ribbon选项卡
引用单元格方法
Sub UsingRangeClass()
'下面几种方式都是引用单元格 并操作
Range("A13").Value = 11
Range("B13").Value = "飘"
Cells(13, 3).Value = "小说"
[D13].Value = 8.1
End Sub
’ 活动单元格的引用
Sub UsingActiveCell()
Range("A14").Select
ActiveCell.Value = 12
Cells(14, 2).Select
ActiveCell.Value = "追风少年"
[C14].Select
ActiveCell.Value = "小说"
Range("D14").Select
ActiveCell.Value = 8.6
End Sub
’ 引用多个单元格
Sub UsingRangeMultiCell()
' 下面选择引用多个单元格 并把内容居中 设置背景色 设置字体
Range("A13:D14").Select
Selection.HorizontalAlignment = xlCenter
Selection.Interior.Color = rgbLightGray
Selection.Font.Name = "宋体"
End Sub
’ 选择最后一行单元格
Sub ReferringTheLastRow()
Range("A2").End(xlDown).Select
ActiveCell.Offset(1, 0).Value = 13
End Sub
’ 根据区域向上走选择一个单元格
Sub ReferringCellsInFourDirection()
Range("A1048567").End(xlUp).Select
End Sub
’ 拷贝单元格区域 到另一个表格
Sub ReferingWholeData()
Range("A1").CurrentRegion.Copy Sheet5.Range("A1")
End Sub
’ 选择一行一列
Sub SelectWholeRowOrColumn()
Rows(1).Select ' 选择第一行
Columns(2).Select ' 选择第二列
Range("A3").EntireRow.Select ' 选择A3所在的一行
End Sub
二、操作工作表对象:
引用工作表、图表对象
1. 在不同表之间切换
2. 选择一个或多个表
3. 通过表名称、代码名称以及索引号引用表格
’ 操作表格对象
1. 添加、删除表格
2. 移动、复制表格
3. 重命名表格
4. 显示与隐藏表格
’ 激活sheet2 表格
Sub MovingBetweenWorkSheets()
Worksheets("Sheet2").Activate
End Sub
'激活图表 表格
Sub MovingBetweenCharts()
Charts("Chart1").Activate
End Sub
’ 激活sheet2 表格
Sub MovingBetweenSheets()
Sheets("Sheet2").Activate
Sheets("Chart1").Activate
End Sub
’ 选择单个表格
Sub SelectSingleSheet()
Worksheets("sheet1").Select
End Sub
’ Select 可以选择多个表格 Activate只能激活一个表格
Sub SelectMultiSheets()
' 下面演示选择多个表格 形成一个工作组 类似ctrl + 鼠标选择
Worksheets("sheet2").Select
Worksheets("sheet3").Select False ' 加false 表示不覆盖之前的选择
Worksheets("sheet4").Select False
End Sub
’ 通过表格的名称引用表格
Sub ReferringSheetBySheetName()
Worksheets("Sheet2").Select
End Sub
’ 通过索引号引用表格
Sub ReferringSheetByIndexNo() ' 针对Sheets
Sheets(1).Select
End Sub
’ 通过Code Name 引用
Sub ReferringSheetByCodeName()
Sheet1.Select
End Sub
’ 添加表格
Sub AddNewSheet()
Sheets.Add
Sheets.Add after:=Sheets(1) '在一个表格之后添加新表格 after 对应 before
Sheets.Add after:=Sheets(Sheets.Count) '在所有表格末尾添加新表格
End Sub
’ 删除表格
Sub DeleteSheets()
Application.DisplayAlerts = False '关闭警告
Worksheets(2).Delete ' 删除
End Sub
’ 移动表格
Sub MovingSheets()
shtBookData.Move after:=Worksheets(Worksheets.Count)
End Sub
Sub MovingBack()
Workbooks("工作簿1").Sheets(1).Move before:=Workbooks("2019排行榜")
End Sub
’ 复制表格
Sub CopySheet()
shtBookData.Copy before:=Worksheets(2)
End Sub
’ 表格重命名
Sub reNameSheet()
shtBookData.Copy before:=Worksheets(1)
ActiveSheet.Name = "排行榜数据备份"
End Sub
’ 显示与隐藏表格
Sub HideSheets()
Sheets("Sheet2").Visible = xlHidden
End Sub
Sub UnHideSheets()
Dim sht As Worksheet
Set sht = Worksheets("Sheet2")
sht.Visible = xlSheetVisible ' xlSheetHidden 隐藏 xlSheetVeryHidden 深度隐藏
End Sub
三、操作工作簿:
’ 工作簿对象
1.引用工作簿
2.打开工作簿
3.新建工作簿
4.保存工作簿
’ 两种方法对工作簿的引用
’ 根据索引引用工作簿,和打开工作表方式一样,索引是打开工作簿的顺序
Sub ReferfingWorkbookByIndex()
Workbooks(1).Activate
End Sub
’ 根据名称引用工作簿
Sub ReferfingWorkbookByName()
Workbooks("2019年高分图书.xlsm").Activate
End Sub
’ 打开工作簿
Sub OpenWorkbook()
Workbooks.Open ("C:\Users\111.xlsx")
End Sub
’ 创建新的工作簿
Sub CreateWorkbook()
Dim wb As Workbook '创建一个变量 wb Workbook是它的类型
Dim wks As Worksheet
Set wb = Workbooks.Add '创建新的工作簿
Set wks = wb.Worksheets.Add '创建新的工作表
wks.Name = "New Sheet" ' 给工作表赋一个名称
' 添加信息
Range("A13").Value = 11
Range("B13").Value = "寄生虫"
Range("C13").Value = ""
Range("D13").Value = 8.9
Range("A13").Resize(1, 4).HorizontalAlignment = xlCenter '居中
Range("A13").Resize(1, 4).Interlor.Color = vbYellow '填充颜色
End Sub
’ vba 操作 excel 有一定层级关系:
’ application 代表 最顶层的程序, workbook 代表 工作簿
’ worksheet 代表 工作表 range 代表 单元格
’ Application.Workbook.Worksheet.Range
’ 保存工作簿
Sub SaveWorkbook()
Workbooks("2019年高分电影.xlsx").Save
End Sub
’ 关闭并保存
Sub CloseAndSave()
Workbooks("2019年高分电影.xlsx").Close True 'Close 附带几个参数 一个True 表示保存更改
End Sub
’ 保存为另一个工作簿
Sub SaveAsWorkbook()
Workbooks("2019年高分电影.xlsx").SaveAs "C:\Users\111.xlsx"
End Sub
四、变量的基本使用:
使用变量:
VBA 的变量可以显示声明 也可以不显示声明, 不显示声明的使用方式类似脚本语言Lua 中变量使用方式。
变量数据类型:
Byte : 1 bytes
Boolean : 2 bytes
Integer : 2 bytes
Long : 4 bytes
LongLong : 8 bytes
Object : 4 bytes
Double : 8 bytes
变量的作用域:
’ 程序 使用未声明的变量
Sub AddNewBookInfo()
bookName = "故宫六百年" ' 1. 使用未声明的变量 bookName
Range("A2").End(xlDown).Offset(1, 0).Select ' 从下往上选最后一个单元格 并向下偏移一行 并选择
ActiveCell.Value = Range("A2").End(xlDown) + 1 ' 给单元格填上序号
ActiveCell.Offset(0,1) = bookName
ActiveCell.Offset(0,2) = "历史"
ActiveCell.Offset(0,3) = 8.8
ActiveCell.Resize(1,4).HorizontalAlignment = xlCenter
MsgBox bookName & "已经成功添加到表格中!" ' & 连接字符串
End Sub
’ 程序 显示地声明变量
Sub AddNewBookInfo()
Dim bookName As String ' 2. 显示地声明变量 声明一个string 类型变量 book
Dim seq As Integer
Dim typeNameStr
Dim score As Double
bookName = "唐吉可德"
seq = 20
typeNameStr = "小说"
score = 8.4
Range("A2").End(xlDown).Offset(1, 0).Select ' 从下往上选最后一个单元格 并向下偏移一行 并选择
ActiveCell.Value = seq
ActiveCell.Offset(0,1) = bookName
ActiveCell.Offset(0,2) = typeNameStr
ActiveCell.Offset(0,3) = score
ActiveCell.Resize(1,4).HorizontalAlignment = xlCenter
MsgBox bookName & "已经成功添加到表格中!" ' & 连接字符串
End Sub
’ 程序 让用户输入变量值
Sub AddNewBookInfo()
Dim bookName As String
Dim seq As Integer
Dim typeNameStr
Dim score As Double
bookName = InputBox("请输入书籍的名称")
typeNameStr = InputBox("请输入书籍的分类")
score = InputBox("请输入书籍的评分")
Range("A2").End(xlDown).Offset(1, 0).Select ' 从下往上选最后一个单元格 并向下偏移一行 并选择
ActiveCell.Value = seq
ActiveCell.Offset(0,1) = bookName
ActiveCell.Offset(0,2) = typeNameStr
ActiveCell.Offset(0,3) = score
ActiveCell.Resize(1,4).HorizontalAlignment = xlCenter
MsgBox bookName & "已经成功添加到表格中!" ' & 连接字符串
End Sub
’ 一个程序调用另一个程序
’ 在这里定义变量 下面函数中都可见
Dim bookName As String ' 也可以写成 Private bookName As String ---- Private表示只在当前模块可见, 如果其他模块也想使用这个变量 可以定义成public
Dim seq As Integer
Dim typeNameStr
Dim score As Double
Sub GetUserInput()
bookName = InputBox("请输入书籍的名称")
typeNameStr = InputBox("请输入书籍的分类")
score = InputBox("请输入书籍的评分")
Call WriteDataToTable ' 调用另一个程序
End Sub
Sub WriteDataToTable()
Range("A2").End(xlDown).Offset(1, 0).Select ' 从下往上选最后一个单元格 并向下偏移一行 并选择
ActiveCell.Value = seq
ActiveCell.Offset(0,1) = bookName
ActiveCell.Offset(0,2) = typeNameStr
ActiveCell.Offset(0,3) = score
ActiveCell.Resize(1,4).HorizontalAlignment = xlCenter
MsgBox bookName & "已经成功添加到表格中!" ' & 连接字符串
End Sub
五、对象变量的使用:
Sub UseRangeObjectVaribles()
Dim rngBookList As Range '声明对象变量
Set rngBookList = Range("B3:B13") ' 建立对象变量 和 对象关联
'Set rngBookList = Range("B3", Range("B3").End(xlDown)) ' 代表B3开始 到 B3 行结束 所有单元格
rngBookList.Font.Color = rgbRed ' 设置对象变量所代表的区域 文字颜色
End Sub
Sub AddNewSheet()
Dim wks As Worksheet '声明对象变量 wks
Set wks = Worksheets.Add ' 新建一个工作表 并赋给wks
wks.Name = "New Sheet" ' 给工作表起一个名字
wks.Range("A1") = "Test" ' 给工作表单元格A1 设置值
wks.Activate
End Sub
Sub UseFindMethod()
Dim sUserInput As String
Dim rngFindCell As Range
sUserInput = InputBox("请输入你需要查找的书籍名称")
Set rngFindCell = Range("B:B").Find(what:= sUserInput)
if rngFindCell Is Nothing Then
MsgBox sUserInput & "这本书不存在!"
Else
MsgBox sUserInput & "在" & rngFindCell.Address & "单元格中找到!"
End if
End Sub
如何声明对象变量:
使用SET关键字:
建立对象变量的引用一般方法
通过方法返回值建立对象的引用
六、消息框Msgbox函数介绍:
Msgbox 函数简介
消息框的常用设定
连接字符串
多行文本的显示
Sub helloWorld()
MsgBox "Hello world", vbYesNo, "提示信息" ' 第一个参数必须的,后面的参数为样式设置
End Sub
Sub MessageWithVaribles()
MsgBox "当前活动单元格的内容是:" & ActiveCell.Value & vbNewLine & "它的分类是:"
End Sub
Sub MessageResponse()
Dim iSeq As Integer
Dim sBookName As String
Dim sBookType As String
Dim dbScore As Double
Dim userResponse As vbMsgBoxResult
Range("A2").End(xlDown).Offset(1,0).Select
iSeq = ActiveCell.Offset(-1, 0) + 1
sBookName = "百年孤独"
sBookType = "小说"
dbScore = 9.2
If dbScore < 8 Then
userResponse = MsgBox("本书籍得分低于8分, 是否确定录入?", vbYesNo + vbExclamation, "低分警告")
If userResponse = vbYes Then ’ 判断用户的选择
' 录入......
End If
Else
' .......
End If
End Sub
七、For Next 循环语句基本使用:
Sub ForNextBasic()
Dim i As Long
For i = 1 To 10
Debug.Print "i =" & i
Next i
For i = 1 To 10 Step 2 'Step 2 代表每次增加2
Debug.Print "i =" & i
Next i
' 逆序循环
For i = 10 To 1 Step -1
Debug.Print "i =" & i
Next i
End Sub
Sub FindFisrtBook()
Dim i As Long
For i = 1 To 16
If Cells(i + 2, "C").Value = "漫画" Then
Cells(i + 2, "B").Interior.Color = vbRed
Exit For ' Exit For 语句 是 结束循环
End If
Next i
End Sub
' 遍历所有工作表
Sub ForLoopInWorksheets()
Dim i As Long
For i = 1 To Worksheets.Count
Debug.Print Worksheets(i).Name
Next i
End Sub
什么是循环变量
Step (步长) 关键字
逆序循环
从循环中提前退出
使用循环遍历集合对象所有元素
八、Do While 循环语句基本使用:
Sub DoLoopExample()
Dim i As Long
Do While i < 10 ' Do Loop 是个循环 While 是判断满足条件将进行下面的循环
i = i + 1
Debug.Print i
Loop
' 上面语句也可以这么写
Do
i = i + 1
Debug.Print i
Loop While i < 10 '
' Do While 和 Do Loop While 还是有区别的, Do while是先判断后执行, Do Loop while是先执行 后判断
Do Until i >= 10 'Until 意思和 While 相反
i = i + 1
Debug.Print i
Loop ' 这里 输出结束和上面一样
End Sub
1. 基本语法
2. 有条件地结束循环
3. Do...While 与 Do...Loop While 的区别
4. Do...Until 的用法
5. 从循环中提前退出
九、Excel VBA 官方文档