How to work in one excel sheet/ workbook when continuous macros is running in another excel sheet/ workbook

0

Hi,

Can you help me understand when macros is continuously running in one excel workbook.

1. Can be able to work on same workbook 

2. Can be able to work on another workbook

Basically I'm running a macro in one workbook which is constantly running and When I open another workbook the macro breaks and its falls out of range. How can I lock/fix the macro for  that particular sheet/workbook.

Thanks,

Nitil

Hi John,
Refer to attached sample sheet, I am finding that whenever i am in sheet 1, the VBA is working, but whenever i shift to sheet 2, the VBA is not working in sheet1 in background and not giving desired results, as there is no change in time in "updated time" column.
I wanted VBA to run in sheet 1 inspite i am viewing sheet 2.

Can you please help me for the solution.
Thanks,

Nitil

Answer
Discuss

Discussion

Nital

As @Variatus said, don't try to continue discussing a question marked as "Selected" (even if you did it by mistake)- ask a fresh question IF that is justified. I think however that you have had fair answers on this.

The file you attached today makes little sense to me. Not sure what you hope to do with the line in each DataUpdate macro: Sheet1.Range("A2:A10") = Sheet1.Range("A2:A10").  .

I think the timers are running in the background but they just write 1 over and over again in column A (for no apparent reason) and all you see is the time updating in C in Sheet1 but with no idea which macro updated it.

Trouble is that when you change to work on Sheet2, the macros then work on Cell(x,1) of Sheet2 instead.  Fix this by changing the lines in BOTH DataUpdate macros to this (and I've made it increment):
Sheet1.Cells(x, 1).Value = Sheet1.Cells(x, 1).Value + 1.


Furthermore, if you change one macro to refer Sheet1.Cells(x, 7), you'll see the effect of that timer in column G of Sheet1 (but they won't trigger the Timestamp).

Please note that I won't be adding further to this discussion.
John_Ru (rep: 1917) Feb 18, '21 at 7:33 am
Add to Discussion

Answers

0
Selected Answer

Nital

You may need to add your file to your Question since I don't get the same.

For example, I opened the attached file and set the two timers in the attached file running (pressing Start and Start 2) buttons. It's based on the code in Don's tutorial Run a Macro at Set Intervals in Excel but has duplicates of three macros (now macro2 etc.) and and slowly update the yellow cells in the sheet.

I can open another workbook (and the cells continue to update) and work in both workbooks. I can also run a macro in the second workbook without crashing the first (timer) workbook.

I guess there's a limit on Excel's internal timers (as used in this file) and the possibility of a clash but I don't know those limits (Don or Variatus may advise).

Revision 1: If the issue is that the macro from the first workbook operates on the second (active) workbook, you can fix that by adding this line at the start of the macro in the first workbook

If ActiveWorkbook.Name <> ThisWorkbook.Name Then Exit Sub
That means the macro won't run if another workbook is currently active (and may not suit your needs). In your first macro, you may instead need to find all lines referring to ActiveCell, ActiveSheet, ActiveWorkbook etc. and limit them to a specific range/  named range/ sheet in that workbook.

Hope this helps.

Discuss

Discussion

Hi John,
I think you didn't get my query.
The solution given for set interval is working for me.

Further, this is my new query that in my one excel, macro is continously running but whenever i open a new excel workbook for different activity, the macro which is running in previous workbook, runs in new excel workbook as well which i don't want.
Can we limit the macros to run to specific workbook and can i continue to work in another workbook for another activity.
Can the macro lock/fix for the particular sheet/workbook.
Hope you understand my query and help me for the solution.
Thanks.
NitilA (rep: 14) Feb 11, '21 at 5:17 am
Nital.   Please see Revision 1 to my Answer.   Also, please note that you don't need to mark an Answer as Selected just to respond to it (in a Discussion). You should mark it as Selected if the Answer solves your question (but you shouldn't add more and more points to expand your Question unreasonably!). Can you see the Rules hyperlink at the top of this page (on the right?)
John_Ru (rep: 1917) Feb 11, '21 at 6:32 am
Hi John,
Can you help me with the code to run the VBA for specific worksheet/ workbook.
So, that the macros will run on that workbook inspite of i open the another workbook.
There will no disturbance to another workbook.
Thanks,
Nitil
NitilA (rep: 14) Feb 18, '21 at 4:44 am
Nital

I think that might be impossible to do in an absolute sense  (given the way the Windows tiimer will interrupt Excel, albeit briefly) but did you try the suggestion from Variatus? In particular, runnin g a second instance of Excel, following the suggestion of his final paragraph- this should allow you to work on another workbook (while your timed workbook updates); I mean this paragraph from his Answer:

"Using VBA, however,it's simple to create a new instance (use the New keyword). To implement that idea you would need to open the other workbook first and then use code to open the workbook with the On Timer macros. Any further workbooks would then be opened in the instance of the workbook that is active when you open them."
John_Ru (rep: 1917) Feb 18, '21 at 4:55 am
Add to Discussion
0

Once an answer is "selected" a question is deemed closed, meaning there is no more need of solutions to the problem and no one else will comment. This case appears different.

When VBA is invoked Excel cedes control to it. That means that Excel functionality will be interrupted until the VBA code has run its course. At that moment control of the CPU is returned to Excel.

The Timer is not a VBA function. It belongs to Windows. Windows will interrupt both Excel and VBA to do whatever it needs doing. Take note that setting the timer doesn't mean that VBA is "running" for longer than it takes to record the set time in memory - an unnoticable fraction of a second.

However, the Timer will start a VBA procedure which will temporarily discontinue Excel functionality while it runs. The duration of the interruption  depends upon how long VBA will be busy.

So far the facts. Now for conjecture. I guess that the hold VBA has on the CPU doesn't extend to other applications. You might try whether a document open in Word is affected by a macro simultaneously running in Excel. If there is no interference the solution to your problem would be to run your On Timer macros in a separate, dedicated instance of Excel.

Yes, you can load several instances of Excel simultaneously on the same computer. MS Office doesn't expressly delegate the choice of instance to the user. Quite to the contrary, Office will decide in its own mysterious ways whether to use an existing instance or create a new one. Sometimes this leads to confusion when you can see that two documentds are open but they cant "see" each other. That's when they are open in different instances of the application.

Using VBA, however,it's simple to create a new instance (use the New keyword). To implement that idea you would need to open the other workbook first and then use code to open the workbook with the On Timer macros. Any further workbooks would then be opened in the instance of the workbook that is active when you open them.

Discuss

Discussion

Thanks Variatus
NitilA (rep: 14) Feb 16, '21 at 2:18 am
Add to Discussion


Answer the Question

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