Excel代码阻止其他Excel表单打开 [英] Excel Code Blocking other Excel Sheets from opening

问题描述

我有以下代码

  Public Sub Workbook_Open（）
 Application.OnTime Now + TimeValue（00:00:03），CommonWorkBook.prepareSheets
 End Sub 
  

在PrepareSheets宏中，我每60秒从txt文件中获取数据。

代码如下

 做，而1 = 1 
'做某事
暂停60 
循环
  

打开我的.xlsm文件后，我无法打开其他.xlsm文件。

如果我关闭了.xlsm文件，其他文件将被打开。

解决方案

问题是当暂停正在运行时仍然运行。如果您正在执行暂停60 正在执行任务管理器，那么您将看到它正在使用您的核心中的几乎所有CPU：

即使您可以修复它 - - 你真的想要这么多的系统资源吗？

VBA是旧技术 - 自20世纪90年代末以来，只有微小的变化。它是单线程的。 Excel应用程序的每个实例只有一个单线程的解释器实例。正在运行的代码是什么阻止另一个启用宏的工作簿打开。

在Excel中确实没有简单的方法来做多线程（但请参阅这个问题）。您应该看看涉及 Application.OnTime 的解决方案。不要在未来60秒钟暂停 60秒 - schedule 60秒。有关详细信息，请参见此（由@Ralph建议）。

My Excel file blocks other Excels files from opening.

I have the below code

Public Sub Workbook_Open()
    Application.OnTime Now + TimeValue("00:00:03"), "CommonWorkBook.prepareSheets"
End Sub

In PrepareSheets Macro, I take data from a txt file every 60 seconds.

The code is as follows

Do While 1 = 1
    ' Do Something
    pause 60
 Loop

After opening my .xlsm file, I am unable to open other .xlsm files.

If I close my .xlsm file, other files gets opened.

解决方案

The problem is that when Pause is running it is still running. If you look in the task manager while pause 60 is being executed then you will see that it is using up almost all CPU from one of your cores:

Even if you could fix it -- do you really want to hog so much of your system's resources?

VBA is old technology -- only minor changes since the late 1990s. It is single-threaded. Only one single-threaded instance of the interpreter for each instance of the Excel application. The running code is what is preventing another macro-enabled workbook from opening.

There really is no easy way to do multithreading in Excel (but see this question). You should look instead for a solution involving Application.OnTime. Don't pause for 60 seconds -- schedule for 60 seconds in the future. See this (as suggested by @Ralph) for more details.

这篇关于Excel代码阻止其他Excel表单打开的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！