VBA

操作工作表

新建表

1
2
3
4
sub 新建()
    Sheets.add after:=sheets(sheets.count)
    sheets(sheets.count).name="工作表" '工作表命名'
end sub

根据模板生成

 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11
12
Sub 生成()
Application.DisplayAlerts = False

Dim i As Integer
For i = 1 To InputBox("生成工作表个数")
    Worksheets(1).Copy after:=Worksheets(Worksheets.Count)
    Worksheets(Worksheets.Count).Name = "12月" & i & "日"
    Worksheets(Worksheets.Count).Range("A1") = "12月" & i & "日"
Next
Worksheets(1).Select
Application.DisplayAlerts = True
End Sub

删除所有工作表

1
2
3
4
5
6
7
Sub 清除()
Application.DisplayAlerts = False
Do While Worksheets.Count <> 1
    Worksheets(Worksheets.Count).Delete
Loop
Application.DisplayAlerts = True
End Sub

根据工作表生成目录

 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11
12
Sub CreateMenu()
Sheets.Add(Before:=Sheets(1)).Name = "目录"  '新建一个目录工作表
Worksheets("目录").Activate
    For i = 2 To Sheets.Count
        If Sheets(i).Visible = True Then
            Cells(i, 1) = Sheets(i).Name  '将其他工作表名称分别填入单元格中
            Cells(i, 1).Select
                ActiveSheet.Hyperlinks.Add Anchor:=Selection, Address:="", SubAddress:="'" & Sheets(i).Name & "'!A1", TextToDisplay:=Cells(i, 1).Value
                '创建超链接
        End If
    Next i
End Sub

操作工作簿

所有工作表另存工作簿

 1
 2
 3
 4
 5
 6
 7
 8
 9
10
Sub 拆分()
Application.ScreenUpdating = False
Dim sht As Worksheet
For Each sht In Worksheets
    sht.Copy
    ActiveWorkbook.SaveAs Filename:="C:\Users\lei\Desktop\T\" & sht.Name & ".xlsx"
    ActiveWorkbook.Close
Next
Application.ScreenUpdating = True
End Sub

操作单元格

常见代码

 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11
12
13
14
[a10]  		'a10单元格,不支持变量'
cells(2,3) 	'第二行第三列,只能选择一个格子,支持变量'
range("a10")	'a10单元格,应用场景广泛'
range("a10").value	'单元格默认属性为 .value,大多情况可以省略'

range("a1").offset(10,0) '下移10行,右移0列'
range("a1000").end(xlUP).Row	'表示a1000单元格往上第一个有数据的格子的行号,.Row返回单元格行号'

range("a10").EntireRow	'a10 单元格所在的整行'
range("a10").resize(1,4)  '重新选择a10单元格区域大小'

range("a10:f10").copy range("a11")		'将a10:f10区域,复制到a11'

range("a10:f10").Merge	'将a10:f10区域合并'

数据拆分到多表(循环)

 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11
12
13
14
15
16
17
18
19
Sub 拆分()
    '工作表建好了情况下,固定列,该段代码仅供参考,通过循环'
Dim i, j, k As Integer
Call 清空   '调用 清空 这个过程
For j = 2 To Sheets.Count
    For i = 2 To Sheets(1).Range("a10000").End(xlUp).Row
        If Sheets(1).Range("d" & i) = Sheets(j).Name Then
            k = Sheets(j).Range("a10000").End(xlUp).Row
            Sheets(1).Range("d" & i).EntireRow.Copy Sheets(j).Range("a" & k + 1)
        End If
    Next
Next
End Sub

Sub 清空()
    For i = 2 To Sheets.Count
        Sheets(i).Range("a2:f1000").ClearContents
    Next
End Sub

数据拆分到多表(筛选)

 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11
