Code that Crashes Excel without fail - Excel 97 SR2 WinNT

[Matt Jensen]

Howdy all

I've been having a problem with Excel 97 crashing that I've been able to
narrow down through a process of elimination to the following cause (there
is no problem on WinXP Excel 2002 by the way):

Steps to produce a crash:

1) Create new workbook
2) Insert a Control Toolbar Checkbox on Sheet1.
3) In any cell on Sheet1, put something in a cell (so that when you choose
the Excel menu option 'Print Preview' it won't say 'nothing to print' and
will attempt to show a print preview) eg. type the letters 'text' in cell A1
of Sheet1.

4) Go to VBE, create new module, enter this code:

Sub ExampleCrash()
Worksheets("Sheet1").OLEObjects("Checkbox1").Delete
End Sub

5) Run Macro

6) Return to Excel

7) Hit Print Preview

Excel crashes WITHOUT FAIL for me on a variety of machines with Excel 97 SR2
on WinNT. It is a large organisation where I'm working - 100s to 1000s of
users with same PC setup (so therefore I have no control over what my users
will be running).

Firstly, does this cause Excel 97 to crash for anyone else?
Secondly, what is the problem? Is my code the problem - if so, how can I
change it to stop Excel crashing?
Any suggestions as to how I can change the code to still achieve the same
effect but not crash Excel? Eg. maybe force a save after the macro or some
other trick of the trade / "workaround" that might prevent Excel from
crashing?

Thanks a lot for any help or ideas - I'm not sure where to start!!
Cheers
Matt

 

[Peter T]

Hi again Matt,

You do keep coming up with some obscure things <g>.

Following your instructions I replicated your crash in xl97, several times.
Then I exited design mode and no more problems, I mean in further testing no
crashes no matter what state design mode - weird - yet again with your
topics!

In our previous conversation I said some things relating to controls in xl97
are best done in design mode. Though it turned out not relevant to that
issue I stand by it as a generalised statement. But now perhaps I should add
some things are better NOT done in design mode. I can no longer replicate
the problem / solution but try folowing (new session's of windows etc):

If Application.CommandBars("Exit Design Mode").Controls(1).State =
msoButtonDown Then
Application.CommandBars("Exit Design Mode").Controls(1).Execute
End If

Regards,
Peter T

 

[Matt Jensen]

Hey Peter
Yeah I know! But an Excel crash is not cool for any application so I had to
find out the cause (wonder if it was the only cause?!). And I try to only
come here for the hard questions!

Some workstations here crash only intermittently , but some others I can get
to crash on those steps without fail - nevertheless, obviously, the fact
that any crash is quite a concern though.

Probably obviously too, if I save and close and then reopen the file before
viewing the print preview, Excel does not crash. Also, the initial recurrent
problem occurs NOT in design mode by the way...

Also, I must admit, I'm rather dubious about programming an application to
enter/exit design mode - it just seems wrong...! Thoughts?

I tried the steps and for the workstations that crash every time, Excel
crashed for me every time whether in design mode or not (although I didn't
test this programmatically, just by pressing the design mode button myself).

I'll try just hiding rather than deleting the (matrix of) checkboxes and
also programmatically entering then exiting design mode after the offending
code too.

Thoughts?

Cheers
Matt

 

[Peter T]

I'll try and look into this for my own purposes later, hadn't come accross
this issue with Print Preview. In the meantime:

Also, I must admit, I'm rather dubious about programming an application to
enter/exit design mode - it just seems wrong...! Thoughts?

Intuitively I would agree. But if this aids a reliable solution, after
testing in every conceivable scenario, I guess it should be OK.

I'll try just hiding rather than deleting the (matrix of) checkboxes

If you are repeatedly creating and deleting similar OLEobjects I would
definately adopt the approach of hiding when needed. I think this is better
practice for all versions regardless of the current problem in xl97. Same
goes for any type of object, including shapes.

If your reason for hiding is merely to avoid printing, maybe:
MySheet.OLEObjects.PrintObject = False

Regards,
Peter

 

[Matt Jensen]

Thanks Peter

If you are repeatedly creating and deleting similar OLEobjects I would
definately adopt the approach of hiding when needed. I think this is better
practice for all versions regardless of the current problem in xl97. Same
goes for any type of object, including shapes.

I've got a (4-7) x 30 matrix of checkboxes (depending on a user selection it
can be 4 or 7 columns wide), and I've been deleting the extra ones when
resizing back to 4 from 7 but I guess the main reason for this was
ultimately for file size reasons. But now that I rebuilt in 97 and the file
size dropped by a 3rd though I guess hiding may suffice.
I'll keep you posted on my trial and tribulations

If your reason for hiding is merely to avoid printing, maybe:
MySheet.OLEObjects.PrintObject = False

