新浪博客

如何在Excel中使用VBA/宏代码

2014-04-16 16:10阅读：

http://blog.sina.cn/dpool/blog/u/2609604152

很多人遇到的Excel问题，用基础技巧操作或者函数没有办法解决，或者没有办法批量解决的，都会寻求到VBA的方法，也就是需要写宏代码，通过执行代码来批量完成，但百度到了一堆代码，却不知何从用起…… 如何在Excel中使用VBA/宏代码

首先，宏代码（除了宏表函数）要放到VBE（Visual Basic Editor，VB编辑器）里，经过编译执行才能起到效果，而不是放到工作表的单元格里的，在Excel里，按Alt+F11 就可以打开VBE，下面所说的操作，也都是在VBE里的菜单。
但是，根据代码的名称可分为几种性质，而不同性质的代码，放的位置则不同，不然可能起不到相应的作用：


1. 普通Sub过程（注意区别类过程）

示例：如下，主要是以Sub + 过程名称 开头，以End Sub结尾的处理过程，很大部分在网上找到的数据处理代码，基本上都属于这类的过程，这也属于笼统意义上的“宏”，我们平时录制的宏代码就是这样的结构。
Sub MySum()
.....
.....
End Sub
放置：这种类型的代码，一般是放到标准模块里的，按Alt+F11打开VBE，“插入”菜单 → “模块”（第3项），然后复制代码粘贴进去就可以使用了。部分没有指向性的通用代码还可以放到工作表代码里，但不太建议。

常用使用方法：
- 在VBE里，鼠标定位在这类代码的中间（随意位置），按F5即可执行；
- 在Excel里，可以按Alt+F8调出宏窗口，选中对应的过程后，按“执行”也可；
- 在Excel里，可以插入图片、窗体控件，然后右键 → 指定宏，选择要指定的宏之后，点击图片或者控件就可以执行代码；
- 另外还可以添加到工具栏/快捷工具栏里，选择“宏” → 指定具体名称的宏，再修改名称和图标即可；
（这种添加到工具栏的宏，一般要存储到“个人宏工作簿”里，不然执行前都要先打开对应的工作簿）


2. Function过程（自定义函数）

示例：如下，这种过程非常容易区分，开头和结尾都有关键字Function，而且后面的括号经常伴随有参数值，这种代码是自定义函数，主要功能是“计算”，比如说最常见的对颜色求和，对颜色统计等。
Function SumColor(rng as Range, r as integer)
.....
.....
End Function

放置：自定义函数代码，对于需要工作表里使用的一般都只能放标准模块里，按Alt+F11打开VBE，“插入”菜单 → “模块”（第3项），然后复制代码粘贴进去就可以使用了。
部分Sub过程的重复计算过程经常也会写成Function过程，但由于工作表里没有使用需求，而且是代码内部调用，因此是伴随主过程放置位置。

使用方法：自定义函数代码粘贴到标准模块后，你就可以直接在工作表里像内置函数一用使用，记得以=号开头，并在括号内添加必要的参数即可计算出结果。


3. 工作表响应过程

示例：如下，这种过程的第一行，主要结构是：Private Sub WorkSheet_动作名()，
最明显就是有一个WorkSheet，下划线后面一般跟的是是工作表响应动作的名称，如下面两个Change和SelectinChange是最为常见的工作表响应过程，分别是当工作表单元格内容发生变化时激发以及当工作表内选择的单元格发生变化时激发。
比如说，很多人希望在同一个单元格输入不同的值，同时在另一个单元格能自动累加，这就可以用到WorkSheet_Change()过程。
Private Sub Worksheet_Change(ByVal Target As Range)
......
......

End Sub


Private Sub Worksheet_SelectionChange(ByVal Target As Range)
......
......

End Sub

放置：这种属于工作表自发响应的私有过程，因此只能放到对应工作表的代码窗口里，而不能放到标准模块里，而工作表的代码窗口不需要插入，是默认就具备的，按Alt+F11打开VBE，按Ctrl+R调出“资源管理器”，双击需要自发产生这类过程的工作表名称（Microsoft Excel Objects 下对应的Sheet1、Sheet2 etc.），粘贴进去即可。
如何在Excel中使用VBA/宏代码
使用方法：代码粘贴到标准模块后，你可以直接回到工作表里，操作相应的“动作”就可以自动激发代码的执行，比如说Change过程，你可以往单元格里输入数据，SelectionChange过程，你可以用鼠标点击不同的单元格。


4. 工作簿响应过程

示例：这种过程跟上面的工作表响应过程相类似，主体对象是WorkBook，后面跟的是对应的“动作”，如下面的Open和SheetActivate，表示当工作簿打开的时候自动运行的过程和当某个工作表激活时要自动执行的过程。
另外还有当工作簿关闭、保存之前所自动执行的过程。
Private Sub Workbook_Open()
.....
End Sub

Private Sub Workbook_SheetActivate(ByVal Sh As Object)
.....
End Sub

放置：按Alt+F11打开VBE，在Microsoft Excel Objects 下对应的ThisWorkbook，双击粘贴进去即可。
如何在Excel中使用VBA/宏代码

使用方法：工作簿自响应过程放置好了之后，你对工作簿里进行相对应操作都可以执行代码，比如说修改工作表内容、切换工作表、保存工作表、刷新透视表之类的……


5. 控件自响应过程

示例：这种过程比较少见，一般是只针对ActiveX 控件写的，比如说按钮、列表框、复选框之类的，跟VB里或者VBA窗体过程相类似，比如说你在新建了一个ActiveX 类型的CommandButton或者复选框，双击那个控件，就会在相应的工作表对象代码窗里生成一段对应的响应代码。
Private Sub CommandButton1_Click()
......
End Sub


Private Sub CheckBox1_Click()
.....
End Sub

放置：这种代码如果是复制回来的，那你要先在对应的工作表里添加一个“名称”相同的同类型控件，然后双击控件或者直接将代码粘贴到对应的工作表里就可以了。

使用方法：设置好代码后，你按相应添加的按钮或者复选框、列表框 etc. 那就可以执行对应过程里的代码了。


6. 类模块过程

示例：类过程一般是用于“封装”的，有点集大成的意思，因此能遇到的机率也不高，要“复制”的过程也会比较繁琐，一方面要把集成的代码添加到“类模块”里，另一方面还要在工作表或者工作簿、标准模块代码过程里，将相应的对象赋成定义的类，不然代码完全不起作用。区别于一般过程，有比如Property Set， Public WithEnents，其他的Sub过程跟普通的没有区别。
Property Set 类名称(rg As Range)
.....
End Property


Public WithEvents cbar As CommandBarButton

Private Sub cbar_Click(ByVal Ctrl As Office.CommandBarButton, CancelDefault As Boolean)
.....
End Sub

放置：按Alt+F11打开VBE，“插入”菜单 → “类模块”（第4项），然后复制代码粘贴进去就可以使用了。部分对象定义赋值的语句，要放到标准模块里，或者Workbook_Open，这样才能随时应用定义好的类，这里需要根据具体情况写代码，就不作多叙述了。

使用方法：成功添加类模块和相应的定义过程后，可以跟工作表/工作簿自响应过程、自定义函数一样直接引用或者执行。





我的更多文章

下载客户端阅读体验更佳

APP专享