Make a custom UDF intentionally not to refresh

[DGMan]

I have a question that might be rather strange to you.

I have made a custom function which accept an input from a cell, and return a result by referencing some data source which will be updated periodically:

FUNCTION GiveSthOut(Input as Variant) As Variant

<do something="" by="" referencing="" a="" constantly-changing="" data="" source="" outside="" excel="">'Code which will reference a data source constantly being updated

GiveSthOut = Something

END FUNCTION

Normally, if the external source is changed and I choose to manually recalculate the UDF, the function result will be updated.

Now for some reason I wish to lock (or freeze) the function display result from being refreshed even though the external source has been changed. Initially I have come up with something like that:

FUNCTION GiveSthOut(Input as Variant, Optional Locked as Variant) As Variant

If Locked then 

   'Code that reference a data source which will be updated constantly<do something="" by="" referencing="" a="" constantly-changing="" data="" source="" outside="" excel="">

  GiveSthOut = Something

End If

END FUNCTION

My thinking is that, if the optional "Locked" argument is set to TRUE, then the bulk of the function would be by-passed so the present result of the function would be preserved or frozen. Unfortunately, the function result will not be frozen, for the value 0 will be return to the function in this case, which is definitely what I wanted. So is there any way, via programming code, to prevent a function result from being updated. Thanks for your advice in advance.

Fred.</do></do>

 

[Stiuart_W]

The only way I can think that you might explore is to paste the result of the formula as a value the first time the UDF runs - maybe by a worksheet change event code

But as you don't specify when you would want to do this it is hard to be more specific

For your example why haven't you got

If NOT Locked then 

   'Code that reference a data source which will be updated constantly<do excel="" outside="" source="" data="" constantly-changing="" a="" referencing="" by="" something="">

  GiveSthOut = Something

End If
</do>

Rather than what you posted?