User Defined Functions (XL XP)

[Ruff_Hi]

We are adding some user defined functions to an excel project that we are working on but we are having difficulty getting them to fire / calculate. I have one function that returns the number of tabs in a file. I've added this function to two excel files and when I flick from one to the other they both show the tab count from the last time I forced a calculation.

Is there a way to get these soft of functions to calculate?

<pre> Public Function NumberOfTabs() As Long
' this function returns the number of tabs in the workbook
NumberOfTabs = ActiveWorkbook.Sheets.Count
End Function</pre>

[TonyE]

You need to tell Excel that it has to re-calculate whenever a change is made, do this by inserting the following line in the function

Application.Volatile True

[HansV]

You can add a line

Application.Volatile

at the beginning of such functions. This means that the result will be updated whenever any recalculation occurs in the spreadsheet. Just inserting a new worksheet may not always trigger a recalculation, however.

[Ruff_Hi]

Thanks Tony / Hans