Category Archives: VBscript

hta example

Like VBA? You will love HTA! (HTA example using VBS)

1 Star2 Stars3 Stars4 Stars5 Stars (1 votes, average: 5.00 out of 5)
Loading...

Comfortable with VBA? Your clients/users need VBA solutions but want them to be lightweight GUI, not cumbersome Excel Workbooks with Macro restrictions? Well why not try HTML Application (HTA)? What’s HTA? It’s a simple HTML application embedded with Visual Basic script or Javascript, or even both if needed! Don’t see the opportunities yet? Imagine a cool Web GUI (feel free to leverage Jquery or other libraries) embedded with a cool feature you already have programmed in Visual Basic. No need to install or open Excel! What is more HTA applications are simply text files with a HTA extension, hence, anyone can easily open and edit them in a simple text editor. Neat right? Interested – read on.

From Zero to VBA Hero

Today I wanted to showcase a simple HTA example. There are tons of VBA developers out there currently. These are often persons who started their developer journeys as analysts or simply Excel powerusers. At some point in time Excel native features are not sufficient to tackle some complex tasks and drive users to learn VBA. It is quite an easy step often as, it is important to remember that Excel has 2 important advantages over other programming languages:

  1. It is available almost on any Windows workstation (as most have MS Office installed)
  2. It features a built in development environment.

Without having to install any additional software, any analyst or other corporate person can use VBA to achieve automation of certain tasks. In a series of posts I will want to encourage some VBA devs to explore some alternatives and new programming possibilities.

I know VBA and can develop anything in MS Office! Why explore other languages, approaches?

From my experience many VBA developers refrain from learning other programming languages, noticing that basically they build roughly any solution or automation resorting only to VBA. This is a common trap due to which many MS Office-based monster-complex solutions are developed which should not have seen the light of day. I have seen many monsters… from complex BI models (calculations running for hours) to … complex databases shared by multiple users at the same time (oh the horror!). Although all these solutions were quite amazing they pushed the limits of VBA more than they should. I won’t ponder to much on the reasons for these solutions to come to existance. Rather I would like to show some alternatives.

VBA developers start exploring new possibilities with HTA!

In today’s post I wanted to encourage VBA dev’s to make their first step out of their VBA caves and to leave Excel or any other MS Office environment they use on their daily basis. I know this comes as a shock for some so I promise we will stick with Visual Basic for now.

So now let us say we want to build a regular desktop app not one hosted with an Excel or Word application. Assuming we do not want to move to more complex solutions like .NET or Java, VBA devs are encouraged to explore HTA.

What is HTA?

HTA is the abbreviation for Html Application. See the Wiki page for more details. In short, however, it is simply a HTML file with a “.hta” file extension that can runs on Internet Explorer, embedded with scripts. Well now, some might say, is it simply a HTML file then? Well, hta file have elevated security privileges allowing them to run like regular desktop apps!

HTA can run like desktop applications being embedded in Internet Explorer instead of e.g. Excel or Word in case of VBA. Additionally as these are HTML files you can build a rich user interface and a much more pleasant user experience. But let’s jump right in!

HTA Example: Hello World!

Let’s start by creating an hta file. Create a text file and change the extension to .hta

HTA file icon
HTA file icon

Next let’s insert some Hellow World code in there:

<html>
<head>
<HTA:APPLICATION ID="HelloExample" BORDER="thin" BORDERSTYLE="complex" maximizeButton="yes" minimizeButton="yes" />
<script type="text/vbscript">
  Sub Hello()
	MsgBox "Hello"
  End Sub
</script>
<title>Blog.tkacprow.pl: HTA Hello World!</title>
</head>
<body>
<table width="100%">
	<tr>
		<td>
			<!--Your content here!-->
			<H1>Hello World!</H1>
			<input type="Button" onClick="Hello()" value="Hello" />
		</td>
	</tr>
</table>
</body>
</html>

Now double click and that’s it!

HTA Example: Hello World!
HTA Example: Hello World!

Simple huh? Now to analyze the code…
If you are familiar with HTML you will notice that the HTA:APPLICATION clearing does not belong in a regular HTML file. This tag defines the HTA application and includes params that let you specify how the application window will look like.
Here you will find more on the HTA application params: HTA Application Params

Notice also the script tag:

<script type="text/vbscript">
  Sub Hello()
	MsgBox "Hello"
  End Sub