Printing is sorted for OLEObjects thanks, although on this topic, how does
one stop a row from printing do you know? - just hide the row? Or is there a
'nicer' way?
Cheers
Matt

 

[Matt Jensen]

Hiding the checkbox and we're sorted...!!
Great.

Actually, just remember one trick of the trade I was wondering if existed
which'd be useful to know if it does...
=> I was wondering if, following this code:

Worksheets("Sheet1").OLEObjects("Checkbox1").Delete

if you could set something to Nothing or do some sort of clean up like that
that might have 'cleared' Excel's memory or something and hence not cause it
to crash on further operations such as Print Preview...?

?
Thanks
Matt

 

[Matt Jensen]

Hiding the checkbox and we're sorted...!!

Sorted on all workstations too btw.
Matt

 

[Peter T]

Actually, just remember one trick of the trade I was wondering if existed

which'd be useful to know if it does...
=> I was wondering if, following this code:

Worksheets("Sheet1").OLEObjects("Checkbox1").Delete

if you could set something to Nothing or do some sort of clean up like that
that might have 'cleared' Excel's memory or something and hence not cause it
to crash on further operations such as Print Preview...?

You can set an object variable = Nothing but not, AFAIK, an object itself. I
tried following yesterday but it didn't make any difference (not
surprisingly):
Dim obOLE As OLEObject
Set obOLE = Worksheets("Sheet1").OLEObjects("Checkbox1")
obOLE.Delete
Set obOLE = Nothing

also, obOLE.PrintObject = False before the Delete doesn't seem to help.

Earlier today I was getting intermittent crashes, you say as well. I assume
this is due to some yet to be discovered consistent situation, rather than
random. If so hopefully the issue can be resolved.

Re your adjacent post

I've got a (4-7) x 30 matrix of checkboxes (depending on a user selection it
can be 4 or 7 columns wide), and I've been deleting the extra ones when
resizing back to 4 from 7 but I guess the main reason for this was
ultimately for file size reasons. But now that I rebuilt in 97 and the file
size dropped by a 3rd though I guess hiding may suffice.

I wouldn't think the difference of 3x30 controls would make a significant
difference in file size, relative to what I assume is already a fairly large
file. How are you trapping events for all these, individually or as a Class.

Regards,
Peter T

 

[Peter T]

Forgot about this when just replying:

Printing is sorted for OLEObjects thanks, although on this topic, how does
one stop a row from printing do you know? - just hide the row? Or is there a
'nicer' way?

You can format font white & no fill, reset when done. It might mean first
paste-special formats to a hidden sheet and back when done. Wouldn't say
that's a "nicer way" but it preserves that overall layout.

Regards,
Peter T

 

[Matt Jensen]

Hey Peter
Re checkbox events, I don't really use them, I've just got them linked to a
corresponding row/column in a separate sheet which stores their value, no
need for events luckily. Apart from the fact that I'm not competent enough
to create classes yet. I don't really understand them actually - I do but I
haven't seen an use for them yet, although if I understood them more maybe I
would use them more often...!
The file is only 550KB or so at the moment after I rebuilt in 97 (was
1.5MB), although it seems to increase in file size with every save, even it
you just open then save then close...!
On a slightly different but related note, how do prevent printing of cell
data - is there a row.PrintObject = False method or something, or do I have
to hide/change colours etc. or something?
Matt

 

[Matt Jensen]

I've been able to narrow it down even further would you believe (by
accident)

1) New workbook
2) type 'text' in cell a1 of sheet1
3) on sheet1, insert any Control Toolbar checkbox (happens for some other
controls too), then delete it immediately
Print Preview => Kaboom!!


Matt

 

[Peter T]

Hey Peter
Re checkbox events, I don't really use them, I've just got them linked to a
corresponding row/column in a separate sheet which stores their value, no
need for events luckily. Apart from the fact that I'm not competent enough
to create classes yet. I don't really understand them actually - I do but I
haven't seen an use for them yet, although if I understood them more maybe I
would use them more often...!

Sure, if the value of the linked cell is used only in worksheet formulas you
wouldn't need events.

Stephen Bullen recently posted an example of trapping events of similar
controls in a class here:
Subject: "running same code with multiple controls" Jan 2005
http://tinyurl.com/4jp2v

Stephen's example was with Labels but easily changed to Checkboxes. In the
class click event could do something like:
Set rng = Range(With-events-variable-name-of-checkbox.LinkedCell)
and then do all sorts of things.

The file is only 550KB or so at the moment after I rebuilt in 97 (was
1.5MB), although it seems to increase in file size with every save, even it
you just open then save then close...!

This could be due to reworking your code. Rob Bovey's Code Cleaner is
universally recommended:
http://www.appspro.com/

