Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Excel 2000 very unstable as a development environment
We have, over the years, developed many Excel 2000 based applications which
make very heavy use of VBA to turn Excel into a workable standalone database application. The key word there is standalone - whilst a real client/server app would be ideal (web client/server even better), these are small one-off projects with limited budgets. But one thing we painfully know is that Excel is not very stable and has a very annoying habit of corrupting the XLS file or simply crashing. The following is a simple example of the frustration we often feel :-) I wonder if there are any other Excel VBA developers out there who can offer any advise. There is nothing wrong with the code. What we suspect has happened is that something BEFORE has corrupt the XLS file and put it in a very unstable and unusable state. We've suspected everything like: o NEW in Private m_XYZ As New Something o Using Class_Initialize o Nested WITH statements But getting rid of them doesn't seem to help. Well maybe it might but the XLS file is already corrupt by now. The example below is a very simple workbook - just two sheets, blank with a VB button on one of them. A prime suspect is using VB buttons on sheets - the ones that trigger VBA events, not the Excel ones that trigger macros. We've had trouble with these before corrupting Excel workbooks. Just to clarify - these as the controls managed by Microsoft Forms 2.0 in FM20.DLL and added to a sheet using the Controls toolbox. Not, confusingly, the Forms toolbox - that's the Excel forms :-) Cheers, Rob. BEGIN Example 1 Open up a workbook Click Alt-F8, select DebugApp macro and start stepping into the code Single step steps through fine using F8 each time END BEGIN Example 2 Open up same workbook Open VBA editor using Alt-F11 Find the Sub DebugApp Start stepping though using F8 Press The code (same in both examples: Sub DebugApp StartApp True .....entering StartApp function.... Private Function StartApp(Debugging As Boolean) As IZincException If Not App Is Nothing Then App.Cleanup End If Set App = New App .....entering App class.... private m_ZincApp As New ZincApp (private variable of App class) Private Sub Class_Initialize() m_ZincApp.ParentApp = Me (2nd method of executing CRASHES here with a memory can't be read exception) .....entering ZincApp class... Private m_ParentApp As IZincApp Property Let ParentApp(RHS As IZincApp) Set m_ParentApp = RHS The code really doesn't do that much: o Starts a subroutine o Calls another subroutine o Creates a class object o Creating triggers the Class_Initialize of the class object o Calls the property routine of an object defined in the class So single step one way, and it's fine. Start single stepping another and it crashes. As I said, unstable :-) |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Excel 2000 very unstable as a development environment
So single step one way, and it's fine. Start single stepping another and
it crashes. As I said, unstable :-) Having worked on this today for quite a while and doing things in a different order, it appears to be a lot more stable. One key thing I'm not doing is creating an ADOX database and then opening an ADO connection to it from a MSForms.CommandButton click event. I might be dreaming it but I have a dim and distant memory of problems running certain code within the event handler of what is in effect, an ActiveX object. I certainly remember problems of programmatically adding MSForms controls to a sheet - it resets the dynamic variables. I wonder if there is some associated problem with calling some ActiveX components within a click event. It certainly can't be ANY ActiveX control as I'm sure we've all done something like this in a click event: Sub MyButton_Click() Dim rst As New ADODB.Recordset rem Carry out some recordset operation End Sub |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
Excel 2000 very unstable as a development environment
Hi
Based on my experience, if Excel file is corrupted, the file will not be opened corrected. So do you mean you still can open the excel file while the code will no longer work? If you wonder if the VBA code's malfuntion is caused by the corrupted, you may try to copy the code to a new created workbook to see if that works. Also office can be consider as a single threaded program, any functional call will be blocked until that call return. If you still have any concern please feel free to post here. Best regards, Perter Huang Microsoft Online Partner Support Get Secure! - www.microsoft.com/security This posting is provided "AS IS" with no warranties, and confers no rights. |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
Excel 2000 very unstable as a development environment
So do you mean you still can open the excel file while the code will no
longer work? Yup - that's about it. The file opens okay but as you start single stepping through it crashes with a "memory cannot be read" which I'm assuming is a bad pointer somewhere inside the binary. If you wonder if the VBA code's malfuntion is caused by the corrupted, you may try to copy the code to a new created workbook to see if that works. Spooky - we tried this on Friday. We've written a module to copy sheets, VBA & references to a new workbook. After the copy, single stepping doesn't crash. However, run it a few more times and the crash will re-appear. It's not completely repeatable though - sometimes it will crash, other times it won't. We've restructured the initialisation code to do more from auto_open (such as establishing connections to ADO Jet database) and this appears to be more stable. I'm not expecting a "this will fix it" kind of answer, just trying to see if anyone has any tips. Obviously corrupt Excel workbooks aren't uncommon considering the number of repair tools. We've used Excel-Fix several times to save a lot of data :-) Cheers, Rob. |
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
Excel 2000 very unstable as a development environment
I'm not expecting a "this will fix it" kind of answer,
If I had these problems I would be seeking one :-) Peter T "Rob Nicholson" wrote in message ... So do you mean you still can open the excel file while the code will no longer work? Yup - that's about it. The file opens okay but as you start single stepping through it crashes with a "memory cannot be read" which I'm assuming is a bad pointer somewhere inside the binary. If you wonder if the VBA code's malfuntion is caused by the corrupted, you may try to copy the code to a new created workbook to see if that works. Spooky - we tried this on Friday. We've written a module to copy sheets, VBA & references to a new workbook. After the copy, single stepping doesn't crash. However, run it a few more times and the crash will re-appear. It's not completely repeatable though - sometimes it will crash, other times it won't. We've restructured the initialisation code to do more from auto_open (such as establishing connections to ADO Jet database) and this appears to be more stable. I'm not expecting a "this will fix it" kind of answer, just trying to see if anyone has any tips. Obviously corrupt Excel workbooks aren't uncommon considering the number of repair tools. We've used Excel-Fix several times to save a lot of data :-) Cheers, Rob. |
#6
Posted to microsoft.public.excel.programming
|
|||
|
|||
Excel 2000 very unstable as a development environment
Hi Rob,
private m_ZincApp As New ZincApp (private variable of App class) Private Sub Class_Initialize() m_ZincApp.ParentApp = Me (2nd method of executing CRASHES here with a memory can't be read exception) .....entering ZincApp class... Private m_ParentApp As IZincApp Property Let ParentApp(RHS As IZincApp) Set m_ParentApp = RHS If I'm reading this correctly, you have a class that defines an interface IZincApp. You have a class called App that implements the IZincApp interface, but also has a child class called ZincApp. When App instantiates ZincApp, you want to pass it a reference to the App class, so some code within ZincApp can call back into App? If so, you might find it more stable to use a Property Set ParentApp instead of a Property Let, and it's best practice to instantiate classes when they're needed, rather than declaring them 'As New': Class IZincApp (defines the IZincApp interface) ============== Public Sub CallBack() End Sub Class App (parent class, implementing the IZincApp interface) ========= Implements IZincApp Private m_ZincApp As ZincApp Private Sub Class_Initialize() Set m_ZincApp = New ZincApp Set m_ZincApp.ParentApp = Me End Sub Private Sub IZincApp_CallBack() End Sub Class ZincApp (child class, calling back via the IZincApp interface) ============= Private m_ParentApp As IZincApp Public Property Set ParentApp(RHS As IZincApp) Set m_ParentApp = RHS End Property Public Sub SomeMethod() m_ParentApp.CallBack End Sub Regards Stephen Bullen Microsoft MVP - Excel www.oaltd.co.uk |
#7
Posted to microsoft.public.excel.programming
|
|||
|
|||
Excel 2000 very unstable as a development environment
Of course, one must be extremely careful when retaining a reference to
a parent in a child object. All references to the parent must be released before the object can be destroyed ... you know where I'm going here. This is one of the best ways to create a memory leak <g. I can see how using As New and setting a reference in Class_Initialize could cause problems. Jamie. -- |
#8
Posted to microsoft.public.excel.programming
|
|||
|
|||
Excel 2000 very unstable as a development environment
Hi Jamie,
Of course, one must be extremely careful when retaining a reference to a parent in a child object. All references to the parent must be released before the object can be destroyed ... you know where I'm going here. This is one of the best ways to create a memory leak <g. I can see how using As New and setting a reference in Class_Initialize could cause problems. Absolutely! Regards Stephen Bullen Microsoft MVP - Excel www.oaltd.co.uk |
#9
Posted to microsoft.public.excel.programming
|
|||
|
|||
Excel 2000 very unstable as a development environment
Hi
Have you tried Jon's suggestion? If you still have any concern, please feel free to post here. Best regards, Perter Huang Microsoft Online Partner Support Get Secure! - www.microsoft.com/security This posting is provided "AS IS" with no warranties, and confers no rights. |
#10
Posted to microsoft.public.excel.programming
|
|||
|
|||
Excel 2000 very unstable as a development environment
If so, you might find it more stable to use a Property Set ParentApp
instead of a Property Let, and it's best practice to instantiate classes when they're needed, rather than declaring them 'As New': After a *lot* (and I mean a lot) of debugging, removing lines, adding lines, I've finally tracked it down to what I think is a bug in interfaces. I'd slowly started rebuilding the application line by line, module by module testing as I went along. Although the context is meaningless, the crashes occur when you try and use your classes as return values in interfaces. For example: IZincScreen.CLS: Option Explicit Function Init() As ZincException End Function Sub DrawScreen() End Sub Function AfterDraw() As ZincException End Function Property Get ZincScreen() As ZincScreen End Property And the crash only occurs when you happen to have two or more classes implementing the same interface: TitleScreen & ChartScreen in my case. Replace the references above to ZincException & ZincScreen to Object (two other classes in the project) then program works a treat. I guess a by-product of using Object is that late binding is used as opposed to early binding. Although it's taken a staggering amount of time to reach this conclusion, at least we can move forward again knowing it won't randomly crash. Cheers, Rob. |
#11
Posted to microsoft.public.excel.programming
|
|||
|
|||
Excel 2000 very unstable as a development environment
Hi
Can you provide a simple reproduce sample and send to me by removing the "online" from my email address? Here is my test code. [Interface Class1] Public Function Test() As Class1 End Function [Class2] Implements Class1 Private Function Class1_Test() As Class1 MsgBox "Class2" Set Class1_Test = New Class3 End Function [Class3] Implements Class1 Private Function Class1_Test() As Class1 MsgBox "Class3" End Function [Test module] Sub Test() Dim o As Class1 Set o = New Class2 o.Test Dim b As Class1 Set b = o.Test() b.Test End Sub If I have any misunderstanding, please feel free to post here. Best regards, Perter Huang Microsoft Online Partner Support Get Secure! - www.microsoft.com/security This posting is provided "AS IS" with no warranties, and confers no rights. |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Excel 97 unstable | Setting up and Configuration of Excel | |||
VBA Integrated Development Environment basic question | Excel Programming | |||
Excel 2000 microsoft development environment | Excel Programming | |||
Any Development environment for Office 2002/2003 | Excel Programming | |||
Excel 2002 VBA App fails in Excel 2000 Environment | Excel Programming |