2021-01-29 vba笔记

宏笔记
1.仅保留所需表，删除其余工作表
Sub 删除工作表1()
Dim j
Excel.Application.DisplayAlerts = False
For Each j In Worksheets
If j.Name <> "汇总" Then
j.Delete
End If
Next
Excel.Application.DisplayAlerts = True
End Sub

Sub 删除工作表2()
Dim i, w
Excel.Application.DisplayAlerts = False
For i = 1 To Worksheets.Count
If Sheets(i).Name <> "汇总" Then
Set w = Sheets(i)
w.Delete
End If
Next
Excel.Application.DisplayAlerts = True
End Sub

3.工作簿，打开、写入、保存
Sub a()
Excel.Application.DisplayAlerts = False
Workbooks.Open "\tsclient\home\Music\A.xlsx"
ActiveWorkbook.Sheets(1).Range("A1") = "0000"
ActiveWorkbook.Save
ActiveWorkbook.Close
Excel.Application.DisplayAlerts = True
End Sub

工作簿，打开、写入、保存
Sub b()
Excel.Application.DisplayAlerts = False
Workbooks.Add
ActiveWorkbook.Sheets(1).Range("A1") = "BBB"
ActiveWorkbook.SaveAs Filename:="\tsclient\home\Music\B.xlsx"
ActiveWorkbook.Close
Excel.Application.DisplayAlerts = True
End Sub

工作簿，另存(待检核 ??)
Sub c()
Excel.Application.ScreenUpdating = False
Dim s1
For Each s1 In Sheets
s1.Copy
ActiveWorkbook.SaveAs Filename:=" \tsclient\home\Music" & s1.Name & ".xlsx"
ActiveWorkbook.Close
Next
Excel.Application.ScreenUpdating = True
End Sub

SaveCopyAs
使用saveas方法将工作簿另存为新文件后，Excel将关闭原文件并自动打开另存为得到的新文件，
如果希望继续保留原文件不打 开新文件，应使用SaveCopyAs方法

基础语句
1、
类型 名称 字符代表 占用存储空间(字节) 包含的数据及范围
整数型 Integer % 2 -32768到32767的整数( 2^16)
长整数型 Long & 4 -2147483648到2147483647的整数( 2^32)
字节型 Byte 1 0到255的整数( 2^8)
日期型 Date 8 范围:100年1月1日至9999年12月31日
字符型 string $ 　 　
单精度 Single ! 4
双精度 Double # 8
货币型 Currency @ 8
工作表 Worksheet 　 当前工作簿所有工作表
单元格 Range 　 当前工作表所有单元格

运算符 作用 计算规则
And 与 左右两边都为T时返回T，否则返回F
Or 或 左右两边其中一个为T就返回T，否则返回F
Not 非 取反
Xor 异域 左右两边值不相同返回T，否则返回F
Eqv 等价 左右两边值相同返回T，否则返回F
Imp 蕴含 等于同 Not 表达式1 Or 表达式2
= 　 等于
<> 　 不等于
< 　 小于

大于
<= 　 小于或等于
= 　 大于或等于
IS 　 比较两个对象的引用变量
Like 　 比较两个字符串是否匹配

函数
MsgBox y 弹出提示文本框
instr(从第几个字符开始找，原字符串，子串)
x = "好好学习，天天向上"
y = InStr(x, "学习")
instrReV() 功能同上，从右边找
split(原字符串，分解成数组的符号)(数组编号)
trim()返回的新字符串去掉原字符两边空格
replace(原字符串，子串，替换成)
lcase() ucase() 字母大小写转换
left(字符串，取n个字符)
Mid(字符串，从第n开始取，取m个字符)

2、选择工作表
Worksheets(1).Select
Worksheets(1).Activate
Sheets("Sheet1").Select
Sheets("Sheet1").Activate

Worksheets只包含工作表，Sheets不仅包含工作表，还包含图表、宏等；
Select代表选定对象（可多个），Activate代表激活对象（仅一个）。

3、添加/删除工作表
Sheets.Add
Sheets.Add before:=Sheets("sheet1") 在sheet1前面加1张工作表
Sheets.Add after:=Sheets("sheet1") 在sheet1后面加1张工作表
Sheets.Add before:=Sheets("sheet1"), Count:=3 在sheet1前面加3张工作表
Sheets.Add after:=Sheets(Sheets.count) 在最后一张工作表后面插入工作表，先计算文件中有几张工作表

Sheets("sheet1").Delete 删除工作表

Excel.Application.ScreenUpdating = False 关闭屏幕更新
Excel.Application.ScreenUpdating = True 重新开启屏幕更新

