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.
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.
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.
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:
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
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.