ExcelVBACh1.docx

ExcelVBACh1.docx

《ExcelVBACh1.docx》由会员分享，可在线阅读，更多相关《ExcelVBACh1.docx（42页珍藏版）》请在冰豆网上搜索。

ExcelVBACh1

学习微软Excel2002VBA编程和XML，ASP技术

作者：

JulittaKorol翻译：

TigerChenNov28’2004

本书展示了Excel2002在标准用户界面之外什么是可行的。

如果你曾经想不使用菜单来打开一个新的工作表，或者创建一个充分自动化的自定义窗体来收集数据和在工作表里存储结果，那么你必须学习一些编程。

本书教你如何通过将一些费时的和重复的工作交给Excel，从而更加成果丰富。

使用Excel内置语言，VBA（VisualBasicforApplications），你将给自己或他人带来非常高的自动化程度的电子表格。

通过使用许多内置的编程工具，你做得可以比想象中容易得多。

你不要增加额外费用，除非你想熟悉Excel背后的秘密。

在Excel窗口下，同时按下Alt+F11，你将进入VB编辑器界面——Excel的编程界面。

既然这个保护得很好的秘密已经公开了，就让我告诉你更多一些。

除了VBA之外，本书还介绍了两种可以和Excel并用的热门英特网技术。

一种是ASP（ActiveServerPages），另一种是XML（ExtensibleMarkupLanguage）。

你也可以学习到许多其它的支持技术。

因此，如果你真正想要获得一些热门技术，请立即购买本书，并且不要浪费时间，马上开始学习。

LearnMicrosoftExcel2002VBAProgrammingwithXMLandASP带领你从始至终创建VBA过程，VBScripts，ASP页面，XML文件和XSL工作表。

沿着这条路，有许多详细的，适用的“如何做”例子和插图。

本书的方法是“由做而学”。

本书的前面几章介绍了一些基本的VBA概念，循序渐进，复杂的主题在后面的章节。

十七章中的每一章是按循序的。

此外，本书还由四章附录，讨论在Excel里针对一些特殊方面的操作和编程。

本书可以当作是一种在办公室或家里学习的课程。

许多课程都有前提条件，本书也不例外。

LearnMicrosoftExcel2002VBAProgrammingwithXMLandASP不会向你介绍Excel的基本东西，例如菜单和快捷键。

我们假设你已经喜欢使用Excel，并且有兴趣学习如何与Excel在它自己的语言里交流，学习如何将它与现在的英特网技术结合。

第一章电子表格自动化简介和了解宏命令

你准备好了增进你的微软Excel2002电子表格的智能吗？

通过将日常工作自动化，你可以使你的电子表格更快，更有效。

本章带领你步入使用宏命令来加速电子表格的过程。

你将学到宏是什么，如何以及什么时候使用它们，乃至如何编写和修改宏代码。

开始学习宏命令很简单。

创建宏并不需要什么，只是一些你已经拥有的知识——基本的微软Excel2002菜单知识和电子表格概念。

你准备好开始了吗？

确保你坐在计算机前并且打开了Excel2002。

了解宏

宏是一些储存了一系列命令的程序。

当你创建一个宏命令的时候，你只是将一系列的键盘输入结合成一个简单的命令，你以后可以“回演”这个命令。

因为宏命令可以减少复杂任务的步骤，使用宏命令可以显著得减少你花在创建，设置格式，修改和打印工作表的时间。

你可以通过Excel内置的录制工具来创建宏命令，也可以在代码编辑器里面直接写代码。

微软Excel2002电子表格具有强大的编程功能。

技巧1－1：

普通语言

Excel5是市场上第一个使用VBA的软件。

从那以后，VBA开拓了在所有微软办公应用软件中的应用。

这意味着你从本书中学习的VBA将来同样可以应用到其它微软办公软件中，例如：

Word,PowerPoint,OutlookorAccess

宏命令的普通应用

微软Excel2002带来了很多内置，节省时间的特点，这些使你工作得更快更聪明。

在你决定用宏命令来自动化工作表任务前，确保没有现成的内置工具来做这项任务。

然而，当你发现你需要反复地做一些动作，或者Excel没有提供一个内置工具帮你完成该任务，那么创建一个宏命令吧。

宏命令可以使你能够将工作表的任何部分工作自动化。

例如，你可以自动化数据录入——创建一个宏命令在工作表输入标题或者用新标签取代列标题。

宏命令可以帮你检查选中的工作表区域里的重复值。

