如何实现仅在触发开启且输入变更时执行耗时自定义Excel函数？

方法1：给自定义函数添加缓存+触发判断 #

直接修改你的MYFUNC函数，加入缓存机制，同时结合触发条件，只有当输入参数变更且触发单元格为TRUE时才重新计算，否则返回缓存的结果。

实现步骤： #

• 声明全局字典存储输入参数的哈希值与对应计算结果（全局变量在工作簿关闭后重置，适配单次会话内的缓存需求）
• 编写辅助函数，将输入的单元格/区域转换为唯一哈希字符串（用于判断内容是否变更）
• 在MYFUNC中先检查触发单元格状态，再对比当前输入哈希与缓存哈希，满足条件才执行计算

代码示例： #

' 全局缓存字典：输入哈希 -> 计算结果
Private FuncCache As Object

Function MYFUNC(inputRng As Range, triggerCell As Range) As Variant
    Dim inputHash As String
    Dim cachedResult As Variant
    
    ' 初始化缓存字典
    If FuncCache Is Nothing Then Set FuncCache = CreateObject("Scripting.Dictionary")
    
    ' 生成输入区域的唯一哈希值
    inputHash = GetRangeHash(inputRng)
    
    ' 判断是否需要重新计算
    If triggerCell.Value = True Then
        If Not FuncCache.Exists(inputHash) Then
            ' 执行原耗时计算逻辑
            cachedResult = OriginalCalculation(inputRng)
            ' 更新缓存
            FuncCache(inputHash) = cachedResult
        Else
            ' 直接返回缓存结果
            cachedResult = FuncCache(inputHash)
        End If
        MYFUNC = cachedResult
    Else
        ' 触发为FALSE时返回当前单元格已有值，避免循环引用
        MYFUNC = Application.Caller.Value
    End If
End Function

' 辅助函数：生成区域内容的哈希字符串
Private Function GetRangeHash(rng As Range) As String
    Dim cell As Range
    Dim hashStr As String
    
    hashStr = ""
    For Each cell In rng
        ' 拼接单元格地址与内容，确保唯一性
        hashStr = hashStr & cell.Address & ":" & CStr(cell.Value) & "|"
    Next cell
    ' 用MD5生成简洁哈希
    GetRangeHash = EncodeMD5(hashStr)
End Function

' MD5编码函数
Private Function EncodeMD5(inputStr As String) As String
    Dim objMD5 As Object
    Set objMD5 = CreateObject("System.Security.Cryptography.MD5CryptoServiceProvider")
    Dim bytes() As Byte
    bytes = StrConv(inputStr, vbFromUnicode)
    bytes = objMD5.ComputeHash(bytes)
    
    Dim i As Integer
    EncodeMD5 = ""
    For i = LBound(bytes) To UBound(bytes)
        EncodeMD5 = EncodeMD5 & Hex(bytes(i))
    Next i
    Set objMD5 = Nothing
End Function

' 替换为你原有的MYFUNC计算逻辑
Private Function OriginalCalculation(inputRng As Range) As Variant
    ' 示例：模拟耗时计算
    Application.Wait Now + TimeValue("00:00:02")
    OriginalCalculation = inputRng.Value * 2
End Function

使用方式： #

在目标单元格输入公式：=MYFUNC(A2, A3)，其中A2为输入参数区域，A3为TRUE/FALSE触发单元格。

方法2：用VBA触发更新，结果存辅助单元格 #

若不想修改原自定义函数，可将计算结果存储在辅助单元格中，仅当输入参数变更且触发条件激活时，通过VBA更新辅助单元格，公式直接引用该单元格。

实现步骤： #

1. 给输入参数区域添加Worksheet_Change事件，记录输入哈希值
2. 触发单元格设为TRUE时，检查输入哈希是否变更，是则执行MYFUNC并更新辅助单元格
3. 公式单元格直接引用辅助单元格，避免重复计算

代码示例（工作表模块中）： #

Private lastInputHash As String
Private inputRange As Range
Private triggerCell As Range
Private resultCell As Range

Private Sub Worksheet_Activate()
    ' 根据实际情况修改区域地址
    Set inputRange = Me.Range("A2:A5")
    Set triggerCell = Me.Range("A3")
    Set resultCell = Me.Range("A1")
    ' 记录初始输入哈希
    lastInputHash = GetRangeHash(inputRange)
End Sub

Private Sub Worksheet_Change(ByVal Target As Range)
    ' 输入区域变更时更新哈希
    If Not Intersect(Target, inputRange) Is Nothing Then
        lastInputHash = GetRangeHash(inputRange)
    End If
    
    ' 触发单元格变更时检查并更新结果
    If Not Intersect(Target, triggerCell) Is Nothing Then
        If triggerCell.Value = True Then
            Dim currentHash As String
            currentHash = GetRangeHash(inputRange)
            If currentHash <> lastInputHash Then
                resultCell.Value = MYFUNC(inputRange)
                lastInputHash = currentHash
            End If
        End If
    End If
End Sub

' 复用哈希生成函数
Private Function GetRangeHash(rng As Range) As String
    Dim cell As Range
    Dim hashStr As String
    
    hashStr = ""
    For Each cell In rng
        hashStr = hashStr & cell.Address & ":" & CStr(cell.Value) & "|"
    Next cell
    GetRangeHash = EncodeMD5(hashStr)
End Function

Private Function EncodeMD5(inputStr As String) As String
    Dim objMD5 As Object
    Set objMD5 = CreateObject("System.Security.Cryptography.MD5CryptoServiceProvider")
    Dim bytes() As Byte
    bytes = StrConv(inputStr, vbFromUnicode)
    bytes = objMD5.ComputeHash(bytes)
    
    Dim i As Integer
    EncodeMD5 = ""
    For i = LBound(bytes) To UBound(bytes)
        EncodeMD5 = EncodeMD5 & Hex(bytes(i))
    Next i
    Set objMD5 = Nothing
End Function

使用方式： #

公式单元格直接引用A1（结果单元格），当A3设为TRUE时，仅A2:A5内容变更过才会重新计算MYFUNC并更新A1。

关键说明 #

• 方法1适合多处调用MYFUNC的场景，每个公式可独立控制触发与缓存
• 方法2适合集中管理计算的场景，减少函数调用开销
• 缓存依赖全局/模块级变量，工作簿重启后会清空缓存，属于正常行为（重启后输入可能已变更）

内容的提问来源于stack exchange，提问作者Abiel