VBA常用语法

最近接触了一下VBA编程，才知道Excel还能编程，而且还如此强大，真的是惊呆了，话不多说，先了解一下VBA常用的语法吧。

1、基础语法

1.  
   //高级for循环
2.  
   Function deleteArrayByIndex(list) As String
3.  
   Dim item
4.  
   For Each item In list
5.  
   MsgBox item
6.  
   Next item
7.  
   End Function
8.  
    
9.  
   //获取当前sheet的名称和位置
10.  
    sheetIndex = ActiveSheet.index
11.  
    sheetName = ActiveSheet.Name
12.  
     
13.  
    //获取最后一行
14.  
    Dim sheetIndex As Integer
15.  
    lastRow = Sheets(sheetName).UsedRange.Rows.Count
16.  
     
17.  
    //删除多行
18.  
    Rows("7:12").Delete
19.  
    Range("A12:Q12").Clear
20.  
    Range("A12:Q12").ClearContents
21.  
     
22.  
    //插入一行
23.  
    Sheets(sheetName).Rows(10).Insert
24.  
     
25.  
    //为某单元格赋值
26.  
    Range("B" & 1).Select
27.  
    Selection.formula = "D"
28.  
     
29.  
    //contains,包含
30.  
    If InStr(str, "zhangsan") <> 0 Then
31.  
    Else
32.  
    End If
33.  
     
34.  
    //for循环 + Replace
35.  
    For i = 0 To UBound(arr)
36.  
    arr(i) = Replace(Trim(arr(i)), ",", "")
37.  
    arr(i) = Replace(valueArr(i), ";", "")
38.  
    Next i
39.  
     
40.  
    //Split
41.  
    list = "1,2,3,4"
42.  
    arr = VBA.Split(list, ",")
43.  
     
44.  
    //获取某单元格公式
45.  
    Range("A12").formula
46.  
     
47.  
    //return
48.  
    exit function(函数)
49.  
    exit sub(事件过程)
50.  
     
51.  
    //截取字符串
52.  
    Mid(a, 2, 3) '提取第二个字符开始之后的3个字符
53.  
     
54.  
    //获取字符串的首字符
55.  
    Left(month, 1)
56.  
     
57.  
    //获取其它sheet单元格数据（第6行第21列）
58.  
    Sheet1.Cells(6, 21).value
59.  
     
60.  
    //获取单元格背景色
61.  
    Range("A1").Interior.ColorIndex
62.  
     
63.  
    //设置单元格背景颜色
64.  
    For Each item In Range(Range("A1"), Range("H10"))
65.  
    If item.value = 0 Then
66.  
    item.Interior.ColorIndex = 10
67.  
    ElseIf item.value = 1 Then
68.  
    item.Interior.ColorIndex = 11
69.  
    End If
70.  
    Next
71.  
     
72.  
    //赋值
73.  
    Dim a As range
74.  
    Set a = Sheets(sheetName).range("A1:C6")
75.  
    a.Borders.LineStyle = 1//表格线宽度
76.  
    a.Font.ColorIndex = 1//颜色
77.  
    a.Font.Name = "Arial"//字体
78.  
    a.HorizontalAlignment = xlCenter//居中
79.  
     
80.  
    //计算方法耗时
81.  
    Dim t As Single
82.  
    t = Timer
83.  
    Timer - t
84.  
     
85.  
    //do循环
86.  
    Sub ClassNamer()
87.  
    Dim MyClasses As New Collection ' 建立一个集合对象（Collection）。
88.  
    Dim Num ' 计数用变量，用来对对象的个数计数。
89.  
    Dim Msg As String ' 提示信息用变量。
90.  
    Dim TheName, MyObject, NameList ' 对象信息用变体。
91.  
    Do
92.  
    Dim Inst As New Class1 ' 建立 Class1 的新实例。
93.  
    Num = Num + 1 ' 把计数变量 Num 加一，然后要求输入新对象个体的名称。
94.  
    Msg = "Please enter a name for this object." & Chr(13) _
95.  
    & "Press Cancel to see names in collection."
96.  
    TheName = InputBox(Msg, "Name the Collection Items")
97.  
    Inst.InstanceName = TheName ' 将名称送入对象实例。
98.  
    ' 若用户输入了名称，将它加入集合。
99.  
    If Inst.InstanceName <> "" Then
100.  
     ' 将命名的对象加入集合。
101.  
     MyClasses.Add item := Inst, key := CStr(Num)
102.  
     End If
103.  
     ' 清除当前的引用，为对下一个对象做准备。
