Just having App_WorkbookOpen crashes Excel

[Aaron Fude]

Hi,

I have workbook with an EventClass whose contents are included below. All
methods are blank. However, just having those blank templates causes Excel
to crash (with 100% consistency) when I try to open a text file a.txt with
the following contents:
1 2
3 4
5 6

I get to tell excel how to parse it (Fixed width or Delimited) but when I
click Finish, the crash occurs.

If the workbook containing this event class is not open, no crash occurs.
Opening an excel workbook rather than a parsable text file is no problem.

Thank you so much for helping me.

Aaron

Option Explicit

Public WithEvents App As Application
Private Sub App_NewWorkbook(ByVal wb As Excel.Workbook)
End Sub
Private Sub App_SheetActivate(ByVal Sh As Object)
End Sub
Private Sub App_WorkbookOpen(ByVal inWB As Excel.Workbook)
End Sub

 

[Tushar Mehta]

XL2003 works just fine. I even went to the extent of instantiating an
object of the class and initializing the App object with the code below
(in a standard module):

Option Explicit
Dim x As Class1
Sub testIt()
Set x = New Class1
Set x.App = Application
End Sub
Sub resetIt()
Set x = Nothing
End Sub

Basically, I ran the testIt code, then opened the text file.
Everything worked just OK.

--
Regards,

Tushar Mehta
www.tushar-mehta.com
Excel, PowerPoint, and VBA add-ins, tutorials
Custom MS Office productivity solutions

 

[Bura Tino]

Yes, I have 2002, but I'm no longer sure it's that class that causes the
crash. I have deleted the class but the crashes remain. Maybe I didn't
delete it completely, but I'll change my question slightly. If crashes that
I described occur when a certain book is open, what kinds of thinks should I
look for in that workbook?

Aaron

 

[steveb]

Some things that caused crashes for me (in the past)
1. Modules and macros having the same name.
1a. Duplicate modules or duplicate macros!!!!
2. Any name used as a named range, module name, macro name, variable name,
etc... that is also a reserved word for excel. Examples: Range, Sheet,
Cells, Lookup, etc... (I check all of mine using the F1 key and hope that
it comes up with 'Keyword Not Found')
3. Declaring a variable as public and than also declaring the same variable
as Dim or Static in a module.
4. In the extreme - too many modules and/or macros

Folders that fill up and cause problems with Excel (clean these
frequently - dependent on your usage)
4. Letting files 'pile' up in my Internet Explorer (use Tools > Delete
Files to clean this up).
5. Temp folders that are too full.
6. Trash bin too full.

hth

 

[Tushar Mehta]

There is some anecdotal evidence that VBE/VBA doesn't clean up 'stuff'
in a workbook that contains code. Eventually, this 'stuff' can lead to
workbook corruption and problems that cannot be explained. One way to
fix the problem is to export all modules to flat files, delete all
modules, and reimport all the modules. Rob Bovey's CodeCleaner
(www.appspro.com) automates the process. An alternative I use on
occasion is to create a *new* workbook, create the same modules as in
the old workbook, and for each of them copy+paste the contents.

--
Regards,

Tushar Mehta
www.tushar-mehta.com
Multi-disciplinary business expertise
+ Technology skills
= Optimal solution to your business problem
Recipient Microsoft MVP award 2000-2004