vba excel编程三日谈(1)



最近由于要修改一个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的代码区中输入如下代码:

[vb] view plain copy

1. Sub test()  
2.     '去读本文件的单元格的内容  
3.     MsgBox Application.Workbooks("test.xls").Worksheets("Sheet1").Range("A1").Value  
4.     '如果是当前的application,则application可以省略  
5.     MsgBox Workbooks("test.xls").Worksheets("Sheet1").Range("A1").Value  
6.     '如果是当前workbooks,则workbooks可以省略  
7.     MsgBox Worksheets("Sheet1").Range("A1").Value  
8.     '如果是当前的sheet,则worksheet可以省略  
9.     MsgBox Range("A1").Value  
10.     '还可以这么用  
11.     MsgBox Sheets("Sheet1").Range("A1").Value  
12.     'ActiveWorkbook代表当前活动的workbook  
13.     '这样就更直接了  
14.      MsgBox Cells(1,1).Value  
15.     '也可以改写cell的value  
16.     Cells(1,1).value = "我在学vba"  
17.     MsgBox ActiveWorkbook.Worksheets(1).Range("A1").Value  
18.     '还可以这么用  
19.     MsgBox ThisWorkbook.Worksheets(1).Range("A1").Value  
20.     '也可读取外部xls文件的内容  
21.     MsgBox Application.Workbooks("1.xls").Worksheets("Sheet1").Range("A1").Value  
22.     '也可以通过下标应用对象  
23.     MsgBox Application.Workbooks(2).Worksheets(1).Range("A1").Value  
24. End Sub  

当然每个对象都有很多属性和方法可以使用.如例:

[vb] view plain copy

1. Sub test1()  
2.     MsgBox Application.Name  
3.     MsgBox Application.Workbooks(2).Name  
4.     MsgBox Worksheets.Count  
5.       
6.     MsgBox "There are " & CStr(Range("A1:D10").Cells.Count) & " Cells"  
7.       
8.     Sheets(2).Select  
9.     ActiveSheet.Cells(1, 1).Value = "This is the first cell in Sheet2"  
10.     Range("A2").Font.FontStyle = "Bold"  
11.     Range("A2").Font.Size = 13  
12.     Range("A3").Borders.LineStyle = xlContinuous  
13.     Range("A3").Borders.Weight = xlThin  
14. 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

[vb] view plain copy

1. Sub test1()  
2.     Dim i As Integer, j As Integer  
3.     Dim s As String  
4.     i = 2  
5.     j = 3  
6.     MsgBox i + j  
7.     s = "The result is: " & (i + j)  
8.     MsgBox s  
9.     Dim d As Date  
10.     d = #12/12/2002 3:23:00 AM#  
11.     MsgBox d  
12.     Dim k%, l&  
13.     k = 5  
14.     l = 6  
15.     MsgBox k + l  
16.     Dim m As Currency  
17.     m = 123.456  
18.     MsgBox m  
19. End Sub  

如果定义的变量是对象类型, 比如WorkSheet, 则要用set来赋值.

[vb] view plain copy

1. Sub test1()  
2.     Dim sh As Worksheet  
3.     Set sh = ActiveWorkbook.Sheets(1)  
4.     MsgBox sh.Cells(1, 1).Value  
5. End Sub  

定义和使用数组:

[vb] view plain copy

1. Sub test6()  
2.     Dim myarr(3) As Integer  
3.     myarr(1) = 3  
4.     myarr(2) = 4  
5.     myarr(3) = 5  
6.     MsgBox myarr(1) + myarr(2)  
7. End Sub  

过程&函数

通过sub subname(param1, param2 ...) ...... end sub 可定义过程, 也可定义function,function和sub的区别是function有返回值.其他都一样, 在这里就不讨论function了.

一个过程可以调用其他过程.

[vb] view plain copy

1. Sub test1()  
2.     MsgBox Application.Name  
3.     MsgBox Worksheets.Count  
4.     ActiveSheet.Cells(1, 1).Value = "This is the first cell in Sheet2"  
5. End Sub  
6.   
7. Sub test2()  
8.     test1  
9.     'inputBox 是一个系统过程  
10.      Dim name As String  
11.     name = InputBox("Please input your name: ")  
12.     MsgBox "Your name is :" & name  
13. End Sub  