你可以通过宏命令快速地将格式应用到多个工作表，并且可以结合不同的格式，例如字体，颜色，边框和阴影等。

尽管如此，Excel还拥有非常强大的图表功能，如果你想要将图表创建和格式设置自动化，那么宏命令是一个好方法。

宏命令也可以帮助你设置打印区域，页边距，页眉，页脚，以及选择特殊的打印选项。

写宏之前的计划

在你创建一个宏命令之前，花几分钟来考虑你究竟想做什么。

因为宏命令是一大堆键盘输入的集合，事先计划你的行动非常重要。

最早的计划宏命令的方法是手动地将宏命令需要做的事情做一遍。

在你做键盘输入的同时，在一张纸上记录下他们实际发生的情况，不要漏掉任何东西。

象录音机一样，Excel可以将你的所有动作录制下来（译者：

事实上并非如此，有些操作是无法录制的）。

如果在录制宏之前，你没有很好地计划，你会录制很多不必要的步骤，而这些都会影响运行速度。

尽管修改宏代码比去除录制宏里面不必要的步骤容易，但是，仅仅录制必要的步骤会节省你修改代码的时间和以后的麻烦。

假设你想看一眼哪些区域是文本，哪些是数字以及哪些是公式，图1－1显示了使用不同的字体颜色和样式来区分这些单元格里潜在的内容。

图1－1

如何设置如图1－1所示的格式？

打开一个含有公式计算的工作表，或者创建一个如图所示的例子。

如果你用图示例子，请确保用SUM函数计算每月和季度的总结。

在录制宏之前，请做如下操作：

1．选取一个单元格

2．选择“编辑”－“定位”

3．在“定位”对话框中，点击“特殊”按钮

4．在“特殊”对话框中，勾选“常数”——勾选“文本”，同时去除“数字”，“逻辑值”和“错误值”的勾选

5．按“确定”返回工作表。

注意，这时含有文本的单元格已经被选中了。

小心，不要改变选中区域，直到你在下一步做一些必要的格式设置

6．对选中区域。

选择“格式”－“单元格”

7．在单元格格式设置对话框，选择“字体”－设置字体为“粗体”，颜色为“紫色”。

然后点击“确定”关闭对话框。

注意，含有文本的单元格显示了不同的颜色。

步骤1到7教你定位到文本单元格，要定位数字单元格，请按如下操作：

8．选取一个单元格

9．选择“编辑”－“定位”

10．在“定位”对话框中，点击“特殊”按钮

11．在“特殊”对话框中，勾选“常数”——勾选“数字”，同时去除“文本”，“逻辑值”和“错误值”的勾选

12．按“确定”返回工作表。

注意，这时含有数字的单元格已经被选中了。

小心，不要改变选中区域，直到你在下一步做一些必要的格式设置

13．对选中区域。

选择“格式”－“单元格”

14．在单元格格式设置对话框，选择“字体”－设置字体颜色为“暗蓝色”。

然后点击“确定”关闭对话框。

注意，含有数字的单元格显示了不同的颜色。

步骤8到14教你定位到数字单元格，要定位公式单元格，请按如下操作：

15．选取一个单元格

16．选择“编辑”－“定位”

17．在“定位”对话框中，点击“特殊”按钮

18．在“特殊”对话框中，勾选“公式”

19．按“确定”返回工作表。

注意，这时含有公式计算结果的单元格已经被选中了。

小心，不要改变选中区域，直到你在下一步做一些必要的格式设置

20．对选中区域。

选择“格式”－“单元格”

21．在单元格格式设置对话框，选择“字体”－设置字体为“粗体”，颜色为“红色”。

然后点击“确定”关闭对话框。

注意，含有公式的单元格显示了不同的颜色。

步骤15到21教你定位到公式单元格。

你可以在工作表中加上图例，以便容易理解。

22．选取区域A1:

A3，选择“插入”－“行”

23．选择单元格A1

24．选择“格式”－“单元格”，并且在“填充色”页点击“紫色”，点击“确定”返回工作表

25．选择B1，输入“文本”

26．选择单元格A2

27．选择“格式”－“单元格”，并且点击“暗蓝色”，“确定”返回

28．选择B2，输入“数字”

29．选择A3

30．选择“格式”－“单元格”，点击“红色”，“确定”返回

31．选择B3，输入“公式”

32．选择A1

完成步骤22到32后，A1:

A3单元格会显示一个简单的颜色图例，如图1－1所示。

