一、基础操作

1.添加4月1日到4月30日的所有表

Sub text()
Dim i
For i = 1 To 30
    Sheets.Add after:=Sheets(Sheets.Count)
    Sheets(Sheets.Count).Name = "4月" & i & "日"
Next
End Sub

输出

连续删除

Sub text()
Sheets.Application.DisplayAlerts = False
Dim i
For i = 1 To 30
    Sheets(Sheets.Count).Delete
Next
Sheets.Application.DisplayAlerts = False
End Sub

2.添加并修改表名

Sub text()
Sheets.Add after:=Sheet1
Sheet2.Name = "叶问"

End Sub

3.将表复制到最后一个，并且重命名

Sub text()
Sheets("sheet1").Copy after:=Sheets(Sheets.Count)
ActiveSheet.Name = "lili"
End Sub

4.提取表的名称并汇总

Sub text()
Sheets("汇总").Range("a1") = "姓名"
Dim i
For i = 2 To 4
    Sheets("汇总").Range("A" & i) = Sheets(i).Name
Next
End Sub

5.提取工作表的名称及数值

Sub text()
Dim i
For i = 2 To Sheets.Count
    Sheets("汇总表").Range("a" & i) = Sheets(i).Name
    Sheets("汇总表").Range("b" & i) = Sheets(i).Range("a2")
    
Next
End Sub

6.移动

Sub text()
Sheets("4月1日").Move before:=Sheets("汇总表")
End Sub

move 与copy 的区别
move只移动位置，原表不保留
copy保留原表

7.利用变量给所有表中的单元格赋值

Sub text()
Dim i, j
For j = 2 To Sheets.Count
    Set i = Sheets(j)
    i.Range("A1") = 500
    
Next
End Sub

8.对多个表同时计算

Sub text()
Dim i, s1, s, j
For i = 1 To Sheets.Count
    Set s1 = Sheets(i)
    s = 0
    For j = 2 To 4
        s = s + s1.Range("b" & j)
    Next
    s1.Range("e1") = s
Next
End Sub

9.分模块汇总计算

Option Explicit

Sub 总分()
Dim i, s1, s, j
For i = 1 To Sheets.Count
    Set s1 = Sheets(i)
    s = 0
    For j = 2 To 4
        s = s + s1.Range("b" & j)
    Next
    If s1.Name <> "汇总" Then
        s1.Range("e1") = s
    End If
Next
End Sub

Sub 汇总()
Dim i, s1
For i = 2 To Sheets.Count
    Set s1 = Sheets(i)
    Sheets("汇总").Range("A" & i) = s1.Name
    Sheets("汇总").Range("b" & i) = s1.Range("e1")
Next
End Sub

Sub 合并()
Call 总分
Call 汇总
End Sub

10.添加控件

二、函数

创建并调用函数

1.

Function 随机数()
    随机数 = Int(Rnd * 10) + 1
End Function

Sub 使用函数()
MsgBox 随机数()
End Sub

单元格中用 =随机数() 调用

2.

Function 称呼(x)
    If x = "女" Then
        称呼 = "女士"
    Else
        称呼 = "男士"
    End If
End Function

Sub text()
Dim i, s
For i = 2 To 7
    Set s = Range("b" & i)
    Range("c" & i) = 称呼(s)
Next
End Sub

3.截取数据

Sub text()
Dim s
Set s = Range("a2")
城市 = InStr(s, "市")
区县 = InStr(s, "区")
Range("E2") = Left(s, 城市)
Range("f2") = Mid(s, 城市 + 1, 区县)
Range("g2") = Right(s, Len(s) - 区县)
End Sub

更高效更准确的用正则表达式，和其他语言也是通过用的

4.

Sub text()
Dim s, i
For i = 2 To 5
    Set s = Range("a" & i)
    Range("b" & i) = Split(s, "-")(2) & "年 第" & Split(s, "-")(3) & "周"
Next
End Sub

5.从身份证中提取出生日期

Sub sr()
Dim x, i
i = 2
Do While Range("a" & i) <> ""
    x = Range("a" & i)
    Range("b" & i) = DateSerial(Mid(x, 7, 4), Mid(x, 11, 2), Mid(x, 13, 2))
i = i + 1
Loop
End Sub

6.for each next

把每个工作表名汇总

Option Explicit

Sub text()
Dim i, j
i = 2
For Each j In Worksheets
    If j.Name <> "汇总" Then
        Range("a" & i) = j.Name
        i = i + 1
    End If

Next
End Sub

再逐一删除

Sub 删除()
Excel.Application.DisplayAlerts = False
Dim j
For Each j In Worksheets
    If j.Name <> "汇总" Then
        j.Delete
    End If
Next
Excel.Application.DisplayAlerts = True
End Sub

以下两者相等

For Each j In Worksheets
For i = 1 To Sheets.Count
    Set w = Sheets(i)

for each next 在单元格中的应用

Sub a()
Dim i, s1
i = 1
For Each s1 In Range("A1:A10")
    s1.Value = i
    i = i + 1
Next
End Sub

for each 实战

Sub 汇总()
Dim i, s1, j, k
Set s1 = Sheets("汇总")
For i = 2 To 7
    Name = s1.Range("a" & i)
    For Each j In Worksheets
        If Right(j.Name, 1) = "月" Then
            k = 2
            Do While j.Range("a" & k) <> ""
            If j.Range("a" & k) = Name Then
                s1.Range("b" & i) = s1.Range("b" & i) + j.Range("b" & k)
            End If
            k = k + 1
            Loop
        End If
    Next
Next
End Sub

三、属性

Sub a()
Range("b2") = ThisWorkbook.Name
Range("b3") = ThisWorkbook.Path
Range("b4") = ThisWorkbook.FullName

End Sub

😊以上是我的笔记整理，希望对需要的朋友有所帮助！