VBA的常见语法整理

1、 for循环

2、字符串连接符 &

Dim i
    For i = 1 To 10
        Cells(i, 2) = "第" & i & "行"
    Next i

3、设定必须显式声明变量

Option Explicit

4、while循环

Dim j
    j = 1
    While Cells(j, 2) <> ""
        Cells(j, 3) = j
        j = j + 1
    Wend

5、do while 循环

Dim j
    j = 1
    Do While Cells(j, 2) <> ""
        Cells(j, 3) = j
        j = j + 1
    Loop

6、删除指定行（1:1为行号，必须为字符串形式）

Rows("1:1").Select
    Selection.Delete shift:=xlUp

7、遍历全部工作表

Dim ws
    For Each ws In Worksheets
        Dim i
        For i = 1 To 10
            ws.Cells(i, 2) = "第" & i & "行"
        Next i
    Next ws

或

Dim h
        For h = 1 To Worksheets.Count Step 1
            Dim ws
            Set ws = Worksheets(h)
            Dim i
            For i = 1 To 10
                ws.Cells(i, 2) = "第" & i & "行"
            Next i
            
        Next h

8、修改特定工作表

Dim ws
        Dim w1 As Worksheet
        Set w1 = Worksheets(3)
        Dim i
        For i = 1 To 10
            w1.Cells(i, 2) = "第" & i & "行"
        Next i

9、添加一个工作表

 Dim w2 As Worksheet
 Set w2 = Worksheets.Add
 w2.Cells(1, 1) = "new"

10、根据工作表名修改工作表

Dim w2 As Worksheet
Set w2 = Worksheets("Sheet2")
w2.Cells(1, 1) = "s2"

11、函数调用

Sub gaga()
    Dim x, y, z
    x = Cells(1, 1)
    y = Cells(1, 2)
    z = jia_fa(x, y)
    Cells(1, 3) = z
End Sub

Function jia_fa(a, b)
    jia_fa = a + b
End Function

12、Sub调用

Sub gaga()
    Dim x, y, z
    x = Cells(1, 1)
    y = Cells(1, 2)
    Call jia_fa(x, y)
End Sub

Sub jia_fa(a, b)
    Cells(1, 3) = a + b
End Sub

13、字符串处理函数

s1 = Len(s) 求长度

s1 = Trim(s) 去两边空格

s1 = Replace(s,a,b) 替换字符串

s1 = LCase(s) 小写字符串

s1 = UCase(s) 大写字符串

s1 = Left(s,n) 从左边取出n个字符

s1 = Right(s,n) 从右边取出n个字符

s1 = Mid(s,i,n) 从s的第i个字符开始取出n个字符

s1 = Instr(s,a) 查找字符串a的位置

s1 = Instr(i,s,a) 从第i个字符开始寻找a，返回a首字母的位置

14、在一个工作薄中访问另一个工作薄

Sub gaga()
    Dim wb As Workbook
    Set wb = Workbooks.Open("G:/True202110789.xls")
    MsgBox wb.Worksheets(1).Cells(1, 1)
    wb.Close
End Sub

15、在一个工作薄中访问另一个工作薄的数据并新建一个工作薄保存

Sub haha()
    Dim wb As Workbook, s
    Set wb = Workbooks.Open("G:/True202110789.xls")
    s = wb.Worksheets(1).Cells(1, 1)
    wb.Close
    Dim wb2 As Workbook
    Set wb2 = Workbooks.Add
    wb2.Worksheets(1).Cells(1, 1) = s
    wb2.SaveAs "D:\测试.xlsx"
    wb2.Close
    
End Sub

16、选择不连续的区域

Dim r As Range
Set r = Range("A5:B7,D6,A2:F4")
r.Value = 2

17、单元格操作

合并单元格 Range.Merge

拆分单元格 Range.UnMerge

清除内容 Range.ClearContents

清除格式 Range.ClearFormats

内容格式全部清除 Range.Clear

修改字号 Range.Font.Size

修改颜色Range.Font.Color = RGB(255,0,0)

修改字颜色Range.Interior.Color = RGB（255，255，0）