M
Mike Mertes
Hello all. Sorry for double posting if that's not appropriate, but I didn't
know which group this question was better suited for.
Test System:
I'm Running Office 2003 on WinXP with all the latest updates to both and
Symantec Antivirus v9 corp. edition running in the background. However, I
have tested this project on a Win2000 machine running Office 2000 with no AV
and I got EXACTLY the same results.
Project Description:
I have a rather long script in a userform that opens a workbook, pulls data
out of it and puts it into a UDT, then passes that UDT to a another workbook
which in turn fills one of it's worksheets with the data contained in the
UDT. It's simple and standard practice.
Crash Description:
The trouble occurs after the code is done executing. At this point,
everything appears to have worked perfectly until you notice that Excel
seems to have magically linked the worksheet containing the source data to
the destination worksheet. (Keep in mind these are in different workbooks
and the only interaction they had was through a set of procedures that read
data from one then passed it to the other.) When I select a cell on the
sheet in one workbook the same cell will have been selected on the sheet in
the other workbook as well, and hilighted too. This works both ways.
Clicking either sheet results in a corresponding selection change on the
other sheet, also hilighting it. If I drag across a large range of cells,
the same range will be selected and hilighted in the other sheet, too. I
have written no event procedures with this functionality. There is no code
running at this point as I have code execution paused in the VBE. Also, none
of the other worksheets in either of the two workbooks are affected. At any
time when the two worksheets are magically "linked" if I close either
workbook containing them (from code, or manually after code execution has
stopped) Excel crashes. Passing End to the VBE to clear all object
references does not undo the magical linking or prevent the crash either.
If that isn't weird enough, it gets worse! If I step through the code in the
second procedure (where I have determined the magical-codeless linking to
occur) the procedure runs as expected, when it's done executing Excel does
NOT magically link the sheets, and I can close either without crashing
Excel.
Another oddity is the failure of the ScreenUpdating property to change when
I try to set it. It stays on. (Removing this code doesn't prevent the crash,
either.)
What can cause this behaivor?! Please help!
-Mike
Example of my code:
(procedure 1, in a userform that opens the source workbook)
Sub Get_Data()
Dim firstWB as Workbook
set firstWB = workbooks.open("path",,ReadOnly:=True)
Dim udt as MyUDT
udt.Foo1 = firstWB.Worksheets("Source").Range("Bar").value
udt.Foo2 = firstWB.Worksheets("Source").Range("Bar").value
.....about 150 more lines of this....
call ws.Put_Data(udt)
End Sub
(procedure 2, in the destination worksheet)
Public Sub Put_Data(udt as MyUDT) <- by reference, could this have
anything to do with the "link"?
'after the line below is executed, screenupdating remains TRUE. WHY?!
Application.ScreenUpdating = False
me.Range("Bar1").value = udt.Foo1
me.Range("Bar2").value = udt.Foo2
.....about 150 more lines of this....
Application.ScreenUpdating = True
End Sub 'after this procedure is finished executing, the worksheets
become, "linked."
know which group this question was better suited for.
Test System:
I'm Running Office 2003 on WinXP with all the latest updates to both and
Symantec Antivirus v9 corp. edition running in the background. However, I
have tested this project on a Win2000 machine running Office 2000 with no AV
and I got EXACTLY the same results.
Project Description:
I have a rather long script in a userform that opens a workbook, pulls data
out of it and puts it into a UDT, then passes that UDT to a another workbook
which in turn fills one of it's worksheets with the data contained in the
UDT. It's simple and standard practice.
Crash Description:
The trouble occurs after the code is done executing. At this point,
everything appears to have worked perfectly until you notice that Excel
seems to have magically linked the worksheet containing the source data to
the destination worksheet. (Keep in mind these are in different workbooks
and the only interaction they had was through a set of procedures that read
data from one then passed it to the other.) When I select a cell on the
sheet in one workbook the same cell will have been selected on the sheet in
the other workbook as well, and hilighted too. This works both ways.
Clicking either sheet results in a corresponding selection change on the
other sheet, also hilighting it. If I drag across a large range of cells,
the same range will be selected and hilighted in the other sheet, too. I
have written no event procedures with this functionality. There is no code
running at this point as I have code execution paused in the VBE. Also, none
of the other worksheets in either of the two workbooks are affected. At any
time when the two worksheets are magically "linked" if I close either
workbook containing them (from code, or manually after code execution has
stopped) Excel crashes. Passing End to the VBE to clear all object
references does not undo the magical linking or prevent the crash either.
If that isn't weird enough, it gets worse! If I step through the code in the
second procedure (where I have determined the magical-codeless linking to
occur) the procedure runs as expected, when it's done executing Excel does
NOT magically link the sheets, and I can close either without crashing
Excel.
Another oddity is the failure of the ScreenUpdating property to change when
I try to set it. It stays on. (Removing this code doesn't prevent the crash,
either.)
What can cause this behaivor?! Please help!
-Mike
Example of my code:
(procedure 1, in a userform that opens the source workbook)
Sub Get_Data()
Dim firstWB as Workbook
set firstWB = workbooks.open("path",,ReadOnly:=True)
Dim udt as MyUDT
udt.Foo1 = firstWB.Worksheets("Source").Range("Bar").value
udt.Foo2 = firstWB.Worksheets("Source").Range("Bar").value
.....about 150 more lines of this....
call ws.Put_Data(udt)
End Sub
(procedure 2, in the destination worksheet)
Public Sub Put_Data(udt as MyUDT) <- by reference, could this have
anything to do with the "link"?
'after the line below is executed, screenupdating remains TRUE. WHY?!
Application.ScreenUpdating = False
me.Range("Bar1").value = udt.Foo1
me.Range("Bar2").value = udt.Foo2
.....about 150 more lines of this....
Application.ScreenUpdating = True
End Sub 'after this procedure is finished executing, the worksheets
become, "linked."