In my excel 2016 workbook I have a macro, that asks for data from an SQL server to place this on a worksheet (“MA-Rohdaten”) and then refreshes a pivot table on another worksheet (“MA-Auswertung”), which is working with that data. I believe it does not really matter for my speed problem here, what the macro exactly does.
But just in case, it looks like this:

Option Explicit

Public Sub enableAll(Optional ByVal opt As Boolean = True)

With Application
    .Calculation = IIf(opt, xlCalculationAutomatic, xlCalculationManual)
    .EnableEvents = opt
    .DisplayAlerts = opt
    .ScreenUpdating = opt
    .DisplayStatusBar = opt
    .DisplayPageBreaks = opt
End With

Dim wb As Workbook, ws As Worksheet

For Each wb In Workbooks
    For Each ws In Worksheets
        ws.EnableCalculation = opt
        ws.EnableFormatConditionsCalculation = opt
    Next
Next

End Sub


Sub ma_neu(maSOLL As String, ab As Date)

enableAll False

Dim i As Long
Dim j As Long
Dim k As Long
Dim m As Long
        
Dim daten As Variant

Dim objConn As Object
Dim objRecSet As Object
Dim strSQL As String

With Sheets("MA-Rohdaten")
    'Aufräumen
    i = 6
    While (.Cells(i, 3) <> "")
        i = i + 1
    Wend
    Range(.Cells(6, 3), .Cells(i, 12)).Clear
End With

On Error GoTo NoDATA:

Set objConn = CreateObject("ADODB.Connection")
objConn.Open "Driver={SQL Server};Server=s-appservername;Database=dbname;Trusted Connection=yes;User Id=customReader;Password=XXXXX;"
Set objRecSet = CreateObject("ADODB.RecordSet")

strSQL = "SELECT dbname.ma.lang, dbname.nachschl.kurz, dbname.zeit.datum, " & _
    "dbname.pr.kurz, dbname.auftraege.kurz, dbname.auftraege.lang, dbname.updata.kurz, " & _
    "dbname.tk.kurz, dbname.zeit.bemerkung, dbname.zeit.dauer FROM dbname.zeit " & _
    "INNER JOIN dbname.ma ON dbname.zeit.maid = dbname.ma.id " & _
    "INNER JOIN dbname.nachschl ON dbname.ma.gruppe = dbname.nachschl.nachschlid " & _
    "INNER JOIN dbname.pr ON dbname.zeit.prid = dbname.pr.id " & _
    "INNER JOIN dbname.auftraege ON dbname.zeit.auftragid = dbname.auftraege.id " & _
    "INNER JOIN dbname.tk ON dbname.zeit.tkid = dbname.tk.id " & _
    "INNER JOIN dbname.updata ON dbname.zeit.upid = dbname.updata.id " & _
    "WHERE dbname.ma.kurz = '" & maSOLL & "' " & _
    "AND dbname.zeit.datum >= '" & Format(ab, "yyyy-mm-dd") & "' " & _
    "ORDER BY dbname.auftraege.kurz, dbname.zeit.datum, dbname.tk.kurz, dbname.updata.kurz ASC;"

objRecSet.Open strSQL, objConn
daten = objRecSet.GetRows
objRecSet.Close
    
If Not IsEmpty(daten) Then
    With Sheets("MA-Rohdaten")
        i = 6
        For k = LBound(daten, 2) To UBound(daten, 2)
            For m = LBound(daten, 1) To UBound(daten, 1)
                .Cells(i + k, 3 + m) = daten(m, k)
            Next m
        Next k
        .Cells.WrapText = False
    End With
End If

With Sheets("MA-Auswertung")
    .PivotTables("PivotTable1").ChangePivotCache ActiveWorkbook.PivotCaches.Create _
    (SourceType:=xlDatabase, SourceData:="MA-Rohdaten!R5C3:R" & k + 6 - 1 & "C12", Version:=6)
    .PivotTables("PivotTable1").PivotCache.Refresh

    i = 3
    While (.Cells(4, i)) = ""
        i = i + 1
    Wend
    .Cells(4, i).Group Start:=True, End:=True, Periods:=Array(False, False, False, False, True, False, True)
    .Select
    .Cells(1, 1).Select
End With

enableAll
    
Exit Sub
NoData:
On Error GoTo 0
MsgBox "Komme nicht auf den SQL Server"

enableAll

End Sub

The Routine “enableAll” is supposed to speed up the VBA code as it is called at the beginning of the routine “ma_neu” to disable the extra excel overhead and at the end to reenable the overhead. The parameters of “ma_neu” are used to filter the SQL data.

The routine “ma_neu” works just fine and takes about half a second to complete. Great.
But if I have another worksheet open, that has no reference whatsoever to my macro worksheet, the macro is slowed down tremendously. The same usage of the routine “ma_neu” costs then more than 20 seconds.
It seems, that the other workbook gets recalculated a couple of times, although I made sure, that Application.Calculation is set to manual and all events are turned off.

The athoer workbook simply consist of 4000 numbers in a corresponding cells and only 2000 simple formulas to add two of the 4000 numbers together in 2000 corresponding cells. The workbook has in total 6000 cells – that is all and that is not much. These 6000 cells seem to slow down my original workbook. If I close the tiny 6000 cell workbook, the macro takes again only half a second.

How can I tell excel to only focus on the actual workbook? How can this behaviour be explained?