Macro runs very slowly as .exe

[Sheemie]

Hi everyone. I'm using Excel 2000 with Xp Pro. I made a macro that opens another workbook and converts about 4000 rows of data using If statements and Select Case statements. The entire process takes about 16 seconds with screen updating disabled. If I make a .exe in VB 6.0 using the same code (with changes to open the workbook and excel), it takes about 6 minutes to complete the process. I'm opening Excel by:

Dim ExcelApp as Object
Dim FixThisCSV as Workbook
Set ExcelApp = CreateObject("Excel.Application")
Set FixThisCSV = ExcelApp.Workbooks.Open(MyFileNameHere)

It seems that the If..Then and Case parts are taking the longest. Any help would be appreciated.
Thanks
Kerry

 

[Tazguy37]

Welcome to MrExcel Board!

Does it have to be an .exe file? Executable files have a lot of machine overhead that you don't need, if you're going to be dealing with Excel workbooks anyway.

If it's a question of security, think about putting the code in an add-in, and protecting the project. There are several posts on the board that cover how to do that, but post back if you have problems or more questions on this topic.

Hope that helps!

 

[Oorang]

The problem with putting the code in an add in, is that the source code can be cracked in real-time.

 

[Sheemie]

Thanks for the input, and thanks for the welcome Tazguy.
That's my hesitation about the add-in. It would seem that if I converted the code correctly to run in the exe, it would only take a little longer (two or three times as long would be ok). It seems likely that I've missed something going from the macro to the VB6, I just don't know what.

 

[Legacy 98055]

Why are you late binding?

Dim ExcelApp as Object

Set a refernce and use

Dim ExcelApp as Excel.Application

Use strong typing. If you use late binding then the runtime has to check the reference hierarchy for every instance of every object. That will definitely slow thing way down. You should not notice any appreciable slowdown at all using VB as opposed to VBA.

If you will, post some or all of your code. You also have the option of creating a COM add-in.

Tom