12
13
14
15
Sub 拆分()
    '工作表建好了情况下,固定列,效率比for循环高很多
    Dim i As Integer
    Call 清空
    For i = 2 To Sheets.Count
        Sheets(1).Range("A1:F1048").AutoFilter Field:=4, Criteria1:=Sheets(i).Name
        Sheets(1).Range("A1:F1048").Copy Sheets(i).Range("a2")
    Next
    Sheets(1).Range("A1:F1048").AutoFilter  '''取消筛选
End Sub
Sub 清空()
        For i = 2 To Sheets.Count
            Sheets(i).Range("a2:f1000").ClearContents
        Next
End Sub

数据根据某列拆分建表

 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
Sub 根据某列建表并复制数据()
'重点 
'1.新建表避免表名重复
'2.通过用户输入 根据哪列进行拆分
'3.通过筛选将相应的数据拷贝到对应工作表
Application.DisplayAlerts = False
Dim sht As Worksheet
Dim i, irow, j, k As Integer
Dim a As Range
Dim l As Integer '按哪列拆分
l = InputBox("请输入按哪列拆分(输入数字)")
irow = Sheets(1).Range("a10000").End(xlUp).Row '数据行

'删除多余工作表
For j = 2 To Sheets.Count
    Sheets(Sheets.Count).DrawingObjects.Delete
Next

'根据列拆分表
For i = 2 To irow
    k = 0
    Set a = Worksheets(1).Cells(i, l)
    For Each sht In Sheets
        If sht.Name = a Then
            k = 1
        End If
    Next
    If k = 0 Then
        Sheets.Add after:=Sheets(Sheets.Count)
        Sheets(Sheets.Count).Name = a.Value
    End If
Next

'通过筛选复制数据
For j = 2 To Sheets.Count
    Sheets(1).Range("a1:f" & irow).AutoFilter field:=l, Criteria1:=Sheets(j).Name   'field:=l 筛选哪一列(这里是变量 l)
    Sheets(1).Range("a1:f" & irow).Copy Sheets(j).Range("a2")
Next
Sheets(1).Range("a1:f" & irow).AutoFilter
Sheets(1).Select
Application.DisplayAlerts = True	
End Sub

合并多个工作表

 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11
Sub 合并多表()
Dim sht As Worksheet
Dim i, j As Integer
For Each sht In Sheets
    i = Sheets(1).Range("a10000").End(xlUp).Row
    j = sht.Range("a10000").End(xlUp).Row
    If sht.Name <> Sheets(1).Name Then
        sht.Range("a2:f" & j).Copy Sheets(1).Range("a" & i + 1) '最后数据行下面粘贴
    End If
Next
End Sub

单元格格式

 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
'字体格式选项卡'
With Selection.Font
    .Name = "华文琥珀"		'字体'
    .Size = 9		'字号'
    .Strikethrough = False		'删除线'
    .Superscript = False		'上标'
    .Subscript = False		'下标'
    .OutlineFont = False		'大纲字体'
    .Shadow = False		'阴影'
    .Underline = xlUnderlineStyleNone		'下划线'
    .ColorIndex = xlAutomatic		'字体颜色'
    .TintAndShade = 0		'字体颜色变深或变浅'
    .ThemeFont = xlThemeFontNone		'主题字体'
End With

'填充色选项卡'
 With Selection.Interior
    .Pattern = xlSolid		'图案样式'
    .PatternColorIndex = xlAutomatic	'图案颜色'
    .ThemeColor = xlThemeColorDark1		'主题颜色'
    .TintAndShade = 0		'颜色变深或变浅'
    .Color = 65535		'填充色'
    .PatternTintAndShade = 0		'对象的单色和底纹图案'
End With

工作表事件案例1

1
2
3
4
5
6
7
8
9
'标亮选中行,工作表事件,当选取发生变化自动执行以下代码'
Private Sub Worksheet_SelectionChange(ByVal Target As Range)	'选区变换执行'
    Cells.Interior.Pattern = xlNone
    With Selection.EntireRow.Interior
        .Pattern = xlSolid
        .PatternColorIndex = xlAutomatic
        .Color = 65535
    End With
End Sub

工作表事件案例2

 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11
12
'通过单元格内容筛选某列,并自动更新'
Private Sub Worksheet_Change(ByVal Target As Range)	'工作表内容变化执行'
    Application.EnableEvents = False    '关闭事件响应,否则此段代码会卡死
    Dim i, j As Integer
    i = ActiveSheet.Range("a10000").End(xlUp).Row    '数据行数量
    j = ActiveSheet.Range("l10000").End(xlUp).Row   '筛选出来的数据行数量
    ActiveSheet.Range("l1:q" & j).ClearContents     '清除筛选出来的数据
    ActiveSheet.Range("A1:F" & i).AutoFilter Field:=4, Criteria1:=Range("i2")
    ActiveSheet.Range("A1:F" & i).Copy ActiveSheet.Range("l1")
    ActiveSheet.Range("A1:F" & i).AutoFilter
    Application.EnableEvents = True
End Sub

工作簿事件案例1

1
2
3
4
5
6
7
'工作簿保存时备份文件'
Private Sub Workbook_BeforeSave(ByVal SaveAsUI As Boolean, Cancel As Boolean)	'工作簿保存前触发事件'
    Dim dateTime As String
    dateTime = Format(Now(), "mmddhhmmss")  '获取当前时间
    '保存工作簿时 新建一份副本
    ThisWorkbook.SaveCopyAs Filename:="G:\code\vba\跟着王佩丰学VBA附件\VBA07\backup\" & dateTime & ".xlsx"
End Sub

工作簿事件案例2

 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
'制作密码验证表,输入对应的密码显示对应的工作表'
Private Sub Workbook_BeforeClose(Cancel As Boolean)	'工作簿关闭之前执行'
'隐藏除了登录以外的表'
Dim sht As Worksheet
For Each sht In Sheets
    If sht.Name <> "登录" Then
            sht.Visible = xlSheetVeryHidden '隐藏除登录外所有工作表
    End If
Next
ActiveWorkbook.Save '保存活动工作簿
End Sub

Private Sub Workbook_Open() 	'工作簿打开时执行事件
Dim i As String
Dim sht As Worksheet
i = InputBox("请输入密码")
If "123" = i Then
    For Each sht In Sheets
        If InStr(sht.Name, "张三") <> 0 Then    '工作表名包含张三时
            sht.Visible = xlSheetVisible    '显示工作表
        End If
    Next
ElseIf "456" = i Then
    For Each sht In Sheets
        If InStr(sht.Name, "李四") <> 0 Then
            sht.Visible = xlSheetVisible
        End If
    Next
Else
    MsgBox ("密码错误")
    ActiveWorkbook.Close Savechanges:=True '不保存关闭工作簿
End If
End Sub

工作表函数

介绍

'Countif  Vlookup这些函数在VBA里调用:Application.WorksheetFunction
Instr() '函数可返回一个字符串在另一个字符串中首次出现的位置'
Split() '用于将特定字符分开'
On Error Resume Next '跳过错误语句'
IsNumeric()	'返回 Boolean 值,返回变量是否为一个数值'
Val()	'文本转化为数值'
Strings文本函数
Math数学函数
Datetime日期时间
FileSystem文件信息
Financial财务函数
Information信息函数
Interaction交互函数

根据学号查询信息

 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
Sub chaxun()
'On Error Resume Next  表示后面的程序出现"运行时错误"时,会继续运行,不中断
'VLookup(a,b,c,d) a:搜索的值  b:搜索的区域(多列) c:返回哪一列  d:是否精确匹配

On Error Resume Next
Sheets(1).Range("d14:d22").ClearContents
d = Sheets(1).Range("d9")
For j = 2 To Sheets.Count
    Sheets(1).Range("d14") = WorksheetFunction.VLookup(d, Sheets(j).Range("a:h"), 5, 0)
    Sheets(1).Range("d16") = WorksheetFunction.VLookup(d, Sheets(j).Range("a:h"), 6, 0)
    Sheets(1).Range("d18") = WorksheetFunction.VLookup(d, Sheets(j).Range("a:h"), 3, 0)
    Sheets(1).Range("d20") = WorksheetFunction.VLookup(d, Sheets(j).Range("a:h"), 8, 0)
    Sheets(1).Range("d22") = Sheets(j).Name
    If Sheets(1).Range("d14") <> "" Then
        Exit For
    End If
Next
End Sub

Sub tongji()
'WorksheetFunction 通过该对象可以使用工作表函数
'CountA(a) a为某一区域,计算非空单元格
'CountIf(a,b) a为某一区域,b为条件,统计满足条件的单元格
For i = 2 To Sheets.Count
    k = k + WorksheetFunction.CountA(Sheets(i).Range("a:a")) - 1
    m = m + WorksheetFunction.CountIf(Sheets(i).Range("f:f"), "男")
    w = w + WorksheetFunction.CountIf(Sheets(i).Range("f:f"), "女")
Next
Sheets(1).Range("d26") = k
Sheets(1).Range("d27") = m
Sheets(1).Range("d28") = w
End Sub

拆分多表通用型

Sub chaifenshuju()

Dim sht As Worksheet
Dim k, i, j As Integer
Dim irow As Integer '这个说的是一共多少行
Dim l As Integer
Dim str As String '这里是第1处修改,加入一个变量,用于提取当前工作表的名字
str = ActiveSheet.Name '这里是第2处修改,取得当前要处理的表的名字
l = InputBox("请输入你要按哪列分")
'删除无意义的表
Application.DisplayAlerts = False
If Sheets.Count > 1 Then
    For Each sht1 In Sheets
        If sht1.Name <> str Then '这里是第3处修改,不在用“数据”这个固定的名字了,而是用之前取到的名字
            sht1.Delete
        End If
    Next
End If
Application.DisplayAlerts = True '这个地方上课的时候我没改成true,请大家注意一下
irow = Sheets(str).Range("a65536").End(xlUp).Row '这里是第4处修改,不在用“sheet1”这个固定的表名字了,而是用之前取到的名字的表
'拆分表
For i = 2 To irow
    k = 0
    For Each sht In Sheets
        If sht.Name = Sheets(str).Cells(i, l) Then '这里是第5处修改,跟第四个修改一样,把原来的sheet1替换成sheets(str)
            k = 1
        End If
    Next
    If k = 0 Then
        Sheets.Add after:=Sheets(Sheets.Count)
        Sheets(Sheets.Count).Name = Sheets(str).Cells(i, l)   '这里是第6处修改,跟第四个修改一样,把原来的sheet1替换成sheets(str)
    End If
Next
'拷贝数据   注意:第7处修改。原有的Range("a1:f" & irow)已经改为Range("a1:z" & irow)  因为数据可能会列数很多,所以写成到z列,故意多写一些。¥¥¥¥¥¥¥¥¥¥¥¥¥
For j = 2 To Sheets.Count
    Sheets(str).Range("a1:z" & irow).AutoFilter Field:=l, Criteria1:=Sheets(j).Name  '这里是第8处修改,跟第四个修改一样,把原来的sheet1替换成sheets(str)
    Sheets(str).Range("a1:z" & irow).Copy Sheets(j).Range("a1")  '这里是第9处修改,跟第四个修改一样,把原来的sheet1替换成sheets(str)¥¥¥¥¥¥¥¥¥¥¥
Next
Sheets(str).Range("a1:z" & irow).AutoFilter   '这里是第10处修改,跟第四个修改一样,把原来的sheet1替换成sheets(str)
Sheets(str).Select   '这里是第11处修改,跟第四个修改一样,把原来的sheet1替换成sheets(str)
MsgBox "已处理完毕,牛逼不"
End Sub

多文件数据合并

Dir

  1. Dir(文件路径):存在文件返回文件名,不存在返回空值
  2. dir后面的参数可以支持通配符,例如:Dir(“d:/data/苏州.xls*”)

查询某文件下所有文件名

'遍历文件,并提取文件名,以下代码会将data下所有文件名写入A列'
Sub test()
    Dim name As String, i
    i = 1
    name = Dir("G:\code\vba\VBA10\data\*.*")
    Do
        Sheets("data").Range("a" & i) = name
        name = Dir  '当dir() 查询结果有多个文件时,dir则依次返回一个文件名,然后为空,再然后出错
        i = i + 1
    Loop While name <> ""
End Sub

文件合并

Sub 合并多文件数据()
Application.ScreenUpdating = False
    Dim name As String, i, wb As Workbook, sht As Worksheet
    i = 1
    name = Dir("G:\code\vba\跟着王佩丰学VBA附件\VBA10\data2\*.*")
    Do
        Set wb = Workbooks.Open("G:\code\vba\跟着王佩丰学VBA附件\VBA10\data2\" & name)
        For Each sht In wb.Sheets
            sht.Copy after:=ThisWorkbook.Sheets(ThisWorkbook.Sheets.Count) '需指定哪个工作簿
            'split 拆分字符串,返回一个数组
            ThisWorkbook.Sheets(ThisWorkbook.Sheets.Count).name = Split(wb.name, ".")(0) & sht.name
        Next
        name = Dir
        wb.Close
    Loop While name <> ""
Application.ScreenUpdating = True
End Sub

find用法(规避错误)

Sub chazhao()
	Dim rng As Range
    '如果找不到以下代码并不会报错,而是rng为空'
	Set rng = Range("d:d").Find(Range("l3"))
    '进行以下判断,这样就可以避免错误'
	If Not rng Is Nothing Then
         Range("m3") = rng.Offset(0, 3)
	End If

End Sub

拆分工作表通用

Sub chaifenshuju()
Dim sht As Worksheet
Dim k, i, j As Integer
Dim irow As Integer '这个说的是一共多少行
Dim l As Integer
Dim sht0 As Worksheet
Set sht0 = ActiveSheet '需要拆分的表'
l = InputBox("请输入你要按哪列分")

'删除无意义的表
Application.DisplayAlerts = False
If Sheets.Count > 1 Then
    For Each sht1 In Sheets
        If sht1.Name <> sht0.Name Then
            sht1.Delete
        End If
    Next
End If
Application.DisplayAlerts = True 

irow = sht0.Range("a65536").End(xlUp).Row
'拆分表
For i = 2 To irow
    k = 0
    For Each sht In Sheets
        If sht.Name = sht0.Cells(i, l) Then
            k = 1
        End If
    Next

    If k = 0 Then
        Sheets.Add after:=Sheets(Sheets.Count)
        Sheets(Sheets.Count).Name = sht0.Cells(i, l)
    End If

Next

'拷贝数据
For j = 2 To Sheets.Count
    sht0.Range("a1:z" & irow).AutoFilter Field:=l, Criteria1:=Sheets(j).Name
    sht0.Range("a1:z" & irow).Copy Sheets(j).Range("a1")
Next

sht0.Range("a1:z" & irow).AutoFilter
sht0.Select
MsgBox "已处理完毕,牛逼不"

End Sub

数组的妙用

统计金额

Sub 总金额1()

Dim i, xrow, j, k, str, ti

ti = Timer

xrow = Range("a1000000").End(xlUp).Row
ReDim arr(1 To 3, 1 To xrow)
str = Range("O5").Value
For i = 2 To xrow
    If Range("H" & i) = str Then
        k = k + Range("J" & i)
        j = j + Range("L" & i)
    End If
Next

Range("P5") = k
Range("Q5") = k

MsgBox (Format(Timer - ti, "0.000000"))
End Sub
------------------------------------------------------------------------
Sub 总金额2()

Dim i, xrow, j, k, str, ti, arr()

ti = Timer	'获取时间'

xrow = Range("a1000000").End(xlUp).Row	
ReDim arr(1 To 3, 1 To xrow)	'重新定义数组大小'
str = Range("O5").Value
arr = Range("H1:J" & xrow).Value	'连续的将单元格区域赋值给数组,注意:带sheets.range(区域),后面必须跟上value'
For i = 2 To xrow
    If arr(i, 1) = str Then
        k = k + arr(i, 3)
        j = j + arr(i, 2)
    End If
Next

Range("P5") = k

MsgBox (Format(Timer - ti, "0.000000"))	'当前获取时间-程序开始时间,结果为程序运行时间'
End Sub

查找销冠

Sub test()
Dim arr()
Dim j, i As Integer

j = Range("a65536").End(xlUp).Row - 1

ReDim arr(1 To j)	'重新定义数组大小'

For i = 1 To j
    arr(i) = Range("b" & i + 1) * Range("c" & i + 1)
Next

Range("h3") = Application.WorksheetFunction.Max(arr)	'调用工作表函数,数组可以看成一个单元格区域'
'match,查看元素在区域内出现的位置'
Range("h2") = Range("a" & Application.WorksheetFunction.Match(Range("h3"), arr, 0) + 1)

MsgBox LBound(arr) '数组下限'

End Sub

计算回款信息

'问题:有许多汇款账单,但不知道谁是谁,现在知道收到的回款总额(124704)以及订单数(4),求是哪些账单(每个账单金额唯一)'
'思路:尝试组合所有可能的组合,将金额加起来为124704的订单找出来'
Sub huikuan()
Dim arr(), i, j, k, xrow, t
t = Timer
xrow = Range("a65535").End(xlUp).Row - 1
arr = Range("A2:A" & xrow + 1)
For i = 1 To xrow
    For j = 1 To xrow
        For k = 1 To xrow
            For l = 1 To xrow
                If arr(i, 1) + arr(j, 1) + arr(k, 1) + arr(l, 1) = Range("k3") Then '四笔订单和为收到汇款总额时
                    Range("F3") = arr(i, 1)
                    Range("G3") = arr(j, 1)
                    Range("H3") = arr(k, 1)
                    Range("I3") = arr(k, 1)
                    GoTo 100    '程序跳到100处,这里结束所有循环
                End If
            Next
        Next
    Next
Next
100
Range("L3") = Timer - t '程序执行时间
End Sub

ADO

 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
Sub adoTest()
Dim conn As New ADODB.Connection
Dim sql As String, arr()

conn.Open "Provider = Microsoft.ACE.OLEDB.12.0;Data Source=G:\data\Edata.xlsx;extended properties=""excel 12.0;HDR=YES""" '打开excle文件,指定扩展属性以及表头

'conn.Open "Provider=Microsoft.ACE.OLEDB.12.0;Data Source=D:\data\Adata.accdb" '打开access数据库,只需要指定提供者和数据源

'conn.Open "Provider=SQLOLEDB;DataSource=" & Path & ";Initial Catolog=" & strDataName 'Mysql数据库

'conn.Open "Provider=MSDASQL;Driver={SQL Server};Server=" & Path & ";Database=" & strDataName 'MSSQL数据库

'conn.Open "Provider=madaora;Data Source=MyOracleDB; User Id=UserID; Password=Password" 'Oracle数据库

sql = "select * from [data$]" '从data工作表查询所有数据
sql = "select * from [data$] where 性别=男" '条件查询
sql = "select * from [data$] union all select * from [data2$]" '合并两个表数据

Range("a2").CopyFromRecordset conn.Execute(sql) '将查询的记录写入A2单元格

arr = Application.WorksheetFunction.Transpose(conn.Execute(sql).GetRows)    '将查询的记录赋值到数组

'关闭链接
conn.Close
End Sub