为什么使用Evaluate函数时,Excel/Excel UDF调用sub两次?

yi0zb3m4  于 2023-11-20  发布在  其他
关注(0)|答案(3)|浏览(100)

我试图让我的udf在使用udf的单元格的偏移量处插入一个矩阵。我使用了一个我在这里看到的'技巧':https://wellsr.com/vba/2016/excel/how-to-change-another-cell-with-a-vba-function-udf/
然而,每次我尝试使用数组执行此操作时,它都会崩溃。在调试该问题时,我了解到被评估的函数被多次调用。然后我设置`Application.EnableEvents = False”。在这样做之后,我仍然看到被评估的函数被调用两次。为什么会这样?

Public Function Test()
    Dim evalstr As String
    On Error GoTo ErrHandler
    Debug.Print "test 1"
    evalstr = "TestProxy(" & ActiveCell.Address(False, False) & ")"
    Application.EnableEvents = False
    Evaluate evalstr
    Debug.Print "test 2"
ExitFunction:
    Application.EnableEvents = True
    Exit Function
    
ErrHandler:
    Debug.Print Err.Description
    Resume ExitFunction
End Function

Private Sub TestProxy(rng As Range)
    Dim arr(1, 1) As Variant, evalstr As String
    On Error GoTo ErrHandler
    Debug.Print "proxy 1"
    arr(0, 0) = 100
    arr(0, 1) = 101
    arr(1, 0) = 101
    arr(1, 1) = 101
    'rng.Offset(1, 0).Resize(2, 2).value = arr
    Debug.Print "proxy 2"

ExitSub:
    Exit Sub
    
ErrHandler:
    Debug.Print Err.Description
    Resume ExitSub
End Sub

字符串
Immediate窗口中的输出显示,求值的Sub,TestProxy被调用了两次:

test 1
proxy 1
proxy 2
proxy 1
proxy 2
test 2


有人知道为什么会这样吗?还有,有人知道为什么当我在上面的代码中取消注解被删除的行时,它第一次工作,然后在随后重新评估单元格时崩溃吗?
谢谢

wnavrhmk

wnavrhmk1#

你至少可以在TestProxy内部短路,当方法被同一个地址调用两次时退出:

Public Function Test()
    
    Dim evalstr As String, c As Range, addr As String
    On Error GoTo ErrHandler
    Set c = Application.ThisCell 'where the formula is
    addr = c.Address(False, False)
    Debug.Print "Test called from " & addr & " on " & c.Parent.Name
    evalstr = "TestProxy(" & addr & ")"
    c.Parent.Evaluate evalstr '### use Worksheet.Evaluate method not Application
    Exit Function
    
ErrHandler:
    Debug.Print Err.Description
End Function

Private Sub TestProxy(rng As Range)
    Static addr As String               'to store address of last call
    On Error GoTo ErrHandler
    If rng.Address = addr Then Exit Sub 'second call?
    Debug.Print "TestProxy for " & rng.Address
    rng.Offset(0, 1).Resize(1, 2).Value = Array("A", "B") 'do something
    addr = rng.Address                  'save this cell address
ExitSub:
    Exit Sub
    
ErrHandler:
    Debug.Print Err.Description
    Resume ExitSub
End Sub

字符串
还要注意Worksheet.Evaluate方法的显式使用(而不是默认的Application.Evaluate,它在调用公式时恰好是活动对象的上下文中运行.)

jchrr9hc

jchrr9hc2#

如果你在TestProxy的开头加上一个Debug.Print Application.Caller.Address,你会看到第一个调用总是指向A1,第二个调用指向你的活动单元。
你可以做的是添加一个这样的检查:

If Application.Caller.Address(False, False) = rng.Address(False, False) Then
        Debug.Print "proxy 1"
        arr(0, 0) = 100
        arr(0, 1) = 101
        arr(1, 0) = 101
        arr(1, 1) = 101
        rng.Offset(1, 0).Resize(2, 2).Value = arr
        Debug.Print "proxy 2"
    End If

字符串
但是当您从A1调用UDF时,它仍然失败。

h7appiyu

h7appiyu3#

将代码更改为:

Public Function Test()
    Dim evalstr As String
    On Error GoTo ErrHandler
    Debug.Print "test 1"
    evalstr = "0+TestProxy(" & ActiveCell.Address(False, False) & ")"
    ActiveSheet.Evaluate evalstr
    Debug.Print "test 2"
ExitFunction:
    Exit Function    
    ErrHandler:
    Debug.Print Err.Description
    Resume ExitFunction
End Function

字符串
更多PS:Stop VBA Evaluate from calling target function twice

相关问题