0

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.

Community
  • 1
  • 1
Frontend developer
  • 469
  • 1
  • 6
  • 18
  • What is the code for `pause`? – John Coleman Dec 22 '15 at 12:15
  • Rather than fixing your (currently) incomplete code you might want to look at the following working solution: http://stackoverflow.com/questions/17924542/excel-recalculating-every-x-seconds – Ralph Dec 22 '15 at 12:38
  • Public Function pause(Optional iSeconds As Integer = 20) Dim dtEnd As Date dtEnd = Now + TimeSerial(0, 0, iSeconds) Do While dtEnd > Now: DoEvents: Loop End Function – Frontend developer Dec 22 '15 at 13:15

1 Answers1

0

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:

enter image description here

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.

Community
  • 1
  • 1
John Coleman
  • 51,337
  • 7
  • 54
  • 119