BET: Microsoft Excel Functions User Defined

Leading the way in Microsoft Office Development

Home

Excel

Word

Microsoft Excel > Functions User Defined > Recalculating Functions

< Previous | Next >

When will our custom functions be recalculated ?

	This is not as straight forward as you might think and depends on the following:
	1) Which shortcut keys are pressed: F9, (Shift + F9), (Ctrl + Shift + F9), etc.
	2) Whether your custom function contains "Application.Volatile = True".
	3) Whether your custom function requires any parameters.

Testing the Functions

We have written four simple custom worksheet functions to illustrate the differences.

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16

Public Function FunctionNoParameters() As String
   FunctionNoParameters = CStr(Format(Now() * 10000000, "0.00000"))
End Function

Public Function FunctionNoParametersWithVolatile() As String
   Application.Volatile
   FunctionNoParametersWithVolatile = CStr(Format(Now() * 10000000, "0.00000"))
End Function

Public Function FunctionWithRedundantParameter(ByVal rgeRange As Range) As String
   FunctionWithRedundantParameter = CStr(Format(Now() * 10000000, "0.00000"))
End Function

Public Function FunctionWithUsedParameter(ByVal rgeRange As Range) As String
   FunctionWithUsedParameter = CStr(Format(rgeRange.Value, "0.00000"))
End Function

Using the Functions

These custom functions are then referenced in the normal way from your worksheet.

Pressing F9

	Pressing F9 recalculates any cells that have changed in all the open workbooks.
	This will only calculale formulas that have changed since the last calculation.
	Only custom functions which contain the Application.Volatile statement or functions that have parameters which are referencing cells whose values change will be recalculated.
	Any functions that use parameters to cell references must use the value in there calculation.

Pressing (Shift + F9)

	Pressing (Shift + F9) is the same as pressing F9 except that it only recalculates cells on the active worksheet.
	Only custom functions which contain the Application.Volatile statement or functions that have parameters which are referencing cells whose values change will be recalculated.
	Any functions that use parameters to cell references must use the value in there calculation.

Pressing (Ctrl + Alt + F9)

	This was added in Excel 2000.
	Pressing (Ctrl + Alt + F9) recalculates all cells in all open workbooks regardless of whether they need to be recalculated.
	This is often referred to as full calculation.
	All custom functions will be recalculated regardless.

Pressing (Ctrl + Shift + F9)

	Pressing (Ctrl + Shift + F9) does not seem to work in Excel 2003 or Excel 2002.
	It is meant to recalculate all cells in the active workbook regardless of whether they need to be recalculated.

Pressing (Ctrl + Alt + Shift + F9)

	This was added in Excel 2002.
	Pressing (Ctrl + Alt + Shift + F9) recalculates all cells in all open workbooks regardless of whether they need to be recalculated.
	This is often referred to a full calculation with dependency tree rebuild.
	This includes all custom worksheet functions and external worksheet functions using a DDE.

Things to Remember

		Pressing (Ctrl + Shift + F9) does not seem to work in Excel 2002 or Excel 2003.
		Remember that the settings on the (Tools > Options)(Calculation tab) are workbook specific but it is the first workbook that is opened that determines what the settings are. Opening subsequent workbooks will not change the options.

< Previous | Top | Next >

Leading the way in Microsoft Office Development

When will our custom functions be recalculated ?

This is not as straight forward as you might think and depends on the following:

1) Which shortcut keys are pressed: F9, (Shift + F9), (Ctrl + Shift + F9), etc.

2) Whether your custom function contains "Application.Volatile = True".

3) Whether your custom function requires any parameters.

Testing the Functions

We have written four simple custom worksheet functions to illustrate the differences.

Using the Functions

These custom functions are then referenced in the normal way from your worksheet.

Pressing F9

Pressing F9 recalculates any cells that have changed in all the open workbooks.

This will only calculale formulas that have changed since the last calculation.

Only custom functions which contain the Application.Volatile statement or functions that have parameters which are referencing cells whose values change will be recalculated.

Any functions that use parameters to cell references must use the value in there calculation.

Pressing (Shift + F9)

Pressing (Shift + F9) is the same as pressing F9 except that it only recalculates cells on the active worksheet.

Only custom functions which contain the Application.Volatile statement or functions that have parameters which are referencing cells whose values change will be recalculated.

Any functions that use parameters to cell references must use the value in there calculation.

Pressing (Ctrl + Alt + F9)

This was added in Excel 2000.

Pressing (Ctrl + Alt + F9) recalculates all cells in all open workbooks regardless of whether they need to be recalculated.

This is often referred to as full calculation.

All custom functions will be recalculated regardless.

Pressing (Ctrl + Shift + F9)

Pressing (Ctrl + Shift + F9) does not seem to work in Excel 2003 or Excel 2002.

It is meant to recalculate all cells in the active workbook regardless of whether they need to be recalculated.

Pressing (Ctrl + Alt + Shift + F9)

This was added in Excel 2002.

Pressing (Ctrl + Alt + Shift + F9) recalculates all cells in all open workbooks regardless of whether they need to be recalculated.

This is often referred to a full calculation with dependency tree rebuild.

This includes all custom worksheet functions and external worksheet functions using a DDE.

Things to Remember

Pressing (Ctrl + Shift + F9) does not seem to work in Excel 2002 or Excel 2003.

Remember that the settings on the (Tools > Options)(Calculation tab) are workbook specific but it is the first workbook that is opened that determines what the settings are. Opening subsequent workbooks will not change the options.