Tag Archives: vbscript

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

Multithreading VBA – VBA vs. VBscript vs. C#.NET

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


I you were looking for an end-to-end analysis of the topic of multithreading VBA. You have come to the right place! So lately I have been really wearing out the topic of multithreading in Excel in VBA. Hopefully you have already read through my three posts on the various methods of achieving multithreading capabilities in VBA:

In all three posts I showed that, although “native” VBA does not feature multithreading, you can in fact achieve multithreading by using either external COM/dlls (e.g. in C#.NET), running VBscripts via the Windows Shell command or run additional VBA worker threads via VBscript. All three approaches have their ups and downs. Now just to clarify: What do I mean by “native”? I mean that VBA as a programming language does not natively support creating new threads, joining threads etc.

Pro’s and Con’s of all multithreading VBA approaches

Let us summarize them in the table below:

Multithreading approach Pro’s Con’s
Using a C#.NET COM/dll
  • Best performance – even singlethreaded C#.NET COM/dlls will significantly reduce the execution time of your algorithms
  • Easy parallelism – C# provides the “Parallel” class which allows you to easily create and manage threads and run for loop in multiple threads
  • Requires learning C#
  • Requires referencing an external dll – must make sure it is not lost when sharing the Excel file
  • Requires a separate programming environment (e.g. Visual Studio) to create, modify and compile
VBscript worker threads
  • Fairly easy to implement
  • VBscript is almost identical with VBA language
  • Worst performance – as VBscript is much slower than native VBA in most cases multithreading your algorithms using multiple VBscript worker threads will extend execution time instead of reducing it. I would recommend using VBscript worker threads only if the threads are to leverage other system libraries/resources
VBA worker threads (via VBscript)
  • Good performance – you should see a proportional reduction of execution time, at some point, when using this approach (2 cores ~= 2x faster)
  • Most complicated – VBA worker threads run via VBscript
  • More room for errors
  • Overhead – multithreading makes sense when VBA algorithm takes at least 5 sec or more depending on the amount of cores/processors available. This is due to the overhead resulting from invoking separate VBA worker threads in separate Excel processes via VBscript

Performance: Native VBA vs. VBA workers vs. VBscript workers vs. C#.NET

I would like to warn you that these are only example metrics run on a specific environment (on a 2-core 4 threaded Intel processor (i5-4300U)). I didn’t focus too much of my time on drafting very precise metrics as these statistics are simply meant to give you the general idea of the relative performance of all approaches.

Comparison of different approaches to multithreading VBA
Comparison of different approaches to multithreading VBA

As you can see the VBscript parallelism approach provides very lousy performance and it gets worse the more operations the algorithm has. I know what you are thinking now: well then want is the sense of using VBscript multithreading if the performance is awful in the provided example? Well there are examples when VBscript threading makes sense see Daniel’s example here. In most common examples you should, however, disregard the VBscript approach therefore I removed it from the chart and focused on the remaining methods:

Comparison of different approaches to multithreading VBA (excluding VBscript worker threads)
Comparison of different approaches to multithreading VBA (excluding VBscript worker threads)

As expected C#.NET rules the stats! While it took at least 26 sec to compute 900mln operations in “native” VBA, C#.NET managed to do this in less than 2 sec. On the other hand the VBA parallel workers managed to reduce the computation time to about 10 sec which is less than half the “native” VBA execution time. Still not bad – would have been much better if my CPU had more cores. Notice that the break-even point between the “native” VBA algorithm and the VBA parallel workers methods was at about 100mln operations – taking approx. 3 seconds. It seems therefore that there is a couple of seconds of overhead in case of the latter approach.

Source code and example

If you want to download the whole source code containing all these example approaches see the link at the bottom of this post. To gather these performance statistics I made a single Excel workbook that allows you to test all multithreading VBA approaches and verify which is the best fit for your algorithm / use case.

Screenshot from the Excel Workbook consolidating the findings (download link at the bottom)
Screenshot from the Excel Workbook consolidating the findings (download link at the bottom)

VBA – No multithreading

Below you can find the a “native” VBA example of a simple division algorithm. This is the baseline for the other algorithms.

VBA – no multithreading (the baseline)

Sub VBASinglethread()
    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 = 1 To divTabSize
       For j = 1 To divTabSize
          x = CDbl(i) / j
       Next
    Next
    endTimeS = Timer
    elapsed = "" & Format(endTimeS - startTimeS, "0.00")
    Range("J2").Value = elapsed
    DoEvents
End Sub

 

C#.NET- Single and multithreaded

Below you can find 2 example algorithms in C#.NET. One is singlethreaded and the other one leverages the C# Parallel class to easily achieve multithreading.

C#.NET COM/dll code

using System.Linq;
using System.Runtime.InteropServices;
using System.Text;
using System.Threading;
using System.Threading.Tasks;
namespace TestLib
{
    [ComVisible(true), ClassInterface(ClassInterfaceType.AutoDual)]
    public class TestClass
    {
        
        [return: MarshalAs(UnmanagedType.SafeArray)]
        public int ParallelMethod(int divTabSize)
        {   
            Parallel.For(0, divTabSize, i =>
            {
                double x;
                for (int j = 1; j < divTabSize; j++)
                {
                    x = i / j;   
                }
            });
            return 0;
        }

        [return: MarshalAs(UnmanagedType.SafeArray)]
        public int SequentialMethod(int divTabSize)
        {
            double x;
            for (int i = 1; i < divTabSize; i++)
            {
                for (int j = 1; j < divTabSize; j++)
                {
                    x = i / j;
                }
            }
            return 0;
        }
    }

    static class UnmanagedExports
    {
        [DllExport]
        [return: MarshalAs(UnmanagedType.IDispatch)]
        static Object CreateTestClass()
        {
            return new TestClass();
        }
    }
}

 

C#.NET COM/dll code – VBA example

Declare Function CreateTestClass Lib "C:Users[PATH TO DLL]TestLib.dll" () As Object
Public Declare Sub Sleep Lib "kernel32" (ByVal dwMilliseconds As Long)
Sub CshartSequential()
    Dim startTimeS As Date, endTimeS As Date
    startTimeS = Timer
    Dim testClass As Object
    Set testClass = CreateTestClass()
    Call testClass.SequentialMethod(divTabSize)
    endTimeS = Timer
    elapsed = "" & Format(endTimeS - startTimeS, "0.00")
    Range("K2").Value = elapsed
    DoEvents
End Sub
Sub CshartParallel()
    Dim startTimeS As Date, endTimeS As Date
    startTimeS = Timer
    Dim testClass As Object
    Set testClass = CreateTestClass()
    Call testClass.ParallelMethod(divTabSize)
    endTimeS = Timer
    elapsed = "" & Format(endTimeS - startTimeS, "0.00")
    Range("L2").Value = elapsed
    DoEvents
End Sub

 

VBscript multithreading

Below the source code for multithreading using VBscript worker threads. Interesting but usually of little use due to terrible performance. See Daniel Ferry’s good example of when it is worth leveraging.

VBscript worker threads

Sub VBscriptParallel(maxThreads As Long)
    Dim thread As Long, threads As Long
    For threads = 1 To maxThreads
        ClearThreadTime
        'A simple division algorithm (a multiplication table would quickly cause overflow)
        startTime = Timer
        For thread = 1 To threads
            CreateVBScriptThread thread, threads, divTabSize
        Next thread
        CheckIfFinished threads
    Next threads
End Sub
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 = CDbl(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)"
    '---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(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("G1").Offset(maxThreads).Value = maxThreads
                Range("H1").Offset(maxThreads).Value = elapsed
                Exit Sub
            End If
        End If
        Sleep 50
    Loop
End Sub

 

VBA multithreading (via VBscript)

Below the code to a good alternative to the C#.NET COM/dll approach. It uses VBscripts just for creating separate VBA worker threads. This approach can provide satisfactory results although not as incredible as C#.NET.

VBA worker threads (via VBscript)

Sub VBAMultithread(maxThreads As Long)
    Dim thread As Long, threads As Long
    For threads = 1 To maxThreads
        ClearThreadTime
        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

 

Summary

Hopefully I have exhausted the topic of multithreading in VBA. I am certain there are other methods of multithreading in VBA although I would not think that it would be worth taking them into consideration. Now for the ultimate conclusions. You can leverage several approaches to VBA multithreading although each has it’s drawback and advantages. I would not eliminate any of the presented approaches as I think this depends on the algorithm you are designing / programming. I personally would almost always go with C#.NET as I am comfortable with C# and when making complex algorithms it is probably better to create them from the very beginning in a language designed to support multithreading. I understand, however, that some of my readers would prefer staying with VBA and for those I would recommend the VBA worker threads – a little more complex but still very efficient.

What do you think? What are your experiences? Did you ever had to consider multithreading in VBA?

Download

Here you can download the whole project source codes and the complete set of files:

Next steps

Check out the VBA Mulithreading Tool (the easiest way to add multithreading to Excel macro):
EXCEL: VBA Multithreading Tool
Check out the VBA Mulithreading Add-In to add multithreading to all Excel Workbooks:
EXCEL: VBA Multithreading AddIn

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

Multithreading VBA using VBscript

1 Star2 Stars3 Stars4 Stars5 Stars (1 votes, average: 5.00 out of 5)
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:

Send email from Excel using VBA

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

Tired of writing tons of similar emails to your recipients? Preparing a personalized newsletter in Excel? Sending an email from Excel is a useful feature in case you need email notifications or you are building a mailing list in Excel. Below find a quickly drafted method for sending emails from your Outlook application. The prerequisite is to have Outlook Exchange installed and configured to some default email account. The procedure will then leverage this account to send emails. Similarly this can be use to send emails from VBscript – just remove any variable data type declarations and you should be good to go (e.g. “Dim outApp” instead of “Dim outApp as Object”).

Send email from Excel

The code below will utilize your Outlook application to created an Mail item, and send the email.

Sub SendEmailFromOutlook(body As String, subject As String, toEmails As String, ccEmails As String, bccEmails As String)
    Dim outApp As Object
    Dim outMail As Object
    Set outApp = CreateObject("Outlook.Application")
    Set outMail = outApp.CreateItem(0)

    With outMail
        .to = toEmails
        .CC = ccEmails
        .BCC = bccEmails
        .subject = subject
        .HTMLBody = body
        .Send 'Send the email
    End With

    Set outMail = Nothing
    Set outApp = Nothing
End Sub

Tweak: Save email in Outlook from Excel

What if you do not want to send the email, but instead just send a draft to finish or send later? Replace Send with Save in the above code snippet as show below:

 With outMail
        .to = toEmails
        .CC = ccEmails
        .BCC = bccEmails
        .subject = subject
        .HTMLBody = body
        .Save 'Instead of send
    End With

Tweak: Display draft instead of sending an email

An additional modification of the procedure could be to simply display the email draft instead of sending/saving it. Replace Send with Display in the above code snippet as show below. You will be presented with the draft email message which you can tinker with and send by yourself.

 With outMail
        .to = toEmails
        .CC = ccEmails
        .BCC = bccEmails
        .subject = subject
        .HTMLBody = body
        .Display 'Instead of send/save
    End With

Adding attachments to email from Excel

Sometimes text/html is not the only content you want to share with your recipients. What about adding some attachments to the email? Just add the code snippet below to the original code snippet above:

Dim filePath as String
filePath = ("C:\someFile.txt")
outMail.Attachments.Add filePath 'Send some text file

'Attach the current Workbook
outMail.Attachments.Add ActiveWorkbook.FullName

Summary

Sending emails from Excel can save you a lot of time. This is a very useful example of Excel automation. I commonly see sending emails from Excel being used in these scenarios:

  • Managing email distribution lists from Excel
  • Sending notifications emails from Excel
  • Creating draft emails

Additional tip: send Excel as PDF

As most of you know Excel is great for preparing reports. Charts, numbers etc. You can of course create a text message with this data. However sometimes that is simply not as convenient as sending an image/pdf and distributing these PDF reports to recipients. Let’s extend our snippet further to send a pdf Excel report with just the output instead of your entire workbook.

Dim ws as Worksheet
Set ws = Activesheet
ws.ExportAsFixedFormat _
        Type:=xlTypePDF, _
        Filename:=myFile, _
        Quality:=xlQualityStandard, _
        IncludeDocProperties:=True, _
        IgnorePrintAreas:=False, _
        OpenAfterPublish:=False

More examples

Above I tried to focus on the most common examples of sending emails via Excel. There are definitely more scenarios. I think Ron de Bruin did a great job of listing most of them. Check his website here.

Schedule VBA Macros to run periodically via VBscript

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

Some time ago I posted an article on how to run an Excel VBA Macro using VBscript and cmd to save time or to be able to run multiple Macros across a number of Excel files without actually needing to open them. Well, I decided to extend this example to allow periodic execution of Excel Macros e.g. in case your macros have to carry out some periodic tasks like load data into a database etc.

How to schedule VBA Macros to run automatically?

The solution is quite similar to the one posted on my previous article with one minor modification…

What we need to do is execute our VBA Macro (can be Excel, Access etc.) from a VBScript script file. To make sure it runs automatically we need to schedule it to run from startup by placing it in the Windows Startup folder. Follow the steps below to setup the script.

Create VBScript to run Excel periodically

First create an empty *.vbs file and input the following code snippet:

RunMacro
Sub RunMacro() 
  dim xl,path,xlBook
  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
  WScript.Sleep(5000)'New line: Sleep for 5 seconds
  RunMacro'New line: Run the Macro again
End Sub 

Replace the highlighted rows with your Workbook name and VBA Macro name.

What does the VBScript do?
So basically what it does is:

  1. Executes the Module.MyMacro VBA Macro in Workbook Book1.xlsm which is situated in the same folder
  2. Sleeps for 5 seconds
  3. Repeat

Add the VBScript to Startup

Schedule VBA: Add VBScript to Startup folder
Add VBScript to Startup folder
To make sure the Macro will run periodically and not require any manual touch, you may want to add it to your Startup folder. This will ensure that the script will start running as soon as you turn on your workstation and will stop as soon as you close.

Turn Off the periodic VBA macro

Schedule VBA: Turn off VBA MacroWhat if you want to turn the script at any moment in time? You might as well turn on the Windows Task Manager. There is an easier way. Just create the following *.bat file and run it when you want to close the periodic script:

taskkill /F /IM wscript.exe

Now you can create Excel Macros and use them to execute certain tasks at intervals automatically!