User Defined Functions (XL XP)

Topic

New Reply

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?

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

Reply | Quote

Replies

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

Reply | Quote

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.

Reply | Quote

Thanks Tony / Hans

Reply | Quote