Multithreaded VBA – An Approach To Processing Using VBScript




Today’s guest post is authored by Daniel Ferry, a longtime professional Excel developer and consultant (and a newbie blogger). A few months ago, I wrote a post on how one can simulate multithreading in VBA using Excel. Daniel picked up a thread from there and commented that he had some good success in marshalling some of windows multithreaded subsystems from within VBA. In this article he describes his approach.

Multithreading VBA – Using VBScript

Try as we might to work around it, Excel VBA is a single threaded affair. Still there are times when being able to spin off independent tasks could dramatically speed up processing times, rather than have all tasks follow in order, one after the other, each waiting for the previous one to finish.

There are a number of times when data needs to finally get stored in Excel from data sources or using transfer processes which are far slower than the processing capabilities of Excel / VBA. Downloading information or data stored from a website is one such example. Now, VBA is a capable scripting language and certainly up to the task. However when we scrap a website, VBA has to wait for over 90% of the time for the website server to respond with the requested data. Once the html is on the Excel computer, the parsing out of the data is virtually instantaneous.

If there ever was a task that could benefit from multithreading in VBA, this is it. In the demo workbook included with this post, the reader will find a tool implementing three different strategies for downloading data from a website using Excel. The demo contains a list of 100 property addresses and the tool retrieves valuation data on these properties from www.realestateabc.com. The demo also includes controls allowing the user to toggle between the three different techniques and to time them.

multithreading-vba-using-vbscript

The first technique instantiates Internet Explorer via OLE. While this allows the user to see the pages being scraped, this is by far the worst strategy. IE is bloated and very slow. The second technique uses MSXML2 as a lightweight alternative that is approximately four times swifter than IE.

msmxml2-vba

These first two techniques are squarely rooted in the single threaded camp, since while they are getting the data Excel is waiting intensely and not able to do much in the meantime. As you can see above, the processing takes place one record at a time and next record is processed only once the previous one has finished. The third innovative technique however, makes this task run much faster and is listed in demo workbook as the Swarm Method.

The Windows Scripting Host is a multithreaded subsystem of the Windows OS that runs VBScript programs. My swarm creates a number of VBScript files in the demo workbook folder and runs them all in the Windows Scripting Host simultaneously. Each one of these agents scrapes one record of valuation data from the website, grabs an OLE handle back to the parent workbook, deposits it data to Excel, and kills itself. The Worksheet_Change function senses that a new data record has been entered and creates a new agent to replace the one that just expired, keeping the swarm size in balance.


(For those interested in knowing more about what a .vbs file is, I recommend reading this explanation before resuming from this point.)

Each agent is programmed with specific range coordinates to deposit the data. The demo includes a control to allow the user to fine tune the number of agents in the swarm. Different computers and versions of Windows can handle different sizes of swarms. Windows Vista seems to have a more robust OLE subsystem, and quite handily tromps on WinXP. On my computer XP performs well with 50 to 60 agents in a swarm, while Vista on the same machine can smoothly handle a couple hundred agents.

multithreading-using-vbscript

The amount of time that can be saved by this multithreaded technique is impressive. It’s a function of the number of simultaneous agents in the swarm.

When selecting the swarm method in the demo, please be patient as there will be a few seconds of delay before results start returning. But when they do it comes in tsunami fashion! The VBA code is well commented and fairly straightforward. In my opinion it is well worth the time to study the code to see how the three methods are achieved using VBA, especially the Swarm method. Since the demo will be creating a number of .vbs agent files, it is best to create a folder for the demo workbook.

Daniel Ferry is a professional Excel developer and consultant. He has been developing business solutions with Excel for two decades and did the same with Lotus 123 before Excel existed. He has just started his own blog at excelhero.com/blog, hoping to provide many insights into creating powerful Excel based solutions. Check out his LED RSS News Ticker at his blog for an interesting demonstration of bitmasking in Excel worksheet formulas.


Here’s some basic information on VBScript.

What is a .VBS File ?

Simply put, a .vbs file is essentially a file containing VBScript code (short for Visual Basic Scripting Edition). VBScript comes installed with all Windows OS since 1998. What make .vbs useful is its abiity to pack html and VBScript code in a single file which can then be stored as a file with “.vbs” extension.

Here’s an example. Open notepad and type in the following lines:

1
MsgBox("Hello World")

I am sure you would have recognized that this is a pretty standard VBA code that anyone would use when writing VBA code. Now save this file as “myfile.vbs“. Now double click on the file and you will see the message box appear with the specified text. When used judiciously, VBScript allows access the DOM (Document Object Model) of a web page and to carry out operations not possible using plain HTML.

