Why the VBA UDF "Range.Formula =" don't work?

user_19133121 0
Viewed 67

I want to copy the formula from one cell/cells to another cell/cells by Range.Formula = . But it not work as expected.

If I run the VBA step by step, the function will ended at Range.Formula = without error.

Function test1(sOURCE As Range, tARGET As Range)
    tARGET.Formula = sOURCE.Formula
    test1 = tARGET.Formula
End Function

Excel cells value

user-defined-functions vba excel
2 Answers

You are trying to change another cell's formula with a UDF. According to the Microsoft documentation this cannot be done, but Ryan Wells has actually found a nice workaround. See: How to change another cell with a VBA function UDF. Compare also: VBA: How to change the value of another cell via a function?

We need to use Evaluate on a "helper" sub. I have slightly adjusted the first example provided by Wells to suit your needs:

Function copyFormula(copyFrom As Range, copyTo As Range)

copyFrom.Parent.Evaluate "copyOver(" & copyFrom.Address() _
                        & "," & copyTo.Address() & ")"
                        
copyFormula = "Formula " & copyFrom.Address() & " -> " & copyTo.Address()

End Function

Private Sub copyOver(copyFrom As Range, copyTo As Range)

copyTo.Formula = copyFrom.Formula
    
End Sub

This works, but please bear in mind that (intriguingly) the formula will not automatically calculate after insertion, even with calculation set to automatic. Implementation:

enter image description here Result:

enter image description here

One way to overcome this problem is to include a Worksheet_Change sub for the worksheet where you are using the formula. E.g. simply:

Private Sub Worksheet_Change(ByVal Target As Range)

Calculate

End Sub

But any subsequent action in the sheet seems to trigger the calculation. Also, please be aware that you won't be able to overwrite the target cell while you have a copyFormula in use with this cell. Since it will just keep overwriting it immediately with the formula (with 0 as output, if the above trick isn't applied). This might be confusing to your users.

user_184706920

This is a restriction by design.

Because a UDF cannot change any other cells/formulas. A UDF can only return a value to the cell the UDF was used in. Therefore tARGET.Formula = sOURCE.Formula is not possible.

Also test1 = tARGET.Formula will return the formula of tARGET as text. It will not replace the formula used in the cell nor will it evaluate the formula.

user_32196130
Related