最简单vba代码（VBA代码优化常用技巧）

威哥 2023-06-27 08:09:25 816

最简单vba代码（VBA代码优化常用技巧）sub批注循环()利用对象循环代替单元格循环，如判断单元格批注，可以循环批注comments对象，直接找到每个批注，再利用parent获取该批注的父对象单元格即可。很多时候我们要实现某些功能，如果本身对excel不熟悉的话，可能会想办法去实现某些看上去很复杂的功能，殊不知，其实excel本身已经提供了类似的功能，有的时候可能仅仅是一个函数就解决了的事情，结果你搞半天，说不定辛苦弄出来，结果效率和效果还没内置的好。在循环中尽量减少对对象的引用，多用with... end with语句圆点越少越省时用set语句将反复引用的对象设置为对象变量，因为变量存在内存中

天下武功，唯快不破

最简单vba代码（VBA代码优化常用技巧）(1)

在这个时间就是金钱、效率就是生命的年代，浪费时间就是罪恶，尤其是对于程序猿来说，如果自己开发的程序，虽然功能实现了，但是在执行上还存在卡、慢的现象，那么不失为一件颇让人沮丧的事情。

大家都知道在Office里面可以用VBA进行二次开发，为日常工作提供了一条快捷的途径，善用它，可以大大的改善我们的工作体验和质量。今天跟大家分享的不是VBA本身，而是一些VBA代码常用的优化技巧，大神请绕路（也欢迎指点），VBA初学者或者爱好者可以看看，哪怕是得到一点点启发和借鉴，那也是极好的。

1.尽量调用内置功能，即，使用系统提供的属性、方法和函数

很多时候我们要实现某些功能，如果本身对excel不熟悉的话，可能会想办法去实现某些看上去很复杂的功能，殊不知，其实excel本身已经提供了类似的功能，有的时候可能仅仅是一个函数就解决了的事情，结果你搞半天，说不定辛苦弄出来，结果效率和效果还没内置的好。

在循环中尽量减少对对象的引用，多用with... end with语句圆点越少越省时

用set语句将反复引用的对象设置为对象变量，因为变量存在内存中

利用对象循环代替单元格循环，如判断单元格批注，可以循环批注comments对象，直接找到每个批注，再利用parent获取该批注的父对象单元格即可。

sub批注循环()
dim com as comment address asstring
for each com in activeSheet.comments
address=address&com.parent.address(0 0)&chr(10)
next
msgbox address
endsub
sub 单元格循环()
dim rng as range address as string bl as boolean
on error resume next
for each rng in activesheet.usedrange
bl=rng.comment.visible
if err=0 then address=address&rng.address(0 0)&chr(10)
err.clear:
next rng
msgbox address
end sub

3.减少对象的激活和选择

少用select和activate语句

这个通常会出现在我们自己录制宏代码的时候，excel记录了大量的点击和激活动作，而这些都不是必须的，大多数都可以省略掉（有些操作必须用到激活或者选择的除外）

4.关闭屏幕更新

就是程序运行的时候会看到屏幕在闪烁，这个会影响计算机的性能，也会闪瞎眼，所以基本上编程的人都会默认使用这一条（尤其是对于运行时间比较长的，短的可以视情况不加）

Application.ScreenUpdating=False '通常放在循环语句前...Application.ScreenUpdating=True

5.变量的使用

强制变量声明（OptionExplicit）：在设置里面勾选，会节省时间，自动带出，而不是自己手动敲出来的
尽量显式声明变量：事前连接--early binding；定义object类型的属于事后连接，late binding。通常事前定义会节省时间，但往往会有兼容性问题，后定义的适配性比较好
选择合适的变量类型：缺省会默认Variant型，但是会增加内存耗用，能明确的就具体指明
善用变量：对于反复出现的数值或者字符串，尽量声明一个常量来取代该值，直接调用，修改的时候也能做到一改都改，而不用改动多个地方

讲到这里，想强调一下，有时候为了效率，有的人喜欢在代码编写的时候采用一些简写或者隐式声明，这个确实会让我们少敲不少字，但是带来的另外一个问题是，一旦代码出现了问题，调试或者找错误的时候就会很抓狂了，可能从整体的时间效率上反而不划算，所以还是建议大家养成良好的编程习惯，变量都写清楚，勤备注，这样易读性和可维护性好

6.善用带$的字符串处理函数

VBA中有2套字符串处理函数，带$和不带$的。

如果不带$的的函数处理字符串，则VBA将字符串作为variant数据计算，用带$的，则当string处理。而前者需要耗费更多的内存

7.善用循环中的步长减少循环次数

如判断奇偶，有了步长，就可以省去判断语句，加快速度

讲到循环，还有一个类似的例子：

就是我们在遍历某个区域的时候，如果能用目标区域（Target）与已用区域（UsedRange）的交集（Intersect）区域来循环的话，会缩小循环的范围，规避掉一些空的单元格的比对

8.利用数组代替单元格对象

将中间过程存在数组中，直接从内存调用，最后再读取

这个应该是应用的最多的，效果是最明显的，少了单元格的交互，会省不少事。

当然字典作为特殊的数组存在形式，一样的也能来做这个事情

实例：

对几千个个学生中不及格的成绩标示“不及格”

sub 对小于60分成绩进行注释（）

Dim i as integer tim as long arr1() arr2()'将成绩赋予数组变量arr1=range([b2] cells(rows.count 2).end(xlup))

'重置第二个数组变量大小

ReDim arr2(1 to UBound(arr1) 1 to 1)

'循环数组for i=1 to UBound(arr1)

if arr1(i 1)<60 then arr2(i 1)="不及格"

next i

'将第二个数组的值赋予单元格

range([c2] cells(rows.count 2).end(xlup).offset(0 1))=arr2

end sub

9.重复调用UDF时才使用它

有点绕啊，UDF，即user-defined-function，即用户自定义函数

为了程序运行的高效，我们通常会把长代码拆成几个子代码或者自定义函数，来相互调用。达到方便调试和互相引用的目的。UDF适合多次调用时，否则调用的速度甚至比执行的速度更慢。

比较短小的和仅使用一次的function，建议直接内置在sub代码中而不是调用UDF。

10.将不改变值或者属性的语句放到循环语句外

这一点往往会被人忽略，可能循环的重要语句本身就一两句，其他无关语句很多的话，在循环的时候会反复check，这个过程会耗时

11.利用长度计算判断单元格是否为空

第一次看到罗刚军老师用这个的时候百思不得姐，上论坛问了很多人也不知道。后来在实践中，逐渐发现这个效率更高（很隐晦，比较难得找合适的例子，老师能发现这个确实是下了功夫的），而且在很多时候用len方法还能解决别的一些比较棘手的问题，比如判断非空单元格的时候或者某些对象的时候，我们有时候搞不清楚返回的值的属性，

xx=""

xx is empty

xx is nothing

都不如一句len（xx）=0来得简便

range("a1")="" '建议len法，此法效率更高len(range("a1"))=0 '.value省略 range的默认属性

好了说了这么多，想必大家都跃跃欲试了，那么怎么知道自己优化结果是积极的呢？我们可以测试啊，下面附上测试代码：

程序运行时间测试代码：

sub aaa()

dim tim as long

tim=timer '获取当前时间

for ...

msgbox format(timer-tim "0.00")&"秒" '执行报告时间