</script>

This procedure is linked to the HTML button so when clicked you will see a simple Hello message.

Accessing Excel files and resources

As HTA files have elevated rights I like to think of them as Vbscript files extended with a user interface. Bascially all Vbscript and Javascript commands are valid in HTA. Let us see a simple example that exercises some of these rights – like accessing Excel files.

I added a simple Excel file to the directory:

Excel and HTA
Excel and HTA

This Excel file contains a single data cell:

Now let us add a piece of code to the HTA file to access the data cell. Let’s replace the previous script tag with this:

<script type="text/vbscript">
  Sub Hello()
    Set objShell = CreateObject("WScript.Shell") 
    myCur = objShell.CurrentDirectory 
	dim excelApp, excelWb, excelVal
	set excelApp = CreateObject("Excel.Application")
	set excelWb = excelApp.Workbooks.Open(myCur & "Test.xlsx")
	excelVal = excelWb.Worksheets(1).Range("A1").Value
	excelWb.Close
	excelApp.Quit
	MsgBox excelVal
  End Sub
  Sub ExitForm()
	Close
  End Sub
</script>

Now when we open the HTA and hit the Hello button we will see the contents of the data cell.

Modified HTA accessing Excel file
Modified HTA accessing Excel file

All this is just a drop in the ocean of the possibilities of using HTA.

Summary

HTA extends new possibilities to VBA developers and provides them we a simple means of making desktop applications in Windows without embedding them in Excel nor other MS Office applications. In my opinion HTA encourages VBA developers to also explore new programming languages and scripts as with HTA you need to learn HTML and probably some Javascript. Over time you will probably learn CSS and start using some cool Javascript libaries like jQuery, AngularJS. HTA enables you to create rich desktop applications while also giving you the safe-haven of being able to keep using VBscript (almost identical to VBA).

Expect more on this topic soon :). Let me know what you think!

vba multithreading tool

Excel VBA Multithreading Tool

1 Star2 Stars3 Stars4 Stars5 Stars (10 votes, average: 4.40 out of 5)
Loading...

The VBA Multithreading Tool, from AnalystCave.com, is a free and open source tool (VBA) for enhancing your VBA Project with Mulithreading capabilities. It was designed to make VBA multithreading quick an easy and to minimize the complexity of your VBA code. VBA is natively singlethreaded hence will utilize only a single logical processor core. Why waist your processors underutilized capacity and significantly speed up VBA code execution time?

vba multithreading tool
Mulithreading VBA: Mulithread Excel running in parallel

Until now I have posted quite a few posts on the topic of multithreading in VBA. Let’s admit – there is no such thing as “native” VBA Multithreading. This is the closest I got to “native” VBA Multithreading. However, I considered one of other of my similar posts on this topic as a good enough alternative to achieving multithreading to VBA – by using VBscript and VBA worker threads.

Having compared at least 3 methods for achieving multithreading in VBA (EXCEL: Multithreading VBA – VBA vs. VBscript vs. C#.NET) I came to the conclusion that the VBA worker threads can be a decent alternative to C#.NET COM / DLLs. Having said that, I decided that it might make sense to make it easier to leverage these multithreading capabilities in VBA. That’s why I created the VBA Multithreading Tool based on the concepts of the C# Parallel Class.

Example 1: VBA Multithreading For loops

For loops are very common cases for multithreading – as, usually, the result of each iteration is independent to the remaining. I would like to show you how you can use the VBA Multithreading Tool to run a loop in separate parallel threads. Let’s say we have a simple procedure as shown below. This procedure simply divides two numbers in a loop. Each iteration is independent of the other and there is no order in which we need to calculate each iteration. Notice the arguments of this procedure, as they will be required for multithreading later on.

Sub RunForVBA(workbookName As String, seqFrom As Long, seqTo As Long)
    For i = seqFrom To seqTo
        x = seqFrom / seqTo
    Next i
End Sub

We can now execute this code like this in a standard single threaded VBA manner:

Sub RunForVBASingleThread()
    RunForVBA ActiveWorkbook.Name, 1, 1000
End Sub

Now let’s use the VBA Multithreading VBA Tool to do the same thing within 4 separate threads.

Sub RunForVBAMultiThread()
    'Declare the Parallel class object
    Dim parallelClass As Parallel
    'Initialize the Parallel class object
    Set parallelClass = New Parallel
    'Declare how many threads are to be created
    parallelClass.SetThreads 4
    'Call 'Run the for loop in 4 threads synchronously
    Call parallelClass.ParallelFor("RunForVBA", 1, 1000)
End Sub

4 lines of code to make this procedure multithreaded :). Easy right? As I mentioned earlier the ‘RunForVBA’ procedure has to have the 3 arguments for the ‘Parallel’ class to be able to run it in separate threads.

