COUNTIFS中用自定义VBA函数替代INDIRECT实现动态区间返回#VALUE错误如何解决

• 你编写的UDF默认返回的是Range对象的值数组，而非Range引用对象，而COUNTIFS的区域参数要求接收引用类型的参数，类型不匹配触发#VALUE错误
• 你没有使用Set关键字给对象类型的返回值赋值，VBA中对象赋值必须用Set，否则默认读取对象的默认属性（Range的默认属性是Value，也就是返回区域的内容）

基础可用版 #

修改代码如下即可直接适配你需要的用法：

Public Function INDIRECTVBA(ref_text As String) As Range
    ' 用Set返回Range引用对象，匹配COUNTIFS的参数要求
    Set INDIRECTVBA = Range(ref_text)
End Function

容错优化版 #

适合实际生产环境使用，增加了异常情况处理：

Public Function INDIRECTVBA(ref_text As Variant) As Variant
    ' 处理输入为错误值的情况（比如上游VLOOKUP返回#N/A）
    If IsError(ref_text) Then
        INDIRECTVBA = ref_text
        Exit Function
    End If
    On Error Resume Next
    Set INDIRECTVBA = Range(CStr(ref_text))
    ' 处理范围不存在的情况，返回标准#REF!错误
    If Err.Number <> 0 Then
        INDIRECTVBA = CVErr(xlErrRef)
    End If
End Function

• 如果使用工作表级别的命名范围，ref_text对应的文本需要带上工作表前缀，格式为工作表名!范围名，避免默认查找当前工作表找不到的问题
• 不支持引用未打开的外部工作簿中的范围，VBA的Range对象无法对未打开的工作簿生效
• 该函数默认非易失性，仅在参数变化时重新计算，性能优于原生INDIRECT；如果需要每次工作表重算都触发刷新，可以在函数代码第一行添加Application.Volatile True

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