如何创建从源单元格获取值和格式的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屋!