I don't know how to implement it. The "Refresh" button that I would put in
the menu would do something like this:

public sub Refresh
bFlag = true
application.recalcultate
bFlag = false
end sub

public function MyMacro (s as string) as variant
's = some parameter
application.volatile = bFlag
MyMacro = textfile(s).value
end function

That doesn't work because it seems that Application.Volatile is either true
or false and can't change value for a same function.

How do I do it?

"Bob Phillips" wrote:

What is wrong with the Refresh button solution?


--

HTH

RP

"Conceptor" wrote in message
...
Hi,

I built a macro that reads data from a text file and returns a string
value
from it. When the user puts "=MyMacro(xxx)" in a cell, the returned value
will be some string from my text file. When the user ask Excel to
recalculate the workbook (F9 I believe), my macro is not executed. It
will
be executed only if I put "Application.Volatile" as the first line of that
macro. I want the user to be able to trigger a recalculation of all the
cells that use my macro.

Small problem: reading from a file takes a long time. With
Application.Volatile in my macro, any change to any cell in my workbook
will
trigger the execution of my macro for every cell that uses it. If my
macro
in used in 1000 cells, I don't want the user to wait for 1000 file
accesses
each time he writes something in a cell.

My solution to that problem is that I put a "Refresh" button in Excel's
menu
to refresh the cells that use my macro. So when the user wants to
recalculate
the cells using my macro, he must press the "Refresh" button, else it
won't
be recalculated, even after a F9. How do I implement this solution using
the
Application.Volatile?

My comprehension of Application.Volatile is that it must be put as the
first
line of a macro to make it auto-recalculable. If I want my macro to
contain
Application.volatile only in certain circumstances, how do I do it?

I tried to put "Application.Volatile = bFlag" in my macro, bFlag being set
to true only when the "Refresh" menu button is pressed. Doesn't work.
Even
worse: in some occasions, once I put the Application.Volatile to false, it
won't go back to true...

Help!
Thanks,
-C.