正如你所看到的，不管工作表显示的任务多么简单，你却可能需要很多步骤来达到预期效果。

创建一个可以重复你刚才操作的宏命令，真的很省时间，特别是当你需要重复这个相同的工作到很多工作表中去。

录制宏

既然你已经知道了你需要做哪些操作，是时候打开你的宏录制器来创建你的宏了。

在你依照下面的录制步骤之前，请确保你已经清除了刚才例子里的格式。

按下Ctrl+A以选中整个工作表，选择“编辑”－“清除”－“格式”选择A1:

A3并且选择“编辑”－“删除”。

在“删除”对话框，选择“整行”然后点击“确定”。

依照以下步骤来创建你的第一个宏命令：

1．选择一个单元格

录制宏之前，你应该想好是否要录制当前单元格的位置。

如果你需要宏总是从一个特定的位置开始，那么先打开宏录制器再选择你想要宏开始选中的特定位置。

如果当前单元格的位置无关紧要，那么先选择一个单元格，然后才打开宏录制器。

选择“工具”－“宏”－“录制新宏”。

出现一个录制宏对话框。

图1－2当你录制新宏的时候，必须有名字。

你也可以给它设置一个快捷键，储存地方以及描述。

2．给宏取个名字“WhatsInACell”

技巧1－2：

宏命名

如果你忘记给宏命名，Excel会给出一个默认的宏名，例如：

Macro1，Macro2，等等。

宏名可以包含字母，数字和下划线，但是第一个字必须是字母（译者：

中文亦可，建议用英文）。

例如：

Report1是有效的宏名，然而1Report则是非法的。

宏名里不能含有空格。

如果你隔开宏名中的每个词，可以使用下划线。

例如：

WhatsInACell，改为Whats_In_A_Cell。

3．在宏的存贮位置里，选择“当前工作簿”

技巧1－3：

保存宏

Excel让你可以将宏保存在三个地方：

个人宏工作簿——如果你将宏保存在这里，你每次使用Excel的时候都可以使用这个宏。

个人宏工作簿在XLStart文件夹中。

如果这个工作簿不存在，那么当你第一次使用这个选项的时候，Excel会自动生成这个工作簿。

新工作簿——Excel将宏放在一个新工作簿里。

当前工作簿——宏将被保存在你正在使用的工作簿里面。

4．在描述框里输入：

显示单元格里潜在的内容：

文本，数字，公式

5．点击“确定”关闭宏录制对话框，并开始录制。

这时，出现了“停止录制”工具栏。

Excel下面的状态栏显示“准备录制”

图1－3停止录制工具栏提供按钮来停止录制，以及让Excel如何在录制宏的时候处理单元格地址

技巧1－4：

单元格地址：

相对或绝对?

绝对——如果在执行宏命令的过程中，无论哪些单元格被选中了，你都希望宏在特定的单元格执行这些录制的操作，那么使用绝对单元格地址。

绝对单元格引用具有如下形式：

$A$1，$C$5等。

Excel宏录制器默认使用绝对引用。

在录制前，确保停止录制工具栏上的第二个按钮没有被按下去。

当鼠标指向这个按钮，工具提示“相对引用”。

相对——如果你想要宏可以用在任何区域，就打开相对引用。

相对引用具有如下形式：

A1，C5等。

在录制前，确保停止录制工具栏上的第二个按钮已经被按下去了。

记住，Excel将会继续使用相对引用，直到退出Excel或者再次点击相对引用按钮。

在录制宏的过程中，你可以使用这两种引用方法。

例如：

你可以选择一个特定单元格（如$A$4），做一个操作，然后选择另外一个相对于当前位置的单元格（如C9，他在当前单元格$A$4往下5行和向右2列的位置）。

当你复制单元格时，相对引用会自动调整引用，而绝对引用则不会。

6．从新进行刚才你手动完成的那些操作（参见“写宏之前的计划“）

录制宏的时候，只有当你按下了回车键或者点击了确定之后，你的操作才会被录制。

如果你按下了Esc键或者点击了取消，宏录制器不会录制任何操作。

7．完成所有操作后，点击停止录制工具栏上的“停止录制”按钮，或者选择“工具”－“宏”－“停止录制”

运行宏

你创建了一个宏命令后，至少要运行一次以确保它运行正确。

在本章的后面，你将学到好几种运行宏的方法，不过，现在，使用菜单命令。

要看到你的成果，确保清除了例子的格式。

按下Ctrl+A以选中整个工作表，选择“编辑”－“清除”－“格式”选择A1:

