个人电脑正常运行的VBA宏在工作笔记本Excel中触发溢出错误

我开发的VBA宏功能是：依据唯一ID将同列多行数据合并至单个单元格，并在对应ID所在行重复显示结果。该宏在个人电脑上可正常运行，但在工作笔记本的Excel中触发溢出错误，调试器高亮指向以下代码行：

concatenatedValue = IIf(isDateColumn, Format(currentData, "mm/dd/yyyy"), currentData)

完整宏代码如下：

Option Explicit

Sub ConcatenateRowsByUniqueID()
    Dim uniqueIDColumn As Range
    Dim dataColumn As Range
    Dim isDateColumn As Boolean
    Dim resultColumn As Range
    Dim uniqueIDDict As Object
    Dim ws As Worksheet
    Dim i As Long
    
    ' 提示用户选择列
    On Error Resume Next
    Set uniqueIDColumn = Application.InputBox("选择包含唯一ID的列", Type:=8)
    On Error GoTo 0
    
    On Error Resume Next
    Set dataColumn = Application.InputBox("选择需要合并的数据列", Type:=8)
    On Error GoTo 0
    
    If uniqueIDColumn Is Nothing Or dataColumn Is Nothing Then
        MsgBox "无效选择，请重试。", vbExclamation
        Exit Sub
    End If
    
    isDateColumn = MsgBox("所选数据列是否为日期类型？", vbYesNo + vbQuestion) = vbYes
    
    ' 设置结果输出列（现有数据右侧第一列）
    Set ws = ActiveSheet
    Set resultColumn = ws.Cells(1, ws.Columns.Count).End(xlToLeft).Offset(, 1)
    
    ' 初始化字典存储ID与合并结果
    Set uniqueIDDict = CreateObject("Scripting.Dictionary")
    
    ' 遍历数据并合并值
    For i = 1 To uniqueIDColumn.Rows.Count
        Dim currentID As Variant
        Dim currentData As Variant
        Dim concatenatedValue As String
        
        currentID = uniqueIDColumn.Cells(i, 1).Value
        currentData = dataColumn.Cells(i, 1).Value
        
        If Not IsEmpty(currentID) Then
            ' 检查ID是否已在字典中
            If uniqueIDDict.Exists(currentID) Then
                ' 拼接已有结果
                concatenatedValue = uniqueIDDict(currentID) & "," & IIf(isDateColumn, Format(currentData, "mm/dd/yyyy"), currentData)
            Else
                ' 新增ID到字典
                concatenatedValue = IIf(isDateColumn, Format(currentData, "mm/dd/yyyy"), currentData)
            End If
            
            ' 更新字典中的合并结果
            uniqueIDDict(currentID) = concatenatedValue
        End If
    Next i
    
    ' 将结果输出到工作表
    For i = 1 To uniqueIDColumn.Rows.Count
        Dim outputID As Variant
        outputID = uniqueIDColumn.Cells(i, 1).Value
        
        If Not IsEmpty(outputID) Then
            ws.Cells(i, resultColumn.Column).Value = uniqueIDDict(outputID)
        End If
    Next i
End Sub

预期效果：

错误原因 #

溢出错误的核心问题是IIf函数的特性：它会同时计算真假两个分支的表达式，无论最终会使用哪个分支。

• 当选择日期列时，即使currentData是非法日期值（比如超出Excel日期范围的数值、非日期类型数据），Format(currentData, "mm/dd/yyyy")仍会被执行，触发溢出或类型错误；
• 非日期场景下，直接将数值类型的currentData赋值给字符串变量，也可能因类型转换不兼容引发错误。

修复后的完整代码 #

将IIf替换为If...Then...Else结构（仅执行符合条件的分支），同时增加空值处理、优化循环范围：

Option Explicit

Sub ConcatenateRowsByUniqueID()
    Dim uniqueIDColumn As Range
    Dim dataColumn As Range
    Dim isDateColumn As Boolean
    Dim resultColumn As Range
    Dim uniqueIDDict As Object
    Dim ws As Worksheet
    Dim i As Long, lastRow As Long
    
    ' 提示用户选择列
    On Error Resume Next
    Set uniqueIDColumn = Application.InputBox("选择包含唯一ID的列", Type:=8)
    On Error GoTo 0
    
    On Error Resume Next
    Set dataColumn = Application.InputBox("选择需要合并的数据列", Type:=8)
    On Error GoTo 0
    
    If uniqueIDColumn Is Nothing Or dataColumn Is Nothing Then
        MsgBox "无效选择，请重试。", vbExclamation
        Exit Sub
    End If
    
    isDateColumn = MsgBox("所选数据列是否为日期类型？", vbYesNo + vbQuestion) = vbYes
    
    ' 设置结果输出列（现有数据右侧第一列）
    Set ws = ActiveSheet
    Set resultColumn = ws.Cells(1, ws.Columns.Count).End(xlToLeft).Offset(, 1)
    
    ' 初始化字典存储ID与合并结果
    Set uniqueIDDict = CreateObject("Scripting.Dictionary")
    
    ' 获取数据最后一行，仅遍历有效行
    lastRow = ws.Cells(ws.Rows.Count, uniqueIDColumn.Column).End(xlUp).Row
    
    ' 遍历数据并合并值
    For i = 1 To lastRow
        Dim currentID As Variant
        Dim currentData As Variant
        Dim concatenatedValue As String
        Dim dataStr As String
        
        currentID = uniqueIDColumn.Cells(i, 1).Value
        currentData = dataColumn.Cells(i, 1).Value
        
        ' 处理空值，避免拼接无效内容
        If IsEmpty(currentData) Then
            dataStr = ""
        ElseIf isDateColumn Then
            dataStr = Format(currentData, "mm/dd/yyyy")
        Else
            dataStr = CStr(currentData) ' 强制转为字符串，确保类型兼容
        End If
        
        If Not IsEmpty(currentID) And dataStr <> "" Then
            ' 检查ID是否已在字典中
            If uniqueIDDict.Exists(currentID) Then
                concatenatedValue = uniqueIDDict(currentID) & "," & dataStr
            Else
                concatenatedValue = dataStr
            End If
            
            ' 更新字典中的合并结果
            uniqueIDDict(currentID) = concatenatedValue
        End If
    Next i
    
    ' 将结果输出到工作表
    For i = 1 To lastRow
        Dim outputID As Variant
        outputID = uniqueIDColumn.Cells(i, 1).Value
        
        If Not IsEmpty(outputID) Then
            ws.Cells(i, resultColumn.Column).Value = uniqueIDDict(outputID)
        End If
    Next i
End Sub

优化说明 #

1. 限制循环范围：仅遍历到数据最后一行，避免无效遍历空白行，提升运行效率；
2. 空值处理：过滤空数据，避免合并结果中出现多余逗号或空内容；
3. 类型安全：通过CStr强制转换非日期数据为字符串，消除类型不匹配风险；
4. 分支安全：用If...Then...Else替代IIf，仅执行符合条件的表达式，从根源避免溢出错误。

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