formula wont update

[Arien]

Hi,

Somebody (forgot the name, sorry) on this forum gave me a formula to
check if a filename exists:

Function BookExists(wb As String)
BookExists = Len(Dir(wb)) > 0
End Function

It worked fine however now I am using it in a different sheet were the
filename is flexible like:

=IF(BookExists(path&filename&"*.xls");TRUE;FALSE)

were path is a named field and filename is a reference to a cell (like
A20)

It still works but only recalculates (checks if the file exists) when I
edit the cell and press enter again. Just F9 won't work.

Is there a way to make this formula recalculate by itself or
recalculate by a macro?

Thanks for your help.

Arien de Haan

 

[Arien]

That somebody must have been Bob Phillips...

 

[Guest]

Add the following line to the custom formula:

application.Volatile

This will force the formula to recalc everytime the spreadsheet recalcs.

Kevin Backmann

 

[Arien]

Kevin,

Thanks for your help, but I am afraid it still does not update.

I now made the formula:

Function BookExists(wb As String)
Application.Volatile
BookExists = Len(Dir(wb)) > 0
End Function

Did I understand you correctly?

I still have to edit the cell with the formule (F2) and press enter.

regards,
Arien

 

[Arien]

Sorry my mistake. It does work now.

Thanks a lot

regards,
Arien