formula wont update

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

That somebody must have been Bob Phillips...

Add the following line to the custom formula:

application.Volatile

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

Kevin Backmann


"Arien" wrote:

>
> 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
> ------------------------------------------------------------------------
> Arien's Profile: http://www.excelforum.com/member.php...fo&userid=1892
> View this thread: http://www.excelforum.com/showthread...hreadid=514849
>
>

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

Sorry my mistake. It does work now.

Thanks a lot

regards,
Arien