VBA应用快速开发(第一版)

目的：让有一定编程基础的人，快速看完本文的情况下便可以顺利开发出满足需求的VBA应用。

开发工具：EXCEl 2007

关键字：单元格，字符串，Sheet，循环，读取，写入 以下是知识点总结：

一，获取某個SHEET中某個单元格中的值，取到的值都是字符串，語句如下 Public Sub show()

Dim grpID As String

grpID=Worksheets(\"sheet1\").Cells(1, 1).value

MsgBox \"Get Cell Value:\" & grpID, vbOKOnly, \"Title\"

End Sub

二，給某個SHEET中某個单元格賦值，語句如下

Public Sub show() Worksheets(\"sheet1\").Cells(1, 1).value = \"123456\"+\"789\"

MsgBox \"Set Value Success\

End Sub

三，將某個SHEET中规定范围内的数据清空

Public Sub show() Worksheets(\"sheet1\").Range(\"A1:Z400\").Clear

MsgBox \"Clear Success\ End Sub 四，for循环 1 Public Sub show()

For i =0 To 10 '當i的值>10的時候跳出循環，每次i的值會默认自加1

MsgBox \"I = \" & i, vbOKOnly, \"Title\" Next i End Sub 五，for循环 2

Public Sub show() For i =0 To 10 Step 2'當i的值>10的時候跳出循環，每次i的值會自加2

MsgBox \"I = \" & i, vbOKOnly, \"Title\" Next i

End Sub

Public Sub show()

Dim value as Integer value = 1

If value = 1 Then

page1 @oacame

六，條件語句

MsgBox \"True\Else

MsgBox \"False\End If

End Sub

七，与break类似，终止for循环

Public Sub show() For i =0 To 10 '當i的值>10的時候跳出循環，每次i的值會默认自加1

MsgBox \"I = \" & i, vbOKOnly, \"Title\" Exit For

Next i

End Sub

八，VBA中With语句的使用

Public Sub show() Dim id as String

With Worksheets(\"sheet1\") id = Cells(1, 1).value

MsgBox \"id = \" & id, vbOKOnly, \"Title\"

End With End Sub

九，获取系统日期，时间 Public Sub show()

MsgBox \"Date = \" & Date, vbOKOnly, \"Title\" MsgBox \"Time = \" & Time, vbOKOnly, \"Title\" End Sub

十，值的比较 用 =

Public Sub show()

If 1 = 1 Then MsgBox \"Yes1\End If

If \"a123\" = \"a123\" Then

MsgBox \"Yes2\End If

End Sub

十一，获取某个Sheet中某个单元格的行号

Public Sub show()

Dim rowNo As Integer

rowNo=Worksheets(\"output\").Cells(1, 1).Row

MsgBox \"rowNo = \" & rowNo, vbOKOnly, \"Title\" End Sub

十二，显示当前选中的单元格的行号

page2 @oacame

Public Sub show() Dim rowNo As Integer

rowNo=Selection.Row

MsgBox \"rowNo = \" & rowNo, vbOKOnly, \"Title\"

End Sub

十三，选中并跳转到某个Sheet Public Sub show()

Sheets(\"LineBean\").Select

MsgBox \"Success\

End Sub

十四，将当前单元格范围选中

Public Sub show()

Range(\"A1:A6\").Select

MsgBox \"Success\

End Sub

十五，行与行之间的连接符号是 _ 如何使用下期补上

Public Sub show() End Sub

十六，判断单元格内容是否为空的方法 Public Sub show()

Dim value As String

value = Worksheets(\"sheet1\").Cells(1, 1).value if value = \"\" Then MsgBox \"True\End If

Dim ln = len(Worksheets(\"sheet1\").Cell(1,1))>0 if ln <= 0 Then

MsgBox \"True\ End If End Sub

十七，定义一个对象需要用到Dim 还有 Set

Public Sub show() Dim obj

Set obj = Worksheets(\"sheet1\").Cells(1, 1) id = obj.value

MsgBox \"id = \" & id, vbOKOnly, \"Title\"

End Sub

Public Sub show() MsgBox \"str1\" & \"str2\

MsgBox \"1234\" + \"str4\

page3 @oacame

十八，字符串的拼接,有两种连接符 + 和 &

End Sub

十九，创建一个具有返回值的函数

Public Sub show()

MsgBox GetComment(\"1234\End Sub

Function GetComment(strTblName As String, strClnName As String) GetComment = strTblName & \" \" & strClnName End Function

二十，While循环，很特别的结束符,对不起大家，是死循环，自己修改下 Public Sub show()

While (1=1)

MsgBox \"Success\Wend

End Sub

二十一，跳出函数使用Exit Function，跳出前请先赋值，否则返回值为空

Public Sub show() MsgBox GetComment(\"1234\End Sub

Function GetComment(strTblName As String, strClnName As String)

if strTblName = \"1111\" Then GetComment = strTblName & \" \" & strClnName Else GetComment = strTblName & \"<0000>\" & strClnName Exit Function End If

MsgBox \"Success\

End Function

二十二，定义常量，用Const关键字 Public Sub show() Const x = 3

MsgBox x, vbOKOnly, \"Title\" End Sub

二十三，定义字符串 Dim x As String Public Sub show()

Dim x As String x = \"1987\"

MsgBox x, vbOKOnly, \"Title\"

End Sub

二十四，定义整型 Dim x As Integer

page4 @oacame

Public Sub show() Const x = 3

MsgBox x, vbOKOnly, \"Title\" End Sub

二十五，字符串分割与数组 Public Sub show()

Dim arr

Dim strSQL As String

strSQL=\"SELECT A,B,C FROM TBL;\" arr = Split(strSQL, \

MsgBox \"数组大小：\" & UBound(arr) + 1, vbOKOnly, \"Title\" For i =0 To UBound(arr) '根据数组的大小进行循环 MsgBox \"元素[\" & i & \"]=\" & arr(i), vbOKOnly, \"Title\"

Next i End Sub

二十六，获取部分字符，在字符串中的索引 Public Sub show()

Dim str As String

str=\"asdfg\"

MsgBox \"位置：\" & InStr(str, \"df\"), vbOKOnly, \"Title\"

End Sub

二十七，字符串范围截取

Public Sub show() Dim str As String str=\"asdfg\" MsgBox \"Result：\" & Mid(str, 3,1), vbOKOnly, \"Title\" End Sub

二十八，字符串左侧截取，右侧截取 Public Sub show() Dim str As String str=\"asdfg\"

MsgBox \"Left->Result：\" & Left(str, 2), vbOKOnly, \"Title\" MsgBox \"Right->Result：\" & Right(str, 2), vbOKOnly, \"Title\"

End Sub

二十九，不等于 <> 三十，转换为大写

Public Sub show()

Dim str As String

str=\"asdfg\"

MsgBox \"Result：\" & UCase(str), vbOKOnly, \"Title\"

End Sub

page5 @oacame

三十一，转化为小写 Public Sub show()

Dim str As String str=\"ASDFG\"

MsgBox \"Result：\" & LCase(str), vbOKOnly, \"Title\" End Sub

三十二，计算选中区域数量

Public Sub show() MsgBox \"Result：\" & Selection.Areas.Count, vbOKOnly, \"Title\"

End Sub

三十三，选择语句Switch

Public Sub show()

Dim theType As String theType=\"a\"

Select Case theType Case \"a\

MsgBox 1, vbOKOnly, \"Title\" Case \"c\

MsgBox 2, vbOKOnly, \"Title\" Case Else MsgBox 3, vbOKOnly, \"Title\"

End Select

End Sub

三十三，字符串字符替换 Public Sub show() Dim theType As String theType=\"a/s/d/f/g\"

MsgBox Replace(theType, \"/\

End Sub

三十四，退出子程序 Public Sub show() MsgBox 1, vbOKOnly, \"Title\"

Exit Sub

MsgBox 2, vbOKOnly, \"Title\"

End Sub

三十五，TXT文件的读取

Public Sub show()

Dim fileObj, fileInfo, retObj, strInfo, strPath As String Dim i, s As Integer

Set fileObj = CreateObject(\"Scripting.FileSystemObject\") strPath = \"c://test.txt\" '文本文件存放的位置 Set fileInfo = fileObj.GetFile(strPath)

page6 @oacame

Set retObj = fileInfo.OpenAsTextStream(1, -2) strInfo = retObj.ReadLine

MsgBox \"Result=\" & strInfo , vbOKOnly, \"Title\" retObj.Close

End Sub

三十六，TXT文件的写入 Public Sub show()

Dim fs,a

Set fs = CreateObject(\"Scripting.FileSystemObject\")'创建一个新文本对象 Set a = fs.CreateTextFile(\"C://test2.txt\创建这个文件 a.WriteLine (\"Hello World.\")'想数据写入TXT文件中 a.Close'文件关闭

MsgBox \"文件写入成功\End Sub

第一版结束@oacame

page7 @oacame