Dit is blogpost nummer 2 uit een reeks van 7 blogs die dieper ingaan op een aantal technieken die datatransformatie in Excel VBA kunnen versnellen.
- Kopiëren van cellen met VBA
- Gegevens aan een tabel toevoegen
- Een bestand openen en data overbrengen naar het doelbestand
- Een reeks bestanden openen en data overbrengen naar het doelbestand
- Gegevens opslaan in een recordset
- Gegevens uit meerdere bestanden uitlezen met een recordset zonder de bestanden te openen
- Een recordset maken, gebaseerd op data uit 2 verschillende worksheets
Deze blog toont aan welke snelheidswinst behaald kan worden wanneer je een grote hoeveelheid cellen aan een tabel moet toevoegen. Als je via VBA-code een grote hoeveelheid data aan een tabel toevoegt, merk je al vrij snel een vertraagde uitvoertijd op. Dit komt omdat Excel voor elke cel die je toevoegt de tabelstructuur moet herdefiniëren, formules moet bijwerken en de grafische opbouw moet uitwerken. Vooral dat laatste is geen sterk punt in Excel.
In deze blog vertrekken we van een tabel met een beperkt aantal rijen. We gaan een nieuwe rij aan de tabel toevoegen. Dat gaan we door middel van een lus meerdere keren herhalen om te zien welk effect het heeft op de uitvoertijd van de VBA-code. Om te eindigen zien we een interessante werkwijze die de uitvoering kan versnellen.
Het bovenstaande voorbeeld toont de tabel (range B4 tot O9) en gegevens in rij 2 die we telkens opnieuw aan de tabel zullen toevoegen.
In de vorige blog hebben we al aangetoond dat het herhaaldelijk kopiëren en plakken een vertragende invloed op de uitvoertijd van de code heeft. Daarom gebruiken we in het eerste voorbeeld een eenvoudige werkwijze om de rij buiten de tabel aan de tabel toe te voegen. In het gebruikte voorbeeld wordt gebruik gemaakt van vaste waarden in plaats van variabelen.
Sub Macro4()
Range(Cells(10, 2), Cells(10, 15)) = Range(Cells(2, 2), Cells(2, 15)).Value
End Sub
Als je gegevens aan een tabel toevoegt, moet Excel ook de opmaak van de rij en de eventueel aanwezige formules controleren. De tabel moet ook het achterliggende adres van de tabel aan het nieuwe gebied aanpassen. Dat gebeurt elke keer opnieuw als je data aan de tabel toevoegt. In het volgende voorbeeld wordt dit aangetoond door dezelfde rij 10.000 keer toe te voegen.
Sub Add10KRows_1()
Dim xRow As Long
Debug.Print Now
For xRow = 10 To 10000
Range(Cells(xRow, 2), Cells(xRow, 15)) = Range(Cells(2, 2), Cells(2, 15)).Value
Next xRow
Debug.Print Now
‘Code Execution 01:16
End Sub
De uitvoertijd van deze code bedraag 1:16 min.
Met de toevoeging van ‘ScreenUpdating’ (Zie blog 1 in deze reeks) wordt dat minder dan 1 minuut (0:57).
Sub Add10KRows_2()
Dim xRow As Long
Debug.Print Now
Application.ScreenUpdating = False
For xRow = 10 To 10000
Range(Cells(xRow, 2), Cells(xRow, 15)) = Range(Cells(2, 2), Cells(2, 15)).Value
Next xRow
Application.ScreenUpdating = True
Debug.Print Now
‘Code Execution 00:57
End Sub
De snelheidswinst is deze keer niet zo spectaculair als in de eerste blog van deze reeks over snelheidswinst. Dat is eerder aan de tabel zelf te wijten.
Opmerking: Probeer niet zoals in de vorige blog alle cellen één per één naar de tabel te kopiëren. Dat gaat 140.000 bewerkingen van de tabel vragen en heeft op mijn laptop enorm lang geduurd (10 tot 15 minuten). Daarna blijft Excel worstelen met de meest eenvoudige opdrachten en is een herstart van de toepassing de enige oplossing.
Daarom gaan we een andere werkwijze gebruiken waarbij we hetzelfde aantal cellen (10.000 rijen * 14 kolommen) eerst een stuk lager dan de tabel plaatsen om vervolgens het geheel naar de tabel te verschuiven. Zo wordt er maar 1 keer iets aan de tabel toegevoegd in plaats van 10.000 of 140.000 keer.
De data wordt deze keer op rij 15 geplaatst. Dat is 5 rijen verder dan de laatste rij van de tabel. Daardoor moet Excel niets aan de tabel toevoegen of wijzigen. Verder worden de cellen terug één per één overgebracht omdat uit de eerste blog bleek dat dat de snelste werkwijze is.
Sub Add10KRows_3 ()
Dim x As Long, y As Long
Debug.Print Now
Application.ScreenUpdating = False
For y = 15 To 10015 Step 1
For x = 2 To 15 Step 1
Cells(y, x) = Cells(2, x)
Next x
Next y
Application.ScreenUpdating = True
Debug.Print Now
‘Code Execution 00:07
End Sub
Deze werkwijze duurt 7 seconden en is aanzienlijk sneller dan de minuut uit het vorige voorbeeld. We moeten de code enkel nog vervolledigen door het nieuwe gebied aan de tabel toe te voegen.
Sub CopyLoop()
Dim x As Long, y As Long
Debug.Print Now
Application.ScreenUpdating = False
For y = 15 To 10015 Step 1
For x = 2 To 15 Step 1
Cells(y, x) = Cells(2, x)
Next x
Next y
Range(“B15”).CurrentRegion.Cut Destination:=Range(“B10”)
Application.ScreenUpdating = True
Debug.Print Now
‘Code Execution 00:07
End Sub
De code heeft er nu ongeveer even lang over gedaan omdat de actie knippen/plakken maar 1 keer uitgevoerd moet worden en Excel de tabel ook maar 1 keer moet updaten.
Opmerking: Als je in VBA bewerkingen met veel data uitvoert, kan je tijdens het schrijven en testen van de code best Excel regelmatig sluiten voordat je een nieuwe run uitprobeert. Anders kan de uitvoertijd een vertekend beeld geven wat je misschien de verkeerde keuze doet maken.
Volgende blog: Een bestand openen en data overbrengen naar het doelbestand. (Blog 3/7)
Heb je na het lezen van deze blogpost de Excel- of zelfs de Power BI-microbe te pakken? Bekijk dan zeker ons aanbod open Excel-opleidingen en wie weet zien we je binnenkort in Antwerpen of Brussel.
