VBA - Shell a VBScript if you want multi-tasking

Whilst VBA cannot multi-thread, you can always shell to a script. You have many options for choice of script language. If you have already written some logic in VBA then you can easily convert to VBScript. The following script below will download a binary file using an XHR.

I wrote the code in VBA to begin with and debugged it until I was happy. Then I converted to VBScript. The hallmarks of a VBScript are

• the absence of type clauses in Dim statements
• one has to use WScript.CreateObject instead of VBA.CreateObject or New
• no support for constants, so one has to use the literals themselves, typically we put the name in a comment above

The script also has extra proxy logic for that is the use case I am working on but you can take that out. For the time being it serves as an example of how constants have to be passed as literals.

So save the file below as wget.vbs

Dim xhr
set xhr = WScript.CreateObject("WinHttp.WinHttpRequest.5.1")
Call xhr.SetClientCertificate("LOCAL_MACHINE\Personal\My Certificate")

''WinHttp.WinHttpRequestOption_SslErrorIgnoreFlags=4
''WinHttp.WinHttpRequestSslErrorFlags.SslErrorFlag_Ignore_All=13056
xhr.Option(4) = 13056

'Const HTTPREQUEST_PROXYSETTING_PROXY As Long = 2
xhr.setProxy 2, "127.0.0.1:8888", ""

call xhr.Open("GET", WScript.Arguments(0), False)
xhr.setRequestHeader "Referrer-Policy", "no-referrer"
xhr.setRequestHeader "User-Agent", "Mozilla/5.0 (Windows NT 10.0; Win64; x64) AppleWebKit/537.36 (KHTML, like Gecko) Chrome/74.0.3729.169 Safari/537.36"
call xhr.Send()



Dim strm
Set strm = WScript.CreateObject("ADODB.Stream")
strm.Type = 1
Call strm.Open()
Call strm.Write(xhr.ResponseBody)

Call strm.SaveToFile(WScript.Arguments(1))
Call strm.Close()

In the same directory save a new workbook then add the following code to call from VBA. Supply web url as the first argument and destination filename (e.g. somewhere in Temp) and the second argument. You can in the script above how it acquires the arguments using WScript.Arguments(0|1)

Sub TestShell()
    
    Dim sShell As String
    sShell = "cscript " & ThisWorkbook.Path & "\wget.vbs https://duckduckgo.com/ " & Environ("temp") & "\wget_example.txt"
    Debug.Print "Shelling" & vbNewLine & sShell
    VBA.Shell sShell
    
End Sub

The only downside to this is that one cannot return variables easily. Often a script will write to a log where the caller can inspect to determine success/error.