VBA Troubles

[tlmorris]

We are having an interesting macro problem and I can’t seem to figure out what the problem is. I am hoping that someone here may have an idea and tell me what to look for.

Here’s the situation: we have a fairly intensive macro that we use to run daily reports.

How it works: we open an input file and two macro files open in the background, hidden. We enter data each day either manually or via importing another excel spreadsheet via a form. Once we enter it in the form, the macro automatically enters the data on the proper page in the input file.

We run the report from another from. We simply click a “report” button and it builds a new workbook: copying some pages from the input file, building other pages, and moving data from the input file to the proper place on the new workbook.

Now for the problem: sometimes the file will “fry”. When this happens, when we try to import the numbers via another excel spreadsheet, the numbers don’t immediately show up on the input screen (but do get sent to the other page they are supposed to go to) and the bigger issue – not all of the data gets entered correctly into the new report (workbook) when we run the report. Another thing I have noticed, when the report is building, I don’t see the toolbars at the top of the page (instead we see a white bar) – when the pages are all done, the toolbars do come back.

Other observations: if I take a file two days old and re-enter the data, then it will run fine – if I do it in the previous days input file, it will still fry. Once a file is fried, it will no longer work at all. I can however copy all of the pages into a new input file and then it will work. This happens on computers running both XP and 2003. It happens randomly to different files.

Also - I do not get any error messages when this happens. I can only tell it did by the way the file looks when done.

We have had different programmers adding/working on this macro and they can’t seem to find the problem.

Anyone have any ideas? Need more information?

Thanks you ever so much!

Tammy

 

[Norie]

Any chance of posting the code?

Is there a particular part of the code causing the problem?

Are you getting any error messages?

 

[tlmorris]

Thanks for the reply. I would post the code but all of it is over 1000 pages long.

I have no idea what area of the code is causing the problem and I get no error messages.

Note sure if this helps but, we use this macro with many different input files. An example of a typical night when this happens. File A and B run perfectly fine. File C fries (and these are all being ran with the same macro). I can make a copy of File A paste all of the data in file C onto that copy and the thing will then run fine! The next night it might be file B that fries...

As a side note - is there a VBA editor you recomment using (other than the one that comes with Office)?

 

[Norie]

Tammy

A 1000 pages of code?

Could you perhaps post an (small) example of the code you are using?

As a side note, no as far as I know there are no other VBA editors out there, though I may be wrong.

I imagine if you wanted to use such a thing it would involve an Add-In.

I've used the standard editor in Excel/Word/Powerpoint/Access and VB6, never had any real problems with any of them.

 

[tlmorris]

Yeah - there is code all over the place in this.

We have some code in the input file itself and then there is the macro file that auto opens when we open the input.

Since the macro file works fine on other input files and the input file wilkl work again when I make a new one with that data...would iot stand to reason that there is something in the macros on the original file that is causing the problem (and perhaps stopping things in the macro file from operating properly?

I can paste the macros from the input file for you - there isn't too much in it. It's just that the macro file has so many different subs and codes on various forms, I wouldn't know where to start.

Thanks again!

Tammy