Random corruption in Excel files

[Guest]

I have coauthored an Excel file with many macros. After I use it a while, it
crashes in random places with "Microsoft Office Excel has encountered a
problem and needs to close..." The error signatures are in vbe6.dll.

The problem seems to be cumulative and associated with running macros.
Indeed, I can temporarily fix the problem by opening the file with macros
disabled, saving it, and reopening with macros enabled. However, it keeps
getting less and less stable the longer it is used, so it appears to be some
kind of accumulating corruption. Eventually, I have to create a new workbook
and manually copy all of the macros over to the new workbook, etc., which
works for a while. Many of my users are experiencing the same phenomenon.

Has anyone else experienced this kind of random corruption associated with
macros? The file is available for public downloading at
www.japaneselearningtool.com.

 

[Guest]

Sorry, typo. URL is www.japaneselearningtools.com

 

[Jim Cone]

You may have problems getting anyone to download and use
a file that is corrupted and crashes your computer.
--
Jim Cone
San Francisco, USA
http://www.realezsites.com/bus/primitivesoftware



"mps" <[email protected]>
wrote in message
Sorry, typo. URL is www.japaneselearningtools.com

 

[Guest]

It doesn't crash the computer. Excel sometimes crashes when running its
macros. What are you suggesting the risk is?

 

[NickHK]

I had a quick look, but with all those forms, WS, modules and classes, I
don't have to check it all.
If you can be more specific, I'll have another look.

You do have a couple of problems :
- "Rebuild All Kanji" menu routine, as I get only 2 rows of characters, with
all rows' height set 254 pixels. Excel XP.
- use the "Kanji Info" menu routine then results in a "Subscript out of
range" error. "Start Test" also errors
- "Start nonaffect Text" menu routine errors with
........NonAffectingTextButton_Click cannot be found

NickHK

 

[Guest]

Hi Nick,
Don't worry about "Rebuild All Kanji" and "Start non-affecting tests". Those
deprecated commands have mundane VBA errors.

