Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Catch-22 with Error 59
I have a situation where the very act of trying to debug a problem
makes the problem go away, which seems like a bit of a Catch-22. In a standard module I have some User Defined Type variables declared: Option Explicit .... Public Type EzRec1 Aaa As String * 100 Bbb As String * 70 End Type Public Type EzRec2 Ccc As String * 50 Ddd As String * 60 Eee As String * 60 End Type .... Then lower down in the same module I have: .... Public Sub WriteEZ() Dim EZ1 As EzRec1 Dim EZ2 As EzRec2 ... Open Fname For Random Access Read Write As #1 Len = 170 [set the Aaa thru Eee variable values here] Put #1, 1, EZ1 Put #1, 2, EZ2 ... End Sub The first PUT works fine. The second PUT raises error 59, Bad Record Length. Through a painful process of elimination I've discovered that if I make any change at the module-level, the error goes away. That is, I can eliminate the error by: 1. Swapping the order of EzRec1 and EzRec2 via a cut and paste, or 2. Adding a dummy module-level declare, such as "Dim XyzyX as Integer" either before or after the Type statements, or 3. Adding any conditional compilation directive. This need not surround the Type statements. In fact, it need not actually do anything. If I add #If Win32 Then #End If anywhere at the module-level, the error goes away. And once the error goes away I can undo the change that I made and the error is still gone. Here are some other bits of information, or maybe just more red herrings. When I do not do any of the three "fixes" then: 1. Adding "Debug.Print Len(EZ1), Len(EZ2)" both before and after the PUT statements always returns 170 and 170. That is, the length of the data block is indeed equal to the "Len=" of the Open statement. 2. Adding "Debug.Print LOF(1)" after the first PUT shows that 170 bytes were written to the file correctly. Adding "On Error Resume Next" before the second PUT and another debug statement after shows that nothing got added to the file (LOF(1) still returns 170) even if the error 59 is ignored. 3. Fully qualifying the Dim statements, such as "Dim EZ2 As ModuleA.EzRec2", has no effect. The error still happens. 4. Any change made at the proc level, such as swapping Dim statements, has no effect. The error still happens. 5. Any changes made at the module level in a different module have no effect, the error still happens. 6. I do most of my development under XL97. I've never seen the problem happen there, only under XL2000. Whether or not it's relevant, usually I copy the workbook from my XL97 machine to my (different) XL2K machine for final testing before I ship a new release out to users. 7. Doing a "Compile VBAProject" on the XL2K machine has no effect, unless I have also made one of the known "fix" changes. 8. Once the problem is fixed it stays fixed for several weeks until I unknowingly make some change which causes it to appear again. There seems to be no correlation with any changes made to this particular proc, although I frequently make changes to other procs in this module. The actual length of the file records and the structure of the Type variables have not changed in years. 9. Opening the file for both Read and Write does not seem to be the problem. (I need to do some GETs later in the proc, but if I temporarily change the Open to just Write I still get the error 59 with the second PUT.) 10. This problem is not unique to my machine. In this latest go-round I missed catching it on my final test and a user brought it to my attention. [blush] He was running XL2K also. Both of us run XL2K under Windows XP. I run my development XL97 under Windows Me. 11. The workbook is fairly complex. Only 11 sheets but about 400 procs (in both standard modules and event code behind the sheets) with about 20000 lines of code. The Type structures are more complex than what I have shown here. EzRec1 has 46 elements and EzRec2 has 48, but they are all fixed length Strings, Singles, or Integers. Nothing esoteric and no variable length Strings or Variants. 12. I'm not back-level on XL2K. The Help/About shows 9.0.6926 SP-3. I've tried to do my homework. I can't find any MS KB articles that address this. It's not mentioned (that I can find) on any of the MVP expert sites (j-walk.com, cpearson.com, etc) that I've checked, although I must admit I always end up spending several hours at these sites learning all kinds of neat new stuff. [g] This problem has been mentioned a few times in this and related newsgroups. As near as I can tell it has never been answered, in those cases where the problem was not something simple like putting a variable length string in the Type declare and not taking into account the extra bytes. If you are one of those folks who had the same problem and you still have a workbook that fails, try adding a dummy Dim at the module-level and see if the problem goes away for you also. I'm baffled. I have no idea why the error happens in the first place, and I have no idea why the act of making a seemingly unrelated change anywhere at the module-level "fixes" the error. The fix seems to have something to do with making a change that forces a re-compile (re-inspection, re-interpretation, re-something ?) of any of the module-level variables. Can anyone shed any light on this? Could it be that making changes to other procs in this module somehow causes VB to get mixed up about the module-level declares? Since I may forget to do the "fix" in the future is it safe to assume that the no-op #If .... Then #EndIf will always work if I just leave it in permanently? My apologies for the length of this post. Dan www.qsl.net/ac6la/ |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
HOW TO CATCH A DIFFERENT/INCONSISTENT FORMULA | Excel Discussion (Misc queries) | |||
Trying to catch data from a EXCEL file | Excel Discussion (Misc queries) | |||
how to catch the second or more cell with vlookup | Excel Worksheet Functions | |||
How do I 'catch' a 'delete cells' event | Excel Discussion (Misc queries) | |||
Catch Update Linked Cells Event | Excel Programming |