为何这段特定VBA代码会触发Out of Memory内存不足错误？

一、基础排查步骤 #

• 打开任务管理器观察Excel进程内存：闲置后若内存占用异常偏高（如数百MB甚至GB级），说明存在内存泄漏。
• 手动右键Power Query表选择「刷新」：若同样报错，问题出在Power Query或Excel环境；若手动刷新正常，再聚焦VBA逻辑优化。

二、优化VBA刷新代码 #

原代码直接调用Refresh，闲置后可能残留后台查询或未释放资源，试试以下版本：

Dim wb As Workbook
Dim targetTbl As ListObject
Set wb = ThisWorkbook
Set targetTbl = wb.Worksheets("Sheet Name").ListObjects("Table_Name")

' 刷新前关闭冗余功能，降低内存消耗
Application.Calculation = xlCalculationManual
Application.ScreenUpdating = False
Application.EnableEvents = False

' 取消可能残留的后台查询
On Error Resume Next
targetTbl.QueryTable.CancelRefresh
On Error GoTo 0

' 执行刷新
targetTbl.QueryTable.Refresh BackgroundQuery:=False

' 恢复Excel默认设置
Application.EnableEvents = True
Application.ScreenUpdating = True
Application.Calculation = xlCalculationAutomatic

若仍报错，尝试重置查询连接后再刷新：

' 在Refresh前添加此行
targetTbl.QueryTable.Connection = targetTbl.QueryTable.Connection

三、精简Power Query逻辑 #

原代码先转换全表类型再筛选，会加载所有数据到内存，改成先筛选再处理，减少内存开销：

let
    Source = Excel.CurrentWorkbook(){[Name="Review_Data"]}[Content],
    // 先筛选目标Index，仅处理需要的数据
    #"Filtered Rows" = Table.SelectRows(Source, each ([Index] = #"Name Filter")),
    // 仅转换筛选后的数据类型
    #"Changed Type" = Table.TransformColumnTypes(#"Filtered Rows",{{"Attribute", type text}, {"Value", type text}}),
    #"Removed Columns" = Table.RemoveColumns(#"Changed Type",{"Index"}),
    #"Added Index" = Table.AddIndexColumn(#"Removed Columns", "Index", 1, 1, Int64.Type)
in
    #"Added Index"

额外检查：

• 确认#"Name Filter"是单个单元格引用，而非范围引用，避免加载多余数据。
• 清理源表Review_Data的空行、重复行或无效数据，减少冗余内存占用。

四、Excel环境优化 #

• 闲置后重启Excel再测试：若恢复正常，说明是Excel进程内存泄漏，建议定期重启或升级到最新版本（微软会修复这类内存bug）。
• 禁用不必要的加载项：通过「文件>选项>加载项」关闭第三方加载项，尤其是与Power Query冲突的工具。
• 临时关闭自动保存/自动恢复：取消「文件>选项>保存」中的「保存自动恢复信息时间间隔」，测试是否是该功能闲置时占用内存。

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