Excel VBA：为什么事件触发两次？

Excel VBA: Why does event trigger twice?

提问by Shawn V. Wilson

I'm trying to avoid Event loops by disabling Events at crucial points. However, it doesn't always work. For instance, this code for a Combo box:

我试图通过在关键点禁用事件来避免事件循环。但是，它并不总是有效。例如，此组合框的代码：

Private Sub TempComboS_Change()
Dim e
e = Application.EnableEvents
Application.EnableEvents = False
  ' 
Application.EnableEvents = e
End Sub

The blank line is where the useful code goes; as it stands it obviously doesn't do anything. However, when I run it this way (with the blank line), it reaches "End Sub", then it goes back to the beginning and runs again. (This would make the useful code run twice).

空行是有用代码所在的位置；就目前而言，它显然没有做任何事情。但是，当我以这种方式（使用空行）运行它时，它到达“End Sub”，然后返回到开头并再次运行。（这将使有用的代码运行两次）。

Why is this happening?

为什么会这样？

EDIT: To clarify for the folks who've been helping me.

编辑：澄清那些一直在帮助我的人。

I have a macro that opens the dropdown list of the Combo box, activates it, then ends. It works properly. When I select an item from the open list, the Change event runs. This is the current version of the change event:

我有一个宏可以打开组合框的下拉列表，激活它，然后结束。它工作正常。当我从打开列表中选择一个项目时，Change 事件就会运行。这是更改事件的当前版本：

Private Sub TempComboS_Change()
End Sub

I put a breakpoint on the Private Sub line. It shows that this Change event runs, then runs again. I suspect that it has been doing this all along, and I noticed it now because I need to add code here.

我在 Private Sub 行上放置了一个断点。它表明此 Change 事件运行，然后再次运行。我怀疑它一直在这样做，现在我注意到了，因为我需要在这里添加代码。

I have no class modules or userforms. The controls are on a worksheet.

我没有类模块或用户表单。控件位于工作表上。

I'm going to try the "Run Once" suggestion, and I'll let you know if it works.

我将尝试“运行一次”建议，如果它有效，我会告诉您。

---

---

I tried the "Run Once" code you suggested. It sortof works, but I seem to have a bigger issue. When I select a drop-down list from a data-validated cell, the TempComboS_Change event triggers -- but not only didn't I touch this combo box, the cell isn't the LinkedCell for the combo box. In other words, it seems to be triggering by actions unconnectedto the combo box!

我尝试了您建议的“运行一次”代码。它有点工作，但我似乎有一个更大的问题。当我从经过数据验证的单元格中选择一个下拉列表时，TempComboS_Change 事件会触发——但我不仅没有触摸这个组合框，而且该单元格不是组合框的 LinkedCell。换句话说，它似乎是由与组合框无关的操作触发的！

Got to find out about that Call Stack thing...

必须找出有关调用堆栈的东西...

回答by Cool Blue

Here is a bit of code to help investigate "sequence of events" issues

这是一些有助于调查“事件顺序”问题的代码

In a Standard Module

在标准模块中

Public Enum eNewLine
    No
    Before
    After
    Both
End Enum

Public Function timeStamp(Optional d As Double = 0, Optional newLine As eNewLine = No, Optional Indent As Long = 0, _
                            Optional Caller As String, Optional Context As String, Optional message As String) As String
Dim errorMessage As String

    If Err.number <> 0 Then
        errorMessage = "ERROR: " & Err.number & ": " & Err.Description
        Err.Clear
    End If
    If d = 0 Then d = Time
    With Application.WorksheetFunction
        timeStamp = .Text(Hour(d), "00") & ":" & .Text(Minute(d), "00") & ":" & .Text(Second(d), "00") & ":" & .rept(Chr(9), Indent)
    End With
    If Len(Caller) <> 0 Then timeStamp = timeStamp & Chr(9) & Caller
    If Len(Context) <> 0 Then timeStamp = timeStamp & ": " & Chr(9) & Context
    If Len(message) <> 0 Then timeStamp = timeStamp & ": " & Chr(9) & message
    Select Case newLine
    Case Before
        timeStamp = Chr(10) & timeStamp
    Case After
        timeStamp = timeStamp & Chr(10)
    Case Both
        timeStamp = Chr(10) & timeStamp & Chr(10)
    Case Else
    End Select
    If Len(errorMessage) <> 0 Then
        timeStamp = timeStamp & Chr(9) & errorMessage
    End If

End Function

At the top of each Module

在每个模块的顶部

