Excel for Mac循环调用.Refresh致运行缓慢及后续卡顿问题求助

问题场景 #

• 设备：2014年末款Mac mini（3GHz Intel Core i7，Office 2011），已在M1设备最新版Excel验证问题复现
• 操作流程：先执行1次基础查询获取约1000条记录，再基于该数据连续发起约1000次查询，最终返回约5000条记录，查询阶段耗时约2.5分钟
• 异常表现：查询结束后点击工作表有约1分钟无响应，后续需约2分钟恢复正常操作；重复运行宏速度逐次变慢，第三次运行卡顿更明显；重启Excel可暂时恢复正常

测试结论 #

• 仅执行首次基础查询、后续仅构建请求不发送时，宏运行仅耗时8秒且无卡顿，推测内存泄漏或资源占用问题出在实际查询执行或数据写入工作表环节
• 已在宏运行前关闭屏幕更新、分页显示与自动计算功能，结束后恢复原有设置，但问题仍存在

核心查询代码 #

With ActiveSheet.QueryTables.Add(Connection:=strURL, Destination:=Range(strStartCell))
    .PostText = "user=" & strUserName & ";password=" & strUserPassword
    .RefreshStyle = xlOverwriteCells
    .SaveData = True
    .BackgroundQuery = False
    .Refresh
End With

排查与解决建议 #

1. 手动清理QueryTables对象 #

每次查询完成后，删除不再需要的QueryTable对象——Excel不会自动释放这些资源，大量残留会导致内存堆积：

' 在每次查询完成后添加清理逻辑
Dim qt As QueryTable
For Each qt In ActiveSheet.QueryTables
    qt.Delete
Next qt
Set qt = Nothing

注意：若需保留查询结果，确保删除前数据已写入工作表，或先将数据复制到普通单元格区域再删除QueryTable。

2. 合并多次查询为批量请求 #

连续1000次查询是资源消耗的核心，若后端接口支持，将多次查询合并为单次批量请求，减少Excel与服务器的交互次数，从根源降低资源占用。

3. 优化数据写入逻辑 #

• 批量写入替代逐次写入：先将所有查询结果存入数组，最后一次性写入工作表，减少Excel界面更新和内存占用：

' 示例：用数组暂存所有结果后批量写入
Dim resultArr As Variant
' ... 执行查询并将结果存入resultArr ...
Range(strStartCell).Resize(UBound(resultArr, 1), UBound(resultArr, 2)).Value = resultArr

• 若必须分多次写入，每次写入后强制系统释放资源：

Application.CutCopyMode = False
ActiveSheet.Calculate ' 若已关闭自动计算，按需手动触发
Application.Wait Now + TimeValue("00:00:01") ' 短暂等待让系统处理后台任务

4. 强制释放VBA内存 #

在宏执行结束时，手动释放所有对象变量，并触发系统内存回收：

' 释放所有用到的对象变量
Set qt = Nothing
Set strStartCell = Nothing ' 若定义了Range对象变量
' 触发系统内存回收（适用于Mac Excel）
Application.ExecuteExcel4Macro("CALL(""KERNEL32"",""SetProcessWorkingSetSize"",""JJJ"",-1,-1)")

5. 调整Excel设置减少后台消耗 #

• 关闭“自动恢复”“实时预览”功能，减少后台资源占用
• 定期清理Excel缓存：前往~/Library/Containers/com.microsoft.Excel/Data/Library/Caches删除缓存文件，重启Excel

6. 替换查询方式为ADODB #

改用ADODB.Connection和ADODB.Recordset执行查询，这种方式对资源控制更灵活，可能避免QueryTables的内存泄漏问题：

Dim conn As Object
Dim rs As Object
Set conn = CreateObject("ADODB.Connection")
Set rs = CreateObject("ADODB.Recordset")

' 替换为你的连接字符串和查询语句
conn.Open "你的数据库连接字符串"
rs.Open "你的查询SQL或API请求逻辑", conn

' 将结果写入工作表
Range(strStartCell).CopyFromRecordset rs

' 清理资源
rs.Close
conn.Close
Set rs = Nothing
Set conn = Nothing

内容的提问来源于stack exchange，提问作者Schrocks