vba程序流程控制的方式是什么样的,VBA程序流程控制语句?

vba程序流程控制的方式是什么样的,VBA程序流程控制语句?

外企十年工作,写了很多解决办公效率问题的VBA程序,为了能让程序更加高效的运行,曾经花了大量时间寻找让VBA程序运行得更快的方法和技巧。

本文《神奇的VBA》分享提速VBA程序运行的主要规则。平时在使用小型VBA代码程序时,我们作为普通的职场人(非专业IT编程人士)很容易陷入不良的编程习惯,编写VBA程序代码时,是怎么方便怎么来,怎么简单怎么写。这点对于以解决问题为目标的方式,其实没啥问题。但对于大型的或者运行时间较长的VBA程序,使用高效的编码是至关重要的。本文《神奇的VBA》主要关注Excel VBA的程序,但其中许多规则也适用于微软其它办公软件的VBA脚本程序。

vba程序流程控制的方式是什么样的,VBA程序流程控制语句?

前五条规则通常对Excel VBA的执行效率影响最大。规则6到11的影响很小。请注意,《神奇的VBA》对以下节省时间的预估可能会因您的具体应用而发生显著变化。

规则 #1. 关闭工作表自动计算

这个规则是众所周知的,它是最重要的规则。在工作表单元格中输入新值时,Excel 将重新计算引用该值的所有单元格。如果VBA程序要将值写入工作表,则 VBA程序 需要等到工作表完成重新计算每个条目后,才能恢复。将自动计算保持打开状态的影响可能非常大。《神奇的VBA》强烈建议在您的VBA程序开头使用以下命令关闭自动计算。

Application.Calculation = xlCalculationManual

如果需要在VBA宏运行时重新计算电子表格值,则可以使用以下任一命令。

第一个命令将重新计算整个工作簿。

Calculate

第二个命令仅重新计算特定工作表。

Worksheets("sheet1").Calculate

第三个命令仅重新计算特定范围。

Range("A1:C5").Calculate

VBA程序完成后,需要使用以下命令重新打开自动计算。如果在处理此命令之前VBA程序过早结束,则需要在Excel中手动将计算重置为自动。

Application.Calculation = xlCalculationAutomatic.

规则 #2. 关闭屏幕刷新

每次VBA将数据写入工作表时,它都会刷新您看到的屏幕图像。刷新图像会对性能造成相当大的拖累。以下命令关闭屏幕更新。

Application.ScreenUpdating = FALSE

在VBA程序的末尾,使用以下命令重新打开屏幕更新。

Application.ScreenUpdating = TRUE

规则 #3. 尽力最低化VBA和工作表的交互

VBA程序执行开始后,请务必避免对工作表进行不必要的引用。从电子表格中抓取数据会拖累性能。尽可能避免在循环中读取或写入工作表数据。读取数据一次并将其保存到内存中比每次重新读取要快得多。

在本例中,VBA反复地从工作表中获取命名的“关键指数”的单元格区域。这是一个常见的错误。当VBA不需要停止并与工作表交互时,其速度要快得多。

For x = 1 To 100b = Range("关键指数") + xNext

在下面的代码中,变量“关键指数”只从工作表中读取一次,VBA和Excel之间的通信最小化。代码快100多倍!

rng = Range("关键指数")For x = 1 to 100b =rng + xNext

这点对于VBA中执行所有的数值计算也很有效。人们经常在电子表格中留下公式,并通过VBA程序调用它们。但是,如果速度很重要,那么将所有的公式直接放在VBA程序中。这将使VBA和Excel的通信最小化,并且不需要重新计算电子表格。

规则 #4. Read and write blocks of data in a single operation

这条规则是第3条规则的延续。这是另一种最小化VBA和Excel之间通信量的方法。在可能的情况下,以块的形式读取和写入数据。有几种方法可以实现这一点。下面是一个将大数据块(2600个单元)读入数组的示例。这个例子比在一个循环中读取每个单元格的速度快大约50倍。

Dim arr() As Variant '请注意,这必须是一个变体
arr= Worksheets("Sheet1").Range("A1:G100").value

同样,下面是将数组写回工作表的示例。所有这些都比在一个循环中分别写入2600个单元快大约40倍。

