How to prevent Excel from appearing and disappearing?

Question

No matter what language, every time I use the Excel object, I have this problem.

Say I'm running a loop, opening & closing workbooks.
The visible property on the Application class is set to false.

If I open an Excel file from the file system manually Excel will show & hide every file that it opens from the code until it is done.

The problem I suspect is because when I open the file manually it uses the same Excel process instantiated in code.

The problem is that I could also break the running code if I manage to close the Excel window.

Is there a way to prevent the OS from using the same process that I created in the code?

Is there a way to keep the automated app isolated from other operations that involve the Excel process so the two can not interfere with one another with the exception that if they happened to access the same file?

score 0 · Answer 1 · answered Oct 08 '22 at 09:30

Use the Application.ScreenUpdating property to avoid UI glitches in Excel. Turn screen updating off to speed up your macro code. You won't be able to see what the macro is doing, but it will run faster. Remember to set the ScreenUpdating property back to True when your macro ends.

Also you may find other Excel properties helpful like shown in the code:

Option Explicit
Dim lCalcSave As Long
Dim bScreenUpdate As Boolean
Sub SwitchOff(bSwitchOff As Boolean)
  Dim ws As Worksheet
    
  With Application
    If bSwitchOff Then 

      ' OFF 
      lCalcSave = .Calculation
    bScreenUpdate = .ScreenUpdating
      .Calculation = xlCalculationManual
      .ScreenUpdating = False
      .EnableAnimations = False
      
      '
      ' switch off display pagebreaks for all worksheets
      '
      For Each ws In ActiveWorkbook.Worksheets
        ws.DisplayPageBreaks = False
      Next ws
    Else
 
      ' ON
      If .Calculation <> lCalcSave And lCalcSave <> 0 Then .Calculation = lCalcSave
      .ScreenUpdating = bScreenUpdate
      .EnableAnimations = True
      
    End If
  End With
End Sub

Sub Main()
  SwitchOff(True) ‘ turn off these features
  MyFunction() ‘ do your processing here
  SwitchOff(False) ‘ turn these features back on
End Sub

How to prevent Excel from appearing and disappearing?

1 Answers1