vba excel编程三日谈(1)

         vba excel编程三日谈(1)

分类： VBA 2010-06-09 14:28 16454人阅读 评论(10) 收藏 举报

vbaexcel编程integerapplicationoffice

最近由于要修改一个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 plaincopy

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 plaincopy

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 plaincopy

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 plaincopy

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 plaincopy

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 plaincopy

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 plaincopy

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 plaincopy

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 plaincopy

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 plaincopy

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 plaincopy

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 plaincopy

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, 太累了,这篇就写到这儿.

• 上一篇说说几种项目构建工具
• 下一篇vba excel编程三日谈(2)

主题推荐

编程 vba excel 应用程序 流程控制

猜你在找

linux xargs详解

Oops 消息的产生 例子 定位出错位置

peewee 一个轻量级的ORM二

最大值最小值查找算法C++实现

Android App安全加固行业分析报告

双引号与换行的困惑

平面上求最近点对问题

Swing 架构概述

域名命名规则及交易平台

SQL Server 2005的各种限制table可容纳列数

查看评论

7楼 cy221235 2014-05-23 23:53发表 [回复]

相当好，收了，认真看看。

6楼 klizzard 2013-06-13 16:38发表 [回复]

hi, I am an intership student named Jiatian Liu from J.P. Morgan from UCL in London. well, you know, no pinyin input method in foreign company. sorry about that. Here is the situation. I am involved in a team who is doing the exact VBA programming. And one main function we need to achieve is what you have mentioned in your article. It is that once you open the excel, this excel will connect to oracle and uat database automately to generate six pictures. This function would be really useful and helpful for me. Could you send a copy of the source code of this function or maybe the whole excel report you have mentioned. I will be really grateful. IF you do so, please send it to [e-mail] or [e-mail] or [e-mail], and please zip it before you send it. Thank you very much.

Re: sunxing007 2013-06-13 18:00发表 [回复]

回复klizzard：JP Morgan, Wow, good for you.
The time I wrote this article was about 2 years ago. In this period I have switched team, and I didn't keep that excel anymore, and more seriously, I can't send you that excel even I keep it, you know, it violates company policy and I would be fired.但是好消息是, 如果你把我的三篇文章结合例子都看完了的话, 你一定能做出来, 后面的两篇我提供了很多例子供下载.

Re: klizzard 2013-06-13 18:36发表 [回复]

回复sunxing007：thanks for your help. good luck with you.

5楼 Paul_Tian 2012-10-25 23:38发表 [回复]

不错，最近正准备研究研究VBA

4楼 benbebnmao 2011-12-02 16:24发表 [回复]

真的说的非常详细！学习了！
谢谢sunxing007的辛勤劳动。

3楼 liuxuejin 2011-03-10 14:33发表 [回复]

好东西啊！另外令我称赞 的是楼主的写作精神，写的非常认真！

2楼 sunxing007 2010-07-13 10:43发表 [回复]

对于vba来说, 所有打开的excel都在一个应用程序里, 这个应用程序就是Microsoft.Excel. 也就是文中例子里面的application. 至于正在运行的代码会作用到哪个workbook, 如果没有明确指出的话,应该是当前激活的那个excel文档. 这可能是你输出外部表test.xls的数据的原因. 可以用鼠标激活, 也可以用Application.Workbooks(index).Activate来激活.

1楼 wanglingzhong 2010-07-13 09:51发表 [回复]

好帖～
有个问题请教一下：
我在运行对象模型的例子时，
调试MsgBox Range("A1").Value 输出的是我当前表的数据，而前面几条语句都是输出的外部表test.xls的数据，如何解释啊？
还有就是什么操作才能激活workbook

谢谢

Re: sunxing007 2010-07-13 10:44发表 [回复]

回复wanglingzhong：对于vba来说, 所有打开的excel都在一个应用程序里, 这个应用程序就是Microsoft.Excel. 也就是文中例子里面的application. 至于正在运行的代码会作用到哪个workbook, 如果没有明确指出的话,应该是当前激活的那个excel文档. 这可能是你输出外部表test.xls的数据的原因. 可以用鼠标激活, 也可以用Application.Workbooks(index).Activate来激活.