VBA的常见语法整理

向右看齐 2022-07-12 08:11 210阅读 0赞

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）