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

[Bill Dika]

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

 

[Peter Beach]

Hi Bill,

Hi:

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

 

[Bill Dika]

Thanks for your quick reply Peter.

The problem went away after I quit XL97 and then started it up again. I
had tried closing the file and reopening it (with no luck) but didn't
think to exit XL (dah???).

Thanks again Peter, I really do appreciate the time you took to read,
think about and respond to my problem. Hopefuly next time the obvious
step of just restarting XL won't elude me.

Many thanks.

Regards,
Bill Dika



*** Sent via Devdex http://www.devdex.com ***
Don't just participate in USENET...get rewarded for it!