How does Multithreading For loops work?

Multithreading For loops
Multithreading For loops

The ParallelFor procedure creates as many separate Excel threads as required and blocks the Master Excel Workbook’s execution until all threads have finished and closed. Each thread is in fact a copy of the Master Excel Workbook therefore all VBA function or procedures which you will be multithreading will have access to exactly the same data as the Master Excel Workbook.

VBA Multithreading Tool: For loop multithreading procedures

Considering For loops the following funtions and procedures are available:

  • SetThreads(threads As Long) – set the amount of parallel threads to be used
  • GetThreads() – get the amount of parallel threads to be used
  • ParallelFor(macroName As String, seqFrom As Long, seqTo As Long) – execute the macro macroName over a sequence of seqFrom to seqTo. The macro macroName should have the following arguments (workbookName As String, seqFrom As Long, seqTo As Long)

Example 2: Asynchronously invoking VBA procedures

VBA synchronously blocks the execution of any called functions or procedures (except for COM calls – but they are not native VBA). This is because it runs in a single thread. By leveraging the VBA Multithreading Tool we can however asynchronously call functions and procedures to run them in the background while we proceed with the execution of other macro code. When we are ready to wait for the async background thread to finish with can call AsyncThreadJoin to Join macro execution on this thread. Alternatively we can also call as many asynchronous threads as we like and not bother waiting for them.

To exemplify this functionality let’s again consider a similar procedure as before. This time the arguments are not important – although it is usually recommended to pass the master Excel Workbook name to an async thread if we want to somehow save the outputs of the threads to the master Excel Workbook.

Sub RunAsyncVBA(workbookName As String, seqFrom As Long, seqTo As Long)
    For i = seqFrom To seqTo
        x = seqFrom / seqTo
    Next i
End Sub

But now instead of running it in parallel threads let’s invoke it asynchronously and continue executing the macro. We will wait for the procedure to end by the end of the procedure.

Sub RunForVBAAndWait()
    Dim parallelClass As Parallel
    Set parallelClass  = New Parallel
    'The line below will not block macro execution
    Call parallelClass.ParallelAsyncInvoke("RunAsyncVBA", 1, 1000)
    'Do other operations here
    '....
    if parallelClass.IsAsyncRunning then ... 'Check if Async thread is still running
    '....
    'Now let's wait until the thread has finished
    parallelClass.AsyncThreadJoin
End Sub

How does asynchronous VBA procedure invoking work?

Async procedure invoke
Async procedure invoke

The ParallelAsyncInvoke procedure is non-blocking therefore allows you to execute other macros within the Master Excel Workbook and choose whether you want to, as some point, wait for it to finish execution or not.

VBA Multithreading Tool: Asynchronous VBA invoking procedures

The following funtions and procedures are available for async invoking:

  • ParallelAsyncInvoke(macroName As String, Optional arg1, … [up to arg 6]) – execute asynchronously the macro macroName with the following arguments arg1, arg2 etc.
  • AsyncThreadJoin – wait for the VBA worker thread to finish

Example 3: Saving data to the master Excel Workbook

All examples above assumed that the code was executed in separate threads without any means of the threads’ outputs being saved to the master Excel Workbook. You have probably noticed that this is actually crucial as otherwise you will not benefit from calculations happening in the void without being able to access their results. I would like to show you how easily you can save your results from the separate threads to the master Excel Workbook while you are keeping the workbook open and even making changes to it!

Again let’s consider the similar procedure as shown above. This time, however, notice the changes:

Sub RunForVBA(workbookName As String, seqFrom As Long, seqTo As Long)
    For i = seqFrom To seqTo
        x = seqFrom / seqTo
    Next i
    'Save the last division operation 'x' to cell 'A1' on worksheet 'Sheet1' in the master Excel Workbook.
    ParallelMethods.SetRangeToMaster(workbookName, "Sheet1","A1", x)
    'Do the same but this time the procedure will reflect the changes made to the thread workbook
    'automatically to the master Excel Workbook based on the provided Range
    Dim tempRange as Range
    Set tempRange = Range("A1")
    tempRange.Value = x
    ParallelMethods.SaveRangeToMaster(workbookName,  tempRange)
