Managing the speed of Macro that access multiple COM interfaces in VBA

A while back I had to deal with the slow speed issues that come from accessing the COM interface of different applications. I needed to evaluate and transfer a complex data set between Excel and MicroStation.  Each time I accessed the properties/methods of Excel from within MicroStation I increased the run time of my macro. For a few accesses, this is not noticeable, however when this grows to hundreds or thousands, and larger in scale, the time lag between start and stop may grow from a half second to 3 or 4 seconds, to minutes.

I found that if I ran those operations in the applications own VBA environment, the speed increased on the order of 10x to 20x or more. I was limited to using the VBA programming environment because of my company's I/S policy, so I needed to find a way to get the information back and forth with as few external COM calls as possible. VBA has methods for writing data to a file. Using the file stream methods within VBA I was able to pass the majority of the complex information set between Excel and MicroStation. This allowed me to limit my COM usage to a few essential operations and a Macro Call.

Along with some other streamlining of code in my VBA routines, I was able to shorten a process taking 30+ seconds per call to 1 to 2 seconds. Given we run this function many times to update our process (as many at 30 times at once), this significantly shortens the time I and my coworkers spend waiting for their updates to process.

As VBA routines get more and more complex, managing the access times become more and more important. Review your code to find the pieces that are taking the longest time to run and start analyzing them for inefficiences. Accessing COM interfaces may be one of these and if so finding ways to minimize the number of calls through the interface will have a large impact on the overall speed of your routine.

Anonymous