Runtime question in vb vs. vba

[Nick.Korynski]

Hi. I was working on a project writing a macro with vba. i finished
writing my macro and decided to use vb to make a stand-alone executable
with numerous forms (as opposed to different excel files) for our
different tools. For some reason, the one tool took about an hour to
run in vba, but it is going much much slower in vb now when I try to
run it. Is this normal? Any ideas what I could be doing wrong to slow
down the process? Thanks!

Nick

 

[RB Smissaert]

Not much chance to know what you are doing wrong without knowing what you
are doing.

RBS

 

[Tim Williams]

Assuming you didn't make some mistake when transferring your code to VB....

When run in VBA your procedures are in the same process as the workbook(s), but when run from VB any references to XL must cross the
boundary between the VB process and the Excel process: this adds a lot of overhead, particularly if you are making many calls to
Excel (eg. iterating through large ranges).

Try to redesign your code so it makes as few calls to Excel as possible (eg. read large ranges into a variant array and iterate
through that in VB rather than reading each cell's value from Excel).