How to make a function in a cell update
 

I have a worksheet with cells that contain the count of the number of files
in a particular folder as defined by "=countfiles(H3)", where H3 equals the
name of the folder I want to count files in. The countfiles function looks
like this:

Function countfiles(ByVal foldername As String)

With Application.FileSearch
.LookIn = "S:\Security" & foldername
.FileType = msoFileTypeWordDocuments
.Execute
countfiles = .FoundFiles.Count
End With

End Function

It works just fine, but the problem comes in when the count in one of those
folder is changed in some way...The cell with the formula in it that refers
to the folder where the count changed doesn't update to reflect the new
count. How can I get these functions to "refresh" or "recalculate"? I've
tried closing and reopening the spreadsheet, but that doesn't work. I've
tried going to Data---!Refresh Data, but that option is grayed out.

Any ideas?

-Brian

How to make a function in a cell update
 

You can add:

application.volatile

to the top of your routine--and your function will recalculate when excel
recalculates. I don't know if this will be a problem for you.

If you use that function lots of times, it could slow down the calculation.

And if something changes in that folder and you don't do anything in excel that
causes it to recalc, then the results of this function won't be up to date.

Brian Beck wrote:

I have a worksheet with cells that contain the count of the number of files
in a particular folder as defined by "=countfiles(H3)", where H3 equals the
name of the folder I want to count files in. The countfiles function looks
like this:

Function countfiles(ByVal foldername As String)

With Application.FileSearch
.LookIn = "S:\Security" & foldername
.FileType = msoFileTypeWordDocuments
.Execute
countfiles = .FoundFiles.Count
End With

End Function

It works just fine, but the problem comes in when the count in one of those
folder is changed in some way...The cell with the formula in it that refers
to the folder where the count changed doesn't update to reflect the new
count. How can I get these functions to "refresh" or "recalculate"? I've
tried closing and reopening the spreadsheet, but that doesn't work. I've
tried going to Data---!Refresh Data, but that option is grayed out.

Any ideas?

-Brian

--

Dave Peterson