manual switching between workbooks while macro is running

[coke2011]

Hi all)

in this topic:

http://chandoo.org/forums/topic/automatting-tabulation-switching-between-worksheet-tabs-automatically


Hui wrote:

I've never tried to do any Multitasking within Excel and having had a quick play, and it has a few idiosyncrasies.


If you run 1 instance of Excel and have 2 seperate workbooks open within it, only the active one will run a Macro. You can run the macro and change between workbooks, but only the active one is running.


However if you start a second instance of Excel you can load separate files in each and they will both run beside each other quite happily...

i noticed that when macro running you cant toggle between workbooks that loaded in one Excel instance.

i mean it is possible to toggle between workbooks if they loaded in different instances of Excel.

also it is possible to toggle between worksheets in active workbook during running macro.

so i cant understand why is that? why running macro blocking manual switching (using Alt+TAB or taskbar) to another workbook that loaded in same inctance of excel?


i think there is some mistake.

i mean if application is too busy and stop interracting with user - it should not allow switching between worksheets too. am i not right?

but it allows.. so i guess that it is not too busy.

and i guess it Can interract with user

so then Why is it not possible to switch between workbooks loaded in one instance of Excel?


what i need is excel vba script that can be running in background mode, minimized and creating no problems to user i.e. allow to open any other workbooks.

and i hope that i miss some little simple thing like some workbook's property or maybe macro's property that allows running macro in Modal/Modeless Mode.


please sorry my english

is my question clear?


i prepared test macro

its dumb but clear:

Sub somebutton_Click()

Dim a As Range

Dim b As Range

Set a = Range("A1:AL100")

For Each b In a

DoEvents: DoEvents

b.Value = "test"

Next

End Sub

try switch to another workbook(in same Excel instance) while its running

 

[Luke M]

Depending on what you're trying to do, can you use an OnTime event?

http://www.ozgrid.com/Excel/run-macro-on-time.htm

 

[coke2011]

thanx for helpful suggestion

Im useing this code for pausing application inside working loop

Sub pause(n As Integer)

Dim Start

Start = Timer

Do While Timer < Start + n

DoEvents

Loop

End Sub

maybe i can rewrite code with OnTime event

but im not sure that its possible because of code design

my script opens internetExplorer instance and just waits until it open URL

i think useing this "pause(3)" is easy and more suitable in this case.

Useing Ontime means that i have to save loop state and all collected data and then finish execution of subroutine until next OnTime event triggered?

 

[Luke M]

You might want to check out this short post. Example code shows how to pause until a web page has loaded:

http://www.dailydoseofexcel.com/archives/2004/09/22/automating-internet-explorer/

 

[coke2011]

my code is pretty same

it also uses

Do

Loop Until IeApp.ReadyState = READYSTATE_COMPLETE


i think that Excel is not supposed to be running in background mode

thats the reason


thank you anyway)