过程可以带参数, 传参数的方式有两种, 传值和传引用. 凡是有编程基础的人都应该明白这两种方式的区别.

[vb] view plain copy

1. '传引用调用  
2. Sub test2()  
3.     Dim i As Integer  
4.     i = 123  
5.     test3 i  
6.     MsgBox i  
7. End Sub  
8.   
9. Sub test3(ByRef i As Integer)  
10.     i = 321  
11.     MsgBox i  
12. End Sub  
13.   
14. '传值调用  
15. Sub test4()  
16.     Dim i As Integer  
17.     i = 123  
18.     test5 i  
19.     MsgBox i  
20. End Sub  
21. Sub test5(ByVal i As Integer)  
22.     i = 321  
23.     MsgBox i  
24. End Sub  

流程控制

if语句:

[vb] view plain copy

1. Sub testif()  
2.     Dim i&  
3.     i = InputBox("Please input your score:", "Score", 60)  
4.     If i > 90 Then  
5.             MsgBox "Your credit is A"  
6.         ElseIf i > 80 Then  
7.             MsgBox "Your credit is B"  
8.         ElseIf i > 70 Then  
9.             MsgBox "Your credit is C"  
10.         ElseIf i > 60 Then  
11.             MsgBox "Your credit is D"  
12.         Else  
13.             MsgBox "Your credit is E"  
14.     End If  
15. End Sub  

select case 语句:

[vb] view plain copy

1. Sub testselectcase()  
2.     Sheets("Sheet3").Select  
3.     Select Case ActiveSheet.Cells(1, 1).Value  
4.         Case Is < 60  
5.             MsgBox "bad"  
6.         Case Is < 70  
7.             MsgBox "So so"  
8.         Case Is < 80  
9.             MsgBox "good"  
10.         Case Is < 90  
11.             MsgBox "very good"  
12.         Case Else  
13.             MsgBox "excellent"  
14.     End Select  
15. End Sub  

循环控制:

[vb] view plain copy

1. Sub testfor1()  
2.     Dim i&, total&  
3.     total = 0  
4.     For i = 1 To 1000 Step 1  
5.         total = total + i  
6.     Next  
7.     MsgBox "total: " & CStr(total)  
8.     total = 0  
9.     For i = 1000 To 1 Step -2  
10.         total = total + i  
11.     Next  
12.     MsgBox "total: " & CStr(total)  
13. End Sub  
14.   
15. Sub testfor2()  
16.  Dim sh As Worksheet  
17.  For Each sh In ActiveWorkbook.Worksheets  
18.     MsgBox sh.Name  
19.  Next  
20. End Sub  
21.   
22. Sub testdowhile()  
23.     Dim i&, total&  
24.     i = 1000  
25.     total = 0  
26.     Do While i > 0  
27.         total = total + i  
28.         i = i - 1  
29.     Loop  
30.     MsgBox total  
31. End Sub  
32.   
33.   
34. Sub testdountil()  
35.     Dim i&, total&  
36.     i = 1000  
37.     total = 0  
38.     Do  
39.         total = total + i  
40.         i = i - 1  
41.     Loop Until i < 0  
42.     MsgBox total  
43. End Sub  

最后说一下vba的with语法和语句换行. with语法是为了减轻程序输入负担, 在with范围类, 默认的当前对象就是with指定的对象, 如下程序 with指定了font, 则下面的.Name .FontStyle等都是指Range("A1").font的成员:

[c-sharp] view plain copy

1. Public Sub testwith()  
2.     With Range("A1").Font  
3.         .Name = "华文彩云"  
4.         .FontStyle = "Bold"  
5.         .Size = 18  
6.         .ColorIndex = 3  
7.         .Underline = 2  
8.     End With  
9. End Sub  

vba程序的基本单元是行, 如果要换行, 需用符号"_"来处理:

[vb] view plain copy

1. Sub test7()  
2.     Dim s As String  
3.     s = "12345" & _  
4.     "67890"  
5.     MsgBox s  
6.     MsgBox ActiveWorkbook.Worksheets(1) _  
7.     .Range("A1").Value  
8. End Sub  

ok, 太累了,这篇就写到这儿.