wimgielis.com

Hoe lang duurt een macro

• 02/01/2012

	Voorbeeldbestanden bij dit artikel:
	Macroduur timen

Allereerst…

Dit is het eerste artikel van 2012, dus GELUKKIG NIEUWJAAR aan iedereen !

Inleiding

In dit artikel gaan we een methode bekijken om nauwkeurig na te gaan hoe lang een bepaalde macro duurt. Wanneer snelheid een belangrijke rol speelt in een toepassing, hebben we nood aan het timen van de effectieve duur van een macro of reeks macro’s. Dit pas ik toe op iets wat ik een tijdje geleden las bij Crystal Long (MS Access MVP) in een handleiding over Access: zie hier. Dit is een goede handleiding om te starten met Access, al is het niet op dummies-niveau en tevens in het Engels. In de handleiding schrijft Crystal dat het voor VBE (de VBA programmeeromgeving) net iets langer duurt om een functie of procedure uit te voeren, indien die in een module staat onder/tussen veel andere code. De idee is dat het fracties van een seconde langer duurt om de functie of procedure te "vinden" in de module.

Testopzet

Het bovenstaande wilde ik eens nagaan op een heel simpel bestandje. Is dit zo, en in welke mate? Ik laat een UDF (User Defined Function) heel veel keer herberekenen (200000 keer in dit geval), en kijk wat de duurtijd is. Dit doe ik zowel voor een module met zeer weinig code, als een module met zeer veel code. Zie hier de instructies om dit op te zetten voor jezelf (of download het bestandje bovenaan dat wel veel minder cellen telt !):

1. plak deze code in een module in een leeg bestand:

Public Function MyFunc() As Long
    Application.Volatile
    MyFunc = [A1] + Application.Caller.Row

End Function

2. zet in cel A2 de formule: =MyFunc()
3. kopieer cel A2 naar de cellen A3:A20000
4. voeg een tweede module in in het bestand, en plak de volgende functie. De functie zal het rijnummer van de cel optellen bij de waarde van cel A1.

Private Declare Function getTickCount Lib "kernel32" Alias "QueryPerformanceCounter" (cyTickCount As Currency) As Long
Private Declare Function getFrequency Lib "kernel32" Alias "QueryPerformanceFrequency" (cyFrequency As Currency) As Long

Public Function Tijdsmeting() As Double
'resultaat is in seconden

    Dim cyTick As Currency
    Static cyFrequentie As Currency

    If cyFrequentie = 0 Then Call getFrequency(cyFrequentie)
    getTickCount cyTick
    If cyFrequentie Then Tijdsmeting = cyTick / cyFrequentie

End Function

Sub Test_het()
    Dim dTijd As Double, i As Single
    dTijd = Tijdsmeting
    For i = 1 To 10: [A1] = i: Next
    Debug.Print Round(0.1 * (Tijdsmeting - dTijd), 3)

End Sub

5. hang de procedure Test_het aan een knop in je werkblad
6. voer de code Test_het uit en kijk in het Immediate Window in VBA (ook wel Debug Window genoemd): je komt hier door in VBE (Alt + F11) de combinatie Ctrl + G te drukken. Alternatief is om via het menu Beeld te gaan in VBE.

Een fijnmazige Timer

Informatie over deze timer kan je hier lezen. Jan Karel Pieterse heeft informatie over Windows API calls (zoals getTickCount en zoals getFrequency). Ik heb de code zelf een 5-tal keer uitgevoerd, en kreeg dit als resultaat:

Hierbij is het venster van VBA gesloten aangezien dit de uitvoering van macros sterk vertraagt. Ook draaien er geen grote programma’s in de achtergrond op mijn PC. Om even duidelijk te zijn: de tijdswaarnemingen die je ziet is de gemiddelde duur van 1 herberekening van 20000 cellen in kolom A. De procedure Test_het zal 10 herberekeningen uitvoeren. Eerst zal via de regel dTijd = Tijdsmeting de variabele dTijd gevuld worden met het aantal milliseconden sinds het opstarten van de computer: dit is bijgevolg een uniek en oplopend nummer. Dan vinden de 10 herberekeningen plaats door cel A1 te updaten met de nummers 1 tot 10, en tot slot registreer ik het verschil tussen het NIEUWE tijdstip na de lus en dTijd (het OUDE tijdstip). Het verschil delen we door 10 voor de gemiddelde duur van 1 herberekening en ronden we af op 3 cijfers na de komma. Tijdens de procedure Test_het wordt 200000 keer de functie MyFunc aangeroepen.

Daarna doen we hetzelfde, maar is de functie MyFunc (ongewijzigde code) de laatste code in een module met nog 700 andere regels code boven MyFunc. De code is afkomstig van een ander project en doet er eigenlijk niet zo toe; je mag ook eigen code nemen. Nu, de idee is dat VBA moet gaan "zoeken" naar de functie, en dus iets meer tijd in beslag zou nemen. Is dit zo? De resultaten:

Resultaten

Er is een effect, weliswaar eerder klein (2 honderdsten van een seconde op een duur van 2 seconden). Dit is uiteraard slechts een test, andere applicaties met VEEL code en VEEL te berekenen cellen kunnen andere tijdsverschillen geven.

Het kwantificeren van dit klein tijdsverschil doen we met een zeer precieze timer (zie de VBA functie Tijdsmeting). Dit werkt nauwkeuriger dan de ingebouwde VBA timer te gebruiken: als je dit wil, vervang dan telkens Tijdsmeting door het woord Timer. Die Timer is onderdeel van de DateTime functies uit de VBA bibliotheek (library). Timer heeft echter iets minder nauwkeurige tijdsregistraties tot gevolg.

Tot slot

Via een procedure en functie-aanroepen kan je dus macro’s en/of delen ervan precies timen. In de praktijk zal een dergelijke fijnmazige timer niet steeds nodig zijn, en kan de simpele Timer instructie reeds volstaan. Beide functies zullen seconden geven als resultaat en zijn direct vergelijkbaar met elkaar.

• « Vorig Excel artikel
• Volgend Excel artikel »