Excel VBA Course
Excel VBA Course - From Beginner to Expert

200+ Video Lessons
50+ Hours of Video
200+ Excel Guides

Become a master of VBA and Macros in Excel and learn how to automate all of your tasks in Excel with this online course. (No VBA experience required.)

View Course

Problem of excel hangs and closes - VBA Macros

0

Hello Experts,

Hope everyone is doing well.

I am finding error of excel workbook hangs and closes at start of the day, when real time data feed start updating for example: 9:00 AM when real time data starts updating C column (sheet- Macro Copy paste and Cut paste Testing 1). 

But when the excel reopen, after the real time starts updates takes place, let say for example at 9:00:20 AM or more, the excel workbook works fine. All the data, macros, remote link excel, etc. is updated fine (Attached two sheets for your ready reference).

I am facing this problem only at the start of the data feed updation, rest the excel open at any other time, it is working fine.

I don't know the reason of excel get hangs and closes at start of the day. May be reason of number of macros coding, timer or remote Link excel.

I am unable to find the solution for the said problem.

Hence, Can i request for your help with your expert knowledge to solve the problem.

Your help will be highly appreciated.

Regards,

Revised Question:

I have attached latest file with recommended solution, but still facing error of excel hangs.

This time i found the error because of Module 1 and Module 2. As the excel is working fine with Module 1 but when i add Module 1 and Module 2, i face of excel hangs at start of real time data feed updates (attached revised sheet for your ready reference).

Also the excel is working fine with all the 3 modules when excel open after the start of real time data feed.

Can you please help with solution for the same.

Regards,

excel
MS Office Version: 2016
posted by: SunilA (rep: 58) on Wed Sep 15, 2021 at 7:09 am
Answer
Discuss

Discussion

Sunil

I worked recently to provide you a revised set of macros under your question Time Interval to run at same flow when open in between of Start time and end time. Are you not using that version for some reason? Does the error occur with that file? 
John_Ru (rep: 6142) Sep 15, '21 at 8:24 am
That file is working fine. Thanks John for that solution. I also selected the answer for the same. I will add that version later to keep it simple.
At present, I am facing this technical error of excel hangs with the file at the start of real time data feed update. But the same excel is working fine, if I open the same excel in the middle of start and end time. I think it might be because of remote link macro conflicting with other VBA Macro module. I don't know the exact reason.
Request your expert knowledge for the solve the problem. 
Regards,
SunilA (rep: 58) Sep 15, '21 at 10:43 am
Sunil, yes thanks for selecting my other answer. I don't have any knowledge of your remote link so dont think I can help here.

If the later solution works without this issue , why don't you just work with that? 
John_Ru (rep: 6142) Sep 15, '21 at 10:49 am
Hi John,
With the later file also, I am facing the excel hangs at start of real time data feed. I have attached the excel sheet. Can I request you please help me with any lead for the same.
Regards,
SunilA (rep: 58) Sep 15, '21 at 11:08 am
If you open this file before 09:00, SetTimer does NOT inititate MyMacro (unlike my later file) so I assume you start it manually. Again, I don't know about your real time data feed but it sounds like that is the problem (so I am wasting my time) What happens if you disable the remote feed then starting the file before 09:00 (or you adjust the start time in the macro to a later time, for you convenience)?
John_Ru (rep: 6142) Sep 15, '21 at 1:23 pm
Add to Discussion

Answers

0
Selected Answer

Sunil

I think the problem relates to your data feed since I made a test today...

I launched your workbook at 07:30:00. It did not crash after the start time (00:09:00), unsurpisingly since the Timers are not initiated in this version. After about an hour, I ran the macro SetTimer and saw that it ran successully (I'd added a Debug.Print line into the MyMacro so I could see it ran at intervals).

There could be some confusion since you use Interval in several places (with different values?) but other than that I think this is one for you to sort.

To your revised question, running more than one version of a solution in parallel (without renaming subs/variables) could cause confusion at best, crashes probably and applications locks at worse. 

posted by: John_Ru (rep: 6142) on Thu Sep 16, 2021 at 5:41 am
Discuss

Discussion

Hi John,
Thank you for your effort.
Can I request you to please share the file with the debug print line.
Regards
SunilA (rep: 58) Sep 16, '21 at 7:55 am
Sunil, I won't be back at my PC fir some hours. Just add the line 
Debug.Print "Done" & Now
just before End Sub in MyMacro. You'll then see the messages in the Intermediate pane of VB Explorer.

Note that it doesn't prevent interference from your remote data feed, it just shows the timer macro is running okay.
John_Ru (rep: 6142) Sep 16, '21 at 8:01 am
P.s. I assume you already tried running ONLY one Excel file and seeing if the remote stsrt caused the crash. 
John_Ru (rep: 6142) Sep 16, '21 at 9:41 am
Hi John, Yes, you are absolutely right, I am really very tried by trying various parameter on checking the said error and put lots of effort for solving the problem for almost 2 months now, but unable to solve the problem.
Can I request you to connect with me live to understand the problem.
I feel you will able to help me solve the problem for which I am struggling for 2 months by now. Else i have to skip this initiative and my effort of 2 month's will be wasted.
Would really appreciate your kind support like always. 
Regards,
SunilA (rep: 58) Sep 17, '21 at 12:21 am
Sunil. Sorry but I won't do that. I'm preparing for two races so my time is precious at present. I give quite a bit of my time to the Forum completely free and get nothing back (apart from the thanks I get)  I can't afford to take on a problem you're being paid to solve presumably. 

If you can't work methodically to isolate the problem that might mean that lots of coding effort by @Variatus and me are also wasted too sadly. One trouble is we never quite know what you're doing on which file 
John_Ru (rep: 6142) Sep 17, '21 at 1:10 am
Hi John,
Thanks for your quick response.
I am taking this initiative in excel for my myself and not for anyone else and not giving any paid service for anyone.
Regards
SunilA (rep: 58) Sep 17, '21 at 2:12 am
I will not take more than 10 min of yours to show the live excel sheet. I think you have that expertise knowledge to crack this problem and you have always given solutions for my past problems in excel. Hence, requesting you for the same.
Please help, if possible.
Regards 
SunilA (rep: 58) Sep 17, '21 at 2:19 am
Sunil

Remember this is a Q&A Forum, not a free consultancy service. I don't want to get dragged into sorting this problem by having a one-to-one call.

If you make a short video of the working sheet with some (written) comments,  post it on a secure site and use the LINK button to provide a URL, I will look at it when I get chance.

If necessary, I'll also tell you once I have so you can remove the post/link.

Please advise too which iteration of your file the crashes first started.
John_Ru (rep: 6142) Sep 17, '21 at 3:09 am
Thank you so much John for your kind gesture, support and patience. 
Can you please help me with you email I'd. So, I can share the link of the same.
I found the exact problem of excel crashes is due of VBA macros because the excel working fine without macros activated.
Regards 
SunilA (rep: 58) Sep 17, '21 at 3:48 am
Sunil, I won't give you my private email address. Please add the video/link as I suggested and answer the question on versiopn plus how many macro-enabled Excel files are running on your PC. I'm running out of patience and time...
John_Ru (rep: 6142) Sep 17, '21 at 4:11 am
Hi John,
I am really very sorry for any kind of inconvience caused to you.
I have 3 macro-enabled in one excel file are running on my PC.
Further, I have made the changes in the sheet which you guided through and checked today in live real time feed, but still i am facing the problem.
This time i came closer to the problem that, the problem of excel hangs arise at the start of the real time data feed is whenever i make addition of macros (Module 1 and Module 2).
The excel is working fine with only Module 1 macros at start of real time data feed.
Also the excel is working fine with all the module 1, 2 and 3 when excel open after start time of real time feed.
I have revised the question to main question and attached latest excel file for your ready reference.
Trust you understand the problem and help me through.
I am really need your help to solve the problem. 
Like always please help me this time also with your genius expertise knowledge  based solution.
Regards,
SunilA (rep: 58) Sep 17, '21 at 5:06 am
Sunil

I have to leave now but took a quick look at your file- why do you have three solutions in the same file? You could have several timers and subs running simultaneously- no wonder it fails! E.g. you have my TimerLogic sub twice.

I won't do any more on this but recommend you pick only ONE module (2 or 2 I'd say) to run and add in any routines not covered in that.
John_Ru (rep: 6142) Sep 17, '21 at 5:31 am
By that I mean that the solution/revised set of macros under your question Time Interval to run at same flow when open in between of Start time and end time was meant to REPLACE earlier versions, not run in parallel with them. 
John_Ru (rep: 6142) Sep 17, '21 at 6:17 am
Sunil

I found the problem with the file attached to your question above (and you should REMOVE it to save other people using it or they too will have probllems.

You have 3 timers running together and one is at 10 second intervals (TimeValue("00:00:10") so even when you try to stop the timers, Excel relaunches! I had to all three Close commands

StopTimer Stop_Timer Stop_Timer1

several times in the Intermediate pane to get it to stop.

You really should start with the version in the previous question referred to above- I checked and the StartTimer has a 5 minute interval 
Interval = TimeValue("00:05")
but you might change it to 00:05:00 for clarity. Adding your Workbook_Close macro to stop the timers seems helpful.

If you sind that works, I suggest you remove this whole question TBH
John_Ru (rep: 6142) Sep 17, '21 at 10:24 am
Add to Discussion


Answer the Question

You must create an account to use the forum. Create an Account or Login