XL2002: How to Mark an Automation Add-In Function as Volatile

This article contains sample Microsoft Visual Basic for Applications code that demonstrates how to mark an Excel Automation Add-In function as volatile.

Microsoft provides programming examples for illustration only, without warranty either expressed or implied. This includes, but is not limited to, the implied warranties of merchantability or fitness for a particular purpose. This article assumes that you are familiar with the programming language that is being demonstrated and with the tools that are used to create and to debug procedures. Microsoft support engineers can help explain the functionality of a particular procedure, but they will not modify these examples to provide added functionality or construct procedures to meet your specific requirements.

When to Use Volatile Functions

When Excel calculates a cell that contains a custom function, it recalculates all cell ranges that are passed as arguments to that custom function. If the result of the custom function depends on cells that the function does not explicitly refer to, those cells may not be recalculated. To work around this behavior, mark the function as volatile.

You may want to mark your custom function as volatile when the following conditions are true:

• Your custom function contains one or more arguments that refer to a range of cells on the worksheet.
  
  -and-
  
  
• The result of your custom function also depends on more cells than it directly references.

NOTE: If you make your custom function volatile, it recalculates every time you change a value or recalculate an open workbook. This may increase the time it takes for your worksheet to recalculate.

How to Mark a Function as Volatile

To mark an Automation add-in function as volatile, follow these steps:

1. In the Microsoft Visual Basic Editor, open the ActiveX dynamic-link library (DLL) that contains the function that you want to make volatile, such as:

   Option Explicit
   
   Function ABC(x As Integer, y As Integer) As Integer
   
       ABC = x + y
   
   End Function
   					

2. Create a reference to the Microsoft Add-In Designer library. To do this, click References on the Project menu. In the References dialog box, click to select the Microsoft Add-In Designer check box. Click OK.
3. Type the following code in the existing class module.
   
   NOTE: The empty subroutines in the following code sample are not required for the IDTExtensibility2 library. The empty subroutines are only required if you create the DLL without using the Designer.

   Implements IDTExtensibility2
   Dim xl As Object
   
   Private Sub IDTExtensibility2_OnConnection(ByVal Application _
    As Object, ByVal ConnectMode As _
    AddInDesignerObjects.ext_ConnectMode, ByVal AddInInst _
    As Object, custom() As Variant)
   
       Set xl = Application
   
   End Sub
   
   Private Sub IDTExtensibility2_OnAddInsUpdate(custom() As Variant)
   
   End Sub
   
   Private Sub IDTExtensibility2_OnBeginShutdown(custom() As Variant)
   
   End Sub
   
   Private Sub IDTExtensibility2_OnDisconnection(ByVal RemoveMode _
    As AddInDesignerObjects.ext_DisconnectMode, custom() As Variant)
   
   End Sub
   
   Private Sub IDTExtensibility2_OnStartupComplete(custom() As Variant)
   
   End Sub
   					

4. Add the following line to your existing function

       xl.Volatile
   						

   so that the final code looks as follows:

   Option Explicit
   Implements IDTExtensibility2
   Dim xl As Object
   
   Private Sub IDTExtensibility2_OnConnection(ByVal Application _
    As Object, ByVal ConnectMode As _
    AddInDesignerObjects.ext_ConnectMode, ByVal AddInInst _
    As Object, custom() As Variant)
   
       Set xl = Application
   
   End Sub
   
   Function ABC(x As Integer, y As Integer) As Integer
   
       xl.Volatile
       ABC = x + y
   
   End Function
   
   Private Sub IDTExtensibility2_OnAddInsUpdate(custom() As Variant)
   
   End Sub
   
   Private Sub IDTExtensibility2_OnBeginShutdown(custom() As Variant)
   
   End Sub
   
   Private Sub IDTExtensibility2_OnDisconnection(ByVal RemoveMode _
    As AddInDesignerObjects.ext_DisconnectMode, custom() As Variant)
   
   End Sub
   
   Private Sub IDTExtensibility2_OnStartupComplete(custom() As Variant)
   
   End Sub