方法 #1

Worksheets("Sheet1").Range("A1:G100").value = arr

方法 #2

With Worksheets("Sheet1").Range("A1:G100").Value = arrEnd With

方法 #3

Dim rng As RangeSet  
rng = Range("A1:G100")theRange.value = arr

规则 #5. 避免使用某些Excel工作表函数

这个规则让我感到惊讶。我天真地认为常见的工作表函数将由VBA有效地处理。事实显然并非如此。例如,大多数 VBA 用户可能都知道 VBA 没有 Max() 或 Min() 函数。Excel 确实具有这些功能。通常使用以下使用 Excel 版本的Max() 的代码:

variable1 = Application.Max(Value1, Value2)

我在互联网上找到了一个单纯用VBA写的Max()函数。它比上面基于Excel的对应物快10倍。但是,下面的代码速度快了80倍以上!下面的函数用两个参数,不支持数组,执行速度提高是实质性的。

Function Max2 (Value1, Value2)
If Value1 > Value2 Then
    Max2 = Value1
Else
    Max2 = Value2
End If
End Function

在大型、耗时的宏中使用工作表函数时要小心。您应评估重写函数的影响。

请注意,任何以"Application."或者 "WorksheetFunction."开头的命令是指 Excel内置函数。我不能说所有的Excel内置函数都很慢。但是,我已经编写或下载了Min(),Max(),Average(),Match(),NormSInv()和StDev()的版本,它们比Excel版本快得多。


最后的思考

我发现编写一个小宏来评估与各种方法相关的时间节省很有用。该宏只是执行一个方法一百万次左右,并记录执行该方法所花费的时间。下面的简单宏将 Excel Max()函数与规则 #5 中所示的 Max2 函数进行了比较。

'**评估第1个函数

Start_time = Now
For i = 1 To 1000000
value1 = Application.Max(amt1, amt2)
Next i
End_time = NowWorksheets("sheet1").Cells(1, 2) = End_Time-Start_Time

'**评估第2个函数

Start_time = NowFor i = 1 To 1000000
value1 = Max2(amt1, amt2)
Next i
End_time = NowWorksheets("sheet1").Cells(2, 2) = End_Time-Start_Time

本篇分享的思路和代码抛砖引玉! 如果您有其他节省时间的提示,盼请不吝分享。


原创码字不易

欢迎"点赞","转发" 或点击"收藏"


vba程序流程控制的方式是什么样的,VBA程序流程控制语句?

vba程序流程控制的方式是什么样的,VBA程序流程控制语句?

更多历史文章

《神奇的VBA》全面改版升级中

《神奇的VBA》编程:另存工作表

《神奇的VBA》编程:批量清除多个字符

《神奇的VBA》-职场牛人都在使用的Excel插件

《神奇的VBA》编程:提取超链接地址

《神奇的VBA》编程:根据次数重复排列数据

《神奇的VBA》编程:批量替换表格文字(1)

《神奇的VBA》编程:批量替换表格文字(3)

《神奇的VBA》编程:批量拆分单元格数据

《神奇的VBA》编程:监控表格单元格值的变化

《神奇的VBA》编程:一次性讲清楚VBA注释

《神奇的VBA》编程:查询Access数据库

神奇的Excel VBA系列之:如何判断某工作表是否存在

《神奇的VBA》编程:禁止修改Excel工作表名称

神奇的Excel VBA代码032:随机生成彩票数据

《神奇的VBA》一款Excel VBA编程学习插件

神奇的Excel VBA代码030:工作表数据的拆分-001

神奇的Excel VBA代码031:工作表数据的拆分-002

神奇的Excel VBA系列之:制作工作表目录

《神奇的VBA》编程:强制变量声明 Option Explicit

本文内容由互联网用户自发贡献,该文观点仅代表作者本人。本站仅提供信息存储空间服务,不拥有所有权,不承担相关法律责任。如发现本站有涉嫌抄袭侵权/违法违规的内容, 请发送邮件至 sumchina520@foxmail.com 举报,一经查实,本站将立刻删除。
如若转载,请注明出处:https://www.yiheng8.com/207422.html