On a slightly different but related note, how do prevent printing of cell
data - is there a row.PrintObject = False method or something, or do I have
to hide/change colours etc. or something?

I posted some comments yesterday to another part of the thread.

Regards,
Peter T

 

[Matt Jensen]

Hey Peter
Thanks for this.
Been thinking about this some more, think my understanding of classes is
slowly improving - effectively, one is 'class'ifying a group of elements
with the intent to treat, particularly events, them all as doing the same
thing - is this right?

Part of my inability to understand classes I think is due to the fact that I
thought that surely when say a control toolbox checkbox event occurred that
you could call a procedure and the procedure would be able to know, among
other things, the name of the checkbox calling it eg. by using 'this.' or
'me.' but from what I can tell this is not the case with VBA?

However, I guess I can see the advantage of "classing" elements as part of
the same class and associating particular events with this class, and hence
modularising application objects and keeping them distinctly separate from
others.

Is this understanding right?

Matt

 

[Peter T]

You gave a better intro to class'ing control events than I could have
written, had you not said otherwise I would assume you already an expert!

You hinted at not being quite sure about how to know which control is
triggering the event, I'm no expert on classes (certainly not in the company
of Jamie C) but maybe this nonsense example will demonstrate a few things:

Manually create three checkboxes on Sheet1 with linked-cells A1, A2 & A3
(don't cover cell E10)

Code in a class named clsCheckBoxEvnts and normal module as indicated -

''start Class named clsCheckBoxEvnts
Option Explicit
Public WithEvents ChBox As MSForms.CheckBox
Private appVer As Long
Private sTrick As String

Private Sub ChBox_Change()
Dim rng As Range, cx As Long, s As String, i As Long
With ChBox
Set rng = .Parent.Range(.LinkedCell) _
.Offset(0, 1).Resize(1, 4)
If .Value Then
.Caption = .Index & " " & MyString
cx = .Index + 24
Else
.Caption = .Name
cx = xlNone
End If

If gcVars.some_var = 8 Then xl97fix
rng.Interior.ColorIndex = cx
For i = 1 To UBound(gaCBs)
If gaCBs(i).ChBox.Value Then
'compare ".Caption = MyString" above and next line
s = s & gaCBs(i).MyString & " "
End If
Next
If s = "" Then s = gcVars.MyString
.Parent.Range("e10") = s
End With
End Sub

Public Property Let MyString(str As String)
sTrick = str
End Property
Property Get MyString() As String
MyString = sTrick
End Property
Public Property Let some_var(n As Long)
appVer = n
End Property
Property Get some_var() As Long
some_var = appVer
End Property
Private Sub xl97fix()
'in xl97, if checkbox has focus can error
'if changing cell formats (but not values), very odd!
On Error GoTo done
'prevent any selection change events
Application.EnableEvents = False
If Intersect(Windows(1).VisibleRange, ActiveCell) Is Nothing Then
Windows(1).VisibleRange(1, 1).Activate
Else
ActiveCell.Activate
End If
done:
Application.EnableEvents = True
End Sub

Private Sub Class_Terminate()
ChBox.Enabled = False
End Sub
''end code in clsCheckBoxEvnts

'''''''''''''''''''''''''''''''''''''''''''''''
''start code in normal module
Option Explicit
Public gaCBs() As New clsCheckBoxEvnts
Public gcVars As New clsCheckBoxEvnts

Sub Setup()
'called by [say] wb-activate
Dim i As Long
Dim obOLE As OLEObject
Dim va
va = Array("Rabbit", "Hat", "Magic")
For Each obOLE In Worksheets("Sheet1").OLEObjects
If TypeOf obOLE.Object Is MSForms.CheckBox Then
i = i + 1
obOLE.Object.Enabled = True
ReDim Preserve gaCBs(1 To i)
Set gaCBs(i).ChBox = obOLE.Object
gaCBs(i).MyVar = va(i - 1)
End If
Next

gcVars.MyString = "No tricks"
gcVars.some_var = CLng(Val(Application.Version))
setCBoxes False
End Sub
Private Sub setCBoxes(bVal As Boolean)
Dim ob As Object, i As Long
For i = 1 To UBound(gaCBs)
gaCBs(i).ChBox.Value = bVal
Next
End Sub
Sub Clearup()
'called by say wb deactivate
Erase gaCBs
End Sub
''end code in normal module

In real life code I can't imagine ever using the "array" method of holding
the controls with an unknown qty using redim preserve. Typically the array
method is used fully dimensioned to hold a known number of similar controls
on a userform. Use the "collection" method (see previous link to Stephen
Bullen's example).

Also, wouldn't store the app-version variable this way.

As you've had so many problems with controls in xl97 look at the "xl97fix"
routine, do you find you need it?

Regards,
Peter T