D
Dan
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/
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/