Propogating errors after closing Workbook.

[Randy MacDonald]

I am having a problem with propogating errors through a
series of error handlers in Excel VBA. Errors are
propogated as expected in most cases, but if I close an
Excel Workbook prior to the error first being raised, then
the error does not propogate normally.

To demonstrate this problem, I have created a new Excel
workbook that contains two classes (Topology and System)
and one module. The module contains only the
procedure "Testit" that shows the problem.

Public Sub Testit()
Dim topTest As New Topology
topTest.TopoMethod
Exit Sub
End Sub

TopoMethod is the only method in the Topology class:

Public Sub TopoMethod()

Dim sysTest As New System
On Error GoTo ErrorHandler
sysTest.SysMethod
Exit Sub

ErrorHandler:
Debug.Print "TopoMethod", Err.Number,
Err.Source, _
vbCrLf & " " &
Err.Description, vbCrLf
Exit Sub
End Sub

SysMethod is the only method in the System class:

Public Sub SysMethod()

Dim wbkSystemDB As Workbook
On Error GoTo ErrorHandler
Set wbkSystemDB = Workbooks.Open
(Filename:=ThisWorkbook.Path & "\" & "TestFile.xls")
'~~ wbkSystemDB.Close SaveChanges:=False '
This is the line that causes the problem!
Err.Raise Number:=vbObjectError + 10000, _
Source:="System", _
Description:="This is a test error
message from SysMethod."
Exit Sub

ErrorHandler:
Debug.Print "SysMethod", Err.Number,
Err.Source, vbCrLf & " " & Err.Description, vbCrLf
Err.Raise Err.Number, Err.Source,
Err.Description

End Sub

"TestFile.xls" can be any Excel Workbook. (In this case
it is a dummy Excel Workbook containing only an empty
sheet.) When the statement that closes the workbook is
commented out as shown, then running Testit results in the
following output in the immediate window:

SysMethod -2147211504 SystemSrc
This is a test error message from
SysMethod.

TopoMethod -2147211504 SystemSrc
This is a test error message from
SysMethod.

This output represents what I expect and want. However,
if the statement that closes the Excel Workbook is not
commented out, then running Testit results in this output
in the immediate window:

SysMethod -2147211504 SystemSrc
This is a test error message from
SysMethod.

TopoMethod -2147211504 VBAProject
Method 'SysMethod' of object 'System'
failed

The error Number property is propogated correctly in both
cases. But in the latter case the Source and Description
properties have been lost/replaced.

I have tried some simple solutions, such as adding
DoEvents after the Workbook close, but have had no success
in finding a workaround for this problem. Any ideas or
suggestions would be greatly appreciated. (I am running
Excel 2000 (9.0.4402 SR-1) on a Windows 2000 (5.00.2195
Service Pack 4) system.)

Thanks for any suggestions.

Cheers,
Randy

 

[Kendal Ferner [MS]]

Randy,

I have tried to duplicate your issue with a couple of different versions of
Excel (2000 and 2003) on a couple of different machines, but have been
unsuccessful in replicating your issue.

A couple of things you could try...

1. You may try checking for the latest service release, at
http://office.microsoft.com and click on "Downloads". I think SP3 is the
latest to date.

2. You may also try adding a delay between opening and closing
"TestFile.xls", and see if that has any effect. (The "testfile.xls" may not
have finished opening completely, and needs to finish initializing.)

Kendal Ferner
Microsoft Developer Support

 

[Randy MacDonald]

Hello, Kendal,

Thanks for your input. I had previously tried various
methods to restore Excel's context after the workbook
close (including adding delays) without any success. I
downloaded the latest service release as you suggested
(I'm now at 9.0.6926 SP-3) but the problem remains.

However, the fact that you were not able to duplicate the
problem is VERY good information. I have now also tried
my test application on a colleague's machine and the
problem does NOT occur there either, so it seems to be
somehow related to my configuration.

The solution may be that I just need to rebuild my
system. Of course it would be nice to know precisely what
is causing the problem, in case it happens on users'
systems, but I expect this will remain a mystery.

Many thanks for your help, Kendal. It was most valuable.

Cheers,
Randy