I have an excel spreadsheet with a custom vba function that will simply return the document property with the name given in the parameter. This function works wonderfully. I can enter it as the formula for a particular cell in the worksheet (such as =DocProperty("MyVariable")) and that cell will now display the value of that document property.

Although this does work as expected the first time the cell's value is calculated it does not automatically update if the document property changes. Excel seems to cache the value of each cell. This makes sense for performance issues however I am using DSOFile.dll to update these document properties from another application (namely Qualtrax.) With this setup the values are changed when the document is closed and I would like the new values of these variables to be displayed each time the document is opened.

I have seen similar behavior with "Fields" in Word documents where the displayed value does not correspond to the actual value stored. The solution in Word documents was to add a VBA macro that runs automatically when the document is opened, so I figured I would create a similar macro that would update my Excel documents. I figured something like the following would work:

Public Sub Auto_Open()
    Dim ws As Worksheet
   
    For Each ws In ThisWorkbook.Worksheets
        ws.Calculate
    Next ws
End Sub

However I wanted to be sure that in the event of a worksheet with a large number of calculated cells I only update the ones that need to be udpated. So I came up with the following code:

Public Sub Auto_Open()
' - This macro will refresh any cells where the formula
' - is a call to the DocProperty function
'
    Dim ws As Worksheet
    Dim cell As Range
   
    For Each ws In ThisWorkbook.Worksheets
        For Each cell In ws.UsedRange.Cells
            If InStr(cell.Formula, "DocProperty") > 0 Then
                cell.Calculate
            End If
        Next cell
    Next ws
End Sub

Much to my surprise this function did not work. I stepped thorough and discovered that the cell.Calculate call was being made for each cell whose formula was a call to my DocProperty function (shown below), however for some reason Excel is not actually recalculating the value! After a couple hours of torment I found that resetting the formula on the cell will force a recalculate so simply changing cell.Calculate to cell.Formula = cell.Formula fixes the problem and actually makes Excel recalculate the value of that cell. WHY OH WHY does the Range.Caclulate function not actually force a recalculate of a cell whose formula calls a vba function?!?!?

Complete working code.

Public Function DocProperty(property As String) As String
    Dim WB As Workbook
    On Error Resume Next
    If TypeOf Application.Caller Is Range Then
        Set WB = Application.Caller.Parent.Parent
    Else
        Set WB = ActiveWorkbook
    End If
    DocProperty = WB.CustomDocumentProperties(property)
    WB.Saved = True
End Function

Public Sub Auto_Open()
' - This macro will refresh any cells where the formula
' - is a call to the DocProperty function
'
    Dim ws As Worksheet
    Dim cell As Range
   
    For Each ws In ThisWorkbook.Worksheets
        For Each cell In ws.UsedRange.Cells
            If InStr(cell.Formula, "DocProperty") > 0 Then
                cell.Formula = cell.Formula
            End If
        Next cell
    Next ws
End Sub