LET OP: na het downloaden de extensie wijzigen in xlsm
Vroeger…. toen was het allemaal anders (en beter?).
Je had toen speciale bureaus, die niets anders deden dan (standaard)berekeningen uitvoeren … “met de hand”.
Tegenwoordig wordt Excel vaak ingezet voor berekeningen. Maar in de praktijk zie ik daar vaak nog veel hand-arbeid aan te pas komen. Daar is op zich niets mis mee, maar het is wel veel foutgevoeliger dan wanneer dit soort processen worden geautomatiseerd.
Daarom deze keer aan de hand van een hypotheek-voorbeeld uitleg hoe met (een beetje) VBA bulk-processen binnen Excel kunnen worden uitgevoerd.
Hypotheek-berekening
De basis van dit artikel is een hypotheekberekening (zie het tabblad BerekHyp van het Voorbeeldbestand). Aan de hand van enkele input-gegevens wordt een overzicht van rente en aflossing gegenereerd; voor de overzichtelijkheid op jaar-basis.
Alle input-cellen (C2:C7) hebben een naam gekregen gelijk aan de tekst in de kolom links er van (via Formules/Namen/Maken obv selectie en dan de optie Linkerkolom).
Via Gegevens/Gegevensvalidatie zijn de invoer-mogelijkheden van enkele cellen ingeperkt.
Het schema van rente en aflossing is in een Excel-tabel weergegeven (met de naam tblBerek).
Cel B11 bevat een harde waarde, gelijk aan 1, cel C11 heeft als formule =Bedrag (het gewenste hypotheekbedrag dus).
In cel D11 wordt de verschuldigde rente voor dat jaar bepaald (voor een annuïteiten- of lineaire hypotheek is dat hetzelfde):
=[@BeginBedr]*Rente, dus het BeginBedr uit dezelfde regel vermenigvuldigd met het verschuldigde rentepercentage (cel C5).
De berekening van de aflossing het eerste jaar (cel E11) is iets ingewikkelder en afhankelijk of het een lineaire of annuïteiten-hypotheek betreft:
=ALS(Soort=”Lin”;
Bedrag/Looptijd;
-BET(Rente;Looptijd;Bedrag) – [@Rente])
Dus: als het een een lineaire hypotheek is dan is de aflossing gelijk aan het Bedrag gedeeld door de beoogde totale Looptijd. Anders (dus bij een annuïteit) is het gelijk aan het resultaat van de functie BET(Rente;Looptijd;Bedrag) minus de verschuldigde Rente van dat jaar.
NB1 de functie BET berekent aan de hand van de parameters rentepercentage, aantal termijnen en beginbedrag het verschuldigde termijnbedrag
NB2 als het beginbedrag in de functie BET positief is, dan is het resultaat van de functie negatief; vandaar het min-teken voor BET.
NB3 moeten de berekeningen exacter dan zul je het schema op maandbasis moeten doorrekenen; de Rente door 12 delen en de Looptijd en de rentevastperiode (RVP) met 12 vermenigvuldigen.
Het volgende Jaar-nummer (cel B12) is gelijk aan het vorige Jaar plus 1; het BeginBedr in cel C12 is gelijk aan het resultaat van cel F11. De formules in de overige cellen van rij 12 zijn gelijk aan die in de vorige regel. De formules uit rij 12 kunnen daarna allemaal naar beneden gekopieerd worden.
NB het schema mag alleen maar gevuld worden voor die jaren, die binnen de rentevastperiode (RVP) vallen. Dat is met behulp van de ALS-functie makkelijk op te lossen (zie Voorbeeldbestand).
In hetzelfde tabblad BerekHyp staat ook een samenvatting van de resultaten: de totale bedragen aan rente en aflossing en de restant-schuld op het einde van de RVP.
De eerste 2 berekeningen zijn rechttoe rechtaan: neem de som van de betreffende kolom in de tabel tblBerek. De bepaling van het eindbedrag gaat via de formule =MIN(tblBerek[EindBedr]), ofwel bepaal het minimum van de kolom EindBedr in de tabel tblBerek.
Bulk-invoer
Moet je nu 10 verschillende berekeningen uitvoeren, dan zullen per berekening de betreffende gegevens moeten worden gewijzigd, Excel berekent de resultaten en deze moeten overgenomen worden of geprint.
Maar wat te doen als er 100 berekeningen nodig zijn of misschien 2.000? Het overnemen van invoer-gegevens is behoorlijk foutgevoelig; de meest voorkomende fouten zijn:
* een 0 te veel of te weinig bij het bedrag
* een decimaal fout in het rentepercentage
* vergeten Ann te veranderen in Lin of andersom
Dus het is zaak om zo min mogelijk gegevens handmatig in te voeren.
Zorg bijvoorbeeld, dat gegevens door een ander systeem in Excel-formaat worden aangeleverd óf laat (als je zelf een overzicht in Excel hebt gevuld) iemand anders het overzicht aan de hand van brongegevens controleren.
Een voorbeeldbestand met 2.000 regels (met de naam tblInput) is opgenomen in het tabblad Input van het Voorbeeldbestand.
Bulk-berekeningen (handmatig)
Als de invoer-gegevens gecontroleerd zijn kunnen die verwerkt worden tot resultaat-gegevens.
In het tabblad Bereken van het Voorbeeldbestand staat een vergelijkbaar schema als in het tabblad BerekHyp.
Het enige verschil is, dat de invoergegevens met behulp van VERT.ZOEKEN-formules worden opgehaald uit de tabel tblInput op basis van het corresponderende Nr.
NB voor het verticaal zoeken hebben we een Hulp-kolom gebruikt om aan te geven uit welke kolom van tblInput het betreffende veld gelezen moet worden.
Op deze manier kunnen redelijk vlot alle berekening doorlopen worden. Maar wat doe je met de resultaten. Eén mogelijkheid is om deze te verwerken in een apart output-tabblad (zie Output1 van het Voorbeeldbestand):
- Vul in cel D2 van het tabblad Bereken een nummer in
- Kopieer de cellen D2:D7 van het tabblad Bereken
- Kies Plakken Speciaal/Waarden en Transponeren in een nieuwe regel in het tabblad Output1
- Kopieer de cellen H2:H4
- Plak die op een vergelijkbare manier achter de vorige gegevens
- herhaal bovenstaande voor alle nummers uit Input
Bulk-berekeningen (met VBA)
Maar we kunnen het hele proces natuurlijk nog verder automatiseren; daar komt dan wat VBA (programmeerwerk) om de hoek kijken.
In het tabblad Bereken staan enkele buttons; de eerste met de titel Vullen 1 start de subroutine OutputVullen1 (rechtsklikken op de button en dan kiezen Macro toewijzen):
Een korte toelichting:
- Allereerst worden met behulp van het commando Dim de variabelen gedeclareerd, die we binnen de routine gaan gebruiken.
- Na de declaraties wordt de variabele MaxHyp gevuld met de waarde van de Range met de naam AantHyp (cel I2 in het tabblad Input)
- Dan een vreemd blok (met de #-tekens): dit is een stuk met zogenaamde Conditional Compilation. De regel, die begint met If MaxHyp> 500, wordt alleen uitgevoerd op een MAC-computer. Omdat een MAC veel langzamer is dan een Windows-machine, beperken we het aantal uit te voeren berekeningen tot maximaal 500.
- De regel na het #-blok plaatst een pop-up op het scherm, die aangeeft hoeveel berekeningen er uitgevoerd zullen gaan worden. Wordt binnen deze pop-up op Cancel (of Annuleren) gedrukt dan wordt de subroutine afgebroken.
- Om straks te kunnen bepalen hoeveel tijd de routine heeft gekost, wordt de starttijd vastgelegd in de variabele StartTijd (beter gezegd de datum en tijd worden bewaard).
- Dan wordt een andere subroutine (OutputLeegMaken1) uitgevoerd (zie hieronder).
- Het belangrijkste gedeelte van de subroutine wordt gevormd door een zogenaamde For-Next-loop. Het stukje programma tussen For en Next wordt een bepaald aantal keren uitgevoerd, afhankelijk van de inhoud van (in dit geval) MaxHyp. De teller i houdt bij welke hypotheek-berekening wordt uitgevoerd.
- Binnen de loop wordt allereerst de waarde van de teller i in de cel met de naam Nr geplaatst (zoals we hiervoor handmatig deden).
- Excel herberekent op dat moment direct alle relevante cellen.
- Het volgende gedeelte van het programma plaatst dan alle relevante waardes in de resultaat-tabel tblOutput1. Dit wordt gedaan door telkens, geredeneerd vanuit de tabelkop (Header) met de naam Nr, een aantal regels i naar beneden en 0, 1 et cetera kolommen naar rechts te gaan.
- De overige regels hebben geen functionele bijdrage.
In bovenstaand programma wordt de routine OutputLeegMaken1 aangeroepen:
Via de Range-opdracht wordt de uitvoer-tabel leeg gemaakt. Als deze toevalligerwijs al leeg zou zijn, zou het programma stoppen met een foutmelding. De regels er omheen zorgen er voor, dat bij een eventuele fout het programma gewoon doorgaat.
Op mijn oude laptop heeft Excel ruim een minuut nodig om de berekeningen uit te voeren en de resultaten weg te schrijven.
Op Windows-computers is goed te zien, dat Excel aan het werk is; cellen veranderen continu van inhoud.
In de praktijk blijkt dat het bijwerken van het scherm het merendeel van de benodigde tijd in beslag neemt.
In de routine OutputVullen1b is daarom een regel toegevoegd: Application.ScreenUpdating = False
Via de button Vullen 1b start u deze routine; nu blijkt er nog maar 13 seconden nodig te zijn voor de verwerking van de 2.000 hypotheken!
LET OP vergeet niet in de VBA-routine de schermverversing weer aan te zetten. Onderaan komt dan Application.ScreenUpdating = True
Bulk-berekeningen (met VBA) vervolg
Maar het kan nog sneller. Wanneer we de For-Next-Loop vervangen door:
dan duurt het totale proces nog maar een paar seconden (klik op de button Vullen 2). De bijbehorende VBA-routine heeft de naam OutputVullen2.
Om dit mogelijk te maken is in het tabblad Output2 van het Voorbeeldbestand een aantal extra cellen toegevoegd, die samen de naam ResultRng hebben gekregen. De inhoud van deze cellen wordt in één keer in de Range vanaf (i,0) tot (i,8) geplaatst.
De cellen van ResultRng bevatten twee matrix-formules TRANSPONEREN, waarmee de benodigde gegevens uit het tabblad Bereken worden opgehaald (zie het artikel Transponeren).
Matrix-formules worden ingevoerd door in plaats van af te sluiten met Enter, de toetscombinatie Control-Shift-Enter te gebruiken (de CSE-methode). Excel plaatst dan automatisch accolades rond de formule.