C


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.

What is xxx? Cell reference, number, text?

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.

Is the text file changing such that you need a recalculation, or is the xxx
changing and it's getting different text from the (static) text file?


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.

In general, you should avoid Volatile. There are some cases where it's
necessary, but often it's used when it shouldn't be. To avoid using it, you
need to pass all of the information that your UDF will need as an argument
to the UDF. That will force a recalc if any of the arguments change.

If some other program is changing the text file, then you can't pass
everything you need and you will probably have to use Volatile. If that's
the case, you may be able to reduce the access times of the text file. For
instance, open your text file for Input the first time the function is
called, but don't close it. Put a test in your function to determine if the
file is still open and skip the opening part on subsequent passes. Then use
the Calculate event (which I think runs after calculation is complete) to
close the text file. I believe that the opening and closing of the text
file is what's taking all the time, not the actual access.

If you post the UDF, someone will be able to tell you how to change your
arguments so that you don't need Volatile. If the text file is changing and
you want to try to hold the file open through the calculation, let me know
and I can walk you through how to do it. Beware that I haven't actually
tested it, so I don't guarantee that it would work or be any faster. But I
think it's worth a look.


--
Dick Kusleika
MVP - Excel
Excel Blog - Daily Dose of Excel
www.dicks-blog.com