CreateObject Excel.Application throws an error on

Question

I have a setup where the sourceApp creates a second instance of Excel with CreateObject and then starts to send in data with a for loop. This code is in an empty workbook:

Option Explicit

Sub StreamToOtherWorkbook()
  Dim sourceApp As Excel.Application
  Dim targetApp As Excel.Application

  Set sourceApp = GetObject(, "excel.application")
  Set targetApp = CreateObject("excel.application")

  Dim targetWb As Workbook
  Set targetWb = targetApp.Workbooks.Add

  targetApp.Visible = True

  Dim i As Long
  For i = 1 To 100000
    Debug.Print "Value", targetWb.Sheets(1).Range("A1").Value
    targetWb.Sheets(1).Range("A1").Value = i
  Next i
End Sub

This works as intended, and the data streams in as seen in the following gif. (sourceApp is a placeholder for a hidden background worker so it doesn't need to stay responsive.)

Nope, the lack of Option Explicit in the gif didn't change anything.

---

The problem is that whenever a cell or sheet is clicked, the for loop throws an error. A 50290 Application-defined or object-defined error to be exact. What's really odd is that everything else I've tried doing to targetApp works just fine. I can:

• right click and use the context menu.
• change sheets with VBA.
• add buttons or pictures.
• add a new sheet from the tabs (but not select a sheet).
• select cells with the keyboard.
• insert functions (even ones that reference the cell that updates).
• save the workbook.
• open additional instances.
• ...

The error seems to always happen on the targetWb.Sheets... line and the final Debug.Print returns the right value. Of course I can just choose to ignore the error with On Error Resume Next and the the stream will resume after a slight pause - but I'm still very curious about what makes left clicking throw the error?

I am running on a 64-bit Windows 7 and using Excel 2010 14.0.7151.5001 (32-bit)

Answer 1

Error 50290 is thrown by Excel when it not ready to accept automation because it is busy with user input (see this thread). You can see this if you add targetApp.Ready to the debug.Print statement - you'll get the error when this returns False.

In theory you could check the Application.Ready property before writing to targetApp -

For i = 1 To 100000
 Debug.Print "Value", targetWb.Sheets(1).Range("A1").Value
 Do
  DoEvents
 Loop Until targetApp.Ready
 targetWb.Sheets(1).Range("A1").Value = i
Next i

However when I test this, it only works sometimes (i.e. you can left-click some of the time without problems, but some of the time you still get the error). Presumably retrieving Application.Ready takes long enough that there is time for the state to change between reading it and executing the next statement.

As you say you can trap the error (and you could check that Err.Number = 50290 before continuing). Another alternative - if it's not critical to have targetWb in a separate instance - is to keep targetWb in the same instance. If you create it in the same instance (i.e. Set targetWb = Workbooks.Add) you don't get this problem. Although you do then have the problem that double-clicking in a cell stops your code.