Long running vb/a code Issues

Hi All,

I need some advice to getting excel, a user form and vb/a to play nice with each other and the os.

I have a user form w/ vb code that can run for a minute to an hour depending on what parameters the user selects.

When the code is running and excel minimized, it can't be restored until the code completes.
additionally,
if I select the form and try to move it there is a few minute delay before it happens
if a application is opened over the user form or excel, then closed, the user form and excel image is blanked and doesn't repaint for several minutes.
on occasion, when I open task manager, I see excel and MS VB for Appl. in a non responding state
There isnm't any issues displayed in task mngr, the machine is running at about 45% cpu utilization.
However, other apps, appear slow to respond at times when the vb code is running.

I've sprinkled do events and repaint through out the code modules but it's a poor attempt at best.

Also the code appears to sporadically hang, if I hit a ctrl+Break, then resume, the code continues
however, at the break point, I add another DoEvents

Is there something I've over looked?

thanks

toddbailey,

Usually when code takes a long time to run, its due to inefficiencies in the code-writing, like using unnecessary loops, sifting through unnecessary data, or making multiple changes directly to the worksheet instead of using arrays.

Without seeing the code, its difficult to say what the problem is. So...
To best describe or illustrate your problem you would be better off attaching a dummy workbook, the workbook should contain the same structure and some dummy data of the same type as the type you have in your real workbook - so, if a cell contains numbers & letters in this format abc-123 then that should be reflected in the dummy workbook.

If needed supply a before and after sheet in the workbook so the person helping you can see what you are trying to achieve.

let's assume the code is optimized

basically the vba code makes a lot of calls to a back-end database(access via ado) for data preparation, there is little the front end does, but wait for the back-end data base to finish the query, only until the very end there are 1/2 a dozen calls used to populate worksheets and run effectively.

I'm looking for a way to make excel and the user form more responsive to windows events, while the code is running. The addition of doevents have helped, but regardless of what the code is doing in the background, the userform and excel shouldn't be locked out from basic windows functions.

Maybe I need to port the code to C# and run the data prep process as a batch job and once the data is prepared,
the user can run a stripped down version to populate the sheet

If we assume the code is optimised, that doesn't really leave us anything to say, as far as I can see.

One idea (I can't take credit, it goes to forum user darkyam):

instead of numerous calls to another database, might it be faster to copy the necessary section of data, post it in the current workbook, work from there, and then delete those pages when finished? Just an idea.

The answer is: post your workbook /code here.

Sorry NDA prevents that, legal issues, Besides, we are talking several hundred lines of code. Each sub assembly has manf. processes that occur equipment setup times, and various operator time as well, each sub assembly has 10 different cost models, each sub assembly has different countries of manufacture with different labor rates. It just would be practical for me to post all the code utilized to calculate all these parameters. Before I went to a user form model, the various run time parameters were entered in a worksheet. There wasn't any of these issues I'm seeing with a user form.

So PLEASE, I need general ideas on what setting should/could be made to excel or user form to minimize code run time issues. This stuff isn't rocket science...

Well, if you ring up your garage and tell them that your car is making funny noises, do you expect them to solve the problem without you bringing in the car? After all, fixing a car is not rocket science, either.

cheers,

Well, in that case use this

Please Login or Register  to view this content.

1) Turn off screen updating
2) Don't loop row by row doing the same thing, see if you can process the entire dataset all at once.
3) If looping is a must, reduce calls to the sheet by loading the data into an array, loop through the array, then put the array back onto the sheet... a total of 2 calls to the sheet even if the array was 50k values
4) Post parts of your code for others to examine and look for ways to optimize further.

Are you using asynchronous queries? That also might improve the performance on the front end (albeit requiring more code).