'Module level Trace Hearder
Const debugEvents as Boolean = True
Const cModuleName As String = "myModuleName"
Const cModuleIndent As Long = 1

You can assign a module level indent for each module to organise the hierarchy an make it easy to understand.

您可以为每个模块分配模块级缩进以组织层次结构并使其易于理解。

In each Sub or Function (or property if you need)...

在每个子或函数（或属性，如果你需要）...

sub mySubName()
Const cMyName As String = "mySubName"

If debugEvents Then Debug.Print timeStamp(NewLine:=Before,Indent:=cModuleIndent, Caller:=cModuleName, Context:=cMyName, Message:="Start")

'Do stuff

If debugEvents Then Debug.Print timeStamp(NewLine:=After,Indent:=cModuleIndent, Caller:=cModuleName, Context:=cMyName, Message:="End")
End Sub

...Or you can use Me.Name for the Context if its a form or a sheet etc. and you can put whatever message or variable values you like in the Message.

...或者您可以使用 Me.Name 作为上下文，如果它是表单或工作表等，并且您可以在消息中放置您喜欢的任何消息或变量值。

You can also use a Timer (eg MicroTimer) and put the result in the Message section.

您还可以使用计时器（例如 MicroTimer）并将结果放入消息部分。

Here is an example output:

这是一个示例输出：

15:54:07:       Roll-Up Select:     Worksheet_Activate:      Start: 3.24591834214516E-03


15:54:07:           cDataViewSheet:     Class_Initialize:   Start

15:54:07:               cRevealTarget:  Class_Initialize:   START
15:54:07:               cRevealTarget:  Class_Initialize:   END

15:54:09:           cDataViewSheet:     startTimer:     : START
15:54:09:           cDataViewSheet:     startTimer:     init Timer
15:54:09:               cOnTime:    Class_Initialize
15:54:09:               cOnTime:    Let PulseTime:  Inheret PulseTime from host sheet
15:54:09:           cDataViewSheet:     startTimer:     : END

15:54:09:       Roll-Up Select:     Worksheet_Activate:      END:   1.38736216780671

回答by Siddharth Rout

The Combobox_Change() will fire whenever there is a change in the combobox. For example

只要组合框发生变化，Combobox_Change() 就会触发。例如

Option Explicit

Private Sub UserForm_Initialize()
    ComboBox1.AddItem "Bah Blah"
End Sub

Private Sub CommandButton1_Click()
    '~~> If something is selected in the combo then
    '~~> this line will cause ComboBox1_Change to fire
    ComboBox1.Clear
End Sub

Private Sub ComboBox1_Change()
    MsgBox "A"
End Sub

So if you load the userform and select an item ComboBox1_Changewill fire. You then use the commanbutton to clear the combo the ComboBox1_Changewill again fire.

因此，如果您加载用户表单并选择一个项目ComboBox1_Change将触发。然后您使用命令按钮清除组合，ComboBox1_Change将再次触发。

There is one more scenario when the change will again fire. When you changethe combobox from the ComboBox1_Changeevent itself. Here is an example. And I believethis is what is happening in your case.

还有一种情况会再次触发更改。当你change从ComboBox1_Change事件本身的组合框。这是一个例子。我believe这就是你的情况。

Scenario 1

场景一

Private Sub UserForm_Initialize()
    ComboBox1.AddItem "Bah Blah"
End Sub

Private Sub ComboBox1_Change()
    MsgBox "A"
    ComboBox1.Clear
End Sub

Scenario 2

场景二

Private Sub UserForm_Initialize()
    ComboBox1.AddItem "Bah Blah"
    ComboBox1.AddItem "Bah Blah Blah"
End Sub

Private Sub ComboBox1_Change()
    MsgBox "A"
    ComboBox1.ListIndex = 1
End Sub

In the first scenario you can getaway with

在第一种情况下，您可以逃脱

Private Sub UserForm_Initialize()
    ComboBox1.AddItem "Bah Blah"
End Sub

Private Sub ComboBox1_Change()
    If ComboBox1 <> "" Then
        MsgBox "A"
    End If
End Sub

In the 2nd Scenario, you can use something like this

在第二个场景中，你可以使用这样的东西

Dim boolRunOnce As Boolean

Private Sub UserForm_Initialize()
    ComboBox1.AddItem "Bah Blah"
    ComboBox1.AddItem "Bah Blah Blah"
End Sub

Private Sub ComboBox1_Change()
    If boolRunOnce = False Then
        MsgBox "A"
        boolRunOnce = True
        ComboBox1.ListIndex = 1
    Else
        boolRunOnce = False
    End If
End Sub