Part1

问题1：如何优化VBA代码并使程序尽可能快的运行？

解答：

1、尽量简化代码

通过简化代码，可以提高程序的性能。另外，删除所有无关的代码，这在所录制宏中表现得尤为明显。在录制宏时，经常会产生一些与所实现的功能无关的代码，您可以将这些代码删除，以使得代码得以简化。

在下面将要讲到的设置对象变量代替长对象引用，使用With…End With语句、执行For Each…Next循环语句，根据程序环境尽量减少OLE引用，等等，均是简化代码的好方法。

2、强制声明变量

在模块代码顶部出现Option Explicit语句，要求在编写代码时对所有出现的变量均进行声明，这样，在使用变量时减少内存需求并加速性能。

(1)要节省内存资源，必须始终用特定的数据类型声明所有变量。如果不使用特定的数据类型声明变量，VBA会创建Variant类型的变量，这将比任何其他数据类型要求更多的内存。

(2)清楚每种数据类型需要多少内存以及它可以存储的值的范围。除使用较小的数据类型会导致隐性转换的情况外，应始终使用尽可能小的数据类型。例如，因为Integer类型的变量将被转换成Long类型的变量，应该将那些存储整型值的变量声明为Long类型，而不是Integer类型。

3、减少变量的作用范围并及时释放变量

主要是对象变量，在其使用完后，及时释放。例如，

DimTempObj As AnyObject,AnObj As AnyObject

SetTempObj=New AnyObject

SetAnObj=TempObj

SetTempObj=Nothing ‘释放对象变量

4、尽可能使用早期绑定

5、关闭屏幕刷新

6、设置计算模式为手动

7、使用ForEach…Next循环

8、使用With…EndWith语句

9、在执行循环时考虑如何能够尽可能地节省资源

10、尽量减少OLE引用

11、避免对象激活或者不需要先进行先择

12、在一个语句中进行复制或者粘贴

在用宏录制代码时，首先是选择一个区域，然后再执行ActiveSheet.Paste。在使用Copy方法时，可以在一个语句中指定复制的内容及要复制到的目的地。

例如，将B5:C6区域的内容复制到以单元格B8开始的区域中，使用宏录制器的代码为：

Range("B5:C6").Select

Selection.Copy

Range("B8").Select

ActiveSheet.Paste

经修改后的最佳代码是：

Range("B5:C6").CopyDestination:=Range("B8")

13、尽可能少使用“.”

例如，引用某单元格数据时，可用如下代码：

Dim iAs Long

Fori=1 to 10

Workbooks(“Book1.xls”).Worksheets(“Sheet1”).Cells(1,i).Value=i

Nexti

但下面的代码运行效率更高，因为代码中引用Workbook对象和Worksheet对象的调用命令只执行一次，而上面的代码中却要执行10次。

Dimws As Worksheet

Dim iAs Long

Setws= Workbooks(“Book1.xls”).Worksheets(“Sheet1”)

Fori=1 to 10

ws.Cells(1,i).Value=i

Nexti

14、合理地使用消息框和窗体

15、尽可能加速对数字的运算

16、提高字符串操作的性能

(1)尽可能少使用连接操作。可以在等号左边使用Mid函数替换字符串中的字符，而不是将它们连接在一起。使用 Mid 函数的缺点是替换字符串必须与要替换的子字符串的长度相同。例如，

Dim strText As String

strText = "this is a test"

Mid(strText, 11, 4) = "tent"

(2)VBA提供许多可用来替换函数调用的内部字符串常量。例如，可以使用vbCrLf常量来表示字符串中的回车/换行组合，而不是使用Chr(13) & Chr(10)。

(3)字符串比较操作的执行速度很慢。有时，可以通过将字符串中的字符转换为 ANSI 值来避免这些操作。例如，下列代码会检查字符串中的第一个字符是否为空格：

If Asc(strText) = 32 Then

上面的代码会比以下代码更快：

If Left(strText, 1) =" " Then

17、使用Asc()检验ANSI的值

在VBA中，可以使用Chr$()函数把数转换成字符，并确定ANSI的值，但是更好的是使用Asc()函数把字符串转换成数值，然后确定它的ANSI值。如果需要进行有限次数的这种检验，对程序代码的效率可能不会产生很大影响，但是，如果需要在多个循环内进行这种检验时，这将节省处理时间并且有助于程序代码更快地执行。

18、使用Len()检验空串

尽管有多种方法可检验空串，但首选的是使用Len()函数。为了测试零长度的串，可以选择把串与””相比较，或者比较串的长度是否为0，但这些方法比用Len()函数要用更多的执行时间。当对字符串应用Len()函数并且函数返回0值时，说明该字符串是空的或者是零长度的字符串。

