使用VBA自定义函数获取单元格值及字体格式时颜色设置失效问题

• Excel 对工作表自定义函数（UDF）有明确的运行限制：仅允许返回计算值，禁止直接修改单元格的格式、位置等非值属性，函数内修改字体颜色的写操作会被Excel的安全机制直接拦截，这是你代码返回值正常、但颜色修改无效的核心原因。
• 代码中用ActiveCell定位公式所在单元格的逻辑存在错误：ActiveCell指的是当前用户选中的单元格，而非公式自身所在的单元格，如果你批量填充公式，所有公式都会尝试修改同一个选中单元格，哪怕无UDF限制，逻辑也无法正确运行。
• 读取单元格属性的操作不受UDF限制，所以你可以通过Debug.Print打印出正确的颜色索引值。

你可以通过Application.Evaluate绕开UDF的格式修改限制，同时用Application.Caller正确定位当前公式所在的单元格，修改后的代码如下：

Function cellclr(y As Variant, x As Variant)
    Dim cl As Long
    Dim formulaCell As Range
    ' 获取当前运行的自定义函数所在的单元格
    Set formulaCell = Application.Caller
    ' 读取目标单元格的字体颜色索引
    cl = Range(x).Font.ColorIndex
    ' 用Evaluate执行格式修改，绕开UDF限制
    Application.Evaluate formulaCell.Address & ".Font.ColorIndex = " & cl
    ' 返回指定值
    cellclr = y
End Function

注意事项 #

• 如果参数x是跨工作表的地址，需要在Range引用中补充工作表名称，避免取错颜色值。
• 如果需要批量使用该公式，建议将需要修改颜色的单元格和对应颜色存入全局变量，配合Worksheet_Calculate事件批量处理，性能会优于单次Evaluate调用，适合大量公式的使用场景。

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