End Sub

Again quite simple. The threads will save their outputs immediately to the Excel master Workbook – as long as it is open and in use.

Summary

I hope the VBA Multithreading Tool will become a useful for you. The Tool makes multithreading almost just as easy as in any other languages that allow for multithreading natively.

Download

You can download the Tool (complete source code) here:


If you prefer to download this as an add-in (the warning won’t appear) please follow this link: here.

Next steps

Want to learn how to add multithreading using C#.NET?
EXCEL: Multithreading VBA using C#.NET
Want to learn how to add multithreading using VBscript?
EXCEL: Multithreading VBA using VBscript
Want to learn how to add multithreading using VBA via VBscript?
EXCEL: Multithreading using VBA via VBscript
Want to see how all multithreading approaches compare?
EXCEL: Multithreading – VBA vs. VBscript vs. C#.NET

invoke vba

Invoke VBA functions and procedures from strings

1 Star2 Stars3 Stars4 Stars5 Stars (2 votes, average: 3.50 out of 5)
Loading...

invoke vba
First let me start with explaining what does it mean to invoke VBA functions and procedures from strings. Then I will follow with an example. Lastly I will conclude this topic showing how you can add arguments.

Invoke VBA functions and procedures by string name

Normally you would invoke a VBA function or procedure like this:

Sub ShowMessage(msg as String)
   MsgBox msg
End Sub

Sub Main
   Call ShowMessage("Hello!")
End Sub

In the above example you are invoking the ShowMessage procedure directly from VBA. This is the usual and efficient approach. Let’s now say that you have 2 alternative VBA procedures you would like to call depending on a certain argument you receive like this:

Sub ShowMessageAlert
   MsgBox "Alert: Watch out!"
End Sub

Sub ShowMessageWarning
   MsgBox "Warning: Look around your back."
End Sub
You may want to you invoke both methods in different circumstances e.g

Sub Main(msgType as String)
   if msgType = "Alert" then ShowMessageAlert
   if msgType = "Warning" then ShowMessageWarning
End Sub

I know the examples above are too simple, but what if you have now much more of these similar methods which differ only in name e.g. suffix, prefix? Well you can invoke them by their name (using a string with the proc., func. name) like this:

Sub Main(msgType as String)
   Application.Run "ShowMessage" & msgType
End Sub

Neater right? Alternatively you could have achieved the same using multiple if statements or a select statement making the code much harder to read.

Invoke VBA functions and procedures with arguments

See the example below for invoking VBA functions and procedures with arguments:

Sub ShowMessageAlert(msg as String)
   MsgBox "Alert: " & msg
End Sub

Sub ShowMessageWarning(msg as String)
   MsgBox "Warning: " & msg
End Sub

Sub Main(msgType as String, msg as String)
   Application.Run "ShowMessage" & msgType , msg
End Sub

Invoke VBA functions and procedures from VBscript

Now this is where invoking VBA really comes in handy. Imagine you want to run an Excel macro without having to open the Excel workbook and run the macro manually. It might be more useful to create therefore a VBscript which would do this for you (file extension “vbs”). The code below will open (invisible in the background) the “Book1.xlsm” Excel workbook which is located in the same working directory as the VBscript file and execute macro “MyMacro”. Then it will close the workbook and quit.

dim xl, xlBook, path
path = CreateObject("Scripting.FileSystemObject").GetAbsolutePathName(".")
Set xl = CreateObject("Excel.application")
Set xlBook = xl.Workbooks.Open(path & "Book1.xlsm", 0, True)      
xl.Application.Visible = False
xl.DisplayAlerts = False     
xl.Application.run "Book1.xlsm!Module.MyMacro"
xl.ActiveWindow.close
Set xlBook = Nothing
xl.Quit 
Set xl = Nothing

Hope you find invoking VBA from strings useful!

Native VBA Multithreading

Multithreading VBA – VBA worker threads via VBscript

1 Star2 Stars3 Stars4 Stars5 Stars (2 votes, average: 4.50 out of 5)
Loading...