4、复制/移动工作表
Sheets("sheet1").Copy before:=Sheets(1)
Sheets("sheet1").Copy after:=Sheets(1)
Sheets("sheet1").copy after:=sheets(sheets.count)
Sheets("sheet1").Copy
无论工作表复制到哪里，复制得到的工作表总会成为活动工作表，都可以使用Activesheet引用

sheets("sheet1").move before:=sheet(1)
sheets("sheet1").move after:=sheet(1)
sheets("sheet1").move

5、获取工作簿属性
Range("B2") = ThisWorkbook.Name 获取工作簿名称
Range("B3") = ThisWorkbook.Path 获取工作簿路径
Range("B4") = ThisWorkbook.FullName 获取工作簿路径+名称

6、添加工作簿
Workbooks.Add 添加工作簿
Workbooks.Add "\tsclient\home\Music\模版.xlsx" 添加工作簿并保存
workbooks("模版.xlsx ") 名称引用工作簿
workbooks(3) 使用索引号引用工作簿

7、打开/关闭工作簿
Workbooks.Open " \tsclient\home\Music\模版.xlsx" 打开工作簿
Workbooks("模版").Activate
虽然可以同时打开多个工作簿，但是只有一个活动工作簿。如果想让不活动的工作簿变为活动工作簿，可以用Workbooks对象的Activate方法激活。

workbooks.close关闭当前打开的所有工作簿
workbooks("book1").close关闭指定工作簿

每次关闭时，如果修改了工作簿，系统会提示是否保存修改
workbooks("book1").close True '保存
workbooks("book1").close False '不保存

8、Range属性引用
Range("A1:A10") 引用单个固定的单元格区域
Range("A1:A10,A4:E6,C3:D9") Union(Range("A1:A10"), Range("C1:C10"))引用多个不连续的单元格区域
Range("B1:B10 A4:D6") 引用多个区域的公共区域(相交)
Range("B6:B10", "D2:D8") 引用两个区域围成的矩形区域
Range("a6").EntireRow.Select 选择A6单元格所在的那一整行
Range("a6").EntireColumn.Select 选择A6单元格所在的那一整列

9、Cells属性引用

工作表对象.Cells(行，列)
ActiveSheet.Cells(3,4) 引用工作表中指定行列交叉的单元格

引用单元格区域中某个单元格
Range("B3:F9").Cells(2,3)

将Cells属性的返回结果设置为Range属性的参数
Set r = Range(Cells(x, y) , Cells(i, j)) 等效于 range(Ai,Bj)

10、直接引用单元格
[A1] A1单元格
[A1:D10] A1:D10单元格区域
[A1:A10,C1:C10,E1:D10] 三个单元格区域的并集
[B1:B10 A5:D5] 两个单元格区域的公共部分
[n] 被定义为名称n的单元格区域
这种引用适合引用一个固定的Range对象，但不能使用变量，缺少灵活性。

11、引用整行/整列单元格
ActivSheet.Rows("3:3") / ActivSheet.Rows(3) 活动工作表第3行
ActivSheet.Rows("3:5") 活动工作表第3到5行
ActivSheet.Rows 活动工作所有行
Rows("3:10").Rows("1:1") 3到10行区域中的第1行

ActiveSheet.Colunms("F:G") 活动工作表F到G列
ActiveSheet.Colunms(6) 活动工作表中第6列
ActiveSheet.Colunms 活动工作表所有列
Colunms("B:G").Colunms("B:B") B:G列区域中的第2列 ??

12、清除
Set r = Range("A1:A10")
r.Clear 清除所有内容(包括批注、内容、格式、超链等)
r.ClearComments 清除批注
r.ClearContents 清除内容
r.ClearFormats 清除格式
r.ClearHyperlinks 清除超链接

13、字体
r.Font.Clolr=RGB(255,0,0) 文字颜色
r.Font.Size =24 文字大小
r.Font.Italic = True 是否斜体
r.Font.Bold = True 是否粗体

14、颜色填充（内部属性）

Set r1 = Range("A1:B4,D2,F3:G6")
r1.Interior.Color = vbRed / r.Interior.Color=RGB(255,0,0)

15、合并单元格
r.Merge合并单元格
r.UnMerge 取消合并单元格

16、Range对象的Resize扩大缩小 ??
Range("B2").Resize(1, 3).Select
使用Resize属性可以将指定的单元格区域扩大，得到一个新的单元格区域
Range("B2:E6").Resize(2, 1).Select
当Resize属性的参数小于其父对象包含的行列数，Resize属性将返回一个较小单元格区域