A3并且选择“编辑”－“删除”。

在“删除”对话框，选择“整行”然后点击“确定”。

稍后，你将在另外一个宏里面录制清除工作表格式的步骤。

1．打开任何包含文本，数字和公式的工作表

2．选择“工具”－“宏”－“运行宏”来打开宏对话框

3．点击你要运行的宏的名称（参见图1－4）

4．选择“运行”，执行宏

图1－4在宏对话框，你可以选择一个宏，运行，编辑或者删除它

你也许经常会发现录制的宏不会按你预期的和你第一次操作那么运行。

也许在录制宏的时候，你选择了错误的字体，或者忘记改变单元格颜色，或者你临时发现最好加上一个步骤。

不必惊慌。

Excel允许你修改代码，而不会强迫你重新录制那些单调的操作。

修改宏代码

你必须知道你的宏代码放在哪里，你才能找到并修改它。

回想你打开宏录制器的时候，你选择了“当前工作簿”作为存储地址。

最容易找到宏的方法是打开宏对话框，如图1－4所示。

1．选择“工具”－“宏”

2．选择宏名（本例中为WhatsInACell）

3．点击“编辑”按钮

Excel打开一个专门的窗口，叫做VisualBasicEditor（VBE）如图1－5所示。

利用快捷键Alt+F11可快速地在Excel表格界面和代码窗口切换。

选择VBE菜单上的关闭选项可以关闭VBA代码窗口，返回到电子表格界面。

代码窗口暂时看上去有些令人迷惑，不必担心。

只要你开始录制宏，以及尝试写一些代码，你终将这个屏幕所有的组件。

现在，看一下代码窗口的菜单和工具栏。

这两个工具栏和Excel窗口的菜单完全不同。

代码窗口的菜单和工具包含一些编程和测试代码所需要的工具。

只要你彻底地学习本书的每一章，你就会成为使用这些工具的专家。

图1－5VBE窗口是编辑宏命令和书写新的VBA代码的地方

VBE窗口的主要部分是多个窗口的集合界面，这些窗口在你创建和测试VBA过程的时候是及其有用的。

图1－5显示了三个集合在一起的窗口：

工程窗口，属性窗口和代码窗口。

工程窗口显示一个开启的模块文件夹，在这里，模块1被选中了。

Excel录制你在工作表里的操作叫做模块1，模块2，等等。

在本书接下来的章节里，你将利用模块来编写你自己的过程代码。

模块类似于Word中的一个空白文档。

储存每个单独模块的文件夹称为“模块”

技巧1－5：

宏还是过程？

宏是通过内置宏录制器录制的，或者在VB编辑器里手动输入的一系列指令或函数。

从Excel5.0开始，“宏”经常被“过程”这个更广的概念所代替。

尽管这两个词可以交替互换使用，但是，许多编程者更喜欢“过程”。

虽然宏可以让你模仿键盘操作，真正的过程则还可以执行一些不能通过鼠标，键盘或者菜单来做的操作。

换句话说，过程是一个更复杂的宏，它结合了传统编程语言的言语结构。

代码窗口（参见图1－5）显示了下列由宏录制器录制的代码：

SubWhatsInACell（）

'

'WhatsInACellMacro

'Macrorecorded5/31/2002byJulittaKorol

'Indicatesthecontentsoftheunderlyingcells:

text,numbers,formulas.

'

Selection.SpecialCells（xlCellTypeConstants,2）.Select

WithSelection.Font

.Name="Arial"

.FontStyle="Bold"

.Size=10

.Strikethrough=False

.Superscript=False

.Subscript=False

.OutlineFont=False

.Shadow=False

.Underline=xlUnderlineStyleNone

.ColorIndex=13

EndWith

Range（"B6"）.Select

Selection.SpecialCells（xlCellTypeConstants,1）.Select

WithSelection.Font

.Name="Arial"

.FontStyle="Regular"

.Size=10

.Strikethrough=False

.Superscript=False

.Subscript=False

.OutlineFont=False

.Shadow=False

.Underline=xlUnderlineStyleNone

.ColorIndex=11

EndWith

Range（"C6"）.Select

Selection.SpecialCells（xlCellTypeFormulas,23）.Select

WithSelection.Font

.Name="Arial"

.FontStyle="Bold"

.Size=10

.Strikethrough=False

.Superscript=False

.Subscript=False

.OutlineFont=False

.Shadow=False

.Underline=xlUnderlineStyleNone

