help wanted for macro used in cell formula

hello,
I'm trying to use as a cell formula a user-defined macro myMacro

Public function myMacro() as integer
ActiveWorkbook.Sheets("Sheet2").Cells(1,1).value = 3
myMacro = ActiveWorkbook.Sheets("Feuil2").Cells(1,1).value
end function

in a cell of Sheet1, with the formula " = myMacro() "

This does not work, as execution of myMacro() is always in error at line 1 (trying to set the value of cell(1,1) of Sheet2) with error 1004.


It seems that when executing a user-defined macro in a cell's formula, the application locks all the sheets, then forbidding the modification of their content, as the macro tries to do.

Is there a way to make this macro work?
I just can't figure out where is the mistake!

Thank you for your help.

Jérôme

I think I've got the answer from another source, and I post it for the benefit of everybody.

It is not possible to use within a cell's formula a user-defined macro that attempts to modify the content of any cell if this macro is fired consecutive of a change in a cell.
Indeed, at this precise time when the macro is executed, one cell is "active", thus forbidding any change in all open workbooks. This is why (I think) the macro fails.

This is exactly the same as using Excel in a interactive way : if you are doing a change in a cell, you must go out of this cell to be able to do something else in Excel, including VBA.

Evident when you know it!

Best regards