I've written a macro in Excel VBA. When I translated the program into VB6 I noticed a significant reduction in the processing speed. The only difference in the code is that VB6 references the application object in excel. How could this slow down the program speed? The processing time went from 3 seconds to more than one minute. How do I get around this problem if I absolutely want to create an executable file in VB6 that controls Excel? (Same computer was used for both versions)

:) mambojedi

When you run the code within Excel, the VBA functionality is a part if the process/thread of Excel. No "communication with" is required.

When using automation, a lot of requests, responses, laoding of resources, etc., must take place. Automation is slick, but it is always going to be slower.

What is it that you are trying to do? Perhaps there is a way to coordinate resources to speed things up.

Brian,
thank you for the info. Which resources would I have to coordinate to speed things up? I thought that the speed problem may have had something to do with "early" or "late" binding. I've read that early binding was better (That's what I'm using) but it didn't produce a significant increase in speed. The program basically imports a tab delimited file, then sorts and copies the data into two columns on a separate worksheet. I've noticed a speed increase if you activate or reference a cell's value directly rather than by selecting the sheet and then the selecting the cell. Makes sense since excel doesn't have to waste time doing such things which could take up memory. The Application.ScreenUpdating feature was turned off and excel is hidden while the program is run from my VB-6 form. That helps speed it up but again, not as fast as VBA. Finally, the main reason I wanted to create an EXE using VB-6 was because I wanted to make it easy to distribute the program and because the new security features in office cause problems when trying to create a rich text box which I absolutely want to use in this program. Note that the rich text box problem is actually caused by Internet explorer and the fix to get around it is to edit the registry, but that only solves the problem on a per day basis since it resets a day or two later on its own. Therefore using VB-6 is less of a hassle when dealing with rich text boxes since it doesn't have the same limitations imposed by office's security features.

mambojedi :)

When you will be reading from a database again and again, you can speed things up by reading an entire table into an array, then using that array.
If you need to grab bits & pieces from a text file, rather than passing a reference to the file around, you can read the whole thing into a string to reduce the overhead of opening the file repeatedly.

Accordingly, perhaps Excel provides a way to get all the values into VB as an array (like a database’s getrows() function). If so, mniipulating that array in VB will be a much bigger programming task, but a lot faster.

Your richtext box issue sounds like your network administrators are tweaking the registry every now and again, then pushing that new registry "version" onto peoples' machines when they log in. If that's the case, see if you can get them to officially endorse your registry change, so that it would be part of every registry change package (if indeed that is what is happening).

I don’t follow where a RTBx would fit into the process you described?