Voor getallen afronden met VBA is de round-functie beschikbaar. De werking van de round-functie is erg simpel. Als eerste parameter wordt het af te ronden getal ingegeven en als tweede optionele parameter het aantal decimalen waarop afgerond moet worden. Als het aantal decimalen niet is opgegeven wordt afgerond op een geheel getal. Tot zover niets aan de hand, maar wat veel mensen niet weten is dat de round-functie een vrij ongebruikelijk afrondmechanisme gebruikt. Dit kan leiden tot onverwachte afrondingen.
Banker’s rounding
De round-functie in VBA maakt namelijk gebruik van Banker’s rounding. Dit houdt in dat een 5 altijd wordt afgerond op het dichtsbijzijnde even getal. Bijvoorbeeld 2,35 wordt afgerond op 2,4 maar 2,45 wordt óók afgerond op 2,4! Deze laatste afronding is bij veel mensen onbekend en is meestal zelfs ongewenst. Het is vanuit de historie zo gegroeid dat Microsoft bij VBA en bij gewoon Visual Basic voor de round-functie gebruikt maakt van Banker’s rounding. Dit mechanisme wordt niet toegepast bij alle Microsoft-producten, want bijvoorbeeld Excel en SQL Server maken gebruik van andere afrondmechanismen.
Aanpassen afronding 1
In de meeste applicaties wordt echter een andere afronding gevraagd. Een getal eindigend op een 5 moet dan bij een positief getal naar boven worden afgerond en bij een negatief getal naar beneden. Dus 2,35 moet 2,4 worden, 2,45 moet 2,5 worden en -2,45 moet -2,5 worden. Hier kan natuurlijk een aparte functie voor geschreven worden, maar mooier is om toch gebruik te blijven maken van de round-functie.
Wat veel gebruikt wordt is om een heel klein getal op te tellen of af te trekken bij het getal wat afgerond moet worden. Dit kan er dan bijvoorbeeld als volgt uitzien:
Function RoundHalfUp(dblNumber As Double, _
Optional iDecimal As Byte = 0) As Double
RoundHalfUp = Round(dblNumber + Sgn(dblNumber) * 0.000001, iDecimal)
End Function
Bij elk getal word 0,000001 opgeteld en dus 2,45 wordt dan 2,450001. Dit getal wordt wel goed afgerond naar 2,5. Dat gaat in veel gevallen goed, maar is toch niet helemaal waterdicht. Ten eerste kan het voorkomen door de optelling juist een getal ontstaat dat weer eindigt op een 5. Als bijvoorbeeld het basisgetal 2,349999 is en daar wordt 0,000001 bij opgeteld, dan komt de optelling precies uit op 2,35. Dat wordt afgerond naar 2,4, terwijl 2,349999 afgerond zou moeten worden naar 2,3. Ten tweede blijkt dat de optelling in VBA verschillende uitkomsten kan hebben. Zie hiervoor onderstaand voorbeelden:
Debug.Print Round(2.649999+0.000001, 1)
2,7
Debug.Print Round(2.64999+0.00001, 1)
2,6
Debug.Print Round(2.6499+0.0001, 1)
2,7
Debug.Print Round(2.649+0.001, 1)
2,6
Elke afzonderlijke optelling komt uit op 2,65 en dat zou volgens Banker’s rounding afgerond moeten worden naar 2,6 maar dat blijkt niet altijd het geval. Dit wordt zeer waarschijnlijk veroorzaakt door de onnauwkeurigheid die kan ontstaan door het binair opslaan van decimale getallen (zie voor verdere info hierover: Omgaan met rekenfouten van Excel). Het geeft in ieder geval wel aan dat de optelmethode in deze gevallen niet betrouwbaar is.
Aanpassen afronding 2
Een betere methode is daarom om de optelling alleen te doen als het af te ronden getal eindigt op een 5. Dit voorkomt bovengenoemde problemen. De functie die hiervoor gebruikt kan worden is:
Function RoundHalfUp(dblNumber As Double, _
Optional iDecimal As Byte = 0) As Double
If Mid(dblNumber - Fix(dblNumber), iDecimal + 2 + Len(CStr(Sgn(dblNumber))), 1) = "5" Then
dblNumber = dblNumber + Sgn(dblNumber) / 10 ^ (iDecimal + 1)
End If
RoundHalfUp = Round(dblNumber, iDecimal)
End Function
Eerst vindt een check plaats of er een 5 staat op de positie waarop afgerond moet worden. Zo ja dan wordt bij een positief getal een klein getal opgeteld en bij een negatief getal een klein getal afgetrokken. De 5 wordt daarmee een 6. Daarna wordt met de round-functie dit getal afgerond. Deze functie kan gebruikt worden om met VBA getallen op de gebruikelijke wijze af te ronden.
Vragen / suggesties
Hopelijk heeft dit artikel geholpen bij het afronden van getallen met VBA. Als er verdere vragen over dit onderwerp zijn of suggesties voor verbetering, plaats dan een reactie hieronder.
Bij nadere beschouwing blijkt echter niet duidelijk wat een tweetal van de gebruikte functies precies doen, omdat het zover ik kan nagaan geen VBA functies zijn. Het betreft de Sgn functie en de Cstr functie.
Om deze verbeterde afronding te kunnen toepassen zou ik graag meer informatie over deze twee hier gebruikte functies willen ontvangen.
Bij voorbaat dank voor de reactie,
Co Vink
Sgn wordt gebruikt om het teken van een getal te bepalen.
Deze functie geeft -1 terug bij een negatief getal en 1 bij een positief getalen (en 0 bij 0).
Dit wordt gebrukt om een kleine waarde op- of af te tellen en ook om de positie van de '5'.
Met CStr wordt het getal omgezet naar een String.