UDF recalc question

[Steve]

I have this UDF:

Function FileCount(FolderName As String, _
Optional FileFilter As String = "*", _
Optional FileTypes As Long = 1, _
Optional SubFolders As Boolean = False)
As Long
With Application.FileSearch
.NewSearch
.LookIn = FolderName
.SearchSubFolders = SubFolders
.Filename = FileFilter
.MatchTextExactly = True
.FileType = FileTypes
.Execute
FileCount = .FoundFiles.Count
End With
End Function

I use it in a group of cells to go to a UNC path to count
the number of files in the folder.

Formula example:

=FileCount
("\\Thtree\.AASJAPN00013_INSTALL.SJ.AA.MI.ER.TH\Nalkits\Sec
urityPatchIE_MS_0216200400_L2K2_40\Patchlog")

This works fine when I initially put the formula into the
cell, but afterward I want it to check for additional
files that may get added.

How do you force this formula to run again???

 

[Frank Kabel]

Hi Steve
try adding the line
Application.volatile
at the beginning of your function

 

[Steve]

What does this statement do??
How does the update occur, is it automatic or does it need
to be started somehow?

 

[Frank Kabel]

Hi
this forces you function to re-calculate every time Excel starts a
recalculation in any sheet.
note: doing this will cause the spreadsheet to ask for 'Save changes'
even if you haven't change anything in your sheet.
for more information have a look at the VBA help