Symptomen
- Werkmappen zijn traag, u bent op zoek naar methoden om ze te versnellen.
- Een herberekening wordt doorgaans vlot uitgevoerd, maar af en toe is de wachttijd tenenkrommend.
- Een Excel-expert heeft gewaarschuwd voor het gebruik van ‘volatiele’ functies en acties. U probeert de diepere betekenis van deze opmerking te achterhalen.
Remedie voor volatiele standaard Excel-functies
- Gebruik ze niet.
Het betreft:
- de wiskundige functies ASELECT en ASELECTTUSSEN,
- de datumfuncties NU en VANDAAG,
- en de functies INDIRECT, VERSCHUIVEN, CEL en INFO.
De waarde van deze functies worden altijd opnieuw bepaald herberekend als Excel gaat herberekenen.
Remedie voor zelfgeschreven Excel-functies
- Neem als eerste instructie in het programma dat de functiewaarde bepaald, de volgende programmacode op:
APPLICATION.VOLATILE
Deze instructie voorkomt dat de zelfgeschreven functie wordt doorlopen telkens wanneer Excel een herberekening uitvoert.
Remedie voor volatiele acties
- Gebruik ze niet.
Het betreft:
- het openen van een niet-Excel-bestand, bijvoorbeeld een .TXT of een .CSV-bestand,
- het openen van een werkmap die laatstelijk door een andere Excel-versie was herberekend,
- het opslaan van een werkmap op het moment dat de optie Berekenen voor opslaan actief is,
- het invoegen of verwijderen van werkbladen, of het wijzigen van de namen ervan,
- het invoegen van cellen, rijen en/of kolommen in een werkblad,
- het filteren of (on)zichtbaar maken van rijen,
- het toevoegen, wijzigen of verwijderen van bereiknamen,
De waarde van deze functies worden altijd opnieuw bepaald herberekend als Excel gaat herberekenen.
Verdieping
Een aantal Excel-functies staat bij een groot aantal Excel-gebruikers in het zwartboek, omdat ze volatiel zouden zijn. Het betreft met name de functie sINDEX, RIJEN, KOLOMMEN en BEREIKEN. De waarde van deze functies wordt alleen opnieuw bepaald als daar daadwerkelijk aanleiding toe is.
Tips
- Op een computer met een snelle processor wordt een werkmap sneller herberekend dan op een pc met een tragere processor.
- Gebruikt u Excel 2007 of recenter, dan wordt een werkmap sneller herberekend op een pc met twee of meer processoren.
- Herberekeningen worden trager als de omvang van alle openstaande werkmappen groter is dan de hoeveelheid RAM die op uw computer is geïnstalleerd. Houd daarbij ook rekening mee dat er RAM-geheugen nodig is voor Excel en het besturingssysteem.
- U kunt de herberekeningstijd proberen te meten met de VBA-functie TIME. Deze is echter vrij onnauwkeurig. U kunt de meting beter verrichten door de functie MICROTIMER te gebruiken:
Private Declare Function getFrequency _ Lib "kernel32" _ Alias "QueryPerformanceFrequency" _ (cyFrequency As Currency) As Long Private Declare Function getTickCount _ Lib "kernel32" _ Alias "QueryPerformanceCounter" _ (cyTickCount As Currency) As Long Function MicroTimer() As Double ' Bepaal het aantal seconden. Dim cyTicks1 As Currency Static cyFrequency As Currency MicroTimer = 0 ' Frequentie ophalen. If cyFrequency = 0 Then getFrequency cyFrequency End if ' Aantal ticks bepalen. getTickCount cyTicks1 ' Seconden If cyFrequency Then MicroTimer = _ cyTicks1 / cyFrequency End Function
Hebt u een werkmap met lange herberekeningstijden? Controleer dan eerst op het gebruik van volatiele functies in de werkbladen en op het gebruik van volatiele acties in macro’s. Is het probleem dan nog niet (helemaal) opgelost, ga dan methodisch te werk om de oorzaak (of: oorzaken) te vinden. Begin met het instellen van de herberekeningsmethode op handmatig, en meet de herberekeningstijd van de gehele werkmap. Meet vervolgens de herberekeningstijd van:
- elk werkblad.
- elk bereik in elk werkblad met een bovengemiddelde herberekeningstijd.
Stel vast welke formules of constructies de lange herberekeningstijden veroorzaken, en los de problemen op.
- Het herberekenen van één cel met een ingewikkelde (= complexe en geneste) functie vergt meer tijd dan het herberekenen van een bereik van cellen die allemaal een klein deeltje van die functie uitrekenen.
- Niet het aantal functies of het aantal gevulde cellen is bepalend voor de herberekeningstijd, maar het aantal cellen waarnaar door functies verwezen wordt. In veel werkmappen is het niet lastig dit aantal te verminderen met een factor 10, 100 of zelfs 1000. Waarmee u de herberekening met ongeveer die factor versnelt.
- Werkt u met Office 2007, en gebruikt u de functie ALS in combinatie met de functie ISFOUT, vervang deze dan door de nieuwe functie ALS.FOUT.
- Zorg ervoor dat formules altijd vooruit (naar cellen rechts en omlaag) verwijzen, en niet achteruit.
- Wees zuinig met data validatie en met voorwaardelijke opmaak. Dit zijn volatiele opmaakvormen: bij elke herberekening worden de cellen met deze opmaak opnieuw uitgerekend.
Zie ook
Actieve gebied (werkbladen met een te groot actief gebied herberekenen trager).
F9 (intelligente herberekening, alle werkmappen).
Shift+F9 (gedwongen herberekening van het actieve werkblad).
Ctrl+Alt+F9 (gedwongen herberekening van de actieve werkmap).
Shift+Ctrl+Alt+F9 (afhankelijkheden opnieuw bepalen en vervolgens gedwongen herberekening van de actieve werkmap).