VBA（Excel）中的Side-Step Application.MsgBox [英] Side-Step Application.MsgBox in VBA (Excel)

问题描述

为了摆脱评论的回应，这里是我的情况：

In order to head off a storm of "comment it out" replies, here is my situation:

我有一个进程通常运行1次迭代1次迭代。用户手动点击调用宏的按钮，完成后会弹出一个消息框，该消息框报告宏运行的总时间长度。诊断问题非常方便。这段代码被锁定，我无法修改它。

I have a process is normally run 1 iteration by 1 iteration. A user manually hits a button that calls a macro which, upon completion, pops up a message box that reports the total time length the macro ran for. It's pretty handy for diagnosing issues. This code is locked down and I cannot modify it.

我正在尝试这样做。因为主电子表格和工作簿中的代码被锁定，所以我有一个单独的工作簿在同一个excel实例中打开，并使用一个操作锁定工作簿的宏。而不是1比1，我有一套300我正试图通过。现在我必须保留这个东西，并打开空格以通过MsgBox。有人知道有什么办法阻止我监视这件事吗？禁用弹出窗口或某种方式使其成为非模态。可能是一个让鼠标点击的技巧？

I am trying to do this at scale. Because the code in the main spreadsheet and workbook are locked, I have a separate workbook open in the same instance of excel with a macro that operates the locked down workbook. Rather than 1 by 1, I've got a set of 300 I'm trying to run through. Right now I have to babysit the thing and hit space to get past the MsgBox. Does anyone know of any tricks to prevent me having to monitor the thing? Either disabling the pop-ups or some way to make them non-modal. Maybe a trick to make the mouse click?

推荐答案

您知道解决问题的最佳方法是纠正代码在这种情况下，您可能会弹出弹出式窗口。

You're right in knowing that the best way to fix the issue is to correct the code. In which case you would probably make the pop-ups toggle-able.

然而，我为你写了这个可以用作潜在的工作。它使用VBScript模拟模拟多线程，以便您可以向模态 Msgbox 发送密钥。假设你可以通过代码做你想做的事情，只需在导致 Msgbox 的操作之前调用 SendDelayedKeys 。您可能需要根据您的情况修改延迟，因为100毫秒可能不够。要更改延迟，只需调用如下： SendDelayedKeys 500 500毫秒。

However, I wrote this for you which could be used as a potential work around. It utilizes VBScript to "sort-of" simulate multithreading so that you can send a key to the modal Msgbox. Assuming you can do what you want to do via code, simply call SendDelayedKeys before the action that will cause a Msgbox. You may have to tinker with the Delay based upon your circumstances as 100 milliseconds may not be enough. To change the Delay, just call like this: SendDelayedKeys 500 for 500 milliseconds.

Sub SendDelayedKeys(Optional Delay As Long = 100, Optional keys As String = """ """)
    Dim oFSO As Object
    Dim oFile As Object
    Dim sFile As String
    sFile = "C:\SendKeys.vbs" 'Make this a valid path to which you can write.
    'Check for the .vbs file.
    If Not Len(Dir$(sFile)) Then
        'Create the vbs file.
        Set oFSO = CreateObject("Scripting.FileSystemObject")
        Set oFile = oFSO.CreateTextFile(sFile)
        oFile.WriteLine "Set WshShell = WScript.CreateObject(""WScript.Shell"")"
        oFile.WriteLine "WScript.Sleep CLng(WScript.Arguments(0))"
        oFile.WriteLine "WshShell.SendKeys WScript.Arguments(1)"
        oFile.Close
    End If
    Shell "wscript C:\SendKeys.vbs " & Delay & " " & keys
End Sub
Sub ProofOfConcept()
    'Using default parameters which sends a space after 100 milliseconds
    SendDelayedKeys
    MsgBox "I disappear on my own!"
End Sub

一个警告词：使用 SendKeys 是一个脆弱的解决方案，尽可能避免。但是，当您的选项有限，您需要避免手动进程时，有时这是您唯一的选择。

A word of warning: Any solution that utilizes SendKeys is a fragile solution and should be avoided when possible. However, when your options are limited and you need to avoid a manual process, sometimes it's your only option.

由于SiddhartRout正确地指出可以使用API​​调用：这里是与 C＃代码的链接这将每秒关闭您的msgbox。

Since SiddhartRout rightly pointed out that this could be solved using API calls: here's a link with C# code that would close your msgbox every second.

这篇关于VBA（Excel）中的Side-Step Application.MsgBox的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！