System Error &H80004005 (-2147467259).

[Welshbird]

I occasionally get this message appearing, at which point Excel closes, and much though it recovers anything in the spreadsheet it loses any VBA I've written.

Yesterday this lost me 3 and a half hours of work. Boo as they say...

I've discussed with my IT department, and they don't really know why this happens. We ran a check on my physical machine overnight, just in case in was the memory or something (which it seems not to be) but all I can find when I Google says this is a 'stack overflow'.

The models I'm working with are all rather small, and in fact yesterday when this happened I only had 6 cells with data.

I'm trying to make myself be more disciplined in clicking the save button, but any other thoughts would be extremely welcome right now.

(As an aside - is there any way to get my VBE to autosave regularly? That might at least be a work-around for the problem.)

Oh, no pattern to when this happens - nothing in the event log either, so we're all clueless over here.

Thanks chaps.

Fee

"The cure for anything is salt water – sweat, tears, or the sea." Isak Dinesen

 

[TonyJollans]

If there's really no consistency to when it happens, then it's most likely due to a build up of something behind the scenes, or abitrary memory being overwritten. Stack overflow ought to be trapped by VBA but if it really is that, do you have any recursive code, or loops that might not end?

It would be helpful if you could post the code but, at least, can you give us an idea of what it is doing? Are you using any API calls that might have faulty parameters? Perhaps implicit instantiation of objects can cause weird errors - hard to say without more detail.


Enjoy,
Tony

------------------------------------------------------------------------------------
We want to help you; help us to do it by reading this: Before you ask a question.

I'm working (slowly) on my own website

 

[Welshbird]

It's been happening with all different kinds of code. The most recent had no loops really: This is what I was working on yesterday which decided to cause the problem:

Private Sub cmdSaveAs_Click()
'Find the next empty column
Dim NextEmptyCol As Long
Dim T As String
Dim U As String
Select Case Len(Range("A1"))
    Case Is = 0
    NextEmptyCol = 1
    Case Is > 0
    NextEmptyCol = Cells.Find("*", [A1], , , xlByColumns, xlPrevious).Column
End Select
    T = Replace(Cells(1, NextEmptyCol + 1).Address(0, 0), 1, "")
    U = Replace(Cells(1, NextEmptyCol).Address(0, 0), 1, "")
'Bit of error checking first then - List name?
If txtListName.TextLength = 0 Then
    MsgBox "Please enter a name for this list."
Exit Sub
End If
'Used a duplicate name?
Dim Rng As Range
Dim c As Range
'MsgBox "yer" & "A1:" & U & "1"
Set Rng = Worksheets("CraigsList").Range("A1:" & U & "1")
For Each c In Rng
    If UCase$(c.Value) = UCase$(txtListName.Value) Then
        MsgBox "That name is already used - please pick a new name"
        Exit Sub
    End If
Next
'Actually chosen some PCO's?
If lstChosen.ListCount < 1 Then
MsgBox "Please select some PCO's to populate this list"
Exit Sub
End If
'Carry on chaps...
Dim R As Integer
Dim S As Integer
    S = 1
Worksheets("CraigsList").Range(T & ":" & T).Clear
Worksheets("CraigsList").Range(T & S) = txtListName
    'see which options have been selected, and write out to the column
    For R = 0 To lstChosen.ListCount - 1
        S = S + 1
        Worksheets("CraigsList").Cells(S, T).Value = lstChosen.List(R)
    Next R
Unload Me
End Sub

Doesn't seem like there is anything that might be causing it there to me, but then my VBA experience is still limited!



Fee

"The cure for anything is salt water – sweat, tears, or the sea." Isak Dinesen

 

[strongm]

One problem here is that System Error &H80004005 is not (necessarily) a stack overflow - it is a generic error message when produced when the system does not really know what happened, and is defined as an "Unspecified error". Of course, this makes it harder to track down what is actually going wrong ...

Often you can run GetLastError() API call to get a better idea of what exactly is happening - but in your case the termination of Excel when the error occurs seems to preclude this. Hmmm... have you got any add-ins running? If so, can you remove any that you don't really need?

 

[Welshbird]

I have XLCubed (which I certainly need) and an add-in I wrote myself. I removed that thinking it must be that, and it still happened, so not guilty m'lud...

Fee

"The cure for anything is salt water – sweat, tears, or the sea." Isak Dinesen

 

[TonyJollans]

There is nothing in that code that should cause Excel to crash, but it is just a part of a UserForm Class Module. Reference to CraigsList makes me wonder what other code you have (an instantiated IE object, perhaps).

As strongm says, the generic error code isn't very helpful by itself but crashes of Excel are quite likely, in my experience, to be a result of some arbitrary memory getting overwritten by an API call.

Even if you can't say what code is running when it abends, is it only when you run code, or can it happen when you are doing something else? And is there any consistency in what you did shortly before the abend. Does it only happen when you are using the workbook with this code? I'm just guessing, really - standard debugging practice

Enjoy,
Tony

------------------------------------------------------------------------------------
We want to help you; help us to do it by reading this: Before you ask a question.

I'm working (slowly) on my own website

 

[Welshbird]

The name CraigsList is purely because the user form is allowing creation of lists, and the user is called Craig! Call it bad programming humour...

It only happens when I am running code and when I am running it as I write each bit really.

Fee

"The cure for anything is salt water – sweat, tears, or the sea." Isak Dinesen

 

[TonyJollans]

OK. Thanks.

I think I misinterpreted something (read: scanned too quickly and typed before I thought).

