Windows 10 seems to be crashing VBA Macro whilst running

[ironmouseuk]

Hello everyone.
I currently run a VBA Macro that can take several hours. Previously I have never had a problem but I have just updated to Windows 10, and now when I come back to my PC I find that Excel has crashed and I am presented with recovery files.

It seems that W10 is doing something that crashes Excel (but only when Macro is running). Is there something I can change or turn off that will stop this from happening?

Thank you in advance.
ironmouseuk

 

[Yongle]

Without seeing the macro it is impossible to know where to start.
Which version of Excel did you upgrade from?

Please post the VBA (inside code tags - click on # icon above post window and paste your code between the tags that appear). Someone may be able to spot something that can be done more efficiently or that can be done in a different way.
Additionally it would be very useful if you can tell us which line causes the VBA to crash.

Before posting the VBA you could carry out some tests and let us know how you get on.
- take copies of your files, delete most of the data (leaving a minimal amount simply to prove that the code actually works) and run the VBA again
- if it works then it is probably excess time taken somewhere in the code that is probably causing the crash (possibly running into other processes etc)
- you could also time various sections of your code to help determine where the logjam is located. If you want help with this let us know

good luck!

 

[ironmouseuk]

Hi Yonge.
Firstly; thank you for your prompt reply. Much appreciated.
I now have the Excel sheet running perfectly on my old WIN7 laptop.
I guess we just wait for a WN10 update to fix it.

Thanks for your thoughts though.
Kind regards,
ironmouse.

 

[Yongle]

I guess we just wait for a WN10 update to fix it

I would not hold my breath waiting for that to happen - the cause may not be a bug but simply a deliberate change in the behaviour of Excel
Microsoft does an excellent job in making virtually everything upwardly compatible within Excel itself
But VBA is not Excel. It is only a tool to help us manipulate Excel
Expect VBA to require modification when specific aspects of Excel change fundamentally between versions. It can be frustrating

 

[JackDanIce]

You said in your initial post, "You have a macro that can take several hours" Generally speaking if a macro is taking a long time to run, it's not optimal and there could be unexpected memory issues which causes the O/S to crash.

As suggested by @Yongle, post your code, it could be there are code improvements that could be suggested to provide better compatibility with Win10.

Also appreciate, you're being very vague. What does the macro do, what is the code, how did it behave under the old O/S, did it still take a long time to run?

If you want help and you do not post detail then people replying can only make vague suggestions to try which may or may not fix the issue.

Currently, it seems you have a slow macro that doesn't work in Win10 and your solution is to wait until MS has a fix or updates; but you do not know what the problem is or hence what Win10 update will fix the problem. So until it works, you're going to do your work without the macro?

 

[ironmouseuk]

Hi JacDanIce
Thanks for your reply too. Much appreciated.
To go through your points:
The macro runs in only 6 seconds. The only reason it takes hours is because I run it on different datapoints in a loop.
I didnt post the code because there are lots of embeded macros and in total the lines come to around 500 including the called macros.
Right now I can run it fine on my old WIN7 PC.

Unfortunately it never gives me the oportunity to see where the code stopped working. Normally if it crashes it comes up with the debug window in Excel. But with this crash; the Excel sheet dissapears and I am confronted with a Recovery page. I dont think its the code at all. I think some windows service starts running in the background because it detects that my laptop is idle (even though its running the macro), but I dont know which one/

A mystery...
ironmouseuk.

 

[JackDanIce]

That seems very odd, it runs in 6 seconds but takes hours if its run on different datapoints in a loop.

To me that does suggest code issues, that time delta intuitively seems too wide a spread to be considered "normal"

It could be you're overloading memory (e.g. due to a infinite or non-existing loop), so cache is running out and Excel crashes entirely before you're notified of what line causes error.

Again, without seeing your code or even selected parts, just guess work based on the information you provide.

 

[ironmouseuk]

Thanks again for coming back so promptly JackDanIce.
Again, just to clarify as I am not explaining properly; The code takes 6 seconds to run, but I run the same code on 5,000 seperate datasets, so takes around 8 hours to run.
It runs fine on my WIN7 PC so not a code issue. Right?
Also, my WIN7 PC has a lot less memory, however you raise an interesting point. Maybe WIN10 handles memory differently? Do you know what would happen if it ran out of memory? Would it go to the Excel recovery screen as it currently does?
I'm afraid I cant show you any code because I dont own the IP. Sorry, but its all pretty standard stuff...
Thanks again for your time on this.
ironmouseuk.

 

[JackDanIce]

Perhaps not a code issue if does run on WIN7 and not WIN10 but 6 seconds on one dataset also seems unduly long!

I have a macro that evaluates data requests we send templates to clients to complete across 6 sheets (unknown row count, usually > 1000) and it returns results in <1 second, highlights all cells on 6 sheets with errors and summarises this in an output report indicating which columns in which sheets contain errors.

When designing it, I did accidentally create an infinite loop and after, I was getting a VBA debug error message of static overload or something like that - it's memory related. This is not what you're experiencing as seems like Excel is crashing entirely, so before it even gets a chance to notify the user. Since it's the code that's triggering it, it's more likely the code has issue than the O/S

If the code is "standard" then it makes less sense for it to not work on WIN10 vs WIN7.

How old is the code? In VBA, as far as I know, unless you have a specific function or key word VBA no longer supports, then o/s change shouldn't be reason why code is crashing.

A previous company was using VBA code written originally in Excel 2003 (this was in 2016, unfortunately I wasn't allowed to optimise it, since it was in production use, but story for another time) and was still running fine when WIN10 was installed.

The code was standard, no libraries or external calls, or classes or anything complex was contained in it.

It seems you're looking for a discussion than actual solution? You can't share code but there's nothing else to comment on, from perspective of an Excel discussion board.

Have you tried to ask on a Window's or Microsoft discussion board?

 

[Derek Brown]

Unfortunately it never gives me the oportunity to see where the code stopped working. Normally if it crashes it comes up with the debug window in Excel. But with this crash; the Excel sheet dissapears and I am confronted with a Recovery page.

Could you have screen updating turned off in the code, with the code that switches it back on not being reached when the failure occurs?
Is Win 7 running 32-bit with the Win 10 running 64-bit? Do you have any code there written for only 32-bit only?
Are you correctly releasing resources within the loop (e.g.setting objects to 'nothing' and not re-declaring them etc.)?
I think that this is not going to be a Windows bug, so would not wait for it to be 'fixed'.