Long Running Vb/a code Causing Screen issues

[toddbailey]

Hi All,

I have a application that can run from a few seconds to over 1/2 an hour depending on the user supplied part numbers entered. The code is well written and optimized. I've recently ported parts of the user interface to a user form and are now running into excel and user form responce issues while the code is executing. The code base has 2 distinct functions, gather and calculate cost data and store in access tables, then populate a series of excel worksheets.

Are there any settings I can make to the form properties or to Excel that will improve responce, ie: form doesn't repaint when a application that covers the form is closed, or moving form doesn't occur instantanously.

The code base is a few thousand lines long and has to loop through a lot of data from an access database.

I can't post the code cause there is just too much plus an NDA


I sprinkled a lot of doevents that seem to have helped, but I expect the form to be movable and continously updated during code execution.

It's possible I need to port the data gather stage to C# but also consider using a different database engine.


Any suggestions or tips on how to make a user form more detached from running vb/a code.

thanks

[Kenneth Hobs]

Welcome to the forum!

If you are writing to Excel in the foreground, that is slower than the background. You can use ADO to write to Excel in the background for certain tasks.

IF you do foreground writing to Excel, try my speedup routine. http://vbaexpress.com/kb/getarticle.php?kb_id=1035

[Paul_Hossler]

The code is well written and optimized.


Well .. I've found that almost all the time, algorithm improvements will always make the biggest improvement.

Ken's SpeedUp is good, and addresses Excel-type issues but without a LOT more details, people are only guessing.

For example, I've used a sub to populate cells with a .Value, instead of relying on a lot of worksheet formulas. I.e. do the VLookup in code based on a collection or dictionary instead of a lot of formulas that need to re-calc.

Also, pull the data into a VBA array, crunch it, and put it back onto the worksheet.

Are you updating controls on the userform? Just showing a unchanging UF most likely wouldn't cause a slowdown, for example

Not exactly what you asked :thumb , but I think you'd get more answers if you could sanitize a sample workbook and post it

Paul