104.  
     Set Inst = Nothing
105.  
     Loop Until TheName = ""
106.  
     For Each MyObject In MyClasses ' 建立名称列表。
107.  
     NameList = NameList & MyObject.InstanceName & Chr(13)
108.  
     Next MyObject
109.  
     ' 将名称列表在消息框中显示出来。
110.  
     MsgBox NameList, , "Instance Names In MyClasses Collection"
111.  
      
112.  
     For Num = 1 To MyClasses.Count ' 从集合中删除名字。
113.  
     MyClasses.Remove 1 ' 因为每删除一个对象后，集合
114.  
     ' 会自动重排顺序，故每次迭代时只需删除第一个
115.  
     Next ' 对象即可。
116.  
     End Sub
117.  
      
118.  
     //debug输出
119.  
     Debug.Print
120.  
      
121.  
     //字符串长度
122.  
     Len(str)
123.  
      
124.  
     //switch
125.  
     Select Case myVal
126.  
     Case 1
127.  
     Case 2
128.  
     Case 3
129.  
     Case Else
130.  
     End Select
131.  
      
132.  
     //获取当月最后一天
133.  
     day(DateSerial(year(Date), month(Date) + 1, 0))
134.  
      
135.  
     //代码换行
136.  
     _
137.  
      
138.  
     //判断是否是数字
139.  
     IsNumeric
140.  
      
141.  
     //退出循环
142.  
     Exit For

2、 excel vba快速填充公式（也就是往下拉）

1.  
   //excel vba快速填充公式（也就是往下拉）
2.  
   Sheet1.range("A1:K1").AutoFill Destination:=range("A1:K10"), Type:=xlFillDefault
3.  
    
4.  
   //**********解决乱码问题***********
5.  
   //（注意：使用[ADODB.Stream]，需要在[VBA代码界面 -> 工具 -> 引用]中添加[microsoft activex data object 2.5 library]以上版本）
6.  
   Dim ts As ADODB.Stream
7.  
   Set ts = New ADODB.Stream
8.  
   ts.Type = adTypeText
9.  
   ts.Charset = "UTF-8"
10.  
     
11.  
    '改行code根据文件而定
12.  
     
13.  
    ts.LineSeparator = adLF
14.  
    ts.Open
15.  
     
16.  
    '文件装载
17.  
     
18.  
    ts.LoadFromFile (filePath)
19.  
    Do While Not (ts.EOS)
20.  
    '行读取：ReadText(adReadLine)
21.  
     
22.  
    '全文件读取：ReadText(adReadAll)
23.  
    lineBuffer = ts.ReadText(adReadLine)
24.  
    …
25.  
     
26.  
    Loop

3、 自定义MsgBox

1.  
   //自定义MsgBox
2.  
   //空 ->默认一个确认按钮
3.  
   //1 ->一个确认，一个取消
4.  
   //2 ->表示三个按钮（中止、再试行、忽略）
5.  
   /*msgBoxRet返回值含义如下：
6.  
   1 OK
7.  
   2 Cancel
8.  
   3 Abort
9.  
   4 Retry
10.  
    5 Ignore
11.  
    6 Yes
12.  
    7 No
13.  
    */
14.  
     
15.  
    msgBoxRet = (MsgBox("I am the most beautiful", 2))

4、文件更改名字

1.  
   //文件更改名字
2.  
   Sub renameFile()
3.  
   Dim f1 As String
4.  
   Dim f2 As String
5.  
    
6.  
       On Error Resume Next
7.  
       f1 = "C:\Users\data\a.html"
8.  
       f2 = "C:\Users\data\b.html"
9.  
       Name f1 As f2
10.  
    End Sub

5、form窗口程序批量更改文本框 

1.  
   //form窗口程序批量更改文本框
2.  
   Private Sub WideToNarrow()
3.  
   Dim item
4.  
    
5.  
   For Each item In UserForm1.Controls
6.  
   If TypeName(item) = "TextBox" Then
7.  
   item.Value = gf_WideToNarrow(item.Value)
8.  
   End If
9.  
   Next item
10.  
    End Sub

6、全角改半角 

1.  
   //全角改半角
2.  
   Public Function gf_WideToNarrow(ByVal strSrc As String) As String
3.  
    
4.  
   Dim strResult As String
5.  
   strResult = StrConv(strSrc, vbNarrow)
6.  
    
7.  
   strResult = Replace(strResult, "乗", "-")
8.  
    
9.  
   gf_WideToNarrow = strResult
10.  
     
11.  
    End Function