function that erase itself after work. vba-CodePudding

i want to write something like this:

Public Function functionThatEraseHerself()
    functionThatEraseHerself = "here is some work"
    'ActiveCell.value = ActiveCell.value
End Function

how it now

how it must to be

CodePudding user response：

It is generally not possible to change cell values from a user defined function called by a formula.

Nevertheless there is a nasty workaround using Evaluate

Option Explicit

'=functionThatEraseHerself()
Public Function functionThatEraseHerself()
    Dim ReturnValue As String
    ReturnValue = "here is some work"

    ' write the value to the cell that called this function
    Application.Caller.Parent.Evaluate "ReplaceWithValue(" & Application.Caller.Address & ", """ & ReturnValue & """)"
End Function

' helper procedure that is called by evaluate
Public Sub ReplaceWithValue(ByVal Cell As Range, ByVal Value As Variant)
    Cell.Value2 = Value
End Sub