How to create a VBA formula that takes value and f

Question

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

Currently I have:

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

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].

Answer 1

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

Answer 2

This is the solution I found to the above question using Tim William's magic:

Function cq(thisCel As Range, srcCel As Range) As Variant
    thisCel.Parent.Evaluate "colorEq(" & srcCel.Address(False, False) _ 
    & "," & thisCel.Address(False, False) & ")"
    cq = srcCel.Value
End Function

Sub colorEq(srcCell, destCell)
    destCell.Interior.Color = srcCell.Interior.Color
End Sub

The destCell is just a cell reference to the cell in which the function is called.

The interior.color can be exchanged or added to with other formatting rules. Three extra points to note in this solution:

1. By keeping the value calculation in the formula this stops the possibility for circular referencing when it destCell refers to itself. If placed in the sub then it continually recalculates; and
2. If the format is only changed when the source value is changed, not the format as this is the only trigger for a UDF to run and thus change the format;
3. Application.Caller or Application.ThisCell cannot be integrated as when it refers to itself and returns a value for itself it triggers an infinite loop or "circular reference" error. If incorporated with an Address to create a string then this works though as per Tim William's answer.