.ColorIndex=3

EndWith

Range（"A1:

A3"）.Select

Selection.EntireRow.Insert

Range（"A1"）.Select

WithSelection.Interior

.ColorIndex=13

.Pattern=xlSolid

.PatternColorIndex=xlAutomatic

EndWith

Range（"B1"）.Select

ActiveCell.FormulaR1C1="Text"

Range（"A2"）.Select

WithSelection.Interior

.ColorIndex=5

.Pattern=xlSolid

.PatternColorIndex=xlAutomatic

EndWith

Range（"B2"）.Select

ActiveCell.FormulaR1C1="Numbers"

Range（"A3"）.Select

WithSelection.Interior

.ColorIndex=3

.Pattern=xlSolid

.PatternColorIndex=xlAutomatic

EndWith

Range（"B3"）.Select

ActiveCell.FormulaR1C1="Formulas"

Range（"B4"）.Select

EndSub

从现在开始，让我们注重于寻找下面两个问题的答案：

如何阅读宏代码？

如何修改宏代码？

添加注释

看一下录制的宏代码，请注意那些开头带单引号的行。

这些行就是注释。

注释默认显示为绿色。

执行宏代码时，VB会忽略这些注释行。

注释经常和宏代码放在一起，来整理那些意义不甚明显的语句。

现在，我们来给宏WhatsInACell添加注释。

1．激活VBE窗口

2．在Selection.SpecialCells（xlCellTypeConstants,2）.Select前面点击一下，将光标移至该语句开头，回车

3．将光标往上移一行到空白处，并且添加如下注释，注意前面有单引号（译者：

英文状态下的单引号）

‘Findandformatcellscontainingtext

4．在Selection.SpecialCells（xlCellTypeConstants,1）.Select前面点击一下，将光标移至该语句开头，回车

5．将光标往上移一行到空白处，并且添加如下注释，注意前面有单引号（译者：

英文状态下的单引号）

‘Findandformatcellscontainingnumbers

6．在Selection.SpecialCells（xlCellTypeFormulas,23）.Select前面点击一下，将光标移至该语句开头，回车

7．将光标往上移一行到空白处，并且添加如下注释，注意前面有单引号（译者：

英文状态下的单引号）

‘Findandformatcellscontainingformulas

8．在Range（"A1:

A3"）.Select前面点击一下，将光标移至该语句开头，回车

技巧1－6：

关于注释

在VBE代码窗口里，以单引号开头的都是注释。

注释的默认颜色是绿色。

可以通过“选项”对话框（“工具”－“选项”－“编辑器格式”）更改注释颜色。

注释也可以写在代码的后面。

例如，在语句.ColorIndex=11之后添加注释。

点击该语句句末，按下Tab键，输入单引号，然后输入注释。

显示如下：

.ColorIndex=11'SetsthefontcolortoViolet

注释除了给用户提供代码目的信息之外，没有任何作用。

请别忘记给你的代码写上注释。

如果你几个月后还要回到你的代码，那么注释将帮你大忙。

同样，注释可以使别人很快理解你的程序。

9．将光标往上移一行到空白处，并且添加如下注释，注意前面有单引号（译者：

英文状态下的单引号）

‘Createlegend

分析宏代码

所有宏过程都以关键词“Sub”开始，以关键词“EndSub”结束。

在关键词“Sub”之后是宏的真正的名字，然后紧跟着是一对括号。

在关键词Sub和EndSub之间是那些你每次运行宏代码时VB执行的语句。

VB从上到下读取语句，忽略那些句前带单引号的语句（参见上节关于注释的内容），读到EndSub时停止。

请注意，录制的宏代码里包含许多停顿（译者：

英文模式下的句号）。

每行代码中都有停顿，用来连接VBA语言中不同的要素。

如何阅读这种语言的用法呢？

要从最后一个停顿的右边向左读。

看看WhatsInACell里的一些语句：

Range（"A1:

A3"）.Select

选择A1到A3单元格

Selection.EntireRow.Insert

往选中的区域中插入行。

因为前面你选中的是三个单元格（译者：

应该说是占据了三行的单元格），VB将插入三行。

ActiveCell.FormulaR1C1="Text"

往当前单元格里输入“Text”。

因为，之前的代码是Range（"B1"）.Select，选择单元格B1，B1是当前激活的单元格，所有VB往B1单元格里面输入文本。

WithSelection.Interior

.ColorIndex=3

.Pattern=xlSolid

.PatternCol