Excel VBA：如何从代码触发事件？
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/20273724/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
Excel VBA: How to trigger an Event from code?
提问by Shawn V. Wilson
I have a Worksheet_BeforeDoubleClick event that opens a listbox in the target cell. I was asked to provide the same functionality via a button instead of (or in addition to) the double-click.
我有一个 Worksheet_BeforeDoubleClick 事件，可以在目标单元格中打开一个列表框。我被要求通过按钮而不是（或除了）双击来提供相同的功能。
In the button's Click event I entered:
在按钮的 Click 事件中，我输入了：
Call Worksheet_BeforeDoubleClick(Selection,true)
...so the button simply "doubleclicks" the cell. It seems to work well, but before I start using this technique throughout my project, I'd like to know if there are pitfalls I should be aware of.
...所以按钮只需“双击”单元格。它似乎工作得很好，但在我开始在整个项目中使用这种技术之前，我想知道是否存在我应该注意的陷阱。
What are the best practices when calling an event, either from another event or from a standard code module?
从另一个事件或从标准代码模块调用事件时的最佳实践是什么？
采纳答案by Siddharth Rout
I'd like to know if there are pitfalls I should be aware of.
我想知道是否有我应该注意的陷阱。
Yes there is one major pitfall. The Selectionnecessarily might not be a range. See this example
是的，有一个主要的陷阱。该Selection必然可能不是一个范围。看这个例子
Insert a button
Insert a blank chart
Insert an image in the chart. Let the image be highlighted
插入按钮
插入空白图表
在图表中插入图像。让图像突出显示
Let's say we have this code
假设我们有这个代码
Private Sub CommandButton1_Click()
Call Worksheet_BeforeDoubleClick(Selection, True)
End Sub
Private Sub Worksheet_BeforeDoubleClick(ByVal Target As Range, Cancel As Boolean)
MsgBox Target.Address
End Sub
Now press the button and you will get an error.
现在按下按钮，你会得到一个错误。
The worksheet events WILLfire when they NEEDtoo... They won't when the selection is not appropriate.
The worksheet events WILLfire when they NEEDtoo... They won't when the selection is not appropriate.
Having said that you CANmake your command button code work like this
说了这么多，你CAN让你的命令按钮的代码工作像这样
Private Sub CommandButton1_Click()
'~~> Check if what the user selected is a valid range
If TypeName(Selection) = "Range" Then
Call Worksheet_BeforeDoubleClick(Selection, True)
Else
MsgBox "Not a Valid Range"
End If
End Sub
Private Sub Worksheet_BeforeDoubleClick(ByVal Target As Range, Cancel As Boolean)
MsgBox Target.Address
End Sub
But then where all and what all CHECKSwill you place :) The best way is to place it in a Sub as @Sam suggested and then call it either from Button/Worksheet_BeforeDoubleClick.
但后来所有，什么都支票，你将会把:)最好的办法是把它放在一个子作为@Sam建议，然后从任何调用它Button/Worksheet_BeforeDoubleClick。
If you still want to call it from a button then ensure that all relevant checks are in place including a proper error handler.
如果您仍然想从按钮调用它，请确保所有相关检查都已到位，包括正确的错误处理程序。
