使用Worksheet_Change事件时ConcRange函数异常，作为公式使用正常

问题描述 #

编写了以英文逗号“,”为分隔符的单元格拼接函数ConcRange，直接作为Excel公式使用时输出正常；但通过Worksheet_Change事件调用时，结果异常，输出单元格自动变为数字格式，后续修改为常规格式也无法恢复正确内容。

原代码 #

拼接函数ConcRange #

Option Explicit
Option Compare Text

Function ConcRange(rng As Range, Optional Delim As String = ",") As String
     
    Dim cel As Range
     
    For Each cel In rng.Cells
        If Trim(cel.Value) <> "" Then
           ConcRange = ConcRange & Delim & CStr(Trim(cel.Value))
        End If
    Next cel
     
   If Len(ConcRange) > 0 Then
     ConcRange = Right(ConcRange, Len(ConcRange) - 1)   '移除最左侧的逗号
   End If
     
End Function

工作表事件代码 #

Option Explicit
Option Compare Text

Private Sub Worksheet_Change(ByVal Target As Range)

If Not Intersect(Target, Me.Range("A:A")) Is Nothing Then
  
  Application.EnableEvents = False
  
    Const ResultCel As String = "C2"
   
    Dim rng As Range: Set rng = Me.Range("A1:A50")
   
    Me.Range(ResultCel).Value = ConcRange(rng)
     
   Application.EnableEvents = True
    
  End If
    
End Sub

问题原因 #

Excel在直接给单元格赋值时，会自动识别内容并转换格式。如果拼接后的字符串符合数字格式特征（例如"123,456"），Excel会将其解析为数字（123456）并自动设置单元格为数字格式。即使后续将单元格改回常规格式，已经被转换的内容也无法复原。

解决方案 #

方案1：先设置单元格为文本格式再赋值 #

修改工作表事件代码，先将目标单元格格式强制设为文本，再赋值，避免Excel自动转换内容：

Option Explicit
Option Compare Text

Private Sub Worksheet_Change(ByVal Target As Range)
    If Not Intersect(Target, Me.Range("A:A")) Is Nothing Then
        Application.EnableEvents = False
        
        Const ResultCel As String = "C2"
        Dim rng As Range: Set rng = Me.Range("A1:A50")
        
        '强制设置单元格为文本格式
        Me.Range(ResultCel).NumberFormat = "@"
        '赋值
        Me.Range(ResultCel).Value = ConcRange(rng)
        
        Application.EnableEvents = True
    End If
End Sub

方案2：在结果前添加单引号强制文本 #

如果不想修改单元格默认格式，可以在拼接结果前添加英文单引号（Excel不会显示该单引号，但会强制识别内容为文本）：

'修改事件中的赋值语句为：
Me.Range(ResultCel).Value = "'" & ConcRange(rng)

说明 #

• 方案1通过锁定单元格格式为文本，从根源避免Excel自动解析转换；
• 方案2通过添加前缀单引号，让Excel将内容视为纯文本，适合需要保留单元格原有格式设置的场景。

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