Error than Aborted Excel

[Neal Zimm]

Hi All,

sHold = sHold & sColData & Space(MaxLenAy(iSortOrder) - Len(sColData))
Debug.Print sHold
sHold = ""

The lines above are part of a proc that prints evenly 'lined-up' columns.
Due to a bug, the Space math worked out to -2. The bug is now fixed,

BUT, Excel ENDED with the "Excel has a problem and has to close..."
display and my files were recovered. (No On Error .... code was in
place at the time.)

1. There was no run-time error. Why ?

When I extracted parts of the code to debug it, in testing the fix
I got a run- time error with a negative number.

2. Without putting all the code here, can you tell me what might be
the difference between getting a run-time error or not ?

3. Do you know of any other "common" uses of VBA that will abort Excel
without a run-time error so I can take special care with them?

Thanks,
Neal

 

[Barb Reinhardt]

If you had an error on that line, I'd check the following:

1) How are the variables declared
2) Break apart the line to see where it's having an error

 

[Neal Zimm]

Hi Barb,
I guess I must learn to write clearer questions.
I know exactly why the line failed, and I have ALREADY fixed that.
It was bad logic, not shown in the post, that resulted in the Space verb
having a value of -2 due to bad values in the expressions that were used.

To restate my questions: (I'd appreciate it if you'd tell me if my
original posting was unclear after you re-read it. Thanks.)

1. I would have expected to get a run time error on: Space( with a neg#
here)
I did not, Excel aborted

Can you tell me WHY there was not a run time error?

2. In debugging the line I extracted it from a larger Sub, and in
playing around with it, forcing negative values in Space( a neg#) I DID get
a run time error when in the original Sub, excel aborted.

This seemed strange. Can you tell me "what" to look for where the
same bad code sometimes aborts Excel and sometimes gets a run-time error?

3. Are there other "common" verbs where if you get an error, Excel
aborts rather than giving you a run-time error? I'd like to pay special
attention to those.
I kinda already know about Space(-2) will either abort excel or give me a
run time error. The difference between the abort and the run time error is
what I am look for.

Thanks again,
Neal

 

[Peter T]

It's not possible to tell you why your Excel aborted with the limited
information you provided.

s = Space(-2)
error - Object doesn't support this property or method

A simple error, that's all

There might be all sorts of reasons your Excel crashed, impossible to say
without knowing what else is going on, or possibly only after seeing your
file. FWIW Excel 97 was particularly prone to giving up with a combination
of unhandled errors whilst holding on to some objects. Maybe your project is
simply corrupted. If you can recreate the abort that'd be useful.

Regards,
Peter T

 

[Dave Peterson]

Excel shouldn't have crashed with this type of error.

And I bet if you do a couple of tests, it won't.

Try building a new workbook with just enough test data to run your code. Copy
over just enough code (with the error) and run it.

I bet excel warned you and didn't crash.

Now try it again with the old workbook (re-introduce the error first). Does it
still crash?

If it does crash, then there's something wrong with your workbook or excel. If
the smaller test workbook didn't crash, it leads me to believe that something is
wrong with your old workbook.

Maybe cleaning the code with Rob Bovey's code cleaner would help:
You can find it here:
http://www.appspro.com/
or directly
http://www.appspro.com/Utilities/CodeCleaner.htm

Or maybe there's a corrupted worksheet (or more) that needs to be rebuilt????

But excel shouldn't crash on a bug like this.

 

[Neal Zimm]

Thanks Peter, I'll give it a try. It's a comfort to know that there's
something else "going on" to cause the crash.

 

[Neal Zimm]

Thanks Dave,
I use Bovey's cleaner pretty regularly.
It's a comfort to know that it should not crash, I'll try your method and
see what happens. Thanks again.