0

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

Hendrik
  • 3
  • 1
  • 1
    First step might be to [avoid `Select`](https://stackoverflow.com/questions/10714251/how-to-avoid-using-select-in-excel-vba). – BigBen Feb 03 '21 at 14:21

1 Answers1

1

VBA runs on the main UI thread, and doesn't do multithreaded execution: the Workbooks.Open call returns when it is complete, which would be after the auto-open macro finishes executing.

Code that runs differently when executed line by line vs when executed "normally", is typically code that suffers from implicit ActiveSheet references and implicit ActiveWorkbook references (these are Rubberduck inspections: Rubberduck can find problems in your code and help you fix them; I manage this open-source project), i.e. there's global state implicitly being relied on, and that makes the code more frail than it needs to be.

The solution is to avoid relying on global state side-effects.

For example, Workbooks.Open is a function that returns a reference to the opened workbook. It also has the side effect of making that workbook the ActiveWorkbook, but your macros should not rely on that. Instead, capture the returned reference with a local variable:

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

Now that you have a reference to the opened workbook, the rest of the code no longer needs to Select things:

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

Becomes:

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

This code should do exactly the same thing, but will now do it reliably, regardless of what other code gets to run and Activate other workbooks and worksheets.

See how to avoid using Select and Activate for more tips.

Mathieu Guindon
  • 69,817
  • 8
  • 107
  • 235
  • Can you tell me where I can find information like these? The official microsoft website does not seem to provice detailed information, or am I missing something? "For example, Workbooks.Open is a function that returns a reference to the opened workbook. It also has the side effect of making that workbook the ActiveWorkbook, but your macros should not rely on that." I have a similar issue with the Workbooks.OpenText function and I would like to know what the returns and side effects are. – Hendrik Feb 05 '21 at 22:08