Yet again I am approaching the subject of multithreading in VBA. This time I am coming with an interesting alternative to multithreading in VBA compared to the ones I presented previously. So again let me start by repeating that multithreading is possible in VBA but not in “native” VBA. What do I mean by “native”? I mean that VBA as a programming language does not natively support creating new threads, joining threads etc. However, you can achieve multithreading by using external libraries, modules or scripts.

This post is the result of my previous post EXCEL: Multithreading in VBA using VBscript. In that article I focus on an idea of using the Windows Shell to call multiple VBscript worker threads. Although this allowed me to quite easily achieve multithreading VBA, on the the other hand VBscript turned out to be much slower than VBA therefore even multithreaded algorithms would work slower than a single threaded VBA algorithm. As a result of these findings I decided to leverage VBA worker threads (instead of VBscript threads) via Windows Shell (and VBscript). I know this is fairly complicated so let me explain and continue with a simple example.

How multithreading VBA with VBA worker threads works

Multithreading using VBA workers via VBscript
Multithreading using VBA workers via VBscript

As shown above the concept is to replicate as many Excel workbooks as we need threads and then run the macros concurrently via VBscript.

Mulithreading VBA example

This is the code. 60-something lines of code – so not too bad :):

Sub VBAMultithread(maxThreads As Long)
    Dim thread As Long, threads As Long
    For threads = 1 To maxThreads
        startTime = Timer
        For thread = 1 To threads
            CreateVBAThread thread, threads, divTabSize
        Next thread
        CheckIfFinishedVBA threads
    Next threads
End Sub

