Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Propogating errors after closing Workbook.
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 |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Propogating errors after closing Workbook.
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 |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
Propogating errors after closing Workbook.
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 -----Original Message----- 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 . |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Closing a workbook | Excel Discussion (Misc queries) | |||
Propogating data | Excel Discussion (Misc queries) | |||
Self propogating list | Excel Worksheet Functions | |||
Help with closing down a workbook?? | Excel Worksheet Functions | |||
closing workbook | Excel Programming |