Excel：自分で定義したFunctionを使用しているセルの自動再計算

Public Function MyTest(引数) As String

    Application.Volatile
    ・・・
    MyTest = ・・・ '計算

End Function

private MyCells As New Collection

Public Function MyTest(引数) As String

    Dim callerCell As Range
    Dim targetCell As Range
    Dim key As String

    Set callerCell = Application.Caller
    key = MyCellKey(callerCell)
    On Error Resume Next
    Set targetCell = MyCells.Item(key)
    On Error GoTo 0
    If targetCell Is Nothing Then
        Call MyCells.Add(callerCell, key)
    End If
    ・・・
    MyTest = ・・・ '計算

End Function

Public Sub Calc() '実際にはVC++による処理

    Dim myCell As Range
    Dim delCell As Range
    Dim deleteCells As New Collection

    For Each myCell in MyCells
        If InStr(1, myCell.Formula, "MyTest", vbTextCompare) >= 1 Then
            myCell.Formula = myCell.Formula '再計算させるため強引に上書き
        Else
            deleteCells.Add myCell
        End If
    Next

    For Each delCell in deleteCells
        MyCells.Remove MyCellKey(delCell)
    Next

End Sub

Private Function MyCellKey(tempCell As Range) As String

    MyCellKey = tempCell.Worksheet.Parent.Name & "," & _
                tempCell.Worksheet.Name & "," & _
                tempCell.Column & "," _
                tempCell.Row

End Function