CODEWITHSUNDEEP
excelvbauser-defined-functions
Vitalii
Vitalii

Reputation: 3

function that erase itself after work. vba

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 now

how it must to be
how it must to be

Upvotes: 0

Views: 63

Answers (1)

Pᴇʜ
Pᴇʜ

Reputation: 57693

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

Upvotes: 3

Related Questions