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

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

CodePudding user response：

You are trying to change another cell's formula with a UDF. According to the Result:

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.

CodePudding user response：

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.