EXCEL VBA 入门与实用例子

VBA这个以前用过，放下很久了，最近因需要用Excel处理数据，用到了它，使用了其它一些方式，对比一下，感到VBA还是有些长处的。故将以前的学习笔记，重新整理了一下，发表到这里，供有需要的人借鉴。

EXCEL VBA基础

Visual Basic for Applications（VBA）是 VisualBasic 的一种宏语言，是微软开发出来在其桌面应用程序中执行通用的自动化(OLE)任务的编程语言。主要能用来扩展 Windows 的应用程序功能，特别是Microsoft Office软件。

如何打开 Excel VBA 编辑器

使用快捷键 Alt + F11，打开VBA 编辑器

或

点击“开发工具”选项卡的“Visual Basiv”图标

（没有看到“开发工具”选项卡？后面介绍）

插入模块

在一个 VBA 工程中想要插入新的模块时，可在 VBA 工程右键，选择插入类型——这里选模块即可，参见下图：

【VBA的模块分类

窗体(窗体模块)，模块(标准模块)，类模块。

窗体，一种特殊的模块，可以设计界面。

模块，也就是普通模块，是代码编辑和执行的容器。

类模块，也是一种特殊的模块，比较复杂，可以自定义 类，自定义类的 属性，方法，甚至事件。关于类，类是对象的原始模型，是没有实例化的对象，类实例化以后叫对象。

一般是用 模块写代码。】

运行 VBA 代码

现在先给出一段简单的演示代码，即在“模块1”中输入以下一段代码

Sub MyCode()

    Sheet1.Range("A1") = "Hello World"

End Sub

★在VBA编辑器中运行 VBA 代码，可用以下 3 种方法之一：

1.使用菜单栏命令

首选，将光标放置在要运行的代码的任意一处，再在菜单栏选择“运行→运行子过程/用户窗体”命令

2.首选，将光标放置在要运行的代码的任意一处，再点击快捷工具栏▶按钮。

3.使用快捷键 F5

首选，将光标放置在要运行的代码的任意一处，再使用快捷键 F5，即可运行代码。

运行后，按Alt + Q 键返回表格窗口，可以看到在 Sheet1 工作表 A1 单元格，写入 “Hello World” 内容，参见下图：

【如何从VBA窗口返回表格窗口？

按Alt + Q 键 】

★除了前面介绍了VBA 代码在VBA 编辑器中运行，还可以在Excel 表格界面中，从“开发工具”选项卡运行。

【若没有看到“开发工具”选项卡？如下处理

在功能区空白处 右击，单击快捷菜单的“开发工具”项，添加“开发工具”选项卡，参见下图：

】

点击“开发工具”选项卡的 “宏”图标，会弹出工作簿包含的所有宏的列表，选择想要的宏，点击右侧“执行”按钮，参见下图：

★通过给“按钮”指定宏的方式运行

找到“开发工具→插入”单击。选中表单控件部分的“按钮”，再在工作表（Worksheet）的合适位置单击，这时出现一个宏列表，其中选择想要指定的宏，点击确定，完成指定宏【之后，右击它将出现的快捷菜单可修改之】。参见下面动图：

保存含有VBA代码的excel文件

若想保存含有VBA代码的文件，应选扩展名为.xlsm，或.xls，【提示：xls是2003版本之前的文件 ，不管有没有宏程序的话都是xls文件 ，从excel2007开始有了区分，.xlsm是含有VBA代码（宏）的，.xlsx是不含VBA代码（宏）的，默认是.xlsx，如果不想含有代码，可以保存为xlsx，即可自动删除其中VBA代码】参见下图：

VBA 过程和函数

☆VBA 过程以 Sub 语句开始，以 End Sub 语句结束，包含一个或多个语句，完成一个特定的目标。

VBA 过程的基本语法如下：

Sub 过程名([参数])

    语句1

    语句2

    ...

    语句n

End Sub

其中 [参数]可以没有，若有语法如下：

变量名1 As 数据类型,...变量名n As 数据类型

☆VBA 函数

VBA 函数与 VBA 过程很相似，除了使用的关键词外，主要区别是，函数可以返回值。

VBA 函数基本语法如下：

Function [函数名]([参数]) As [返回值类型]

    语句1

    语句2

    ...

    语句n

    [函数名] = [返回值]

End Function

函数包含的语句中，相比过程，可以看到多一个 [函数名] = [返回值] 语句，这是函数的返回值语句。

Excel VBA对象模型

Excel VBA对象模型是编程时可以使用的对象的层次结构，它使得引用要操控的对象更容易。Excel VBA里有好多对象，这些对象其实都是有层级关系的，就像一棵树一样，最高级的是Application，简化的对象的层次结构参见下图：

这些对象一般常用的有以下四种：

应用程序（Application）

工作簿（Workbook）

工作表（Worksheet）

范围（Range）

许多使用 Excel 完成的工作都是围绕这四种对象及其成员进行的。位于顶层的是Application对象，也就是Excel应用程序本身，它包含Excel中的其它的对象，如Workbook对象；一个Workbook对象包含其它一些对象，如Worksheet对象；而一个Worksheet对象又可以包含其它对象，如Range对象， 一个 Range 对象，表示一个单元格、行、列、包含一个或多个单元格块的单元格选定区域（选定区域可能是连续的，也可能不是连续的）或甚至多个工作表上的一组单元格）。

