Macro won't execute when "For Each 'range' in 'range'" is added
 

Hi:

I am using XL 97 at work. On the weekend I did some work at home on
the file in XL 2000. When I came into work on Monday morning
everything worked fine in XL 97 except for the annoying message on
saving that "it was created in in a higher version and I could lose
some features by saving in XL 97". I get this message even after I
have saved the file in XL 97.

In any case, my more serious problem is as follows. In the VBE I have
the following code fragment:

Sub Etc()
Dim c as Range
'For Each c In Range ("AmortRng_Dates")
MsgBox "Got up to here."
....
'Next

If I run the macro as is the MsgBox pops up.
The problem is that if I uncomment the For Each ... Next construct the
macro doesn't run. No error messages. It simply does not run. The
MsgBox never pops up.

I have an addin that I created installed with some udf's. After
reading some posts here on udf problems stopping macro execution, I
entered On Error Resume Next in all my udf functions. The problem
still persists.

This problem seemed to start after I saved in XL 97, although I can't
be sure. In other words this code seemed to work when I first started
using it with XL 97 at work.

The same problem results when the VBE is closed and I run the macro
from the worksheet.

Could there be something wrong Tools-References?

Any help would be much appreciated.

Regards,
Bill Dika

Macro won't execute when "For Each 'range' in 'range'" is added
 

Hi Bill,

"Bill Dika" wrote in message
om...
Hi:

<snip
In any case, my more serious problem is as follows. In the VBE I have
the following code fragment:

Sub Etc()
Dim c as Range
'For Each c In Range ("AmortRng_Dates")
MsgBox "Got up to here."
....
'Next

If I run the macro as is the MsgBox pops up.
The problem is that if I uncomment the For Each ... Next construct the
macro doesn't run. No error messages. It simply does not run. The
MsgBox never pops up.

Can't see anything obviously wrong with that code. If you have any error
handling in the routine, comment it out (that way you will see the error
message describing what is wrong).

Put a breakpoint on the For Each line (F9) and when the program breaks go to
the Immediate Window (Ctrl G if it is not visible) and type in
?Range("AmortRng_Dates").Address This will show you what the Range is
referring to. You may need to qualify your Range with workbook/worksheet.

I have an addin that I created installed with some udf's. After
reading some posts here on udf problems stopping macro execution, I
entered On Error Resume Next in all my udf functions. The problem
still persists.

There is a bug in XL97 relating to UDF's, but it is subtle, and unlikely to
be a factor here. FYI the bug is that if a macro does something which
triggers a recalc (e.g. changing the value of a cell) *and* if that change
causes a UDF to be recalculated *and* if that UDF generates an unhandled
run-time error (e.g. divide by 0) *then* the macro will terminate
"unexpectedly". BTW this is fixed in XL2000.

However your code snippet doesn't indicate that you are doing anything
likely to activate that bug.

This problem seemed to start after I saved in XL 97, although I can't
be sure. In other words this code seemed to work when I first started
using it with XL 97 at work.

The same problem results when the VBE is closed and I run the macro
from the worksheet.

Could there be something wrong Tools-References?

Well, does Tools/References show that anything is "Missing"?

Sorry not to have a more precise solution.

Regards,

Peter Beach