excel - Run VBA Macro hold-mode error due to simultaneous execution

Question

Since I didnt find a similar question here or at any other board, I decided to open a new question.

I have a very basic macro, like this:

Sub test()

Dim path as String
Dim filename as String

path = Range("path_name").Value
filename= Range("filename_name").Value

Workbooks.Open Filename:=path & filename
Sheets("XF").Select
Cells.Select
Selection.Copy

End Sub

The new datafile which is opened executes a new macros, which only inserts a date in one cell, thats it. If I execute the macro step by step it works fine. If I execute it at once it shows an hold-mode error just before ("Sheets("XF").Select"). I think its somehow stuck after it opened the new datafile(.xls). I assume that it executes the new macro and simultaneously wants to continue the original macro which of course doesnt work.

I tried to use a wait method before "Sheets("XF").Select" which did not work.

I use Excel 2016. The format of the main data file is XSML and the format of the data file which is opened is XSL.

Sorry, I cannot share my original skript. I hope you have any suggestions how the problem can be solved.

Sorry, if I made any basic mistakes at this board, since it is my first post.

Best regards, Hendrik

标签： excelvba

Answer 1

VBA 在主 UI 线程上运行，并且不进行多线程执行：Workbooks.Open调用完成时返回，这将在自动打开宏完成执行之后。

逐行执行与“正常”执行时运行方式不同的代码通常是受隐式 ActiveSheet 引用和隐式 ActiveWorkbook 引用影响的代码（这些是 Rubberduck 检查：Rubberduck 可以在您的代码中发现问题并帮助您修复它们；我管理这个开源项目），即隐含地依赖全局状态，这使得代码比它需要的更脆弱。

解决方案是避免依赖全局状态副作用。

例如，Workbooks.Open是一个返回对打开的工作簿的引用的函数。它还具有使该工作簿成为的副作用ActiveWorkbook，但您的宏不应该依赖于它。相反，使用局部变量捕获返回的引用：

Dim book As Excel.Workbook
Set book = Application.Workbooks.Open(path & filename)

现在您有了对打开的工作簿的引用，其余代码不再需要Select：

Sheets("XF").Select
Cells.Select
Selection.Copy

变成：

book.Worksheets("XF").Cells.Copy

这段代码应该做完全相同的事情，但现在可以可靠地完成它，而不管其他代码运行什么以及Activate其他工作簿和工作表。

了解如何避免使用选择和激活以获取更多提示。