excel - 如何加速 VBA for Mac 中的 shell 函数

Question

我一直在使用这个问题的最佳答案中的函数在 VBA 中运行 shell 脚本(execShell 函数)，它使用 libc.dylib:

VBA Shell function in Office 2011 for Mac

函数内有一个 while 循环，它的运行速度比 popen 慢得多。我猜这部分在 shell 中执行并且 popen 正在初始化，但我真的不明白它在做什么。

Time taken to run popen:
0.01171875 seconds
Time taken to run while loop:
0.8710938 seconds

我想知道如果执行时间很长，是否可以中止或超时该函数？有没有一种方法可以用来处理多个函数调用？我已将超时添加到 shell 脚本中的 curl 命令。 "curl --connect-timeout 5 --max-time 10 --retry 2 --retry-delay 0 --retry-max-time 40 "，并且还在每次调用前添加了0.1秒的延迟.如果它运行顺利，那将不是问题，但有时它似乎完全锁定。如果 curl 请求有很长的延迟，我宁愿中止它并继续执行脚本。也许在连续几次失败后完全终止脚本。我认为函数调用将被线性处理，但我认为它可能会同时处理 shell 脚本，这可能会导致问题。理想情况下，我希望获得有关脚本状态的反馈，我尝试使用状态栏来实现它，但由于 2011 版 Excel 的限制，这不起作用。在运行开始时，我可以看到工作表正在更新并且一切运行顺利，但稍后在脚本中 excel 卡住直到完成。

另一种解决方案是使用 MacScript 命令，但此 shell 脚本包含带有“\”和多个引号的正则表达式，并且很难转换为 applescript，因此我更喜欢使用此方法，因为它已经可用。不能使用 system() 命令，因为 VBA 脚本需要输出。

    Private Declare Function popen Lib "libc.dylib" (ByVal command As String, ByVal mode As String) As Long
    Private Declare Function pclose Lib "libc.dylib" (ByVal file As Long) As Long
    Private Declare Function fread Lib "libc.dylib" (ByVal outStr As String, ByVal size As Long, ByVal items As Long, ByVal stream As Long) As Long
    Private Declare Function feof Lib "libc.dylib" (ByVal file As Long) As Long

    Function execShell(command As String, Optional ByRef exitCode As Long) As String
        Dim file As Long
        file = popen(command, "r")

        If file = 0 Then
            Exit Function
        End If

        While feof(file) = 0
            Dim chunk As String
            Dim read As Long
            chunk = Space(50)
            read = fread(chunk, 1, Len(chunk) - 1, file)
            If read > 0 Then
                chunk = Left$(chunk, read)
                execShell = execShell & chunk
            End If
        Wend

        exitCode = pclose(file)
    End Function

Answer 1

popen 运行速度更快，因为它是用 C/C++ 编写的，并且是二进制可执行文件。它在操作系统级别运行，而不是在 VBA 虚拟机 (VBA VM) 中运行。您的 While 循环和 VBA 中的所有其他内容都是在 VBA VM 中运行的脚本语言，它必须将您的脚本转换为 p 代码，然后由 VBA VM 执行。 “Visual Basic for Applications”如果您有兴趣，维基百科上的文章提供了更详细的解释。

VBA 代码总是比 Excel 对象或外部函数(如 popen)提供的任何方法都慢。例如，在使用值循环填充范围时，使用范围对象自动填充方法总是比 VBA 更快。

向您的代码添加“超时” 的最佳方法是添加 VBA 方法 DoEvents在你的 while 循环中。然后

我会把它放在 End If 和 Wend 之间，像这样:

        End If
    VBA.DoEvents
Wend

这将导致 While 循环允许 Excel 和 Windows 处理事件。除了 VBA Shell() 函数外，VBA 代码是串行执行的。如果没有 VBA.DoEvents，您的代码将继续执行直到完成或出错。这Stack Overflow question thread提供了很好的解释。

可以加快代码速度的一件事是使用此命令关闭屏幕更新 Application.ScreenUpdating = False。请记住在子结尾使用 Application.ScreenUpdating = True 将其重新打开。我会像这样将它放在 While 循环中:

Application.ScreenUpdating = False
While feof(file) = 0 
.
.
.
Wend
Application.ScreenUpdating = True

此外，如果您真的很喜欢冒险，您可以编写一个事件处理程序。 Chip Pearson 有一篇很棒的文章 “Events and Event Procedures in VBA” .