OnKey和SendKeys开始递归循环，为什么？

Question

每个按a-zA-Z0-9键都启动一个名为LetterPress的宏，按下该键作为参数。基本上，我对每个a-zA-Z0-9键都有一个Application.OnKey。

如果活动单元是一个特定的单元，我想启动一个子，否则我只想发送键像正常。它启动一个不结束的递归循环。这是最初的代码：

Sub LetterPress(B as String)
If Application.ActiveCell.Row = 3 And Application.ActiveCell.Column = 5 And Application.ActiveSheet.Name = "Search" And Not IsNumeric(B) Then
    Call Search1(B)
Else
    Application.SendKeys B       'This line starts a recursive loop
End if                                                      

我尝试重置OnKey，发送密钥，然后将密钥重新分配给宏。但是最后一行(赋值位)仍然启动递归循环。

Sub LetterPress(B as String)
If Application.ActiveCell.Row = 3 And Application.ActiveCell.Column = 5 And Application.ActiveSheet.Name = "Search" And Not IsNumeric(B) Then
    Call Search1(B)
Else
    Application.OnKey B          'Reset Application.OnKey so a recursive loop doesn't start. This line works.
    Application.SendKeys B       'Send the string B (a letter). This works too.
    Application.OnKey B, "'LetterPress """ & B & """'"      'Assign whatever is in string variable B back to the 
End if                                                      'macro, with it self as the parameter. 
                                                            'This line somehow triggers B and starts the 
                                                            'recursive loop.

有人知道为什么吗？

Answer 1

你的尝试没那么糟。问题是，使用Application.SendKeys B，您正在发送密钥，但它没有被处理。首先执行代码直到结束，然后Excel返回控件并处理关键笔画。那时，您的OnKey-definition已经处于活动状态。

然而，有一个简单的解决方案:给Excel做这项工作的机会。您可以使用DoEvents来完成这个任务。

Application.OnKey B
Application.SendKeys B       
DoEvents
Application.OnKey B, "'LetterPress """ & B & """'" 

我做了个测试，至少对我有用--我很好奇你是否能确认。

Answer 2

这样做可能会奏效：

Sub LetterPress(B as String)
If Application.ActiveCell.Row = 3 And Application.ActiveCell.Column = 5 And Application.ActiveSheet.Name = "Search" And Not IsNumeric(B) Then
    Call Search1(B)
Else
    Application.OnKey B          'Reset Application.OnKey so a recursive loop doesn't start. This line works.
    Application.SendKeys B       'Send the string B (a letter). This works too.
    Application.DoEvents
    Application.OnKey B, "'LetterPress """ & B & """'"      'Assign whatever is in string variable B back to the 
End if                                                      'macro, with it self as the parameter. 
                                                            'This line somehow triggers B and starts the 
                                                            'recursive loop.

话虽如此，我从未真正尝试过使用DoEvents()函数。如果有用的话请告诉我。