How to speed up shell function in VBA for Mac

Question

I've been using the function from the top answer to this question to run shell scripts within VBA (the execShell function), which uses libc.dylib:

VBA Shell function in Office 2011 for Mac

Within the function is a while loop, and it runs much slower than the popen. I'm guessing that this part is executing within the shell and popen is initialising, but I don't really understand what it's doing.

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

I'm wondering if it's possible to abort or timeout the function if it's taking a long time to execute? Is there a method that could be used to handle multiple function calls? I've added timeout to the curl command in the shell script. "curl --connect-timeout 5 --max-time 10 --retry 2 --retry-delay 0 --retry-max-time 40 ", and also added 0.1 second delay before each call. If it ran smoothly it wouldn't be a problem, but at times it can seem to lock up completely. If there's a lengthy delay with a curl request, I'd prefer to abort it and continue with the script. Maybe after a few consecutive failures terminate the script completely. I thought that the function calls would be processed linearly, but I'm thinking that maybe it could be concurrently processing the shell scripts, and this could be causing the problem. Ideally I'd like to have feedback on the status of the script, which I tried to implement with a status bar, but this didn't work because of a limitation with 2011 version of Excel. At the beginning of the run, I can see the sheets being updated and everything running smoothly, but later in the script excel freezes until it completes.

The other solution would be to use the MacScript command, but this shell script contains regex with "\" and multiple quotes, and it's very difficult to translate to applescript, so my preference is to use this method as it already works. Using the system() command isn't an option as the output is needed for the VBA script.

    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 runs faster because it is written in C/C++ and is binary executable. It runs at the operating system level and not in the VBA virtual machine (VBA VM). Your While loop and everything else in VBA is a scripting language that is run in VBA VM, which has to convert your script into a p-code that is then executed by the VBA VM. The “Visual Basic for Applications” article on Wikipedia provides a more detailed explanation if you’re interested.

VBA code will always be slower than any method provided by an Excel object or an external function like popen. For example, using the Range Objects AutoFill Method will always be faster than a VBA while loop filling a range with values.

The best way to add a “timeout” to your code is by adding the VBA method DoEvents inside your while loop. Then

I’d place it in between End If and Wend something like this:

        End If
    VBA.DoEvents
Wend

This will cause the While loop to allow Excel and Windows to process events. With the exception of VBA Shell() function, VBA code is serially executed. Without the VBA.DoEvents, your code will continue to execute until completion or error. This Stack Overflow question thread provides a great explanation.

One thing that could speed up your code is to turn off screen updating using this command Application.ScreenUpdating = False. Remember to turn it back on at the end of the sub with Application.ScreenUpdating = True. I'd put it around the While loop like this:

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

Also if you’re feeling really adventurous, you could write an event handler. Chip Pearson has a great article “Events and Event Procedures in VBA”.