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.

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.

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.

I think that what I really mean when I use the term "Macro" is a "User
Defined Function". Sorry for the mixup.

"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.

I wouldn't bother with recalculate, I would just get the Refresh button to
run a macro that re-reads the text files and populates the sheet. Any cells
that reference the refreshed cells will the recalculate.

--

HTH

RP

"Conceptor" wrote in message
...
I think that what I really mean when I use the term "Macro" is a "User
Defined Function". Sorry for the mixup.

"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.

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