Hi All,

I have the following UDF to check if a file exists:-

Function FileExists(fname) As Boolean

If Dir(fname) < "" Then _

FileExists = True _

Else FileExists = False





End Function



The problem is that it doesn't re-calculate when you press F9. You have to
go into the cell by pressing F2 in order for it to re-calculate.



Adding "Applicaiton.Volatile" doesn't seem to work either.



How do you work around this?



Ta



Andi

Hi

The problem is that Excel doesn't know that the formula should be
're-calculated'. If you add another "volatile" parameter to the function it
will get reclaced every time Excel does a recalc. So changing the function
to:
Function FileExists(fname, CurrentDateTime) As Boolean

If Dir(fname) < "" Then
FileExists = True
Else
FileExists = False
End If

End Function

and then calling it as =FileExists("c:\somefile.txt",NOW())

causes it to check for the file every time something in the workbook has
been changed.

Alternatively, you could look into the OnTime method of the Application
object which lets you schedule when code gets run. Your code would need to
get called from an event or a button initially but it would then schedule
iteslf to run every x seconds/minutes.
--
HTH

Simon


"Andibevan" wrote:

Hi All,

I have the following UDF to check if a file exists:-

Function FileExists(fname) As Boolean

If Dir(fname) < "" Then _

FileExists = True _

Else FileExists = False





End Function



The problem is that it doesn't re-calculate when you press F9. You have to
go into the cell by pressing F2 in order for it to re-calculate.



Adding "Applicaiton.Volatile" doesn't seem to work either.



How do you work around this?



Ta



Andi

Great Simon - I think I will add the now() actually into the code that
should do the same I presume.

"Simon Letten" wrote in message
...
Hi

The problem is that Excel doesn't know that the formula should be
're-calculated'. If you add another "volatile" parameter to the function
it
will get reclaced every time Excel does a recalc. So changing the
function
to:
Function FileExists(fname, CurrentDateTime) As Boolean

If Dir(fname) < "" Then
FileExists = True
Else
FileExists = False
End If

End Function

and then calling it as =FileExists("c:\somefile.txt",NOW())

causes it to check for the file every time something in the workbook has
been changed.

Alternatively, you could look into the OnTime method of the Application
object which lets you schedule when code gets run. Your code would need
to
get called from an event or a button initially but it would then schedule
iteslf to run every x seconds/minutes.
--
HTH

Simon


"Andibevan" wrote:

Hi All,

I have the following UDF to check if a file exists:-

Function FileExists(fname) As Boolean

If Dir(fname) < "" Then _

FileExists = True _

Else FileExists = False





End Function



The problem is that it doesn't re-calculate when you press F9. You have
to
go into the cell by pressing F2 in order for it to re-calculate.



Adding "Applicaiton.Volatile" doesn't seem to work either.



How do you work around this?



Ta



Andi

I think you'll find that it needs to get passed as a parameter to the
function so that Excel knows it needs to call the function as part of the
recalculation. Just putting a call to the Now() function inside the
function's VBAS code won't tell Excel that the code needs to get run in the
first place.
--

Simon


"Andibevan" wrote:

Great Simon - I think I will add the now() actually into the code that
should do the same I presume.

"Simon Letten" wrote in message
...
Hi

The problem is that Excel doesn't know that the formula should be
're-calculated'. If you add another "volatile" parameter to the function
it
will get reclaced every time Excel does a recalc. So changing the
function
to:
Function FileExists(fname, CurrentDateTime) As Boolean

If Dir(fname) < "" Then
FileExists = True
Else
FileExists = False
End If

End Function

and then calling it as =FileExists("c:\somefile.txt",NOW())

causes it to check for the file every time something in the workbook has
been changed.

Alternatively, you could look into the OnTime method of the Application
object which lets you schedule when code gets run. Your code would need
to
get called from an event or a button initially but it would then schedule
iteslf to run every x seconds/minutes.
--
HTH

Simon


"Andibevan" wrote:

Hi All,

I have the following UDF to check if a file exists:-

Function FileExists(fname) As Boolean

If Dir(fname) < "" Then _

FileExists = True _

Else FileExists = False





End Function



The problem is that it doesn't re-calculate when you press F9. You have
to
go into the cell by pressing F2 in order for it to re-calculate.



Adding "Applicaiton.Volatile" doesn't seem to work either.



How do you work around this?



Ta



Andi

Simon - My approach does seem to have worked. It does only seem to work
when you press F9 to re-calculate. Doesn't work if you sent calculation to
automatic

"Simon Letten" wrote in message
...
I think you'll find that it needs to get passed as a parameter to the
function so that Excel knows it needs to call the function as part of the
recalculation. Just putting a call to the Now() function inside the
function's VBAS code won't tell Excel that the code needs to get run in
the
first place.
--

Simon


"Andibevan" wrote:

Great Simon - I think I will add the now() actually into the code that
should do the same I presume.

"Simon Letten" wrote in message
...
Hi

The problem is that Excel doesn't know that the formula should be
're-calculated'. If you add another "volatile" parameter to the
function
it
will get reclaced every time Excel does a recalc. So changing the
function
to:
Function FileExists(fname, CurrentDateTime) As Boolean

If Dir(fname) < "" Then
FileExists = True
Else
FileExists = False
End If

End Function

and then calling it as =FileExists("c:\somefile.txt",NOW())

causes it to check for the file every time something in the workbook
has
been changed.

Alternatively, you could look into the OnTime method of the
Application
object which lets you schedule when code gets run. Your code would
need
to
get called from an event or a button initially but it would then
schedule
iteslf to run every x seconds/minutes.
--
HTH

Simon


"Andibevan" wrote:

Hi All,

I have the following UDF to check if a file exists:-

Function FileExists(fname) As Boolean

If Dir(fname) < "" Then _

FileExists = True _

Else FileExists = False





End Function



The problem is that it doesn't re-calculate when you press F9. You
have
to
go into the cell by pressing F2 in order for it to re-calculate.



Adding "Applicaiton.Volatile" doesn't seem to work either.



How do you work around this?



Ta



Andi