如何用VBA实现类Excel公式的单元格全属性复制功能

Excel自定义函数（UDF）本身无法直接修改单元格格式（仅能返回计算值），因此需要结合工作表事件+标记函数的方式实现需求，以下是具体步骤：

方法1：公式触发自动复制格式 #

步骤1：添加工作表事件代码 #

按Alt+F11打开VBA编辑器，双击左侧需要应用功能的工作表（比如Sheet1），在代码窗口粘贴以下代码：

Private Sub Worksheet_Change(ByVal Target As Range)
    Dim sourceCell As Range
    Dim formulaText As String
    Dim sourceAddr As String
    
    ' 检查单元格是否使用=CopyCell(...)公式
    If Target.HasFormula Then
        formulaText = Target.Formula
        If Left(formulaText, 9) = "=CopyCell(" Then
            ' 提取公式中的源单元格地址
            sourceAddr = Mid(formulaText, 10, Len(formulaText) - 10)
            sourceAddr = Replace(Replace(sourceAddr, ")", ""), """", "")
            
            On Error Resume Next
            Set sourceCell = Me.Range(sourceAddr)
            On Error GoTo 0
            
            If Not sourceCell Is Nothing Then
                ' 复制源单元格所有格式到目标单元格
                sourceCell.Copy
                Target.PasteSpecial Paste:=xlPasteFormats, Operation:=xlNone, _
                    SkipBlanks:=False, Transpose:=False
                Application.CutCopyMode = False
                ' 可选：清除公式，仅保留格式和源单元格的值
                Target.Value = sourceCell.Value
            End If
        End If
    End If
End Sub

步骤2：添加标记用自定义函数 #

右键左侧VBAProject，选择「插入」→「模块」，粘贴以下代码：

Public Function CopyCell(source As Range) As Variant
    ' 仅作为公式标记，实际格式复制逻辑在工作表事件中执行
    CopyCell = source.Value ' 可选：返回源单元格的值，也可改为CopyCell = ""
End Function

使用方式 #

在需要应用格式的单元格输入=CopyCell(B2)，按回车后，该单元格会自动复制B2的所有格式（字体颜色、背景色、边框等）；若源单元格在其他工作表，输入=CopyCell(Sheet2!B2)即可。

方法2：优化原按钮触发的宏 #

如果仍想用按钮触发，可优化原代码使其支持直接复制到活动单元格：

Public Sub CopyCellFormatToActive()
    Dim sourceAddr As String
    Dim sourceRng As Range
    
    sourceAddr = InputBox("请输入源单元格地址（如B2或Sheet2!B2）：")
    If sourceAddr = "" Then Exit Sub
    
    On Error Resume Next
    Set sourceRng = Range(sourceAddr)
    On Error GoTo 0
    
    If Not sourceRng Is Nothing Then
        sourceRng.Copy
        ActiveCell.PasteSpecial xlPasteFormats
        Application.CutCopyMode = False
    Else
        MsgBox "输入的单元格地址无效！"
    End If
End Sub

将按钮绑定这个宏，点击后输入源单元格地址，即可复制格式到当前活动单元格。

注意事项 #

• 文件需另存为.xlsm格式（启用宏的工作簿）
• 方法1中，修改单元格公式时会自动触发格式复制，手动刷新可按F9

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