Visual Basic for Applications/避免更改事件递归

摘要

此 VBA 代码旨在在可以运行宏的 Microsoft Office 应用程序中运行，例如 Excel 或 Word。
它提供了两个不同的文本框更改事件示例，一个用于 TextBox1，另一个用于 TextBox2。
需要注意的是，在显示表单并在 TextBox1 中输入一个字符后，在那里找到的数字约为 290；这是代码显示已发生的更改事件的迭代次数。
在 TextBox2 中执行相同的操作显示该事件只运行了一次。
TextBox2_Change 事件的代码避免了该过程的多次运行，从而避免了在某些情况下出现错误结果的可能性。

VBA 代码

代码更改

用于 ThisWorkbook 模块

'...............................................
' Notes: Code needs a user form named UserForm1,
' with two text boxes, TextBox1 and Textbox2,
'...............................................

Private Sub Workbook_Open()
    'Runs on opening the workbook
   
    Load UserForm1
    UserForm1.Show

End Sub

用于 UserForm1 模块

Private Sub TextBox1_Change()
    ' This Change event runs about 294 times
    ' for each character entered
    
    Static nC As Long
    
    'yield to commands-just in case
    DoEvents
    
    'increment for each iteration
    nC = nC + 1
    
    'this line causes this procedure to run again
    TextBox1.Value = nC

End Sub

Private Sub TextBox2_Change()
    ' This Change event runs only once
    ' for each character entered
    
    Static nC As Long
    Static bEnableEvents As Boolean
    
    'yield to commands-just in case
    DoEvents
    
    ' increment for each iteration
    nC = nC + 1
    
    ' false to start then true after that
    If bEnableEvents = True Then
        Exit Sub
    End If
    bEnableEvents = True

    ' this runs only once
    TextBox2.Value = nC
    
    ' reset flag
    bEnableEvents = False

End Sub

另请参阅

外部链接