Get UDF to Re-calculate without entering cell

[Andibevan]

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

 

[Guest]

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.

 

[Andibevan]

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

 

[Guest]

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.

 

[Andibevan]

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