De round-functie

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 zogenaamde 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 velen onbekend en is meestal zelfs ongewenst. Het is vanuit de historie zo gegroeid dat Microsoft bij VBA en gewoon Visual Basic bij 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 RoundAbsHalfUp(dblNumber As Double, _
        Optional iDecimal As Byte = 0) As Double
    RoundAbsHalfUp = 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 bleek mij 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. Misschien heeft dit te maken met de onnauwkeurigheid die kan ontstaan door het binair opslaan van decimale getallen. Zo niet, dan heb ik hiervoor geen verklaring (een bug?). 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. Daarmee worden bovengenoemde problemen voorkomen. Dit is de functie die hiervoor gebruikt kan worden:

Function RoundAbsHalfUp(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
    RoundAbsHalfUp = 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.

Geef een reactie

Het e-mailadres wordt niet gepubliceerd.