Macro Suddenly Fails

[Bill Martin]

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

[Norman Jones]

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" <wylie@earthlink.net> wrote in message
news:OY%23Y6cnuFHA.1132@TK2MSFTNGP10.phx.gbl...
>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

[Bill Martin]

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" <wylie@earthlink.net> wrote in message
> news:OY%23Y6cnuFHA.1132@TK2MSFTNGP10.phx.gbl...
>
>>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
>
>
>