Excel 2003 error, looking for potential causes

[mialls]

When a simple macro is run, often the screen will flash as it works. And when the macro is running, you can't make any other changes to Excel until the macro has finished running.

My problem is this : at random times, an excel file that I'm working on will start to flash as if a Macro is running, and it will not stop. Occassionally, I can cause it to select a cell, which stops the screen from flashing. And I can make changes to that single cell. I can also close the program with the X's, and can save information. For both of those to happen I need to rapidly click my mouse until it seems to slip in between whatever commands Excel seems to be repeating.

This happens mostly when Maros are enabled, but has happened on occasion when they are not enabled. There is no way to intentionally trigger it - it is completely random. For a time, this would happen whenever I ran a macro in the workbook, but that no longer is guaranteed to cause this to happen.

Is this a known issue, and if so, is there a potential cause and/or solution?

 

[p45cal]

There are likely to event-triggered macros in the workbook. In addition the macros probably do a lot of selecting while running (which is rarely needed).
You could probably do with the addition of lines such as
Application.screenupdating =False
Application.screenupdating =True
and if one event triggered macro triggers another, or even itself, this could cause such behaviour, since you might end up with an infinite loop. You may need to look at stopping that perhaps with Application.EnableEvents = False and Application.EnableEvents = True in places (or using other methods).

This would not be a bug, just not the best programming!
I'd really need to see the code.

 

[tweedle]

When it flickers hit CTRL+Break.
That should stop it in whatever macro is running and show you the VBA Code window.
From that, one can decipher if the code is in a single workbok, or in a personal.xl? file. In any case; it will be a start to discovering the root cause.

 

[mialls]

There are likely to event-triggered macros in the workbook. In addition the macros probably do a lot of selecting while running (which is rarely needed).
You could probably do with the addition of lines such as
Application.screenupdating =False
Application.screenupdating =True
and if one event triggered macro triggers another, or even itself, this could cause such behaviour, since you might end up with an infinite loop. You may need to look at stopping that perhaps with Application.EnableEvents = False and Application.EnableEvents = True in places (or using other methods).

This would not be a bug, just not the best programming!
I'd really need to see the code.

Quite likely not the best programming. Essentially there are two different codes in the workbook currently; one to copy and paste from one area to another, and one to clear the new area.

Code 1:

Application.Goto Reference:="A_1"
Selection.Copy
Application.Goto Reference:="B_1"
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
:=False, Transpose:=False
...
Application.Goto Reference:="A_100"
Selection.Copy
Application.Goto Reference:="B_100"
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
:=False, Transpose:=False
Application.ScreenUpdating = False (added after the problem began)

Code 2 :

Application.Goto Reference:="B_1"
Selection.ClearContents
...
Application.Goto Reference:="B_100"
Selection.ClearContents
Application.ScreenUpdating = False (added after the problem began)

If there is also a way to clean up the code so that 100 Named Ranges or Cells can be copied and pasted, and the area it has been pasted to can be cleared, I'm all ears. Or eyes, as the case may be.

Essentially what needs to be done is for information to be extracted from one worksheet (which is huge; 800+ rows and 200 +/- columns) so it can be easily viewed, then if edits are needed, a new line is added with updated information. My current plan is to copy the information from area A, paste it to Area B, where it can be edited, then copied from Area B to Area C. Seems simple enough, but it's been a headache so far.

 

[mialls]

When it flickers hit CTRL+Break.
That should stop it in whatever macro is running and show you the VBA Code window.
From that, one can decipher if the code is in a single workbok, or in a personal.xl? file. In any case; it will be a start to discovering the root cause.

Thanks for that tip - the next time the workbook acts up, I'll try that and hopefully it will help get to the root of this problem.

 

[tweedle]

What Subs does this code live in?
i.e. Private Sub Workbook_SheetChange ?

 

[mialls]

What Subs does this code live in?
i.e. Private Sub Workbook_SheetChange ?

I don't think I quite understand the question; how would I find the answer for you? Looking at its location in the VBA window, it is in:

VBAProject (TheFilename)
-Modules
--Code1
--Code2

And it is not found when I open any other file.

 

[p45cal]

Try right-clicking on the sheet's tab and choosing view code…, is there any code there. And/Or what is the first line of each macro?

Additionally, are the range names all single cells? If so the likes of:

Application.Goto Reference:="A_1"
Selection.Copy
Application.Goto Reference:="B_1"
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
:=False, Transpose:=False

can be replaced with:

Range("B_1").value=Range("A_1").value

You'd get away with the above code if the corresponding ranges were multicellular, contained only one area (no non-contiguous ranges) and they were the same size.
If the A_1 and B_1 corresponded, as well as say A_100 and B_100 corresponded, and you've used the same naming convention for many of the ranges, we could use a loop to run through them.

I don't think I quite understand the question; how would I find the answer for you? Looking at its location in the VBA window, it is in:

VBAProject (TheFilename)
-Modules
--Code1
--Code2

And it is not found when I open any other file.