How to write a formula in a cell with a function?

Question

I would like write a formula in a specific cells with a function.

I have written this, but it doesn't work and I don't understand why :

Function formulaCell(x, y)

ActiveSheet.Cells(x, y).FormulaR1C1 = "=IF(R[-1]C=0,"""",R[-1]C)"

End Function

Excel return #VALEUR

Thanks in advance

Wait. Are you writing a formula into a cell so that it changes formulas in other cells? — ttaaoossuuuu, Mar 06 '15 at 09:35
This is a case of *"you can't get there from here"*. Functions are intended (and restricted) to return a value into the cell that they originate in. They cannot change the values or formulas in other cells. TBH, I remember seeing someone overcome this to change another cell's color by calling a public sub macro from within the function but never to insert a formula into a cell. — , Mar 06 '15 at 09:55
@Jeeped: Is this what you're thinking of? [Using a UDF in Excel to update the worksheet](http://stackoverflow.com/questions/23433096/using-a-udf-in-excel-to-update-the-worksheet) — Jean-François Corbett, Mar 06 '15 at 10:05
@Jean-FrançoisCorbett - Although I believe the one I saw was in the TechNet Excel mailing list, that one looks pretty close and it seems like the same time frame. — , Mar 06 '15 at 10:14
@Crapsy you need to read some VB documentation: Functions need to make an **asingnment** so the code needs: `formulaCell = "Something"` that xplains the #VALEUR — Antonio E., Mar 06 '15 at 11:11
@Taosique Yes, it's what I'm doing. I choose to do this because I want that the function recalculate when there are something change in the sheet. — Crapsy, Mar 06 '15 at 11:56
@Jeeped : Do you think I can applicate this for my function ? — Crapsy, Mar 06 '15 at 11:58
@OlegarioFuentes Yes, it's true but even with this didn't work ! — Crapsy, Mar 06 '15 at 12:02
From your expanded description in these comments, it sounds like an `INDIRECT(ADDRESS(...))` might work. Why not try editing your question to describe what you are actually trying to accomplish and we can see if an alternate solution is applicable? A dynamic named range might be another possibility. — , Mar 06 '15 at 12:09
It may be easier to just have the formula [recalculate](https://msdn.microsoft.com/en-us/library/office/ff195517.aspx) on a [`Worksheet_Change`-event?](https://msdn.microsoft.com/en-us/library/office/ff839775.aspx). Most functions should recalculate automatically if relevant changes are made anyway, I believe? — eirikdaude, Mar 06 '15 at 12:22
@Jeeped I'm interested by your idee, but I don't understand how apply this in my function ? — Crapsy, Mar 06 '15 at 12:52

score 1 · Answer 1 · answered Mar 06 '15 at 12:37

Your function will work just fine if the function is not called from a cell.

For example, here it is call from a Sub:

Sub MAIN()
    Dim msg As String
    msg = formulaCell(3, 3)
    MsgBox msg
End Sub

Function formulaCell(x As Long, y As Long) As String
    ActiveSheet.Cells(x, y).FormulaR1C1 = "=IF(R[-1]C=0,"""",R[-1]C)"
    formulaCell = "Mission Accomplished!!"
End Function

A UDF() in a cell can only return a value to that cell. A UDF() in a Sub can do much more!

How to write a formula in a cell with a function?

1 Answers1