Microsoft KB Archive/278328

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

Article ID: 278328

Article Last Modified on 1/29/2007

-

APPLIES TO


 * Microsoft Excel 2002 Standard Edition

-



This article was previously published under Q278328



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



MORE INFORMATION
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:   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  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.  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   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 

Additional query words: OfficeKBHowTo inf XL2002 precedent dependent dependant contingent

Keywords: kbhowto KB278328

-

[mailto:TECHNET@MICROSOFT.COM Send feedback to Microsoft]

© Microsoft Corporation. All rights reserved.