Where Does VBScript Get Used

It can be imbedded in an HTML webpage as a sort of analog to JavaScript. Microsoft developed Internet Explorer early on to use VBScript natively for essentially any task that a website developer might do in JavaScript, such as AJAX, animation, etc. The only problem with using VBScript inside of a webpage to control that page is that the only browser that supports it is Internet Explorer; all other browsers will just ignore the VBScript. In this scenario, VBScript is being interpreted and run by Internet Explorer.

The second major use is as the native language used in Active Server Pages (ASP), which was Microsoft’s first server-side script engine for dynamically-generated web pages. Entire web applications can be developed using ASP Classic and VBScript, and millions have been. In this scenario, VBScript is being interpreted and run by Internet Information Services (IIS), Microsoft’s web server.

The third major use, and the scenario used in this article, to run VBScript in the Windows OS subsystem called the Windows Scripting Host. In effect, this allows VBScript to act as a modern day batch file on steroids. IT administrators often use VBScript to automate management of company workstations, including the company-wide deployment of updates and new programs, backups, diagnostics, etc. In this scenario, VBScript is being interpreted and run by the Windows Scripting Host on any modern Windows computer.

VBScript is a subset of VB6 and VBA. One important difference is that VBScript does not support variable typing, so all variables in VBScript are variants.

Click here to go back to the article.


Excel Formula, Excel Chart, Excel Macro, Excel VBA, Pivot Table Excel, Excel Dashboard

What Do You Think ?


XHTML: You can use these tags: <a href="" title=""> <abbr title=""> <acronym title=""> <b> <blockquote cite=""> <cite> <code> <del datetime=""> <em> <i> <q cite=""> <strike> <strong>


Comments and Trackbacks

  1. Phil wrote:

    Hi,

    Nice idea, however, there is an error in the code that creates the swarm agents.

    In the function CreateVBScriptAgentAndLaunch, change:

    s = s & “oXL.workbooks(“”Scraping_Demo.xls”").sheets(“”Demo”").Range(“”" & sOutputRangeAddress & “”") = vResults” & vbCrLf

    to:

    s = s & “oXL.workbooks(“”" & ActiveWorkbook.Name & “”").sheets(“”Demo”").Range(“”" & sOutputRangeAddress & “”") = vResults” & vbCrLf

    and it should work nicely.

    Or you can change the name of the workbook to Scraping_Demo.xls :)

  2. Ajay wrote:

    Tell you what Phil – Daniel had the original file named Scraping_Demo.xls ;-) So dumb of me to have changed the name at the last minute without remembering the implications !

    Changed now and thanks for the help.

    Regards,

  3. Ajay wrote:

    A great workaround Excel limitations Daniel. Kudos!!!

  4. Trisha wrote:

    I just downloaded the attachment. I must say a very impressive demonstration. I can already think of a number of uses for this technique.

    Thank you

  5. Daniel Ferry wrote:

    Trisha,

    I’m glad you like it. Let me know how you use the technique!

    Regards,

    Daniel Ferry
    excelhero.com/blog

  6. sam wrote:

    Hi Daniel,
    Fantastic…I tried to comment on your blog but it does not seem to send me the confirmation e-mail at sgbhide@gmail.com

    Can you kindly enable this

  7. Daniel Ferry wrote:

    Sam,

    Sorry, I’m just learning my blogging platform. It should work now.

    Thanks.

  8. Anil Verma wrote:

    Nice Job. I need one script same type.Kindly mail at verma1975@gmail.com for further details

  9. bembi wrote:

    Really nice demo, but the agent files is kind of troublesome if you use like hundreds or more agents.

    So is there a way to erase all the agent files automatically after the task has been done?

  10. Siddhartha wrote:

    this is certainly fantastic … Truely amazing and revolutionary breakthrough.

  11. John Plauche wrote:

    The multithreaded example did not work for me.
    It just lists the agent_1 agent_2 etc then it stops.

    I think I have all the references set properly.

    Any advice would be appreciated. This example is very interesting to me.

  1. Best method for scraping data from Web using VB macro? wrote:

Subscribe

Keep up with the latest stories - Delivered right to your inbox
feedburner

Translate

English flagChinese (Simplified) flagPortuguese flagGerman flagFrench flagSpanish flagJapanese flagArabic flagDutch flagHindi flagPolish flagSwedish flagFilipino flagHebrew flagIndonesian flagUkrainian flagThai flagTurkish flag
treeemap software for excel