CreateObject Excel.Application在循环中在左键单击时抛出一个错误 [英] CreateObject Excel.Application throws an error on left click during a loop

问题描述

我有一个安装程序，其中 sourceApp 创建具有 CreateObject 的Excel的第二个实例，然后开始发送数据与for循环。此代码位于空的工作簿中：

  Option Explicit 
 
 Sub StreamToOtherWorkbook（）
 Dim sourceApp作为Excel.Application 
 Dim targetApp作为Excel.Application 
 
设置sourceApp = GetObject（，excel.application）
设置targetApp = CreateObject（excel.application）
 
 Dim targetWb As Workbook 
设置targetWb = targetApp.Workbooks.Add 
 
 targetApp.Visible = True 
 
 Dim i As Long 
 For i = 1 To 100000 
 Debug.PrintValue，targetWb.Sheets（1）.Range（A1）。value 
 targetWb.Sheets （1）.Range（A1）。Value = i 
 Next i 
 End Sub 
  

这个工作原理和数据流如下面的gif所示。 （ sourceApp 是隐藏的后台工作者的占位符，因此不需要保持响应。）

< img src =https://i.stack.imgur.com/lMy2e.gifalt =streaming>
不，缺少 Option Explicit gif中的 没有更改任何内容。

---

问题是，只要点击单元格或工作表，，for循环会引发错误。一个 50290应用程序定义或对象定义的错误是准确的。真正奇怪的是，我尝试过的其他任何事情都可以使用 targetApp 。我可以：

• 右键单击并使用上下文菜单。


• 使用VBA更改工作表。 / li>
  
• 添加按钮或图片。


• 从选项卡添加新的工作表（但不要选择工作表）。

<使用键盘选择单元格

• 插入函数（甚至引用更新的单元格）。


• 保存工作簿。 >
  
• 打开其他实例。


• ...

错误似乎总是发生在 targetWb.Sheets ... 行和最终 Debug.Print 返回正确的值。当然，我可以选择忽略错误恢复下一步的错误，并且稍后暂停后，流将恢复，但我仍然非常好奇什么让左键点击错误？

我正在64位Windows 7上运行，并使用Excel 2010 14.0.7151.5001（32- bit）

解决方案

错误50290由于Excel没有准备好接受自动化，因为它正忙于用户输入（参见这个线程）。如果您将 targetApp.Ready 添加到debug.Print语句，则可以看到这一点 - 当您返回 False 。

理论上你可以在写入 Application.Ready > targetApp -

 对于i = 1至100000 
 Debug.PrintValue ，targetWb.Sheets（1）.Range（A1）。值
 Do 
 DoEvents 
循环直到targetApp.Ready 
 targetWb.Sheets（1）.Range（A1 ）.Value = i 
 Next i 
  

然而，当我测试这个，它只有工作有时（即你可以左键点击一些时间没有问题，但有些时候你仍然会得到错误）。推测可以检索 Application.Ready 需要足够长的时间，状态在读取和执行下一条语句之间有所改变。

$ b $如你所说，您可以捕获错误（您可以在继续之前检查 Err.Number = 50290 ）。另一个选择 - 如果在单独的实例中有 targetWb 并不重要 - 在同一个实例中保持 targetWb 。如果您在同一个实例（即 Set targetWb = Workbooks.Add ）中创建它，您不会遇到此问题。虽然你做，但是有一个问题是双击单元格停止你的代码。

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)

解决方案

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.

这篇关于CreateObject Excel.Application在循环中在左键单击时抛出一个错误的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！