Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Random corruption in Excel files
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. |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Random corruption in Excel files
|
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
Random corruption in Excel files
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" wrote in message Sorry, typo. URL is www.japaneselearningtools.com |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
Random corruption in Excel files
It doesn't crash the computer. Excel sometimes crashes when running its
macros. What are you suggesting the risk is? "Jim Cone" wrote: 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" wrote in message Sorry, typo. URL is www.japaneselearningtools.com |
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
Random corruption in Excel files
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 "mps" wrote in message ... It doesn't crash the computer. Excel sometimes crashes when running its macros. What are you suggesting the risk is? "Jim Cone" wrote: 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" wrote in message Sorry, typo. URL is www.japaneselearningtools.com |
#6
Posted to microsoft.public.excel.programming
|
|||
|
|||
Random corruption in Excel files
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" wrote: 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 "mps" wrote in message ... It doesn't crash the computer. Excel sometimes crashes when running its macros. What are you suggesting the risk is? "Jim Cone" wrote: 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" wrote in message Sorry, typo. URL is www.japaneselearningtools.com |
#7
Posted to microsoft.public.excel.programming
|
|||
|
|||
Random corruption in Excel files
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 DebugCompile VBAProject. You should fix these first NickHK "mps" wrote in message ... 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" wrote: 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 "mps" wrote in message ... It doesn't crash the computer. Excel sometimes crashes when running its macros. What are you suggesting the risk is? "Jim Cone" wrote: 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" wrote in message Sorry, typo. URL is www.japaneselearningtools.com |
#8
Posted to microsoft.public.excel.programming
|
|||
|
|||
Random corruption in Excel files
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 "NickHK" wrote: 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 DebugCompile VBAProject. You should fix these first NickHK |
#9
Posted to microsoft.public.excel.programming
|
|||
|
|||
Random corruption in Excel files
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" wrote: 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 DebugCompile VBAProject. You should fix these first NickHK |
#10
Posted to microsoft.public.excel.programming
|
|||
|
|||
Random corruption in Excel files
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 "mps" wrote in message ... 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 "NickHK" wrote: 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 DebugCompile VBAProject. You should fix these first NickHK |
#11
Posted to microsoft.public.excel.programming
|
|||
|
|||
Random corruption in Excel files
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" wrote: 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 "mps" wrote in message ... 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 "NickHK" wrote: 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 DebugCompile VBAProject. You should fix these first NickHK |
#12
Posted to microsoft.public.excel.programming
|
|||
|
|||
Random corruption in Excel files
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 "mps" wrote in message ... 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" wrote: 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 "mps" wrote in message ... 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 "NickHK" wrote: 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 DebugCompile VBAProject. You should fix these first NickHK |
#13
Posted to microsoft.public.excel.programming
|
|||
|
|||
Random corruption in Excel files
1- Your WB seems to be jumping around in size a lot 8MB4MB*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 "mps" wrote in message ... 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" wrote: 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 "mps" wrote in message ... 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 "NickHK" wrote: 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 DebugCompile VBAProject. You should fix these first NickHK |
#14
Posted to microsoft.public.excel.programming
|
|||
|
|||
Random corruption in Excel files
Nick, Even after addressing those issues, the result is the same. See my comments below and http://www.japaneselearningtools.com/crash3.zip "NickHK" wrote: 1- Your WB seems to be jumping around in size a lot 8MB4MB*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 |
#15
Posted to microsoft.public.excel.programming
|
|||
|
|||
Random corruption in Excel files
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 "mps" ... Nick, Even after addressing those issues, the result is the same. See my comments below and http://www.japaneselearningtools.com/crash3.zip "NickHK" wrote: 1- Your WB seems to be jumping around in size a lot 8MB4MB*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 |
#16
Posted to microsoft.public.excel.programming
|
|||
|
|||
Random corruption in Excel files
"NickHK" wrote: 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. |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Why do some Excel files I save have 36+ random characters on the e | Excel Discussion (Misc queries) | |||
EXCEL file corruption | Excel Programming | |||
sum in random files.xls how? | Excel Programming | |||
Do Macros cause corruption of big files | Excel Discussion (Misc queries) | |||
Corruption in Excel? | Excel Discussion (Misc queries) |