Excel自定义函数返回Range对象并保留单元格格式的实现疑问

错误原因说明 #

1. 第一次报错：你声明函数返回Range类型，但未用Set关键字初始化对象，就直接访问Text属性，相当于操作未定义的对象，触发Object variable or With block variable not set错误。
2. 第二、三次报错：Excel自定义函数（UDF）是纯函数设计，仅允许返回值到公式所在单元格，不允许修改任何单元格的内容、格式或其他属性，这是Excel的安全限制，因此修改Application.Caller或传入的tempCell都会触发Object Required错误。

实现方案 #

Excel UDF无法直接修改单元格格式，要实现带格式的单元格合并，有两种可行方式：

方案1：用子过程（Sub）直接执行合并

这是最直接的方式，通过手动触发完成格式合并：

Sub MergeCellsWithFormatting(source1 As Range, source2 As Range, target As Range)
    ' 清空目标单元格原有内容与格式
    target.Clear
    
    ' 写入合并后的文本
    target.Value = source1.Text & source2.Text
    
    ' 获取第一个单元格的文本长度
    Dim len1 As Integer
    len1 = Len(source1.Text)
    
    ' 复制第一个单元格的格式到对应字符段
    With source1.Characters(1, len1).Font
        target.Characters(1, len1).Font.Bold = .Bold
        target.Characters(1, len1).Font.Italic = .Italic
        target.Characters(1, len1).Font.Color = .Color
        target.Characters(1, len1).Font.Size = .Size
        ' 可按需添加更多格式属性（如下划线、字体名称等）
    End With
    
    ' 复制第二个单元格的格式到对应字符段
    With source2.Characters(1, Len(source2.Text)).Font
        target.Characters(len1 + 1, Len(source2.Text)).Font.Bold = .Bold
        target.Characters(len1 + 1, Len(source2.Text)).Font.Italic = .Italic
        target.Characters(len1 + 1, Len(source2.Text)).Font.Color = .Color
        target.Characters(len1 + 1, Len(source2.Text)).Font.Size = .Size
    End With
End Sub

使用方式：

• 按Alt+F11打开VBA编辑器，插入模块并粘贴代码。
• 回到Excel，按Alt+F8选择MergeCellsWithFormatting，点击运行，输入源单元格（如A1、B1）和目标单元格（如C1）即可完成合并。

方案2：结合函数与工作表事件（模拟公式触发）

若需通过公式调用的形式实现，可让函数返回占位文本，再利用工作表Calculate事件自动应用格式：

1. 模块中添加函数：

Function MergeWithFormat(source1 As Range, source2 As Range) As String
    ' 返回合并后的文本作为占位值
    MergeWithFormat = source1.Text & source2.Text
End Function

2. 双击VBA编辑器左侧的目标工作表，添加Calculate事件代码：

Private Sub Worksheet_Calculate()
    Dim cell As Range
    Dim formulaText As String
    Dim source1Addr As String, source2Addr As String
    Dim source1 As Range, source2 As Range
    
    ' 遍历所有含MergeWithFormat函数的单元格
    For Each cell In Me.UsedRange
        If cell.HasFormula Then
            formulaText = cell.Formula
            If InStr(formulaText, "MergeWithFormat(") > 0 Then
                ' 提取公式中的源单元格地址
                source1Addr = Mid(formulaText, InStr(formulaText, "(") + 1, InStr(formulaText, ",") - InStr(formulaText, "(") - 1)
                source2Addr = Mid(formulaText, InStr(formulaText, ",") + 1, InStr(formulaText, ")") - InStr(formulaText, ",") - 1)
                
                ' 转换为Range对象
                Set source1 = Me.Range(source1Addr)
                Set source2 = Me.Range(source2Addr)
                
                ' 调用格式合并子过程
                MergeCellsWithFormatting source1, source2, cell
            End If
        End If
    Next cell
End Sub

使用方式：

• 在单元格输入公式=MergeWithFormat(A1,B1)，回车后先显示合并文本。
• 修改源单元格内容/格式时，工作表重新计算会自动触发格式应用。

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