17、Range对象的CurrentRegion连续区域
Range("E7").CurrentRegion.Select

Worksheet对象的UsedRange使用区域
ActiveSheet.UsedRange.Select
Usedrange属性是工作表的属性，是返回工作表中已经使用了的单元格区域

18、Range对象的offset(下移行，右移列)
Range. Offset(2,3) 向下移动2行，向右移动3列

19、Range对象的End属性
Range(“A10”).End(xlUP)
Range("a65536").End(xlUp).Select 选中A列最后一个被使用的单元格
Range("a65536").End(xlUp).Row Row是行号
可设置的参数 参数说明
xlToLeft End+左方向键
xlToRight End+右方向键
xlUp End+上方向键
xlDown End+下方向键

20、Count属性，区域中包含单元格的个数

MsgBox Range("B4:F10").Count 指定区域中单元格的个数
ActiveSheet.UsedRange.Rows.Count 活动工作表中已使用区域的行数
ActiveSheet.UsedRange.Columns.Count 活动工作表中已使用区域的列数

21、Address属性获得单元格的地址
MsgBox "当前单元格地址为:" & Selection.Address

22、用Activate与Select方法选中单元格
Sub a()
ActiveSheet.Range("A1:F5").Select
ActiveSheet.Range("B5").Select
End Sub
Sub b() ActiveSheet.Range("A1:F5").Select
ActiveSheet.Range("B5").Activate
End Sub
选中单元格区域后，再用Activate方法激活该区域里的一个单元格，该区域依然呈选中状态。
如果使用Select方法，只有Select选中的那个单元格呈选中状态。

23、用Copy方法复制、Cut方法剪切单元格区域
ange("A1").Copy Range("C1")
Range("A7").EntireRow.Copy Range("A23") 将A7那一整行拷贝到A23
Range("A7").EntireColumn.Copy Range("A23")
Range("A1").CurrentRegion.Copy Range("H1")

Range("F1:I10").Value = Range("A1:D10").Value 不要格式，只复制数值

Range("A1").Cut Range("C1")
Range("A1:D4").Cut Range("G1")

24、用Delete方法删除指定单元格
Range("B3").Delete shift:=xlToLeft 删除B3单元格，删除后右侧单元格左移
Range("B3").Delete shift:=xlUp 删除B3单元格，删除后下方单元格上移 等于同 Range("B3").Delete
Range("B3").EntireRow.Delete 删除B3单元格所在的行
Range("B3").EntireColumn.Delete 删除B3单元格所在的列

