最近由于要修改一个excel report，见识了vba的强大。 这个report是一个大牛3年前写的，每天只需打开该文件， 就会自动连接oracle的dev和uat数据库读取最新的市场数据， 生成6个透视图，并比较dev和uat的数据的异同。vba操作数据之方便，生成的report之复杂，深深的吸引了我， 于是乎觉得不学点vba真的对不起老本行。花了3天时间学习， 目前觉得基本可以满足大多数需求，即便是有不懂的地方， 也知道在哪里查资料，该怎么查资料。为了防止自己很快忘记， 于是有了vba excel编程3日谈, 内容涵盖对象模型， 基本语法，excel表格基本操作，事件，ADODB连接数据库读取数据， 生成透视图。本人没有学过vb，写出来的东西在过来人看来未免太简单， 但只求对新手有帮助。
vba excel编程三日谈(1)
vba excel编程三日谈(2)
vba excel编程三日谈(3)
准备工作
vba是一种寄宿语言，像javascript生存在浏览器中一样，vba生存在office应用程序中。所以请确保你安装了office，本人学习的时候采用的office2007， 版本差异会引起一些问题， 但是office2003和office2007的差异不是很大。
新建一个excel文档test.xls， 按alt＋F11进入vba编程界面。双击左面的thisWorkbook，在右边输入：
Private Sub Workbook_Open()
MsgBox "Hello, world"
End Sub
保存并退出excel，然后重新打开该excel，则会看到弹出的消息Hello，world。 如果遇到macro安全警告，请enable。这就是第一个vba程序，Workbook_Open是对事件open的响应函数（关于事件在后面会有更多介绍）。
当然你可以把代码写在任何一个sheet里面，也可以就地运行，调试。双击sheet1，在右边输入：
Sub test()
MsgBox "Hello, world"
End Sub
然后把光标移到函数名test上，点击工具栏上的绿色的箭头即可运行测试该函数。
我们还可以在excel的工作表上添加一个控件（比如按钮），通过点击控件来执行函数。把菜单切换到Developer标签，如下图点击Insert即可选择插入的控件(如果是office2003则在菜单试图->工具栏->控件工具箱)
点击按钮，在工作表的任意位置画一个按钮，同时在选择框中点击你刚编写的函数test，保存。 此时点击按钮即可触发该函数。
当然你还可以录制宏， 这也是学习vba的好方法。在这里不做介绍。
OK， 至此，我们学会在怎么定义一个过程(函数)， 然后怎么触发运行它(通过事件，通过vba运行调试，通过按钮)，准备工作到此结束。
vba excel对象模型
类似javascript中的dom模型, windows编程中的组件对象模型, vba也有自己的对象模型. excel的模型中的对象有很多(自己可以google一下), 但是经常用到的无非这四五个:application -> workbooks -> worksheets -> range ->cells. application处于最顶上,表示正在运行的程序(Excel)本身. workbook工作薄, 即一个excel文件单元, 对应着一个xls文件. worksheet是工作表, 新建一个workbook里面默认包含了3个worksheet; range表示工作表中的一块区域, 比如Range("A1:D10")表示A1:D10之间的那块区域, 一个range包含多个Cell,一个cell即worksheet中的一个小格子. 新建一个文件1.xls,并在sheet1的A1单元格中随便输入内容, 下面的程序将更清楚的表明它们之间的关系. 继续在刚才的test.xls的sheet1的代码区中输入如下代码:
Sub test()
'去读本文件的单元格的内容
MsgBox Application.Workbooks("test.xls").Worksheets("Sheet1").Range("A1").Value
'如果是当前的application,则application可以省略
MsgBox Workbooks("test.xls").Worksheets("Sheet1").Range("A1").Value
'如果是当前workbooks,则workbooks可以省略
MsgBox Worksheets("Sheet1").Range("A1").Value
'如果是当前的sheet,则worksheet可以省略
MsgBox Range("A1").Value
'还可以这么用
MsgBox Sheets("Sheet1").Range("A1").Value
'ActiveWorkbook代表当前活动的workbook
'这样就更直接了
MsgBox Cells(1,1).Value
'也可以改写cell的value
Cells(1,1).value = "我在学vba"
MsgBox ActiveWorkbook.Worksheets(1).Range("A1").Value
'还可以这么用
MsgBox ThisWorkbook.Worksheets(1).Range("A1").Value
'也可读取外部xls文件的内容
MsgBox Application.Workbooks("1.xls").Worksheets("Sheet1").Range("A1").Value
'也可以通过下标应用对象
MsgBox Application.Workbooks(2).Worksheets(1).Range("A1").Value
End Sub
当然每个对象都有很多属性和方法可以使用.如例:
Sub test1()
MsgBox Application.Name
MsgBox Application.Workbooks(2).Name
MsgBox Worksheets.Count
MsgBox "There are " & CStr(Range("A1:D10").Cells.Count) & " Cells"
Sheets(2).Select
ActiveSheet.Cells(1, 1).Value = "This is the first cell in Sheet2"
Range("A2").Font.FontStyle = "Bold"
Range("A2").Font.Size = 13
Range("A3").Borders.LineStyle = xlContinuous
Range("A3").Borders.Weight = xlThin
End Sub
这么多方法和属性记不住怎么办? 没关系,有自动语法提示: 菜单->tools->options 勾选auto list members.
关于对象模型就说这么多, 记不住或不了解的, 可通过录制宏, 自动代码提示, google等渠道获得.
基础语法
数据类型和定义变量
vba的基础数据类型有byte boolean integer long single double currency decimal date...长度精度上有所差别.
如下展示基本数据类型的定义和使用, 其中要注意的是Date类型的赋值比较特殊, 要用##包含起来, 常用类型还有更简单的定义方式:
常用类型说明符
% integer
& long
! single
# Double
$ string
@ currency
Sub test1()
Dim i As Integer, j As Integer
Dim s As String
i = 2
j = 3
MsgBox i + j
s = "The result is: " & (i + j)
MsgBox s
Dim d As Date
d = #12/12/2002 3:23:00 AM#
MsgBox d
Dim k%, l&
k = 5
l = 6
MsgBox k + l
Dim m As Currency
m = 123.456
MsgBox m
End Sub
如果定义的变量是对象类型, 比如WorkSheet, 则要用set来赋值.
Sub test1()
Dim sh As Worksheet
Set sh = ActiveWorkbook.Sheets(1)
MsgBox sh.Cells(1, 1).Value
End Sub
定义和使用数组:
Sub test6()
Dim myarr(3) As Integer
myarr(1) = 3
myarr(2) = 4
myarr(3) = 5
MsgBox myarr(1) + myarr(2)
End Sub
过程&函数
通过sub subname(param1, param2 ...) ...... end sub 可定义过程, 也可定义function,function和sub的区别是function有返回值.其他都一样, 在这里就不讨论function了.
一个过程可以调用其他过程.
Sub test1()
MsgBox Application.Name
MsgBox Worksheets.Count
ActiveSheet.Cells(1, 1).Value = "This is the first cell in Sheet2"
End Sub
Sub test2()
test1
'inputBox 是一个系统过程
Dim name As String
name = InputBox("Please input your name: ")
MsgBox "Your name is :" & name
End Sub
过程可以带参数, 传参数的方式有两种, 传值和传引用. 凡是有编程基础的人都应该明白这两种方式的区别.
'传引用调用
Sub test2()
Dim i As Integer
i = 123
test3 i
MsgBox i
End Sub
Sub test3(ByRef i As Integer)
i = 321
MsgBox i
End Sub
'传值调用
Sub test4()
Dim i As Integer
i = 123
test5 i
MsgBox i
End Sub
Sub test5(ByVal i As Integer)
i = 321
MsgBox i
End Sub
流程控制
if语句:
Sub testif()
Dim i&
i = InputBox("Please input your score:", "Score", 60)
If i > 90 Then
MsgBox "Your credit is A"
ElseIf i > 80 Then
MsgBox "Your credit is B"
ElseIf i > 70 Then
MsgBox "Your credit is C"
ElseIf i > 60 Then
MsgBox "Your credit is D"
Else
MsgBox "Your credit is E"
End If
End Sub
select case 语句:
Sub testselectcase()
Sheets("Sheet3").Select
Select Case ActiveSheet.Cells(1, 1).Value
Case Is < 60
MsgBox "bad"
Case Is < 70
MsgBox "So so"
Case Is < 80
MsgBox "good"
Case Is < 90
MsgBox "very good"
Case Else
MsgBox "excellent"
End Select
End Sub
循环控制:
Sub testfor1()
Dim i&, total&
total = 0
For i = 1 To 1000 Step 1
total = total + i
Next
MsgBox "total: " & CStr(total)
total = 0
For i = 1000 To 1 Step -2
total = total + i
Next
MsgBox "total: " & CStr(total)
End Sub
Sub testfor2()
Dim sh As Worksheet
For Each sh In ActiveWorkbook.Worksheets
MsgBox sh.Name
Next
End Sub
Sub testdowhile()
Dim i&, total&
i = 1000
total = 0
Do While i > 0
total = total + i
i = i - 1
Loop
MsgBox total
End Sub
Sub testdountil()
Dim i&, total&
i = 1000
total = 0
Do
total = total + i
i = i - 1
Loop Until i < 0
MsgBox total
End Sub
最后说一下vba的with语法和语句换行. with语法是为了减轻程序输入负担, 在with范围类, 默认的当前对象就是with指定的对象, 如下程序 with指定了font, 则下面的.Name .FontStyle等都是指Range("A1").font的成员:
Public Sub testwith()
With Range("A1").Font
.Name = "华文彩云"
.FontStyle = "Bold"
.Size = 18
.ColorIndex = 3
.Underline = 2
End With
End Sub
vba程序的基本单元是行, 如果要换行, 需用符号"_"来处理:
Sub test7()
Dim s As String
s = "12345" & _
"67890"
MsgBox s
MsgBox ActiveWorkbook.Worksheets(1) _
.Range("A1").Value
End Sub
ok, 太累了,这篇就写到这儿.