Blog Betterwin
Formule versus VBA
In Excel kan met de functie SPATIES.WISSEN (Engels: TRIM) de spaties van een tekst in een cel gewist worden. In VBA bestaat ook de Trim-functie, maar die werkt net even wat anders en die geeft in een aantal situaties ook andere resultaten.
Als voorbeeld nemen we de tekst:
" Jan Piet "
Het gebruik van de de Excel-functie:
=SPATIES.WISSEN(" Jan Piet ")
geeft als resultaat:
"Jan Piet"
Het resultaat van de VBA-functie:
Trim(" Jan Piet ")
is echter:
"Jan Piet"
CellColorChange-gebeurtenis
In Excel zijn behoorlijk veel standaard gebeurtenissen beschikbaar, zoals Workbook_Open en Worksheet_SelectionChange, maar het wijzigen van de achtergrondkleur van een cel triggert geen gebeurtenis. Dit kan soms wel gewenst zijn (bijvoorbeeld in het geval van het Overrulen van voorwaardelijke opmaak). Het is echter wel mogelijk om hiervoor een apart event te definiëren. In dit artikel zal ik demonstreren hoe dat werkt.
Klassemodule clsCellColorChange aanmaken
Het nieuwe CellColorChange-event zal worden afgehandeld door een klasse-module. Hiervoor moet met behulp van de VBA-editor een nieuwe klassemodule worden aangemaakt (via Invoegen->Klassemodule). De naam van deze module moet daarna veranderd worden in clsCellColorChange. In deze klassemodule moet de volgende code gezet worden:
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.
Inleiding
Dit is een nieuwe blog. In deze blog worden met enige regelmaat berichten gepost met ervaringen en wetenswaardigheden op het gebied van vooral Excel, Access en VBA. De aanleiding voor deze blog is dat ik tegen onverwachte problemen aanliep bij het instellen van voorwaardelijke opmaak met VBA. Voorwaardelijke opmaak in Excel is een erg handig hulpmiddel. Als echter in een sheet met voorwaardelijke opmaak veel cellen gekopieerd worden, dan wordt de voorwaardelijke opmaak van deze cellen ook meegekopieerd. Op zich is dat natuurlijk goed, maar Excel gaat hier niet altijd goed mee om. Bereiken voor voorwaardelijke opmaak kunnen door Excel ten onrechte worden opgesplitst, waardoor er heel veel extra regels voor voorwaardelijke opmaak in het bestand terecht kunnen komen. Hierdoor neemt de bestandsgrootte toe en neemt de snelheid af. Het kan zelfs leiden tot het crashen van Excel. Ik was met een applicatie bezig met daarin een sheet met voorwaardelijke opmaak waarbij veel gekopieerd, verplaatst en geknipt en geplakt gaat worden. Ik besloot daarom de voorwaardelijke opmaak in te stellen met VBA. Bij het activeren van de betreffende sheet wordt daarbij eerst de voorwaardelijke opmaak verwijderd en daarna opnieuw ingesteld (N.B.: bij het opstarten van dit werkboek wordt altijd eerst een andere sheet getoond. Om de sheet met voorwaardelijke opmaak te zien moet deze eerst aangeklikt worden, zodat het activate-event altijd wordt getriggerd. In andere gevallen kan de code beter in een module gezet worden en kan deze bijvoorbeeld ook bij het Workbook_Open-event aangeroepen worden).
Voorwaardelijke opmaak met VBA
Het instellen van voorwaardelijke opmaak met VBA is op zich niet zo moeilijk dus ik dacht: ‘dat is zo gepiept’. Maar dat viel in de praktijk tegen. Ik liep namelijk tegen twee voor mij onbekende problemen aan die mij in eerste instantie wat hoofdbrekens kosten. Zoals gezegd wilde ik eerst de voorwaardelijke opmaak verwijderen en daarna opnieuw instellen. Dit wilde ik doen met het Sheet_Activate-event. Ik had daar in beginsel de volgende code voor gemaakt:
