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

VBA code conflicts RTD (Real time Data)

0

Hello Everyone,

I am facing error of RTD (Real time data) is not updating when i open the excel sheet before RTD start updating i.e. before 9:00 AM, but when i open the same excel sheet after 9:00 AM, the excel works fine, also the RTD (Real time data).

The problem might be due to something wrong with my VBA coding in excel (may in Module2 or Module3). Attached sheet for your ready reference.

May be VBA code conflicts with RTD (Real time data).

Can I request for your support to solve the problem.

Would really appreciate your valuable solution for the said problem.

Thank you, 

excel
MS Office Version: 2016
posted by: SunilA (rep: 58) on Wed Sep 22, 2021 at 2:24 am
Answer
Discuss

Discussion

Sunil

It looks like you've posted the same file (problematic, with 10 and 20 seconds intervals) that I'd asked you previously to remove. Please consider revising your question so I can look at it.

Thanks for Selecting my previous Answers (to your recent questions) but you didn't say if the files (e.g.  "Macro Copy paste and Cut paste Testing 1.xlsm" from yesterday) worked for you. Did they? Also, thanks for altering your greeting for this question (but it doesn't need to be in bold in future)
John_Ru (rep: 6152) Sep 22, '21 at 4:26 am
Hang on- the Timer Logic module should launch macros to start after the StartTime (09:00am). Are you saying the files doesn't crash but the macros don't run?
John_Ru (rep: 6152) Sep 22, '21 at 4:29 am
Hi John,
Yes, your Yesterday's solution have worked. The excel sheet didn't hanged and closed.
Thank you for the solution. 

Further, Yes, the time logic module to be launch macros to start after the start time i.e. after 9:00 AM.
When I start the excel before 9:00 am, the RTD is not get updated on real time basis. The file doesn't crash and the macros works fine.
I think the problem is with module 1 and module 2 because when I open the excel before 9:00 AM with only module 1 (module1 and module 2 deleted from file), the excel works fine, also RTD is updated on real time.
And when I open the same excel after 9:00 AM with all the 3 modules, the excel works fine. 
Hence, I am facing technical  problem of RTD not updating on real time, when the excel open before start time i.e. 9:00 AM.
Please help with your expert knowledge based solution. 
I would be really thankful to you.
Regards,
SunilA (rep: 58) Sep 22, '21 at 4:55 am
Will try to look later. I suggest myMacro and myATR macros run on a single timer each 5 mins between working times  but when does myClear run please? (I'm away from my PC for a while) 
John_Ru (rep: 6152) Sep 22, '21 at 7:07 am
Below time intervals:
Mymacro 10 sec
MyATR 20 sec
Myclear 30 sec
SunilA (rep: 58) Sep 22, '21 at 7:14 am
Why.must they differ? (They will  coincide anyway , at different gaps)

Also is your data uodated more often than every 10 seconds or decisions made on the new data within a minute? 
John_Ru (rep: 6152) Sep 22, '21 at 7:30 am
Data is updated every second
SunilA (rep: 58) Sep 22, '21 at 8:16 am
Thanks. Worksheet constantly monitored and decisons made within 10 seconds? 
John_Ru (rep: 6152) Sep 22, '21 at 8:20 am
Yes
SunilA (rep: 58) Sep 22, '21 at 10:35 am
Okay Sunil, that seems expensive for a business but my revised solution below can have the timing reduced to suit (hopefully).

I just tried it again (08:55 local) and it reported:
TimerActions due(with interval and rounding)= 24/09/2021 09:00:00
Actioned myClearMacro: 24/09/2021 09:00:00
Actioned myMacro: 24/09/2021 09:00:00
Actioned myATR: 24/09/2021 09:00:00
TimerActions due(with interval and rounding)= 24/09/2021 09:01:00
since I have the Interval set to 1 minute.

Don't forget (as a test) you can open the file, stop the timer macros, modifiy the StartTime to a few minutes ahead then close/ re-open to check it works 
John_Ru (rep: 6152) Sep 24, '21 at 3:55 am
Add to Discussion

Answers

0
Selected Answer

Sunil

In the revised file attached, I've rationalised your various modules, timers and subs into a single, consistent approach in Module 1.

When the workbook opens, a single Interval is set in sub SetStartTime, currently 1 minute via TimeValue("00:01:00") but you could reduce it to 10 seconds at your risk. Then the sub TimerLogic is launched.

That determines the NextTime that macros will be run (dependent on the current time c.f. the Start and stop times set in SetStartTime) and gives a MsgBox if the start is in the future (i.e. the file opened before 9:00am currently)..

TimerLogic uses Application.OnTime to launch the new macro "TimerActions" below (at NextTime)which calls the other macros in this sequence (currently):

Sub TimerActions()
'run macros after another (arrange sequence to suit)
    Myclearmacro
    MyMacro
    MyATR
' check and set timer if needed
    TimerLogic
End Sub
(each of which reports its completion in the Immediate pane of VB Explorer via a Debug.Print line, which you can comment out after testing). That macro runs the other macros and so gives Intermediate output like this:
TimerActions due (with interval and rounding)= 22/09/2021 14:43:00
Actioned myClearMacro: 22/09/2021 14:43:00
Actioned myMacro: 22/09/2021 14:43:00
Actioned myATR: 22/09/2021 14:43:00
TimerActions due (with interval and rounding)= 22/09/2021 14:44:00
Actioned myClearMacro: 22/09/2021 14:44:00
Actioned myMacro: 22/09/2021 14:44:00
Actioned myATR: 22/09/2021 14:44:00
TimerActions due (with interval and rounding)= 22/09/2021 14:45:00
Actioned myClearMacro: 22/09/2021 14:45:00
Actioned myMacro: 22/09/2021 14:45:00
Actioned myATR: 22/09/2021 14:45:00
TimerActions due (with interval and rounding)= 22/09/2021 14:46:00
TimerActions cancelled at 22/09/2021 14:46:17
where the last line follows a manual run of the single StopTimer sub.

Please do NOT add additional modules/ code until you've checked that this does what you need. You should only need to modify two macros i.e.the intervals and thresholds in StartTime and the sequence of macros under TimerActions. (I didn't know what to do with sub RecordMinMax but left it in Module1).

I hope this finally fixes your problems since I've spent far too long oniterations of this workbook.

posted by: John_Ru (rep: 6152) on Wed Sep 22, 2021 at 1:00 pm
Discuss

Discussion

Forgot to say, suggest you run this on its own to prevent conflicts, either without other macro-enabled worksheets running or on a separate PC.
John_Ru (rep: 6152) Sep 23, '21 at 4:22 am
Sunil, Did you try that new file?
John_Ru (rep: 6152) Sep 27, '21 at 6:52 am
Add to Discussion


Answer the Question

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