25、DateDiff函数 计算两个日期的时间差
格式：DateDiff（单位，起始时间，截至时间）
单位：”yyyy” -年，“m”-月， “q”-季度， “d”-日， “h”-时，“n”-分钟，“s”-秒
“y”-当年低几日，一般情况与”d”相同；
“w”-周，按实际天数计算，不足7天为0周；
“ww” –周，按跨越周日数计算，只要相隔一个周日即为一周；
DateDiff(“yyyy”,#8/3/1996#,#3/5/2003#)

26、DateAdd函数（时间段）计算一个时间点加上指定时间段后的新时间
格式：DateAdd（单位，时间长度，截至时间）
单位：同上
DateAdd(“d”,500,#3/5/2003#)

27、GoTo语句，程序跳转执行指定语句

Dim a, i
i= 1
x: a = a + i
i = i +1
If i <= 100 Then GoTo x
MsgBox "从1到100的累加和是:" & a End Sub

28、错误处理运行语句
a.错误提示
On Error GoTo a
Sheets("孙兴华").Select
Exit Sub
a: MsgBox "没有这张工作表!"

b. 忽略错误代码的存在，接着执行错误行之后的代码
On Error Resume Next
Sheets("Sheet1").Select
Exit Sub
MsgBox "没有这张工作表!"
无论工作簿中是否存在名称为“Sheet1”的工作表，这行代码都不会得到执行的机会

c. 关闭对程序中运行时错误的捕捉
On Error Resume Next
Sheets("孙兴华").Select
On Error GoTo 0
Sheets("李小龙").Select
Exit Sub
a: MsgBox "没有这张工作表"

使用 On Error GoTo 0 语句后，将关闭对程序中运行时错误的捕捉。
如果程序在 On Error GoTo 0 语句后出现运行时错误，将不会再被捕捉。

  1. 判断函数
    IsDate 判断是否为日期
    IsNumeric判断是否为数字
    TypeName查看变量的数据类型
    返回T/F
    注意:Excel中的isnumber也是判断是否为数字，若遇到日期时，也认为是数字；
    但isnumeric遇到日期时就判断不是数字。

  2. 数据类型转换函数
    函数 作用
    Cbool(x) 转换成逻辑类型的数据
    Cdate(x) 转换成日期类型的数据
    CStr(x) 转换成字符串类型的数据
    Cint(x) 转换成整型的数据，“银行家四舍五入”;
    CInt(3.5)=4 int(4.5)=4 并不是遇到5以上就进位，而是将其舍入到最近的偶数。
    Int(x) 转换成整型的数据，直接取整。int(5.9)=5 int(5.3)=5
    CLng(x) 转换成长整型Long类型的数据
    CDbl(x) 转换成Double双精度浮点型数据
    CCur(x) 转换成Currency货币型数据
    CSng(x) 转换成Single单精度浮点型数据
    CByte(x) 转换成Byte字节类型的数据
    CDec(x) 转换成Decimal小数型的数据

31、数组
a. 起始和终止索引号定义数组的大小
Dim 数组名称(a To b) As 数据类型
a和b是整数不能是变量，a和b定义数组的起始和终止索引号
Dim arr(1 To 100) As Byte
定义一个字节型的数组，名称为arr，可以存储100个数据
可以通过不同的索引号来引用其中存储的各数据，例如:arr(3)数组中的第1个数据

b. Dim arr(99) As Byte 等同于 Dim arr(0 To 99) As Byte

c. 多维数组
Dim 数组名称 (a To b) As 数据类型
Dim arr(1 To 3, 1 To 5) As Integer '定义了一个3行5列，类型为Integer的二维数组
Dim arr(2,4) As Integer 等同于 Dim arr(0 To 1，0 To3) As Integer
Dim arr(2,2,4) As Integer 等同于 Dim arr(0 To 1，0 To 1，0 To 3) As Integer

d. 动态数组
Dim 数组名称( ) AS 数据类型
如果预先不知道数组的大小（维数不确定或可存储的数据个数不确定），在定义数组时只写空括号；
Sub a()
Dim a, i
a = Excel.Application.WorksheetFunction.CountA(Range("A:A")) Dim arr()
ReDim arr(1 To a)
For i = 1 To a
arr(i) = Range("A" & i)
Range("G" & i) = arr(i)
Next
End Sub
将数组定义为动态数组以后，可以用ReDim语句重新定义它的大小，ReDim就可以用变量定义了

e. 用Array函数创建数组
arr = Array(0, 1, 2, 3, 4, 5, 6, 7, 8, 9)

f．用Split函数创建数组
split(等待拆分的字符串，分隔符字符串) 将字符串按照指定分隔符拆分成多个子串
arr = Split("叶问,李小龙,孙兴华", ",")

arr = Split(Range("A2"), "、")
i= 2
For Each a In arr
Range("C" & i) = a
i= i+ 1
Next
用For each遍历数组的时候，数组必需是变体型

g. 单元格区域直接创建数组
arr = Range("A1:C3")
将数组中保存的数据写入单元格区域时，单元格区域的行列数必须与数组的维数相同。

f. 用Join函数将一维数组合并成字符串
Join(数组名称，连接符号)
arr = Array(0, 1, 2, 3, 4, 5, 6,)
a = Join(arr, "#")

j. 数组的最大和最小索引号
Sub a()
Dim arr(1 To 3, 1 To 5), a, b
a = UBound(arr, 1) '求一维数组最大索引
b = UBound(arr, 2) '求二维数组最大索引
MsgBox "第一维的最大索引号是:" & a & Chr(13) & _"第二维的最大索引号是:" & b
End Sub

h. 求数组包含的元素个数
一维数组（ 最大索引减最小索引加1)
Ubound(数组名称)-Lbound(数组名称)+1
二维数组(第一维的总个数乘以第二维的总个数) 二维数组可以看成是一个工作表，只要把“长”和“宽”求出来相乘即可。
arr = Range("A1:C3")
a = UBound(arr, 1)
b = LBound(arr, 1)
c = UBound(arr, 2)
d = LBound(arr, 2)
MsgBox "数组中包含的元素个数是:" & (a - b + 1) * (c - d + 1)

i. Transpose将数组中的数据写入单元格区域
arr = Array(1, 2, 3, 4, 5, 6)
Range("A1:A6") = Excel.Application.WorksheetFunction.Transpose(arr)

©著作权归作者所有,转载或内容合作请联系作者
  • 人面猴
    序言：七十年代末，一起剥皮案震惊了整个滨河市，随后出现的几起案子，更是在滨河造成了极大的恐慌，老刑警刘岩，带你破解...
    沈念sama阅读 202,607评论 5赞 476
  • 死咒
    序言：滨河连续发生了三起死亡事件，死亡现场离奇诡异，居然都是意外死亡，警方通过查阅死者的电脑和手机，发现死者居然都...
    沈念sama阅读 85,047评论 2赞 379
  • 救了他两次的神仙让他今天三更去死
    文/潘晓璐 我一进店门，熙熙楼的掌柜王于贵愁眉苦脸地迎上来，“玉大人，你说我怎么就摊上这事。” “怎么了？”我有些...
    开封第一讲书人阅读 149,496评论 0赞 335
  • 道士缉凶录：失踪的卖姜人
    文/不坏的土叔 我叫张陵，是天一观的道长。 经常有香客问我，道长，这世上最难降的妖魔是什么？ 我笑而不...
    开封第一讲书人阅读 54,405评论 1赞 273
  • 港岛之恋（遗憾婚礼）
    正文 为了忘掉前任，我火速办了婚礼，结果婚礼上，老公的妹妹穿的比我还像新娘。我一直安慰自己，他们只是感情好，可当我...
    茶点故事阅读 63,400评论 5赞 364
  • 恶毒庶女顶嫁案：这布局不是一般人想出来的
    文/花漫 我一把揭开白布。 她就那样静静地躺着，像睡着了一般。 火红的嫁衣衬着肌肤如雪。 梳的纹丝不乱的头发上，一...
    开封第一讲书人阅读 48,479评论 1赞 281
  • 城市分裂传说
    那天，我揣着相机与录音，去河边找鬼。 笑死，一个胖子当着我的面吹牛，可吹牛的内容都是我干的。 我是一名探鬼主播，决...
    沈念sama阅读 37,883评论 3赞 395
  • 双鸳鸯连环套：你想象不到人心有多黑
    文/苍兰香墨 我猛地睁开眼，长吁一口气：“原来是场噩梦啊……” “哼！你这毒妇竟也来了？” 一声冷哼从身侧响起，我...
    开封第一讲书人阅读 36,535评论 0赞 256
  • 万荣杀人案实录
    序言：老挝万荣一对情侣失踪，失踪者是张志新（化名）和其女友刘颖，没想到半个月后，有当地人在树林里发现了一具尸体，经...
    沈念sama阅读 40,743评论 1赞 295
  • 护林员之死
    正文 独居荒郊野岭守林人离奇死亡，尸身上长有42处带血的脓包…… 初始之章·张勋 以下内容为张勋视角 年9月15日...
    茶点故事阅读 35,544评论 2赞 319
  • 白月光启示录
    正文 我和宋清朗相恋三年，在试婚纱的时候发现自己被绿了。 大学时的朋友给我发了我未婚夫和他白月光在一起吃饭的照片。...
    茶点故事阅读 37,612评论 1赞 329
  • 活死人
    序言：一个原本活蹦乱跳的男人离奇死亡，死状恐怖，灵堂内的尸体忽然破棺而出，到底是诈尸还是另有隐情，我是刑警宁泽，带...
    沈念sama阅读 33,309评论 4赞 318
  • 日本核电站爆炸内幕
    正文 年R本政府宣布，位于F岛的核电站，受9级特大地震影响，放射性物质发生泄漏。R本人自食恶果不足惜，却给世界环境...
    茶点故事阅读 38,881评论 3赞 306
  • 男人毒药：我在死后第九天来索命
    文/蒙蒙 一、第九天 我趴在偏房一处隐蔽的房顶上张望。 院中可真热闹，春花似锦、人声如沸。这庄子的主人今日做“春日...
    开封第一讲书人阅读 29,891评论 0赞 19
  • 一桩弑父案，背后竟有这般阴谋
    文/苍兰香墨 我抬头看了看天上的太阳。三九已至，却和暖如春，着一层夹袄步出监牢的瞬间，已是汗流浃背。 一阵脚步声响...
    开封第一讲书人阅读 31,136评论 1赞 259
  • 情欲美人皮
    我被黑心中介骗来泰国打工， 没想到刚下飞机就差点儿被人妖公主榨干…… 1. 我叫王不留，地道东北人。 一个月前我还...
    沈念sama阅读 42,783评论 2赞 349
  • 代替公主和亲
    正文 我出身青楼，却偏偏与公主长得像，于是被迫代替她去往敌国和亲。 传闻我的和亲对象是个残疾皇子，可洞房花烛夜当晚...
    茶点故事阅读 42,316评论 2赞 342

推荐阅读更多精彩内容