VBA-kod skriver till celler långsamt när ActiveX kontroller är osynliga i Excel 2016

Anteckning

Office 365 ProPlus byter namn till Microsoft 365-appar för företag. Mer information om den här ändringen finns i det här blogginlägget.

Symptom

Tänk dig följande situation:

  • Du arbetar i en Excel för Office 365, Excel 2016 eller en senare version.
  • Ett kalkylblad innehåller ActiveX kontroller som är inställda att vara osynliga.
  • Du har VBA-kod som skriver många formler till celler.

I det här scenariot Excel mycket långsamt till cellerna när du kör VBA-koden.

Exempel

  1. I ett Excel 2016 kalkylblad skapar du 105 ActiveX som alternativknappar.

  2. Ställ in egenskapen Synlig för alternativknapparna på Falskt.

  3. Kör följande VBA-kod:

    For row = 1 To 20000
    
        For col = 1 To 5
           Cells(row, col).Formula = "=Func()"
        Next
    Next
    

Du märker att den här koden körs mycket långsammare än den gör i tidigare versioner av Excel.

Orsak

Det här problemet uppstår på grund av en ändring i designen Excel. På grund av den här ändringen inträffar följande när VBA-kod skriver en formel till en cell:

  • Excel gör osynliga ActiveX kontroller visas.
  • VBA-koden skriver en formel till en cell.
  • Excel gör ActiveX kontrollerna osynliga igen.

Lösning

Använd någon av följande metoder för att lösa problemet:

  • Granska koden och arkitekturen och kontrollera om du behöver så många ActiveX kontroller som du har.
  • Ersätt kontrollerna ActiveX formulärkontroller i berörda arbetsböcker.
  • Gör tillfälligt ActiveX synliga när koden körs.

Anteckning

Om det finns många figurobjekt i kalkylbladet kan VBA-koden fortfarande köras långsamt när du använder lösningarna. I det här fallet tar du bort formobjekten.

Status

Microsoft har bekräftat att det här är ett problem i de produkter som listas i avsnittet "Gäller för".