如何创建从源单元格获取值和格式的VBA公式 [英] How to create a VBA formula that takes value and format from source cell

问题描述

在Excel的VBA中，我想创建一个公式，它们都从源单元格和格式获取值。

In Excel's VBA I want to create a formula which both takes the value from the source cell and the format.

目前我有：

Function formEq(cellRefd As Range) As Variant
    'thisBackCol = cellRefd.Interior.Color
    'With Application.Caller
    '    .Interior.Color = thisBackCol
    'End With
    formEq = cellRefd.Value
End Function`

这将返回单元格的当前值。我已注释掉的部分在单元格中返回 #VALUE 错误。当取消注释时，似乎保存了引用的颜色，但是 Application.Caller 返回2023错误。这是否意味着这不会返回所需的Range对象？

This returns the current value of the cell. The parts that I have commented out return a #VALUE error in the cell. When uncommented it seems the colour of the reference is saved however the Application.Caller returns a 2023 Error. Does this mean that this is not returning the required Range object?

如果是，如何获取引用该函数的单元格的范围对象？ [显然为了将颜色设置为源值]。

If so how do I get the range object that refers to the cell that the function is used? [obviously in order to set the colour to the source value].

推荐答案

这是一种显示如何仍然可以使用 ThisCell ：

Here's one approach showing how you can still use ThisCell:

Function CopyFormat(rngFrom, rngTo)
    rngTo.Interior.Color = rngFrom.Interior.Color
    rngTo.Font.Color = rngFrom.Font.Color
End Function



Function formEq(cellRefd As Range) As Variant
    cellRefd.Parent.Evaluate "CopyFormat(" & cellRefd.Address() & "," & _
                         Application.ThisCell.Address() & ")"
    formEq = cellRefd.Value
End Function

这篇关于如何创建从源单元格获取值和格式的VBA公式的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！