从 VBA 中的另一个子程序中抑制 MsgBox

Suppress MsgBox from another Subroutine in VBA

提问by Eric

I have a VBA sub that makes a call to a sub that was written by someone else. occasionally, the other sub opens a MsgBox with an OK button. The other sub takes a long time to run, and I am calling it hundreds of times, so I want to be able to run this overnight. Unfortunately, I can't figure out a way to automatically click OK on the MsgBox.

我有一个 VBA 子程序，它可以调用由其他人编写的子程序。偶尔，另一个 sub 会打开一个带有 OK 按钮的 MsgBox。另一个 sub 需要很长时间才能运行，我调用了数百次，所以我希望能够在一夜之间运行它。不幸的是，我想不出一种在 MsgBox 上自动单击“确定”的方法。

I have tried

我试过了

Application.DisplayAlerts = False

but this doesn't suppress message boxes.

但这不会抑制消息框。

Is there any way to do this?

有没有办法做到这一点？

Thanks

谢谢

采纳答案by Bart Van Eyndhoven

One way to do this is slightly modifying the code of the original sub. You will need to have the necessary permissions tough...

一种方法是稍微修改原始子的代码。您将需要获得必要的权限...

• Modify the header of the original sub by throwing in an extra optinal parameter at the end setting the default value to True. This will result in something like Sub OriginalSubName(original set of parameters, Optional ShowMessages = True)

• At the point where the msgbox is called, modify the code this way:

  If showMessages = True Then 'The = True part is important here - see below.
  showMessages is a Variant type
      'The original statement that calls the msgBox
  End If
  

• Leave the rest of the code of the original sub unchanged

• Modify the line where you call the original sub by throwing in Falseas an extra parameter. This results in OriginalSubNameyour set of parameters, False. This way you don't suppress the dialog box by default, but you do when you use it in your sub.

• 通过在末尾添加一个额外的可选参数来修改原始 sub 的标题，将默认值设置为True. 这将导致类似于Sub OriginalSubName(原始参数集, Optional ShowMessages = True)

• 在调用 msgbox 的地方，这样修改代码：

  If showMessages = True Then 'The = True part is important here - see below.
  showMessages is a Variant type
      'The original statement that calls the msgBox
  End If
  

• 保留原始 sub 的其余代码不变

• 通过False作为额外参数加入来修改调用原始 sub 的行。这将导致OriginalSubName您的参数集, False。这样你就不会默认抑制对话框，但是当你在你的子中使用它时你会这样做。

Wonder why I use an optional Variant type parameter?

想知道为什么我使用可选的 Variant 类型参数？

• The optional part: this prevents other existing subs from crashing when they call the modified sub
• The Variant type part: optional parameters are always Variant type. That's also why you need to use If showMessages = True Theninstead of just If showMessages Then.

• 可选部分：这可以防止其他现有子程序在调用修改后的子程序时崩溃
• Variant 类型部分：可选参数始终为 Variant 类型。这也是为什么您需要使用If showMessages = True Then而不仅仅是If showMessages Then.