并且，因为在If语句内非零值被认为是True，所以直接使用Len()函数而不必与””或0比较，减少了处理时间，因此执行更快。

19、有效地使用数组

用VBA数组而不是单元格区域来处理数据，即可以先将数据写入到某个数组，然后用一个语句就可以将数组中的数据传递到单元格区域中。

在创建已知元素的确定数组时，使用Array函数对于节约空间和时间以及写出更具效率的代码是非常理想的。例如，

Dim Names As Variant

Names=Array(“Fan”,“Yang”,“Wu”,“Shen”)

此外，应该尽量使用固定大小的数组。如果确实选择使用了动态数组，应该避免数组每增加一个元素就改变一次数组的大小，最好是每次增加一定数量的元素。

问题2：当OnTime方法或OnAction属性中设置的所要运行的宏带有参数时，如何传递参数到这些宏程序中？即传递参数到OnTime方法和OnAction属性所调用的宏程序中。

解答：

因为运用Application.OnTime或Object.OnAction调用宏程序的语法基本相似，因此，下面介绍的OnTime方法所使用的语法同样适用于OnAction属性。

为了便于理解，以下介绍均使用一段相似的代码，只不过传递给所调用宏程序MyProcedure的参数不同而已，以此来讲解传递给宏程序不同参数的方法。例如，下面的代码将使MyProcedure宏程序在从现在起的2秒后运行：

Application.OnTime Now + TimeValue("00:00:02"),"MyProcedure"

问题3：如何禁用用户窗体的关闭按钮？

解答：例如，下面的示例提示用户只能通过单击用户窗体上的“确定”按钮来关闭该用户窗体。您可以在VBE编辑器中插入一个用户窗体，并在用户窗体上放置一个名为“Ok”的按钮，在用户窗体代码模块中输入下面的代码进行调试。

‘**************************************************

PrivateSub UserForm_QueryClose(Cancel As Integer, CloseMode As Integer)

‘CloseMode参数表明事件发生的原因

‘若其值等于vbFormControlMenu则意味着用户单击了X图标按钮

If CloseMode =vbFormControlMenu Then

MsgBox "请单击""确定""按钮关闭本窗体"

Cancel = True

End If

EndSub

‘**************************************************

PrivateSub Ok_Click()

Unload UserForm1

EndSub

===================================================================

问题4：可以撤销宏所执行的操作吗？

解答：可以，但不能通过Excel内置的功能自动实现。您可以使用VBA代码记录下运行宏程序前单元格或单元格区域原先的内容，在“撤销”命令中调用以恢复程序运行前的状态。

您可以使用Application对象的OnUndo方法作为宏程序结束前的最后一个代码，该方法允许您指定出现在“撤销”菜单项中的文本以及点击该文本后所运行的过程。如下面的代码所示：

Application.Onundo“撤销最后一个宏”，”恢复宏程序”

为说明上述方法，下面列出了一个完整的示例。示例的完整代码以及代码说明如下：

‘**************************************************

TypeRangeCellInfo '自定义类型存储宏运行所作出的改变

CellContent As Variant

CellAddress As String

EndType

PublicOrgWB As Workbook

PublicOrgWS As Worksheet

PublicOrgCells() As RangeCellInfo

‘**************************************************

SubEditRange()

' 在所有被选取的单元格中插入X

Dim i As Integer, cl As Range

If TypeName(Selection) <>"Range" Then Exit Sub

Application.ScreenUpdating = False

ReDim OrgCells(Selection.Count)

Set OrgWB = ActiveWorkbook

Set OrgWS = ActiveSheet

i = 1

‘记录下宏程序对工作表作出改变前的状态

For Each cl In Selection

OrgCells(i).CellContent = cl.Formula

OrgCells(i).CellAddress = cl.Address

i = i + 1

Next cl

‘在所选单元格中填允X

Selection.Formula = "X"

‘指定在“撤销”菜单项中的文字及选择该命令时所执行的宏程序

Application.OnUndo "撤销最后运行的宏过程操作", "UndoEditRange"

EndSub

‘**************************************************

‘恢复工作表原先的状态

SubUndoEditRange()

Dim i As Integer

Application.ScreenUpdating = False

On Error GoTo NoWBorWS

OrgWB.Activate

OrgWS.Activate

On Error GoTo 0

'恢复宏运行所作的改变

For i = 1 To UBound(OrgCells)

Range(OrgCells(i).CellAddress).Formula =OrgCells(i).CellContent

Next i

Set OrgWB = Nothing

Set OrgWS = Nothing

Erase OrgCells

NoWBorWS:

EndSub

To Be Continued