Volatile Functions

A volatile function is recalculated whenever calculation occurs in any cells on the worksheet.  These redundant calculations increase the time it takes to refresh data.  Furthermore, special care must be taken when using volatile functions in conjunction with the TSWrite function.  If a TSWrite function depends (either directly or indirectly) on a volatile function, then when you try to Send Data to Sage 300 CRE you will receive an error message saying that you must remove a duplicate function.

To work around these issues, you can replace the volatile function with a macro that inserts the desired value into the cell that would normally contain the volatile function.  For example, if you used the volatile function Now(), you could eliminate it by creating a macro called TimeStamp and running that before sending the data to Timberline.  The sample code below illustrates what the TimeStamp function might look like:

Public Sub TimeStamp()

Sheet1.Range("A1").Value = Now

End Sub

Following is a list of volatile functions.  This list is for your reference and is not guaranteed to be complete.

  • AREAS()
  • CELL()
  • INDEX()
  • NOW()
  • OFFSET()
  • RAND()
  • ROWS()