请求排查Microsoft 365中VBA统计命名范围代码的内存不足问题

原代码问题分析 #

原代码在Office 365中触发“内存不足”报错，核心问题如下：

• 直接读取nm.RefersTo会强制解析命名范围的公式文本，若工作簿存在大量关联动态数组、外部链接或复杂计算的命名，会瞬间占用大量内存；
• Split(nm.RefersTo, "!")(0)的写法存在风险：如果命名范围不含!（比如工作簿级命名默认引用Sheet1，或名称本身无感叹号），会触发下标越界错误，间接引发内存异常；
• 计数变量用Integer类型，若命名范围数量超过32767会直接溢出，也可能导致内存相关报错。

修复后的代码 #

Sub countNamedRanges()
    Dim nm As Name
    Dim nameCount As Long ' 改用Long避免溢出
    Dim targetSheet As Worksheet
    
    Set targetSheet = ThisWorkbook.Worksheets("Sheet1")
    
    For Each nm In ThisWorkbook.Names
        ' 可选：跳过隐藏的命名范围，不需要可删除此判断
        If nm.Visible Then
            On Error Resume Next ' 捕获无效命名（比如指向已删除区域的名称）
            ' 直接对比工作表对象，避免解析公式文本
            If Not nm.RefersToRange Is Nothing Then
                If nm.RefersToRange.Parent Is targetSheet Then
                    nameCount = nameCount + 1
                End If
            End If
            On Error GoTo 0 ' 恢复默认错误捕获
        End If
    Next
    
    Debug.Print "Sheet1的命名范围数量：" & nameCount
End Sub

修复说明 #

• 用nm.RefersToRange.Parent直接获取命名范围所属工作表，无需解析公式文本，大幅降低内存占用；
• 加入错误捕获处理无效命名，避免运行时中断；
• 改用Long类型存储计数，支持更大的数量范围；
• 可选跳过隐藏命名范围，按需调整即可。

内容的提问来源于stack exchange，提问作者Plinio P.