Application.Ready

[malik641]

What is this used for? Has anyone used it for something meaningful?

I've checked the help, but the help is no help at all with this.

I'm guessing it would return False if the application is busy, i.e. saving a large file....but how would I check / test that?

BTW, I also ran a test with it while a macro is running, apparently it is 'Ready' even while a macro is running...:dunno

TIA

[CBrine]

Malik,
I built a quick VB6 program to test it using an outside source, and got the same results as you. It's always ready? Maybe it's not ready if it's in the middle of a large recalc? That's the only thing I can think of.

Cal

[Bob Phillips]

I think it is one of theose good ideas that is a load of rubbish.

As I understand, you are supposed to be able to do something like



Dim XLApp as Excel.Application
Set XLApp=New Excel.Application


and then check if XLApp is ready. But seeing as it won't execute any more code until it is ready or has bombed, I can't quite see the point.

[CBrine]

xld,
I'm wondering if it might be useful to determine if the calculation is completed in a large spreadsheet that you are building programmatically? It's to early in the morning to think of a method to test this though. I'm going to see if I can find one of mine that takes a while to calc, and try opening it programmatically and test the .ready state during the recalc. Might be difficult to find one that takes a while to recalc, since I normally try to avoid that.

Cal

[Bob Phillips]

I've never tried to test duruing calculation, but I would think that Excel grabs total control until it is done, akin to what I was saying about staring a new instance. I say that because when Excel is calculating, the only thing you can do is to break it, Excel doesn't seem to yield.

It will be interesting to see what you turn up.

[malik641]

I don't think the method I was doing was all that great, but it was worth a shot. I created a new instance of Excel, added a workbook and held the connection through module-wide variables. Then I manually created a bunch of formulas (3000 rows and from A:R). I tried a copy paste routine that would take time for all those calculations to re-calculate (in addition to the time it takes to copy & paste).

It still came up with "True...True" (before and after):
Option Explicit
Public xlApp As Excel.Application
Public xlWB As Excel.Workbook
Public Sub SetConnection()
Set xlApp = New Excel.Application
Set xlWB = xlApp.Workbooks.Add
xlApp.Visible = True
End Sub
Public Sub TestReady()
Debug.Print xlApp.Ready
xlWB.Sheets("Sheet1").UsedRange.Copy Destination:=xlWB.Sheets("Sheet2").Range("A1")
Debug.Print xlApp.Ready
End Sub:dunno

EDIT: I extended the formulas to be from A:FE, still True-True

[moa]

Try running a couple of procedures at the same time by scheduling them and see if either of them says that the app is not ready. Saw something about this in help menu... check the OnTime method

[CBrine]

I'm trying the same as moa, I'm using a timer that updates a textbox with the state, and I'm going to open a workbook that has a some massive vlookup's, and makes a change to force the recalc.

[malik641]

Okay, here's one decent use for it.

Run "SetConnection" to open a new instance of excel and set a reference to it.

Make both the New instance and the VBE's immediate window visible.

Run the "ShowReady" procedure.
Option Explicit
Public xlApp As Excel.Application
Public xlWB As Excel.Workbook
Public Sub SetConnection()
Set xlApp = New Excel.Application
Set xlWB = xlApp.Workbooks.Add
xlApp.Visible = True
End Sub
Public Sub ShowReady()
Debug.Print xlApp.Ready
Application.OnTime Now() + TimeSerial(0, 0, 1), "ShowReady"
End Sub
Notice if you don't do anything with excel the immediate window shows true. But to return False the only thing I've seen is when you have the Left mouse button held down (whether moving it or not) or the middle mouse button (wheel) is pressed (not scrolling with the wheel).

It won't return False even if your typing or editing a formula...haven't tried testing while it's calculating, though

Thank you Moa for the suggestion on OnTime method. I tried the way you suggested, doing multiple procedures at the same time (ran from within the host workbook, not the new instance), but no luck. Now I'm starting to think you meant to run the procedures in the new app instance.

I'm going to test for xlApp.Ready when xlApp (new instance) is running a macro.

[CBrine]

Looks like that doesn't work. I get an Excel not repsonding error if I try to read that property while the recalc is executing.

I think xld may be right, maybe it's a leftover from some great idea that was never fully implemented.