Sub CreateVBAThread(threadNr As Long, maxThreads As Long, divTabSize As Long)
    Dim s As String, sFileName As String, wsh As Object, threadFileName As String
    '---Save a copy of the Excel workbook---
    threadFileName = ActiveWorkbook.Path & "Thread_" & maxThreads & "_" & threadNr & ".xls"
    Call ActiveWorkbook.SaveCopyAs(threadFileName)
    '---Save the VBscript---
    s = "Set objExcel = CreateObject(""Excel.Application"")" & vbCrLf
    s = s & "Set objWorkbook = objExcel.Workbooks.Open(""" & threadFileName & """)" & vbCrLf
    s = s & "objExcel.Application.Visible = False" & vbCrLf
    s = s & "objExcel.Application.Run ""Thread_" & maxThreads & "_" & threadNr & ".xls!RunVBAMultithread"" ," & threadNr & "," & maxThreads & "," & divTabSize & ",""" & ActiveWorkbook.Name & """" & vbCrLf
    s = s & "objExcel.ActiveWorkbook.Close" & vbCrLf
    s = s & "objExcel.Application.Quit"
    '---Save the VBscript file---
    sFileName = ActiveWorkbook.Path & "Thread_" & threadNr & ".vbs"
    Open sFileName For Output As #1
    Print #1, s
    Close #1
    '---Execute the VBscript file asynchronously---
    Set wsh = VBA.CreateObject("WScript.Shell")
    wsh.Run """" & sFileName & """"
    Set wsh = Nothing
End Sub

Public Sub RunVBAMultithread(threadNr As Long, maxThreads As Long, divTabSize As Long, workbookName As String)
    'A simple division algorithm (a multiplication table would quickly cause overflow)
    Dim i As Long, j As Long, x As Double, startTimeS As Date, endTimeS As Date
    startTimeS = Timer
    '---Compute partition of the division table---
    For i = CInt(CDbl(divTabSize) * (threadNr - 1) / maxThreads + 1) To CInt(CDbl(divTabSize) * threadNr / maxThreads)
       For j = 1 To divTabSize
          x = CDbl(i) / j
       Next
    Next
    endTimeS = Timer
    elapsed = "" & Format(endTimeS - startTimeS, "0.00")
    Dim oXL
    Set oXL = GetObject(, "Excel.Application")
    oXL.Workbooks(workbookName).Sheets("Status").Range("A" & (threadNr + 1)) = elapsed
    Set oXL = Nothing
End Sub

'---------Join threads - wait until all finish and save elapsed time to worksheet----------
Sub CheckIfFinishedVBA(maxThreads As Long)
    Dim endTime As Double, elapsed As String
    Do Until False
        DoEvents
        If Range("A2").Value <> "" Then
            If Range("A2:A" & Range("A1").End(xlDown).Row).Count = maxThreads Then
                endTime = Timer
                elapsed = "" & Format(endTime - startTime, "0.00")
                Range("I1").Offset(maxThreads).Value = elapsed
                Exit Sub
            End If
        End If
        Sleep 50
    Loop
End Sub

Now what do the various procedures do?:

  • VBAMultithread – the main thread which creates all the worker threads
  • CreateVBAThread – creates a single thread. Copies the current workbook and creates a VBscript which is executed via Windows Shell. This script will run the RunVBAMultithread macro
  • RunVBAMultithread – the worker macro which does the computations: which calculates a partition of the division table and save the elapsed time to the Master Excel Workbook
  • CheckIfFinishedVBA – this macro will execute once all worker threads have been created. It will wait (Sleep and check in loop) until all worker threads have finished in order to evaluate the total execution time of the algorithm

Performance

Please see this post on how this method of multithreading in VBA compares to other available approaches:
EXCEL: Multithreading – VBA vs. VBscript vs. C#.NET

Download

You can download the source code here:

More links on Mulithreading VBA

Check out the VBA Mulithreading Tool (the easiest way to add multithreading to Excel macro):
EXCEL: VBA Multithreading Tool

Want to learn how to add multithreading using C#.NET?
EXCEL: Multithreading VBA using C#.NET
Want to learn how to add multithreading using VBscript?
EXCEL: Multithreading VBA using VBscript
Want to see how all multithreading approaches compare?
EXCEL: Multithreading – VBA vs. VBscript vs. C#.NET

Native VBA Multithreading

Multithreading VBA using VBscript

1 Star2 Stars3 Stars4 Stars5 Stars (No Ratings Yet)
Loading...

Recently I published the post on using C#.NET to do multithreading in VBA – find it here: EXCEL: Multithreading in VBA using C#.NET. In that post I stated that multithreading is not possible natively in VBA only using external libraries e.g. COM/dll in C#.NET. In result of that post my attention was drawn to an idea I once thought about – using the Windows Shell to execute VBscript. Daniel Ferry’s article looks into it and gives a great example of how you can actually achieve multithreading in VBA using a VBscript swarm without resorting to external libraries although still reaching out of native VBA.

I strongly encourage you to visit Daniel’s article page and download his fantastic example of website scraping using a VBscript swarm vs. singlethreaded VBA IE / MSXML2 scraping. I loved the animation showing how the swarm runs!

However, in this post I wanted to present you with a basic example and provide you with some reusable code that you might use for your own purposes to implement multithreading, w/o resorting to external libraries. Next I will take a look at the increase in performance which I achieved on a simple algorithm due to multithreading.

Multithreading VBA using VBscript example

So let’s start with the example:

Public Declare Sub Sleep Lib "kernel32" (ByVal dwMilliseconds As Long)
Dim maxThreads As Long, divTabSize As Long, startTime As Double
Public Sub MultithreadedMultiplication()
    '---Clear previous values---
    If Range("A2").Value <> "" Then
        Range("A2:A" & Range("A1").End(xlDown).Row).Value = ""
    End If
    
    Dim thread As Long
    maxThreads = 4
    divTabSize = 10000 'Amount of elements in the division table
    'A simple division algorithm (a multiplication table would quickly cause overflow)
    startTime = Timer
    For thread = 1 To maxThreads
        CreateVBScriptThread thread, maxThreads, divTabSize
    Next thread
    CheckIfFinished 'Wait until all threads finish
End Sub
Public Sub CreateVBScriptThread(threadNr As Long, maxThreads As Long, divTabSize As Long)
    Dim s As String, sFileName As String, wsh As Object
    '---Copy parameters to VBscript---
    s = s & "dim i, j, oXL, x, startTime, endTime" & vbCrLf
    s = s & "startTime = Timer" & vbCrLf
    '---Compute partition of the division table---
    s = s & "For i = " & CInt(CDbl(divTabSize) * (threadNr - 1) / maxThreads + 1) & " To " & CInt(CDbl(divTabSize) * threadNr / maxThreads) & vbCrLf
    s = s & "   For j = 1 to " & divTabSize & vbCrLf
    s = s & "      x = i / j" & vbCrLf
    s = s & "   Next" & vbCrLf
    s = s & "Next" & vbCrLf
    '---Save the threadNr back to your Excel file! - this is the incredible part really
    s = s & "Set oXL = GetObject(, ""Excel.Application"")" & vbCrLf
    s = s & "endTime = Timer" & vbCrLf
    s = s & "oXL.workbooks(""" & ThisWorkbook.Name & """).sheets(""Status"").Range(""" & "A" & (threadNr + 1) & """) = (endTime - startTime)" & vbCrLf
    '---Save the VBscript file---
    sFileName = ActiveWorkbook.Path & "Thread_" & threadNr & ".vbs"
    Open sFileName For Output As #1
    Print #1, s
    Close #1
    '---Execute the VBscript file asynchronously---
    Set wsh = VBA.CreateObject("WScript.Shell")
    wsh.Run """" & sFileName & """"
    Set wsh = Nothing
End Sub
'---------Verify if finished----------
Sub CheckIfFinished()
    Dim endTime As Double, elapsed As String
    Do Until False
        DoEvents
        If Range("A2").Value <> "" Then
            If Range("A2:A" & Range("A1").End(xlDown).Row).Count = maxThreads Then
                endTime = Timer
                elapsed = "" & Format(endTime - startTime, "0.00")
                Range("F1").Offset(maxThreads).Value = maxThreads
                Range("G1").Offset(maxThreads).Value = elapsed
                Exit Sub
            End If
        End If
        Sleep 100
    Loop
End Sub

Yeah, that’s basically it! Just 40-something lines of code for a simple multithreading example in VBA. I would have thought that it would be much more complicated, but apparently as you can see this is not the case.

The example code runs a very simple algorithm which aim is to calculate a division table (just to avoid multiplication overflow). If you use more than one thread the division table will be partitioned so each thread gets an equal slice of the action. Each VBscript will run in a separate thread, calculate the partition and simply return it’s run time directly to our current Excel Workbook. The result will look somewhat like this:

Multithreading in VBA using VBscript
Multithreading in VBA using VBscript

The basic approach is the following:

  • Take any algorithm and partition it for each separate thread
  • Create as many VBscript files and you need threads
  • Execute each one concurrently using the WScript.Shell object

As mentioned each VBscript thread, if required, can save it’s output directly to the current Excel Worbook which is incredible and actually increases the practicality of this example.

Now for comparison I could right the same algorithm in native VBA:

Sub DivisionSingleThread()
    Dim i, j, x, startTimeS, endTimeS
    startTimeS = Timer
    '---Compute partition of the division table---
    For i = 1 To divTabSize
       For j = 1 To divTabSize
          x = i / j
       Next
    Next
    endTimeS = Timer
    Range("A2") = (endTimeS - startTimeS)
    elapsed = "" & Format(endTimeS - startTimeS, "0.00")
    Range("G1").Offset(maxThreads).Value = elapsed
End Sub

Performance analysis

Now what about performance. You would expect multithreading to significantly increase your algorithms performance. I tested the algorithm with a 5000 x 5000 table of elements (5000×5000 divisions) on a 2-core 4 threaded Intel processor (i5-4300U) with the following results:

VBA vs. VBscript
VBA vs. VBscript

To be frank I was very surprised to see that the singlethreaded VBA algorithm was actually faster than any VBscript multithreaded example. The VBscript algorithm reached its highest improvement in terms of performance at 2 VBscript threads. Adding additional threads did not seems to significantly improve performance, although I was expecting more or less to improve execution time proportionally until 4 threads are reached. This is probably because my processor is only 2-core and the additional threads are virtual.

To make the 2 algorithms as similar as possible I did not explicitly declare my variable types which Michael from VBA4All rightfully noticed (in my previous post) can help significantly improve VBA performance. If I would modify the VBA algorithm accordingly the execution time would drop to even approx. 1 second! Unfortunately VBscript forbids explicit variable data type declaration which is a serious setback.

Does this mean that multithreading in VBA using VBscript does not make sense? In most cases it won’t. However, there are cases when this could be useful e.g. in Daniel VBscript swarm web scraping example where VBscript utilizes similar objects/system resources as VBA. What is more, although VBscript was much slower on my 2-core laptop, if I had much more cores/processors VBscript might actually make up the distance.

Let me know what you think!

Be sure to checkout a comparison of all available approaches to VBA multithreading:
EXCEL: Multithreading VBA – VBA vs. VBscript vs. C#.NET.

Find the example XLSM file here: