嫌Excel VBA执行速度慢,这些建议你一定要看

  Excel是办公利器,这无需多言。尤其在办公室,Excel用的熟练与否,会的Excel知识点多不多,很大程度上决定了你工作是否高效,能否按时打卡下班。可我们也时常听到这样的吐槽:Excel好是好,可就是表格大了之后,公式多了之后,它运算起来忒慢了。

  我们写VBA宏,除了要实现特定的自动化功能,还肩负一个极其重要的使命:让Excel快起来~那么如何优化我们的VBA代码,给Excel宏提速呢?

方法一:启用【手动计算】

我们都知道【公式】菜单栏有【计算选项】,可以选择【自动计算】、【手动计算】,一般Excel默认是自动计算。如果我们的工具是VBA+Excel公式结合,那么VBA每执行一行,更改了某些单元格值,则整个Excel都会自动计算一遍公式。我们完全可以在VBA代码开始时先切换到【手动计算】,等VBA主体执行完后,再切换到【自动计算】,减少过程中不必要的计算开销。

 

 

 具体到代码层面,写起来也是非常简便:

    Application.Calculation = xlManual '手动
    
    '在【手动】状态下,需要点击【开始计算】来触发excel计算公式
    Application.Calculate '开始计算

    Application.Calculation = xlAutomatic '自动计算

 

方法二:关掉Excel窗口的刷新功能

当我们的VBA代码飞速执行时,伴随着单元格值在不停变化,Excel界面也在快速计算和刷新着,这都会拖慢VBA的执行速度,因此大多数情况下,我们可以选择关掉页面的刷新,执行完不要忘记恢复它的刷新功能,代码如下:

Application.ScreenUpdating = False '关掉屏幕刷新
Application.ScreenUpdating = True '重新启用屏幕的刷新功能

 

方法三:代码中少用Excel公式,多用字典

很多刚开始写VBA的人,由于对语法还不是很熟悉,这类人更倾向于在脚本里大量调用EXCEL基本的公式,如application.WorksheetFunction.VLookup(),这样可以显著提升脚本的开发效率,本是无可厚非的事儿。随着我们对VBA原生语法和数据类型越来越熟悉,不妨把Vlookup、Hlookup等函数,替换为用VBA字典实现。它语法更灵活,可以轻松实现表格从右往左的反向查找。因为字典这类数据结构(Key,Value)查询要比Vlookup等公式的匹配速度快很多。

 

方法四:把Excel文件当数据库来访问

把每个工作表看作是数据库表,用SQL查询来提升VBA速度。VBA中使用SQL(结构化查询语言(Structured Query Language))连接某个Excel数据源(DataSource),可以隐式连接,而无需像workbooks.open那样显示加载打开的表,对代码速度的提升显而易见。使用SQL来过滤、筛选、条件判断、分组、求最值等,更是可以让VBA速度快到起飞,数据透视表都瞬间变得不香了。唯一的缺点,SQL的学习成本比VBA和公式还是要稍高些,间接拉高了学习门槛。但话又说回来,真正会写SQL之后,你会发现以前一些略显复杂的需求瞬间变得SO EASY。大胆去学吧,相信你绝不会后悔!

改进了VBA代码中这些细节后,你会发现,VBA原来可以这么快!

 

后续的随笔中,小爬会带大家一起认识SQL,解锁更多数据分析的技能,共同领略VBA+SQL的威力~

快来扫码关注我的公众号 获取更多爬虫、数据分析的知识!

 

posted @ 2022-01-14 17:32  NewJune  阅读(291)  评论(0编辑  收藏  举报