If you are getting a MsgBox giving you the error code (assuming that's how you know what it is) that is because VBA is catching an error, even if it doesn't know what the error is. Being followed by an abend is unusual but does the abend happen immediately? Does it make any difference if you choose "End" or "Debug"?

Clutching at straws here - debugging remotely is difficult



Enjoy,
Tony

------------------------------------------------------------------------------------
We want to help you; help us to do it by reading this: Before you ask a question.

I'm working (slowly) on my own website

 

[Welshbird]

I don't think that VBA is catching the error - I think somehow that Windows 7 catches it.

The reason being that Excel is unresponsive and I lose everything I'm doing - the autosave is saving the workbook but not the VBA I've been working on. Hence losing 3 and a half hours work on Monday.

The other problem is that it just isn't consistent. I haven't had any problems since Monday - and have been working on the same VBA with no issue (having re-written it, as I lorst it all).

It's just really annoying. I'm getting around it by meing more vigilent with saving regularly, but when one gets involved in the logic that often gets forgotten...

Fee

"The cure for anything is salt water – sweat, tears, or the sea." Isak Dinesen

 

[Welshbird]

Bump... and update.

OK - This morning I've had no error messages. Until... Excel switched off autosave, and then said it couldn't save, but had tried to recover, and I lost about an hour.

Since the last problem we've installed the Office 2010 SP's, so maybe I'm just not getting the errror message now!

Ihad truioed to import a form this morning to save me some time, but now I've just written a new form instead I'm not having the problem. (I had it twice with the imported form).

GOod job I've cut my hair - I'd be tearing it out otherwise.

Any thoughts?

Fee

"The cure for anything is salt water – sweat, tears, or the sea." Isak Dinesen

 

[TonyJollans]

Sorry, I have no more thoughts! I'm unclear as to what is happening - what, exactly, is the sequence of events when you get the failure?

Office 2010 is much improved over earlier versions in terms of resiliency and you really shouldn't be losing an hour's work - unless you work really slowly <bg>.


Enjoy,
Tony

------------------------------------------------------------------------------------
We want to help you; help us to do it by reading this: Before you ask a question.

I'm working (slowly) on my own website

 

[Welshbird]

Well, it's happened again. 2 hours gone this time, and after having come into the office really early to get ahead today.

IT here are going to swap out my laptop to try and trouble shoot - they are wondering if it might be an issue with 64bit windows and 32bit office?

(I can't upgrade to 64bit office as some other software isn't compatible...)

Really could have a cry now....

Right, so I had moved some controls from one user form to another, and copied and pasted code.

Then I'd checked all references and object names etc were valid, and changed the code apropriately.

I was running the form (via a macro - not stepping through or anything) and then code a 'file not found' error. then Excel said it couldn't save, and closed, and all my morning's work was no more.

Thanks for any thoughts guys. This is now getting beyond coping with really! (And in fact beyond grammar).

Fee

"The cure for anything is salt water – sweat, tears, or the sea." Isak Dinesen

 

[TonyJollans]

If your Support people have taken it away, you can't do much more, I guess, but what seems to be the random nature of this makes me wonder whether it's nothing to do with what you're working on. Have you tried, or can you try, repairing your installation of Office?

Enjoy,
Tony

------------------------------------------------------------------------------------
We want to help you; help us to do it by reading this: Before you ask a question.

I'm working (slowly) on my own website

 

[Welshbird]

They are going to try to trouble shoot my laptop. Hopefully something turns up in that.

Fee

"The cure for anything is salt water – sweat, tears, or the sea." Isak Dinesen

 

[Welshbird]

Right. Not my laptop - the 'new' laptopn (which is actually new) has just done the same thing, and I've lost another two hours.

Any suggestions at all would be welcome right now. I've tried banging my head on the wall, but that just gave me a headache rather than a solution.

Think I might just have a cry and go home...

Fee

"The cure for anything is salt water – sweat, tears, or the sea." Isak Dinesen

 

[TonyJollans]

OK. Brand new laptop. Windows 7? Excel 2010? Any Add-Ins?

Brand new workbook. Add your code. Crash!

If not a brand new workbook, can you replicate the issue in a brand new workbook? I know that could be hard to set up but if the fault is a corrupt workbook you may have to do it anyway. And if the fault isn't a corrupt workbook then your code (or something else that's unique to your environment) must be to blame. There is nothing in the code you have posted so far that could cause this (AFAICT!).


Enjoy,
Tony

------------------------------------------------------------------------------------
We want to help you; help us to do it by reading this: Before you ask a question.

I'm working (slowly) on my own website

 

[strongm]

I remain suspicious of XLCubed

 

[N1GHTEYES]

I know this is a really stupid suggestion, but so far the clever suggestions haven't got anywhere, and maybe it is time to clutch at some straws...

Is it possible it is something weird going on on the worksheet, rather than in the code? Have you, for example, got iteration switched on (perhaps unintentionally)? Could you be causing some odd interaction between the worksheet and the code such that the normal application logic which limits the iterations is failing to do so - hence the stack oveflow (if that really is the problem).

Tony

 

[Welshbird]

It's not just one workbook - it happens in any workbook when I'm writing VBA.

So, I've been downgraded to Excel 2007 to see if that helps. Google suggests I'm not the only person having this issue in Office 2010.

Fee

"The cure for anything is salt water – sweat, tears, or the sea." Isak Dinesen

 

[Welshbird]

So far so good in Excel 2007.

Fee

"The cure for anything is salt water – sweat, tears, or the sea." Isak Dinesen