VBA自定义函数MostOccuring返回#VALUE!错误的原因排查

咱们来一步步揪出这个函数报错的问题，代码里踩了好几个VBA特有的语法和对象使用坑：

主要错误点 #

• 变量拼写错误：代码里把strings写成了srings（少了个t），VBA会将这个未声明的变量当作Variant类型，调用Exists方法时直接触发错误。
• 错误的对象声明：你想使用键值对来统计次数，但却把strings声明成了Object数组——VBA的数组根本没有Exists、IndexOf这类方法，应该用Scripting.Dictionary对象来实现这个功能。
• 数组边界获取错误：VBA中没有items.Length的写法，获取数组的遍历范围应该用LBound(items)（数组下界）和UBound(items)（数组上界），而且Excel单元格区域传入的数组默认是从1开始索引，不是0。
• 不存在的数组方法：VBA的数组没有IndexOf和Max()这些方法（这是VB.NET或C#的语法），要找到最大值对应的项，必须手动遍历统计。
• 未初始化的数组：count()数组没有提前用ReDim初始化大小，直接赋值会导致下标越界错误。

修正后的函数代码 #

下面是修复后的版本，兼顾了Excel中常见的使用场景（比如单个单元格、空单元格、混合数据类型），用后期绑定的Scripting.Dictionary（不需要手动添加引用）：

Public Function MostOccuring(items As Variant) As String
    ' 处理单个单元格输入的情况
    If IsArray(items) = False Then
        MostOccuring = CStr(items)
        Exit Function
    End If
    
    Dim itemDict As Object
    Set itemDict = CreateObject("Scripting.Dictionary")
    
    Dim Index As Long
    Dim currentItem As Variant
    
    ' 遍历数组统计每个项的出现次数
    For Index = LBound(items) To UBound(items)
        currentItem = items(Index)
        ' 跳过空单元格
        If Not IsEmpty(currentItem) Then
            ' 统一转换为字符串，避免数字和文本格式数字被判定为不同项
            currentItem = CStr(currentItem)
            If itemDict.Exists(currentItem) Then
                itemDict(currentItem) = itemDict(currentItem) + 1
            Else
                itemDict.Add currentItem, 1
            End If
        End If
    Next Index
    
    ' 找出出现次数最多的项
    Dim maxCount As Long
    Dim mostItem As String
    maxCount = 0
    
    For Each currentItem In itemDict.Keys
        If itemDict(currentItem) > maxCount Then
            maxCount = itemDict(currentItem)
            mostItem = currentItem
        End If
    Next currentItem
    
    MostOccuring = mostItem
End Function

额外说明 #

• 如果需要支持早期绑定（代码提示更友好），可以先打开VBA编辑器的「工具」→「引用」，勾选「Microsoft Scripting Runtime」，然后把Dim itemDict As Object改成Dim itemDict As New Scripting.Dictionary。
• 函数里加入了空单元格判断，避免统计空值；同时把所有项转成字符串，确保123和"123"被当成同一个项统计。

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