# vba网易课堂 第14、15、18回要认真看
# 1 VBA
代码分为：
- 事件编程代码——比如弹窗等；
- 窗体代码
- 标准代码——模块（比如单击运行等）
- 类代码
# 2 强制声明变量
在“模块”的最上面写 `option explicit`
# 3 for循环
```
for i=1 to 20 step 1
cells(i,5) = cells(i,1) + cells(i,3)
next i
```
next i 可以简写成 next，但如果有很多循环的时候，一般不好观察，所以还是用 i 表示
step1 也可以省略，因为默认步距是1
### 倒序删除
```
for i = g to 2 step -1
if cells(i,2) = "" then
row(i).delete
end if
next i
```
# if 语句
```
option explicit // 强制执行变量的定义
sub 检查()
dim i = cells(2,2)
if(i > 30) then
cells(3,3) = "good"
else
cells(3,3) = "fail"
end if
end sub
```
### elseif 语句
相当于 swich
```
if (sore >=85) then
cells(3,3) = "A"
elseif (score > 75) then
cells(3,3) = "B"
elseif (score > 60) then
cells(3,3) = "c"
end if
```
# 4 逻辑运算
and if or
` if cells(1,1) = "rmb" or cells(1,1) = "人民币" or cells(1,1) = CNY then `
# 5 程序调试
设置断点 ——按程序左边的红色小点，
然后`F8`执行单步操作，接着会看到程序往下运行了一行，到了黄色区，我们这时候想知道变量 i 的值，只要鼠标放到 i 上面点一下就可以知道。
如果想知道此时 cells 的值，也只要点击 cells 即可。
黄色的表示有问题的代码或者是当前运行的代码
# 6 录制宏
例：希望在成绩中找到小于60的，标记成红色
如果不知道如何编写，则可以直接录制一段宏，然后复制其中的代码即可。
```
option explicit
sub 检查是否及格()
dim i
for i=2 to 8
if cells(i,2) < 60
cells(i,2).select
with selection.font
.color = -16776961
.TintAndShade = 0
end with
end if
next i
end sub
```
// 如果是 Rows("6:6")，表示第六行，那么当在 if i 循环中，可以写成
`Rows( i &":" &i )` 不能直接写成"i:i"，因为会变成一个字符串"i:i"，不是循环的 i 变量。
# 7 类 对象、属性、方法
VBA也是面向对象的编程，所以有类。几个常用的：
- Application 代表EXCEL这个程序本身，如果要执行关闭，打开等操作，就在这里；
- Workbook 代表已打开的EXCEL文件。比如：“项目管理台账.xlsx”就是一个workbook类；
- worksheet 代表工作表；
- range 代表单元格范围。
从属关系：
1、每个application 默认有个workbooks对象，指代很多个WorKBOOK
2、每个workbook 默认有个worksheet对象，指代很多个worksheet
3、每个worksheet 默认有个cells对象或range兑现，指代很多个cell。
# 8 子程序
vba 视频教程第15回
```
sub All()
call 客户信息 // 直接调用
call 用户密码
end sub
```
# 9 函数
vba 视频教程第15回
关键字function。主程序可以调用函数。
**在表格中可以调用函数作为公式，函数中也可以调用公式。**
# 10 值传递
# 11 字符串检查
len()
trim()
replace()
left()
right()
mid() 从中间开始查找分解字符串
instr （s, "翔安") 意思是，查找s字符串里面，“翔安”这两个字从第几个字符开始。
# 12 统计行数
```
Dim j
j = ActiveSheet.UsedRange.Rows.Count
```
# 13 range 格式
```
dim w as workbook
set w = workbooks.add
dim r as range
set r = range("A2:B5")
r = 5
r.clearcontents //清除单元格格式
```
- range.clearcontents //清除内容，保留格式
- range.clearfomats //清除所有格式，保留内容
- range.clear //清除所有格式以及内容
- r.font.size
- range.interior.color //表示单元格内部的背景色
- range.merge //合并单元格
- range.unmerge //拆分所有单元格
```
with r.font
.color = rgb(255,0,0)
.bold = true
.size = 12
end with
```
# 14 Application
vba视频第21回
Application.ActiveWorkBook //当前处于激活状态的工作簿book
Application.ActiveSheet //当前处于激活状态的工作表sheet
可以将公式调用到代码中
`application.worksheetfunction`
`m = application.worksheetfunction.max(range("A2:B5"))`
> **范围要写max(range("A2:B7"))，不能只写MAX("A2:B7")**
- Application.displayAlerts = false 禁止弹窗
- Application.displayAlerts = true 允许弹窗
但完了以后要记得再设置成true，以免所有的提示都没弹窗
# 表格结构不一样时，比如第一个表格第一行为“张三”，第二个表格第二行为“张三”，如何解决
> **不用参数的传递**
**vba视频第18回**
# VBA使用vlookup查找
可直接录制一个宏，然后复制代码，或者：
```
dim i,j
i=2
??????j = activesheets.usedrange.rows.count
for i=2 to j
cells(i,5) ="=iferror(vlookup(cells(i,1),[填写_回款登记.xlsx]回款登记!c1:c3,3,false),0)"
```
# 判断有几行
j = activesheets.usedrange.rows.count
或者是指定 dim w as worksheet, set w = workbook("过程资料.xlsx").worksheet(1)
然后可以直接调用w.userange.rows.count
比如：
```
g = w预测.UsedRange.Rows.Count '得出来的值就是这个数字
w预测.Range("E2:F" & g).ClearContents
```
j =Range("A1048576").end(xlup).row
xlToLeft ：向左移动，相当于在源区域按Ctrl+左[方向键]
xlToRight：向右移动，相当于在源区域按Ctrl+右方向键。
xlUp：向上移动，相当于在源区域按Ctrl+上方向键。
xlDown：向下移动，相当于在源区域按Ctrl+下方向键。
# 13 数组
### excel中要表示数组：在某一格输入`=`，然后点击范围，
然后直接按F9，
此时表示的就是数组的形式。
### v转置
选中相关4个单元格，然后输入`=transpose`，
同时按ctrl+shift+enter，即可
### VBA里面都是一维横向数组，无纵向数组，会默认变为二维数组，但可以用transpose转置，如下
`range("A60:E60") = application.worksheetfunction.transpose[{1;2;3;4;5}]`
### **常用直接引用区域产生数组**
引用选中区域，再赋予一个变量。
### EVALUATE 输入数组
`range("A2:B5") = evaluate("{1,2,3,4,5}")` 括号里面要再双引号。
如果是文本，则每个文本旁边都要输入两个双引号
`range("a1:a5") = evaluate("{ ""姓名"", ""性别"" }")`
如果是列方向，就用分号
`range("a1:a5") = evaluate("{ ""姓名""; ""性别"" }")`
### array函数
只能产生横向数组，不能纵向数组，所以不能用分号`;`
`range("") = array(1,2,3,4)`
`range("") = array("a","f","w")`
# 14 VBA 常量
https://zhidao.baidu.com/question/195111214.html
LookIn:=xlValues,按值查找
LookAt:=xlWhole，全部匹配，xlPart部分匹配
SearchOrder:=xlByRows，按行查找
SearchDirection:=xlNext，查找方向，向下查找
# 15Excel强制类型转换
有一些顽固的数值类型无法通过设置单元格格式转成文本，这时候可以用强制类型转换
选中某一列 - 数据 - 分组，直接点击下一步，到最后一步，选择格式，即可完成强制转换。
# 16 数组内的元素连接成字符串
使用join 关键字即可
```
dim arr()
...
cells(i,j) = join(arr, "@")
```
# 17 逻辑变量
exit、goto、not true
**vba 高级 第6回**
想写一个表达式：逐行查找，一旦找到第一个符合逻辑的变量以后，则标记为黄色，并且退出循环，不再继续循环。
跳出循环：
```
sub findFirstData()
dim i as long, found as boolean
i = 3: found = false
do while not found and trim(cells(i,2)) <>""
if inStr(cells(i,6) , "施工图") > 0 then ' 意思是，这个单元格中的字符串包括一个或多个“施工图”字样
range(cells(i,2),cells(i,6)).interior.color = vbyellow
found = true '可以及时退出循环，不用 执行后续啰嗦代码
end if
i = i+1
loop
end sub
```
## exit do 或者 exit for
可以直接用EXIT DO退出do 循环，用 exit for 退出 for 循环，不用再写逻辑判断
> 但只能用于do while 循环，而不能用 while 循环
```
sub find()
dim i as long
do while trim(cells(i,2)) <>""
if instr(cells(i,2),"施工图")>0 then
range(cells(i,2), cells(i,6)).interior.color = vbyellow
exit do '只要写这句话即可退出 do 循环，一找到就退出，不用再一直循环
end if
i = i + 1
loop
end sub
```
## exit sub 退出子过程
```
sub exitText()
msgbox "找到了"
exit sub
msgbox "没找到"
end sub
```
exit 只能跳出自己所在的循环，而不能所有都跳出，除非多个 exit 组合使用。或者用 goto
# 18 goto / on error resume next
goto 不经常使用，但可以直接跳到某个标签中。标签用`啦啦啦:` 文字加冒号来表示
```
sub goto()
do while
if xxxxx= false
goto 啦啦啦:
end if
loop
啦啦啦:
msgbox "跳转成功"
end sub
```
用于错误处理
```
sub goto()
dim i
i = cells(i,2)
on error goto myError:
i = 2 / i '由于这个地方有可能 i 会变成0，使得结果出错，所以我们可以直接 goto，这样就避免别人在使用我们的程序的时候无法调试
myError:
msgbox "if error, please call me!"
end sub
```
但此时程序还是会往下运行。如何只在需要的时候运行：此时可以用 exit sub
```
sub goto()
dim i
i = cells(i,2)
on error goto myError:
i = 2 / i '由于这个地方有可能 i 会变成0，使得结果出错，所以我们可以直接 goto，这样就避免别人在使用我们的程序的时候无法调试
exit sub
myError:
msgbox "if error, please call me!"
end sub
```
## on error resume next
在vba 中引用 excel 函数的时候经常会遇到#N/A的情况，此时就可以用这句话执行。
# 19 判断类型 以及类型转换
vba 高级 第7回
isdate()
isNumeric() '不是 numberic
typename() 可以告诉你输入的是什么类型，返回一个字符串
# 20 ASCII 编码
```
sub ascii()
dim i
a = asc("A") '返回65
s = chr(65) '返回 A
end sub
```
可以用ASCII 编码表示回车和换行符
chr(13) 意思是回车 chr (10)意思是换行
# 21 数组
下标：
```
dim arr(),
for i = Lbound() to ubound(arr)
```
split拆分数组后，返回的是一个数组，于是另外写函数，让 a(i)显示出来。
```
sub split()
dim a() as string, i as long, k as long
a = split(cells(i,2),",")
k = 3
for i = lbound(a) to ubound(a)
cells(k,4) = a(i)
k = k+1
next i
end sub
```
或者用for each x in a
```
dim i , x '如果要用 x 来指代数组中的某个值，则 x 必须为变体类型，不能指定类型。
i = 3
for each x in a
cells(i,4) = x
i=i+1 '这样写了就不用在写 for i = 3 to m
```
## 动态数组 redim()
# 21 vba 操作文本
vba 高级 第11回
## 读取文件：四步：
1. 打开文本。但不会显示在屏幕上，而是后台打开。指定到底是 input 读取（从文本文件中读取并保存到 vba 中）还是 output 输出（把 vba 中的内容输入到 txt 文件中），并指定唯一的数字编号#1，以及：`open "d:\demo\客户信息.txt" for input as #1`
2. 读取内容：` line input # 1, s` 从#1 号文本文件中，读取文本中的一行，并保存成字符串，赋值给 s 变量
3. 判断是否到达末尾 用`EOF(1)`表示（end of file）。括号中的1为文件编号 #1
4. 关闭文件 `close(1)`
> 一定要记得 close
```
sub txt()
dim s as tring, i as long
open "d:\demo\hello.txt" for input as #1
i = 1
do while EOF(1) = false '当代号为1的文件，还没有到达末尾的时候，执行 do，或者直接写成not EOF(1)
line input #1, as s ' line input 函数作为从上到下读取 txt 文件
cells(i,1) = s '赋值给 s 变量
i = i+1
loop
close #1
end sub
```
## 写入文件
三步：打开文件 写入一行 关闭文件
> for output 如果没有这个文件，则新建一个。如果已经有了，则覆盖原有文件内容。
> 如果不想覆盖而想新建：改成open for append追加
```
sub txt()
dim i as integer
open "d:\demo\hello.txt" for output as #2
print #1, trim(cells(3,3));
print #1, trim(cells(3,4))
print #1, trim(cells(3,5)); '有分号，表示这一行跟下一行是连续的，如果没分号，则是换行的。
print #1, trim(cells(3,6))
close #1
end sub
```
# 22 事件函数
原来写在模块中的是要点击运行才会运行，如果要自动运行的话，需要写在“事件”里面。也就是 sheet1或者 sheet2里面。
但只会对当前的 sheet 起作用，如果要对所有 sheet 起作用，则可以在模块中编辑，然后在 sheet 中调用。
> 注意函数参数的传递。
# 23 窗体事件
`form.show` 显示窗体
`form.hide` 隐藏窗体
`unload form` 完全卸载，之前填写的数据都清空
# 24 全局变量
如何表达被选中单元格（被选中的单元格）
也就是说，如果选中了某个记录，想修改后保存，如何处理？
这里涉及到不同事件的跳转。也就是说，当选中某个记录以后，其实系统内这个事件就结束了，需要转到另一个事件中处理。但我们仍需要把现在这个事件中选中的行号传入下一个事件才行。这时候有两种方法：方法 1，用application.selectcell来记录这个被选中的单元格。
## 方法 1：直接写成`application.activecell`即可
```
dim r as range
set r = application.activecell
cells(r.row,2) = txtName.text
cells(r.row,3) = txtAge.text
```
## 方法 2 全局变量
在标准模块中定义一个全局变量 `public rowNumber as range`，在双击事件中，将该事件的行号直接保存入全局变量rowNumber，接着在第二个事件中，就可以直接使用 iRow 了。但要记住，要写成`模块 1.rowNumber`才可以。
- 先在模块 1 中定义全局变量。
- 再从 sheet1 双击事件中，将该行号先暂存到rowNumber 中。
- 最后当单机“修改”按钮时，将 `i = 模块1.rowNumber`即可
# 25 字典！！！
字典是个外部对象。
## 1. 创建字典
```
dim i as integer, k as string, myDic as object //字典是个外部对象，所以要用 object
set myDic = createobject("scripting.dictionary")
```
> 一个字典'可以对应一个item对象，这个ITEM可以是字符串，也可以是数组。
## 2. 几种常用方法
`dim k as integer, myDic as dictionary // k表示字典中的关键字 key`
`myDic.add k, cells(i,4).value` //add为向字典内添加内容的方法
`myDic.key(k)`
`myDic.item(k)`
```
myDic.key("hello") = "hallo" //myDic.key(k)关键字用来查找
```
`myDic.key("乔峰") = '萧峰'` //因为乔峰姓名是关键字，所以要用 myDic.key来处理
`myDic.remove("乔峰")`
`myDic.removeAll`
`myDic.count(k) //统计字典里面有多少个元素`
`myDic.exists(k) //判断字典中是否包含某个字`
```
Sub 不重复统计()
//1.创建字典外部对象
Dim i As Integer, k As String, myDic As Object, j As Integer, a(), b() //k表示字典当中的key关键字
Set myDic = CreateObject("scripting.dictionary")
//2.将原表格中符合条件的信息存放到字典myDic中
For i = 2 To 5
k = Cells(i, 1) //用来记录A列中每找到的项目编号
If Not myDic.Exists(k) Then
myDic.add k, Cells(i, 2) //add后面跟一个keyword关键字，以及我们需要计入的item，item表示对象，可以是字符串，也可以是数字，也可以是数组
Else
myDic.Item(k) = myDic.Item(k) + Cells(i, 2).Value
// 这里必须是 value
End If
Next i
//3.把字典里面的东西写入excel中
j = myDic.Count //count用来统计字典里面到底有几组数据
If j > 0 Then
Dim m As Integer
m = j + 1
a = myDic.keys() //注意是keys复数
b = myDic.Items()
Range("F2:G" & m).ClearContents
Range("F2:F" & m) = Application.Transpose(a)
//一定要记得转置，因为数组是一维横向的，而我们要填的 excel 表格是纵向的
Range("G2:G" & m) = Application.Transpose(b)
End If
End Sub
```
也可以简写写成myDic(k)，默认就是myDic.item(k)，这样的话，如果字典中没存在这个关键字，就可以帮我们创建一个，从而也不用再写if else 了。
```
//整段替换成
myDic(k) = myDic(k) + cells(i,2).value
```
## 3. key可以是字符串，也可以是一个range
所以如果上一句代码写成了`myDic(cells(i,2))`则会出错，因为不同的 range 被当成了不同的对象，所以就无法去重，非要写的话只能写成
`myDic(cells(i,2).value)`
# 25 打开 workbook
一般用法是：
```
dim w as worksheet
set w = workbook.open("d:\……")
```
**一旦 open 了以后一定要记得写 `w.close`**
另一种办法是：用 `getobject`
```
dim i as long, doc as object
for i = 1 to 8 //依次打开文件夹下面的所有文件，这些文件命名为1.docx、2.docx……8.docx
set doc = GetObjecy("d:\演示\" & i & ".docx")
//将文档中的文字写入 excel 中，第 i 行。
cells(i,1) = doc.range.text
//关闭文档
doc.close
```
```
Sub create()
Dim i As Long, doc As Object, m As Worksheet
For i = 1 To 3
//依次打开文件夹下面的所有文件，这些文件命名为1.docx、2.docx……3.docx
Set doc = GetObject("\\Mac\Home\Desktop\" & i & ".docx")
//将文档中的文字写入 excel 中，第 i 行。
Cells(i, 1) = doc.Range.Text
//记得关闭文档
doc.Close
Next i
End Sub
```
# 26 VBA 做网络爬虫
```
Sub APIpachong()
Dim s As String, xh As Object
Set xh = CreateObject("Microsoft.XMLHTTP")
xh.Open "GET", "http://www.boc.cn/sourcedb/whpj", False
xh.send
Cells(1, 1) = xh.responsetext
End Sub
```
则会在相关 cells 中显示网页源代码。再通过正则表达式的识别，就可以抓取相关数据信息了。