I put up a particularly unstable version at
http://www.japaneselearningtools.com/crash.zip. This crashes Excel for me
just by opening the file (with macros enabled) and pressing the save button.
On different machines, it fails differently at different times, but if you
try saving, using "Show Kanji Info" and run a test via "Start Test" (just
enter any text if you don't know Japanese), it will probably crash Excel
pretty soon (not just give a VBA error).

Thanks for looking at this,

Mike

PS. I apologize if this is a double post, but my previous post command
failed, so I don't know if this will be a duplicate.

 

[NickHK]

I have noticed you are not removing the Toolbar you install upon opening.

Excel XP & Excel 2000:
This version now gives Update Links request upon opening
Opening this version does not crash Excel, but if I drag the floating
toolbar around, I "erase" the visible cell and see the cell "underneath".
The tabs are indicating "Words" is the activesheet, but it is 'All Kanji"
that is "visible" and erased. After a save and reopen, this does not happen
again.

After a test via "Start Test", saving does not crash, but errors on the
SaveTest routine with sub/function not found for "correctWords".

I have noticed you have numerous basic error :
Missing End If
Missing End Function
Can't assign to array

These found with Debug>Compile VBAProject. You should fix these first

NickHK

 

[Guest]

Hi Nick,
Thanks for suggesting Debug/Compile VBA Project. Very useful.

A couple of questions from what it shows:

1. I can't figure out why I'm getting the "Can't Assign to Array" error
because the array I'm assigning to is resizable, which is supposed to be OK.
Can you look at that?

2. I didn't realize that I should remove the attached toolbar. When/how/why
should I do that?

Thanks so much,

Mike

 

[Guest]

Hi Nick,
Thanks for suggesting Debug/Compile VBA Project.

A couple of questions:

1. I don't understand why I'm getting the "Can't assign to array" error. The
array on the left is resizable, which is supposed to be OK. Can you please
look at that?

2. I didn't realize I should remove the Toolbar. Why/when/how should I do
that?

Thanks so much,

Mike

 

[NickHK]

Mike,
1. For one thing, allWords(i).CharRows returns an array of CharRow
objects. But your variable CharRows is an array of Integers.
Also, to use array assignment like this, use
Dim CharRows As Variant
For i = 1 To UBound(allWords)
CharRows = allWords(i).CharRows

It maybe better not to use CharRows as the name of the variable, to avoid
confusion with the CharRows property of allWords.

2. To me that toolbar is useless without the WB. You install it so it's
you responsibility to remove it.
Probably in the Workbook_Close event.
Application.CommandBars("Excel@Japanese Toolbar").Delete

NickHK

 

[Guest]

Hey Nick,
It compiles without errors now and removes the toolbar, but still
intermittently crashes Excel. I have uploaded
http://www.japaneselearningtools.com/crash2.zip. On my machine, choosing
Excel@Japanese Tools/Clear History, followed by saving, crashes Excel.

Do you see anything else?

Thanks so much for all your help,

Mike

 

[NickHK]

Mike,

Problem with the Toolbar deletion :
It is deleted then you try to make it invisible > error.

Excel@Japanese Tools/Clear History without doing any entry > "Divison by
zero" error :
Sub: MakeKanjiTable
Line: fontSize = CInt(Application.Height / (2 * KanjiRows))

Excel@Japanese Tools/Clear History, followed by saving > OK
But upon reopening > "Invalid Procedural call or argument
Sub: MakeWord
Line: Set MakeWord = New Word
Looking at you Word class, then causes Excel to crash.

Not sure if your use of Word as a class name is confusing VBA with
Word.Application.
Your could change it something else.

NickHK

 

[NickHK]

1- Your WB seems to be jumping around in size a lot 8MB>4MB>*MB for small
changes.
Would be a good idea to get one of the Code Cleaners, e.g.
http://www.appspro.com/Downloads/CodeCleaner.exe

2- Also, you need more checking of the current situation before you try to
change some value/property.

Public Function CurrentJlptLevel() As Integer
If lastTestedWordIndex = 0 Then
'....
Test!KanjiTestRules.RemoveItem 1
This line error because Test!KanjiTestRules.ListCount=0

And Combox index is 0 based, not 1 based.

3- People report trouble with lines like
..UsedRange.Rows.count - 1
in some Excel versions/situations, maintaining a correct value of .UsedRange
without a save. Check that this range is the range you think you should be
working on.

4- I tend to avoid using variable/Function names like Row, Text, Character
that are used by Excel/VBA to avoid confusion.

NickHK

 

[Guest]

Nick,
Even after addressing those issues, the result is the same. See my comments
below and http://www.japaneselearningtools.com/crash3.zip

1- Your WB seems to be jumping around in size a lot 8MB>4MB>*MB for small
changes.
Would be a good idea to get one of the Code Cleaners, e.g.
http://www.appspro.com/Downloads/CodeCleaner.exe

I am using it now (though it is not having any affect on the code size).

2- Also, you need more checking of the current situation before you try to
change some value/property.

Public Function CurrentJlptLevel() As Integer
If lastTestedWordIndex = 0 Then
'....
Test!KanjiTestRules.RemoveItem 1
This line error because Test!KanjiTestRules.ListCount=0

And Combox index is 0 based, not 1 based.

The code above is correct. In the Workbook_Open method I am putting 3 items
in the listbox. The code above changes the 2nd item.

3- People report trouble with lines like
..UsedRange.Rows.count - 1
in some Excel versions/situations, maintaining a correct value of .UsedRange
without a save. Check that this range is the range you think you should be
working on.

I don't use it for spreadsheets that are changing shapes.

4- I tend to avoid using variable/Function names like Row, Text, Character
that are used by Excel/VBA to avoid confusion.

I changed my code to not use those.

Any more ideas?

Mike

 

[NickHK]

I'm using a Chinese system now and the everything seems a bit more stable.
If that is due to the system or the changes, I cannot tell at the moment.

Still same problem with "Rebuild All kanji" ; only two rows 192 pixels high.
Ah, but now I get 5 rows (A1:BC5), mostly filled shades of green, except one
cell in red and last row no fill.

If you put a break point on the line
Private Sub UserForm_QueryClose(Cancel As Integer, CloseMode As Integer)
on the "test' userform, I then get errors.
After that it continues to error, whatever.
Without the break point, I seems to work, somewhat.

Without a lot more to see what you are actually doing, I cant't much more
than this.
There's certainly problems with maintaining a consistent state of the WB.

I'll see what I can determine over the weekend, but you certianly need a
more stable environment before release to the public.

NickHK

 

[Guest]

I'm using a Chinese system now and the everything seems a bit more stable.
If that is due to the system or the changes, I cannot tell at the moment.

Still same problem with "Rebuild All kanji" ; only two rows 192 pixels high.
Ah, but now I get 5 rows (A1:BC5), mostly filled shades of green, except one
cell in red and last row no fill.

Don't waste time on "Rebuild All Kanji". It is not meant to be used and will
be removed from the toolbar shortly.

If you put a break point on the line
Private Sub UserForm_QueryClose(Cancel As Integer, CloseMode As Integer)
on the "test' userform, I then get errors.
After that it continues to error, whatever.
Without the break point, I seems to work, somewhat.

Where and if it fails varies greatly from machine to machine.

Without a lot more to see what you are actually doing, I cant't much more
than this.
There's certainly problems with maintaining a consistent state of the WB.

I'll see what I can determine over the weekend, but you certianly need a
more stable environment before release to the public.

The key issue seems to be that there is something in the Workbook causing
the VM to be unstable. When it crashes, it's almost always on innocuous lines
of code. I greatly appreciate your continuing to look at it.