引用对象

在编写VBA代码时，了解如何引用对象是至关重要的。很明显，想要开始处理特定的VBA对象时，必须先识别它，也就是说，告诉VBA要处理哪个对象。

可以用句点连接对象名来限定是对某个对象成员的引用。

例如，Application.Workbooks(“Book1.xls”).Worksheets(“Sheet1”).Range(“A1”) 表明是对工作簿Book1上的工作表Sheet1中单元格A1的引用，其中Application代表Excel应用程序本身，可省略。特别地，若Book1是当前活动工作簿，则上述语句可简写为 Worksheets(“Sheet1”).Range(“A1”) ；若Sheet1是当前活动工作表，则又可简写为 Range(“A1”) 。因此，若在引用中省略了工作簿对象，则表明是使用当前活动工作簿；若再省略了工作表对象，则表明是使用当前活动工作表。

Worksheet.Cells 属性语法

expression.Cells

expression 一个代表 Worksheet 对象的 变量。可以在 Cells 关键字后面紧接着指定行和列索引。

返回一 个 Range 对象，该对象代表工作表上 (单元格，而不只是当前使用的单元格) 。

如：

Worksheets("Sheet1").Cells(5, 3).Font.Size = 14 '将 Sheet1 中单元格 C5 的字号设置为 14 磅。

一个实用实例：比较两个Excel表中相同、不同的数据行

数据如下图所示：

Sheet1和Sheet2表，其中“项名1”、“项名2”列是要比对异同的数据，“条件”列中的数据是由强制连接运算符&将“项名1”、“项名2”列连接起来，这里假设要比对的数据行由两列组成，更多的列可依此类推。“比对1”和“比对2”过程用Sheet1表的 “标志”列来记比对结果。“Sheet1中有Sheet2中也有”和 “Sheet1中有而Sheet2中没有”过程用Sheet3表来记处理结果。

【“&”和“+”两种运算符

 “&”是强制性连接，就是不管什么都连接。

 “+”是对字符串进行连接，对数字则进行加法运算。（用“+”号的时候，“+”前后类型要一致）】

源码如下：

Sub 比对1()  '在sheet1的标志列，将sheet1中有,sheet2也有的，加标记A；未有A 的说明将sheet1中有sheet2没有的
  Sheet1.Range("D2:D1000").ClearContents
  
  Dim Sheet1数据行数, Sheet2数据行数
  Sheet1数据行数 = 15
  Sheet2数据行数 = 11

  For I = 2 To Sheet1数据行数  'Worksheets("Sheet1").Cells(行,列) 和 Sheet1.Cells(行,列)等效
     For j = 2 To Sheet2数据行数
         If Worksheets("Sheet1").Cells(I, 3) = Worksheets("Sheet2").Cells(j, 3) Then   '两表的比较列——条件
            Worksheets("Sheet1").Cells(I, 4) = "A"  '在标志列加标志
            Exit For
         End If
     Next
  Next

End Sub


'Sheet1中有Sheet2中也有,拷贝到heet3中
Sub Sheet1中有Sheet2中也有()
  Sheet3.Range("A2:D1000").ClearContents
  Set conn = CreateObject("adodb.connection")
  conn.Open "provider=microsoft.jet.oledb.4.0;extended properties=excel 8.0;data source=" & ThisWorkbook.FullName
  Sq1 = "select  *  from [Sheet1$] where 条件 in(select 条件 from [Sheet2$])"    ''在select之后加Distinct将删除重复的
  Sheet3.[B2].CopyFromRecordset conn.Execute(Sq1)
  conn.Close
  Set conn = Nothing
End Sub


Sub 比对2()  '在sheet1标志列，将sheet1中有,sheet2没有的，加标记B
  Sheet1.Range("D2:D1000").ClearContents
  
  Dim Sheet1数据行数, Sheet2数据行数
  Sheet1数据行数 = 15
  Sheet2数据行数 = 11

  For I = 2 To Sheet1数据行数  'Worksheets("Sheet1").Cells(行,列) 和 Sheet1.Cells(行,列)等效
     Worksheets("Sheet1").Cells(I, 4) = "B"  '在标志列加标志
     For j = 2 To Sheet2数据行数
         If Worksheets("Sheet1").Cells(I, 3) = Worksheets("Sheet2").Cells(j, 3) Then   '两表的比较列——条件
            Worksheets("Sheet1").Cells(I, 4) = ""
            Exit For
         End If
     Next
  Next

End Sub


'Sheet1中有而Sheet2中没有,拷贝到heet3中
Sub Sheet1中有而Sheet2中没有()
  Sheet3.Range("A2:D1000").ClearContents
  Set conn = CreateObject("adodb.connection")
  conn.Open "provider=microsoft.jet.oledb.4.0;extended properties=excel 8.0;data source=" & ThisWorkbook.FullName
  Sq1 = "select  *  from [Sheet1$] where 条件 not in(select 条件 from [Sheet2$])"  ''在select之后加Distinct将删除重复的
  Sheet3.[a2].CopyFromRecordset conn.Execute(Sq1)
  conn.Close
  Set conn = Nothing
End Sub

现在，你可以用前面介绍的运行方法试试了，如：