VBA and Excel Macro's not mixing well

[larissalle]

I have 30 or so pages of VBA code that is stepping through one to four separate excel files that contain raw data. The VBA code opens, saves, and closes the other excel files, and manipulates the raw data (extracts, imports, and plots/reports the revised data). The VBA code is accessed by opening an excel file that serves as the user-interface. The user (me) clicks buttons that are assigned to the functions in the VBA code. In the excel raw data files, I have buttons assigned to macro's for sorting, filtering, and hiding columns. When I have the VBA code and the raw data excel files open at the same time:

1) If I click a button in the VBA excel user-interface file, sometimes, not always, my VBA code seems to hang up because it gets lost between the VBA code and the excel macro's in the excel raw data files. After 10 minutes, the VBA code finally runs after it seems to have run through all the macro code in the other open files and eventually finds the function that button was assigned to.

2) Alternately, if I click one of the buttons assigned to the macro's in the excel raw data files, excel goes off into the VBA code instead, sometimes eventually running and sometimes ending in debug mode where excel has interestingly ended in one of the VBA code functions. If I choose "End" and go back to the excel file, I can click the excel macro button again, and this time it will run. I have tried ensuring that I first click within the excel raw data file to begin with, but it doesn't make a difference.

3) Sometimes, if I close all files and then open just the raw data files, even if I don't have the VBA user-interface file open, the raw data file sometimes takes 10 minutes to open, seeming to be in search of the VBA code before it gives up and opens up.

I am running Windows 2000, Excel 2000 (VBA included). Is there some VBA/Excel patch for this? Anyone else have a problem with this?

 

[larissalle]

No response received on this thread. But, I did figure out some of the problem, in case anyone is interested. It had to do with a bug in Excel 2000 where .emf files were not being deleted automatically. We had tens of thousands of .emf files in the temp directory created each time a button in the user interface file was clicked. Caused out of memory errors and long time to open excel files.

Another part of the problem was the way the VBA code was saving the individual files as it opened and closed them. We streamlined this, and the system worked much better with almost no crashes.

A additional problem was that widespread use of local variables was not allowing excel to free up memory. Still working on this one.