#1   Report Post  
Bill Martin
 
Posts: n/a
Default Macro Suddenly Fails

I have a large macro I wrote, and which I've used for some time. It takes a
string of characters and parses it into separate cells using some arcane rules.
Anyhow, it worked when I used it last week, but this week it suddenly does a
little bit and quits with no errors. After poking around the code I've come to
the point that I know exactly *where* it fails, but I have no idea *why* -- and
why not last week. Here's the relevant code fragment:


' Lots of stuff above here that all works.

Call GetNbrRight(X, DataIn)
MsgBox "Exited Call with: " & X

' Things are still working at this point.
' The message box above shows the proper X value

ActiveCell.Offset(0, 9) = X

' The line above works ok -- the X value shows up in the correct cell

MsgBox "Why doesn't this execute?"

' But after the ActiveCell = statement above, the macro stops and never
' does execute the MsgBox statement -- or the whole rest of the macro.


When the macro stops it just appears to be a normal completion and return to the
spreadsheet. But the last half of the macro never did execute.

After banging my head on this in various ways I even rebooted the computer and
restarted Excel cleanly. No difference. I tried assigning a different Ctrl key
to it which of course made no difference. I've tried other input strings to be
parsed thinking that perhaps it was somehow data sensitive, but it made no
difference. Various other things.

I am stumped. Any ideas...?

Bill
  #2   Report Post  
Norman Jones
 
Posts: n/a
Default

Hi Bill,

Is there an operative error handler?

If you step (F8) through the code, which line is highlighted immediately
after:

ActiveCell.Offset(0, 9) = X


?


---
Regards,
Norman



"Bill Martin" wrote in message
...
I have a large macro I wrote, and which I've used for some time. It takes
a string of characters and parses it into separate cells using some arcane
rules. Anyhow, it worked when I used it last week, but this week it
suddenly does a little bit and quits with no errors. After poking around
the code I've come to the point that I know exactly *where* it fails, but I
have no idea *why* -- and why not last week. Here's the relevant code
fragment:


' Lots of stuff above here that all works.

Call GetNbrRight(X, DataIn)
MsgBox "Exited Call with: " & X

' Things are still working at this point.
' The message box above shows the proper X value

ActiveCell.Offset(0, 9) = X

' The line above works ok -- the X value shows up in the correct cell

MsgBox "Why doesn't this execute?"

' But after the ActiveCell = statement above, the macro stops and
never
' does execute the MsgBox statement -- or the whole rest of the macro.


When the macro stops it just appears to be a normal completion and return
to the spreadsheet. But the last half of the macro never did execute.

After banging my head on this in various ways I even rebooted the computer
and restarted Excel cleanly. No difference. I tried assigning a
different Ctrl key to it which of course made no difference. I've tried
other input strings to be parsed thinking that perhaps it was somehow data
sensitive, but it made no difference. Various other things.

I am stumped. Any ideas...?

Bill



  #3   Report Post  
Bill Martin
 
Posts: n/a
Default

My problem turned out to be .... ME! When the X value was placed into the cell,
that caused some normal spreadsheet formula elsewhere to generate a Div/0 error
-- which would have been eliminated if the macro had proceeded to the next step.
In the macro I simply turned off recalc at the top and all is well with the
world.

The only mystery now is why it's never failed up to now? I'm not going to lose
much sleep over that one though. Recalc should have been off anyhow just to
speed things up if nothing else.

You were pretty much right on the mark! Thanks...

Bill
--------------------
Norman Jones wrote:
Hi Bill,

Is there an operative error handler?

If you step (F8) through the code, which line is highlighted immediately
after:


ActiveCell.Offset(0, 9) = X



?


---
Regards,
Norman



"Bill Martin" wrote in message
...

I have a large macro I wrote, and which I've used for some time. It takes
a string of characters and parses it into separate cells using some arcane
rules. Anyhow, it worked when I used it last week, but this week it
suddenly does a little bit and quits with no errors. After poking around
the code I've come to the point that I know exactly *where* it fails, but I
have no idea *why* -- and why not last week. Here's the relevant code
fragment:


' Lots of stuff above here that all works.

Call GetNbrRight(X, DataIn)
MsgBox "Exited Call with: " & X

' Things are still working at this point.
' The message box above shows the proper X value

ActiveCell.Offset(0, 9) = X

' The line above works ok -- the X value shows up in the correct cell

MsgBox "Why doesn't this execute?"

' But after the ActiveCell = statement above, the macro stops and
never
' does execute the MsgBox statement -- or the whole rest of the macro.


When the macro stops it just appears to be a normal completion and return
to the spreadsheet. But the last half of the macro never did execute.

After banging my head on this in various ways I even rebooted the computer
and restarted Excel cleanly. No difference. I tried assigning a
different Ctrl key to it which of course made no difference. I've tried
other input strings to be parsed thinking that perhaps it was somehow data
sensitive, but it made no difference. Various other things.

I am stumped. Any ideas...?

Bill




Reply
Thread Tools Search this Thread
Search this Thread:

Advanced Search
Display Modes

Posting Rules

Smilies are On
[IMG] code is On
HTML code is Off
Trackbacks are On
Pingbacks are On
Refbacks are On


Similar Threads
Thread Thread Starter Forum Replies Last Post
Button fails to call macro when open an Excel via Intranet tigertax Excel Discussion (Misc queries) 1 April 12th 05 11:21 AM
.ONACTION macro call fails Wayne Excel Discussion (Misc queries) 2 March 2nd 05 06:10 PM
Playing a macro from another workbook Jim Excel Discussion (Misc queries) 1 February 23rd 05 11:12 PM
Date macro Hiking Excel Discussion (Misc queries) 9 February 3rd 05 01:40 AM
Macro suddenly causes the screen to blink Top Spin New Users to Excel 3 November 29th 04 03:07 AM


All times are GMT +1. The time now is 12:46 AM.

Powered by vBulletin® Copyright ©2000 - 2025, Jelsoft Enterprises Ltd.
Copyright ©2004-2025 ExcelBanter.
The comments are property of their posters.
 

About Us

"It's about Microsoft Excel"