[Bob Phillips]

Looks like that doesn't work. I get an Excel not repsonding error if I try to read that property while the recalc is executing.

I think xld may be right, maybe it's a leftover from some great idea that was never fully implemented.

I think it might make sense it a multi-threaded environment, but struggle to see why it would be implemented (or not) in VBA.

[CBrine]

Malik,
I used your test on my sheet, and the recalc doesn't seem to effect the flag at all. I did notice that when you select a value from a cell validation list, you get a false response while the list is visible.

Cal

[malik641]

Looks like that doesn't work. I get an Excel not repsonding error if I try to read that property while the recalc is executing.

I think xld may be right, maybe it's a leftover from some great idea that was never fully implemented.
Did you mean my code or your code?
Nevermind.

My code worked for me just fine. The only conclusion I can make (from my code) is that you can check if the user is interacting with excel via mouse left-click or wheel-click.

[malik641]

Ok. I set a refence to another app instance and opened a workbook that ran a macro upon opening (using ontime, waiting 10 seconds so I can see the App before the macro is run). And noticed that .Ready wasn't False the whole time. So it doesn't seem to catch macros running.

I changed my recursive procedure:
Public Sub ShowReady()
Static i As Integer
i = i + 1
Debug.Print xlApp.Ready, i
Application.OnTime Now() + TimeSerial(0, 0, 1), "ShowReady"
End Sub
So I know if the macro is continuing to run. I noticed it was paused during the run of the Workbook_Open macro of the new instance. Apparently all instances of Excel will wait for an individual macro to be ran before running the next.

I did notice, however, that it turns up False whenever a dialogue box is open. And this is any modal form. It returns True when the 'Find and Replace' form is up (a modeless form).

....Except when you have a modeless form (like data validation) but you can select cells, it still turns up False.


I think I'm starting to see the point of Application.Ready.

[CBrine]

So it sounds like it's a good way to determine if a modal form is open within excel.

[malik641]

Yes it is.

I ran the following code while intentionally making Application.Ready = False (by opening a modal form or holding left-click on a cell):
Option Explicit
Public xlApp As Excel.Application
Public xlWB As Excel.Workbook
Public Sub SetConnection()
Set xlApp = New Excel.Application
Set xlWB = xlApp.Workbooks.Open("C:\Test.xls")
xlApp.Visible = True
Application.OnTime Now() + TimeSerial(0, 0, 1), "ShowReady"
Application.OnTime Now() + TimeSerial(0, 0, 10), "TestReady"
End Sub
Public Sub ShowReady()
Static i As Integer
i = i + 1
Debug.Print xlApp.Ready, i
Application.OnTime Now() + TimeSerial(0, 0, 1), "ShowReady"
End Sub
Public Sub Testme()
Application.OnTime Now() + TimeSerial(0, 0, 10), "ChangeWindow"
End Sub
Public Sub ChangeWindow()
xlApp.Windows(1).Caption = "Hey"
End Sub
I ran SetConnection, then when the workbook was loaded I ran the TestMe procedure, then left-clicked on a cell (in new instance) to cause Application.Ready = False, and in the original instance (where ChangeWindow was ran) I received the error:
"Application-defined or Object-defined error"

When I debugged, I pressed F5 to try again (Where xlApp.Ready = True) and it changed the window caption as expected.

Now, I could've used Application.Ready as a preliminary check :)

[CBrine]

It's a good thing the Excel Help is so Detailed and informitive. Only took 4-5 of us about 3 hours to figure out what to do with it.

:devil:

[malik641]

Seriously.

Maybe we should email Microsoft about it...you think they'd listen?

[mdmackillop]

Microsoft.ReadyToListen = False

[Bob Phillips]

Microsoft.ReadyToListen = False

I think you mean ...

Microsoft.ReadyToListen = False

[malik641]

I think it is one of theose good ideas that is a load of rubbish.
So Bob, is it still rubbish :)

[Bob Phillips]

I think so. You have come up with a very convoluted situation, which even if correct (not doubting, just haven't tested it myself) has no practical usage that I can see. I still think it was intended for something bigger, but not thought through.

[malik641]

Maybe I'll get lucky and think of a practical usage for this ;)