Multithreading in VBA for Excel

Multithreading in VBA for Excel

• masuday

  http://msdn.microsoft.com/isv/technology/vba/faq/default.aspx

  Q. What are the important new features of Visual Basic for Applications SDK 6.3

  A. Visual Basic for Applications 6.3 builds on the strong features of VBA 6.0 (the IDE, Microsoft Forms, ActiveX Control support, and IntelliSense technology) with enhanced tools for developer productivity and solution management.
  
  Every VBA 6.3-enabled product includes support for modeless dialog boxes, full core language parity with Visual Basic 6.0, enhanced project password protection, plus support for developer add-ins within the IDE that make programmers more productive. Some products that host VBA 6.3 may also take advantage of digital signing of VBA projects (to protect against "macro viruses"), support for ActiveX Control designers (easing the creation of complex project components), and multithreaded projects (for better performance with server-based applications).
  
  Version 6.3 of the VBA SDK includes merge modules for the Windows Installer, enhanced documentation, comprehensive samples, and the integration wizard for Visual Basic.

  
  
  

• Yogesh Ramakrishna

  Wouldn't running each thread within it's own process (ala AppDomain) solve the problem

  AppDomains can be setup to define their own process context - which should solve the points you have listed. An AppDomain can be unloaded - and should it fault - it won't affect other AppDomains or the main application.

  This is the model used generally when defining AddIns to an application - the idea being that should an AddIn misbehave - it doesn't take everything down.

  
  
  

• Geffo

  All of the Excel object model objects are associated with the main single threaded apartment (STA).

  
  This very much sounds like the cause of the problem we're facing when generating Excel workbooks on simultaneous threads. I guess even if we quit using multithreading and run multiple instances of the same program to do the job, we would still see the same "choking" effect
  
  
  

• rockman88

  Yes, these are the multi-threading capabilities that I was referring to in my original post.  We did enable the functionality for ISVs but Office never took advantage of it.  And again, IIRC this particular feature doesn't actually allow a developer to spawn threads in Vba code, it allows an ISV to expose their project in such a way that code can run on multiple threads.
  
  So unfortunately, if you want to use multiple threads in Vba for any Office application (i.e. Access), you are more or less out of luck.  There is no support for this in the respective Vba integrations.  I suppose if you really had to make this work, you might be able to call the Win32 apis directly from Vba (and/or write a dll to give you the support you needed).
  
  However, you are still faced with the limits surrounding COM threading that may usurp any attempt on your part to make truly multithreaded OM calls.  Basically, you should find the CoClass entries in the registry for any Office class you are trying to automate.  Unless there is a ThreadingModel value and unless that value is set to "Free" or "Both" you would gain nothing from calling into that object on multiple threads (because COM will still serialize the calls).
  
  Hope that helps / sorry it wasn't the answer you were looking for.
  
  Geoff Darst
  Microsoft VSTO Tools
  

• ptreturns

  It should be pretty simple to write something under the .NET framework that performed the main grunt of the work (and hence used multiple threads) - which was driven by VBA. Depending on what you wanted to do - and assuming it's a data driven task - you should be able to write a generic data processor. With a little imagination - this could even take a VBA script for on-the-fly processing.

  The .NET framework allows objects defined to be exposed via COM - and therefore act as a shim or bridge between VBA driven task and .NET.

  Of course it would be easier to use the PIA for Office in .NET.

  I can knock up a simple diagram to illustrate if you'd like.

  Regards,

  Dasher

  
  
  

• cjshrader

  http://msdn.microsoft.com/isv/technology/vba/faq/default.aspx

  Q. What advantage do VBA multithreaded projects have over Visual Basic or Visual C++

  A. VBA multithreaded projects provide tighter integration with a host application. This makes it easier for a developer to write VBA code that responds to application events and that interacts with the host application.
  
  This may be where the impression came from that VBA supports multiple threading...
  
  I'm also looking for some help with multiple threads in Access using VBA 6.3... I guess I need to look elsewhere

  
  
  

• JesusChris

  Hi,
  
  I'm curious where you got the impression that Vba supported multiple threading in Excel   While we did enable some limited multiple threading support for Vba in ISV integrations, AFIK Office never integrated the feature.  Also, from what I can remember, that support was entirely host based--there were no thread primitives that would allow a customization itself to proactively spin up threads.  I'm not on the Office team so I can't give you the absolute definitive answer here, but I did work on Vba (and active development on Vba ceased years ago) and I'd be very suprised if this feature was added for Vba 6.3.
  
  That being said, based on the example you are looking for, I'm not sure it would help you even if it were there.  The thing to remember when dealing with threading and COM is that apartments determine the level of concurrency that is allowed.  All of the Excel object model objects are associated with the main single threaded apartment (STA).  So even if you could have multiple threads trying to make calls against the Excel object model, those calls would always be serialized by COM.  In the context of your example, things would always be "turn based" regardless of the number of threads involved in making calls.
  
  Finally, I should mention that Visual Studio Tools For Office does have support for writing multiple threaded customizations against Excel (the limitations described above still apply).  If you have Microsoft Visual Studio 2003 or 2005, you may consider whether VSTO would work better for you.
  
  Sincerely,
  
  Geoff Darst
  Microsoft VSTO Tools
  

• Wei Ming

  The "main grunt" is unfortunately the macros (VBA functions) embedded in the pre-written Excel templates handed to us. For each worker thread, it will instantiate an Excel application object, and on each database record iteration, add a workbook and call its macros to populate the necessary data.
  
  When running simultaneously, they can possibly cause errors like:
  1. Type mismatch encountered while populating data (but the database records are correct; running a second time is fine)
  2. Sometimes stalling altogether, one by one, leaving only one worker thread to do all the work.
  3. Data range (a range of cells, possibly) copy errors.
  
  If everything is done in a single thread, and _even_ a single process, then it is all fine.
  
  
  

• Garg

  http://msdn.microsoft.com/isv/technology/vba/faq/default.aspx

  Q. What elements of Visual Basic for Applications are being licensed

  A. Licensees of Visual Basic for Applications embed the VBA integrated development environment (IDE), the VBA language engine, Microsoft Forms, IntelliSense technology productivity features, and development tools (including a debugger, an object browser, and a code editor). In addition, licensees can take advantage of the new multithreading capabilities of VBA 6.3, as well as support for digital signatures.

  
  
  

• Bill Schulz

  Does anyone have an example of the API calls that would be used for threading My threading has used OWL2 up to this point - so no direct API calls.

  
  

• ingriT

  Yes we have tried running multi-process, single-threaded setup as well. in fact that was the first thing we tried. That unfortunately yields similar errors (exhibiting a Copy range error).
  
  What I'd like to know is whether there is a "common gateway" offered by the OS that everybody must go through when they work with COM objects Meaning no matter how many Excel Application objects we spawn (with their corresponding Excel.exe processes), the RCW wrappers are really all just proxy to a singular COM object This is a topic I am not familiar with.
  
  
  

• Coreth

  BTW here is a good article that will explain how COM threading works in more detail:
  
  http://support.microsoft.com/kb/q150777/
  
• Multithreading in VBA for Excel