Getallen opmaken in Excel VBA
Getallen zijn er in allerlei formaten in Excel-werkbladen. U bent misschien al bekend met het pop-upvenster in Excel om gebruik te maken van verschillende numerieke formaten:
De opmaak van getallen maakt de getallen gemakkelijker te lezen en te begrijpen. De Excel-standaard voor getallen die in cellen worden ingevoerd, is de indeling 'Algemeen', wat betekent dat het getal precies wordt weergegeven zoals u het hebt ingevoerd.
Als u bijvoorbeeld een rond getal invoert, b.v. 4238, wordt het weergegeven als 4238 zonder decimaalteken of scheidingstekens voor duizendtallen. Een decimaal getal zoals 9325.89 wordt weergegeven met de komma en de decimalen. Dit betekent dat het niet zal worden uitgelijnd in de kolom met de ronde cijfers en er extreem rommelig uitziet.
Ook is het, zonder de scheidingstekens voor duizendtallen, moeilijk te zien hoe groot een getal werkelijk is zonder de afzonderlijke cijfers te tellen. Gaat het om miljoenen of tientallen miljoenen?
Vanuit het oogpunt van een gebruiker die door een kolom met getallen kijkt, maakt dit het vrij moeilijk om te lezen en te vergelijken.
In VBA heb je toegang tot precies dezelfde reeks formaten die je aan de voorkant van Excel hebt. Dit geldt niet alleen voor een ingevoerde waarde in een cel op een werkblad, maar ook voor zaken als berichtvakken, UserForm-besturingselementen, grafieken en grafieken en de Excel-statusbalk in de linkerbenedenhoek van het werkblad.
De functie Opmaak is een uiterst nuttige functie in VBA in presentatietermen, maar het is ook erg complex in termen van de flexibiliteit die wordt geboden in de manier waarop getallen worden weergegeven.
Hoe de formaatfunctie in VBA te gebruiken
Als u een berichtvenster toont, kan de functie Opmaak direct worden gebruikt:
| 1 | MsgBox-indeling (1234567.89, "#,##0.00") |
Hierdoor wordt een groot getal weergegeven met komma's om de duizendtallen te scheiden en om 2 decimalen weer te geven. Het resultaat is 1.234.567,89. De nullen in plaats van de hash zorgen ervoor dat decimalen worden weergegeven als 00 in hele getallen en dat er een voorloopnul is voor een getal dat kleiner is dan 1
Het hashtag-symbool (#) vertegenwoordigt een tijdelijke aanduiding voor een cijfer dat een cijfer weergeeft als het op die positie beschikbaar is, of anders niets.
U kunt ook de opmaakfunctie gebruiken om een afzonderlijke cel aan te spreken, of een celbereik om de opmaak te wijzigen:
| 1 | Bladen ("Blad1").Bereik ("A1:A10").NumberFormat = "#,##0.00" |
Deze code stelt het celbereik (A1 tot A10) in op een aangepast formaat dat de duizendtallen scheidt met komma's en 2 decimalen toont.
Als u de opmaak van de cellen op de Excel-frontend controleert, zult u zien dat er een nieuwe aangepaste opmaak is gemaakt.
U kunt ook getallen opmaken op de Excel-statusbalk in de linkerbenedenhoek van het Excel-venster:
| 1 | Application.StatusBar = Format(1234567.89, "#,##0.00") |
U wist dit uit de statusbalk met behulp van:
| 1 | Toepassing.StatusBar = "" |
Een opmaakreeks maken
In dit voorbeeld wordt de tekst 'Totale verkoop' na elk nummer toegevoegd, evenals een scheidingsteken voor duizendtallen
| 1 | Sheets("Blad1").Range("A1:A6").NumberFormat = "#,##0.00"" Totale verkoop""" |
Zo zien je cijfers eruit:
Merk op dat cel A6 een formule 'SOM' heeft, en deze bevat de tekst 'Totale verkoop' zonder opmaak. Als de opmaak wordt toegepast, zoals in de bovenstaande code, wordt er geen extra exemplaar van 'Totale verkoop' in cel A6 geplaatst
Hoewel de cellen nu alfanumerieke tekens weergeven, zijn de cijfers nog steeds aanwezig in numerieke vorm. De 'SOM'-formule werkt nog steeds omdat deze de numerieke waarde op de achtergrond gebruikt, niet hoe het getal is opgemaakt.
De komma in de opmaakreeks levert het scheidingsteken voor duizendtallen. Merk op dat je dit maar één keer in de string hoeft te zetten. Als het aantal in de miljoenen of miljarden loopt, worden de cijfers nog steeds in groepen van 3 . verdeeld
De nul in de notatiereeks (0) is een tijdelijke aanduiding voor cijfers. Het geeft een cijfer weer als het er is, of een nul. De positionering is erg belangrijk om uniformiteit met de opmaak te garanderen
In de opmaakreeks zullen de hash-tekens (#) niets weergeven als er geen cijfer is. Als er echter een getal is zoals .8 (alle decimalen), willen we dat het wordt weergegeven als 0,80, zodat het op één lijn ligt met de andere getallen.
Door een enkele nul links van de komma en twee nullen rechts van de komma in de opmaakreeks te gebruiken, geeft dit het vereiste resultaat (0,80).
Als er maar één nul rechts van de komma zou staan, dan zou het resultaat '0,8' zijn en zou alles tot op één decimaal worden weergegeven.
Een opmaakreeks gebruiken voor uitlijning
We willen misschien alle decimale getallen in een bereik op hun decimale punten uitgelijnd zien, zodat alle decimalen direct onder elkaar staan, hoeveel decimalen er ook op elk getal staan.
U kunt hiervoor een vraagteken (?) gebruiken in uw opmaakreeks. De '?' geeft aan dat een nummer wordt weergegeven als het beschikbaar is, of een spatie
| 1 | Sheets("Blad1").Range("A1:A6").NumberFormat = "#,##0.00?? |
Hierdoor worden uw nummers als volgt weergegeven:
Alle decimale punten staan nu onder elkaar. Cel A5 heeft drie decimalen en dit zou de uitlijning normaal weggooien, maar het gebruik van het '?'-teken lijnt alles perfect uit.
Letterlijke tekens gebruiken binnen de opmaakreeks
U kunt elk letterlijk teken aan uw opmaakreeks toevoegen door er een backslash (\) voor te zetten.
Stel dat u een bepaalde valuta-indicator voor uw getallen wilt weergeven die niet is gebaseerd op uw landinstelling. Het probleem is dat als u een valuta-indicator gebruikt, Excel automatisch naar uw locale verwijst en deze wijzigt in de locale die is ingesteld in het Configuratiescherm van Windows. Dit kan gevolgen hebben als uw Excel-toepassing in andere landen wordt gedistribueerd en u er zeker van wilt zijn dat, ongeacht de landinstelling, de valuta-indicator altijd hetzelfde is.
U kunt in het volgende voorbeeld ook aangeven dat de getallen in miljoenen zijn:
| 1 | Sheets("Blad1").Bereik("A1:A6").NumberFormat = "\$#,##0.00 \m" |
Dit levert de volgende resultaten op uw werkblad op:
Als u een backslash gebruikt om letterlijke tekens weer te geven, hoeft u geen backslash te gebruiken voor elk afzonderlijk teken in een tekenreeks. Je kunt gebruiken:
| 1 | Sheets("Blad1").Range("A1:A6").NumberFormat = "\$#,##0.00 \mill" |
Hierdoor wordt 'mill' weergegeven na elk nummer binnen het opgemaakte bereik.
U kunt de meeste tekens als letterlijke tekens gebruiken, maar geen gereserveerde tekens zoals 0, #,?
Gebruik van komma's in een opmaakreeks
We hebben al gezien dat komma's kunnen worden gebruikt om scheidingstekens voor duizendtallen voor grote getallen te maken, maar ze kunnen ook op een andere manier worden gebruikt.
Door ze aan het einde van het numerieke deel van de opmaakreeks te gebruiken, fungeren ze als scalers van duizenden. Met andere woorden, ze delen elk getal door 1.000 elke keer dat er een komma staat.
In de voorbeeldgegevens laten we het zien met een indicator dat het in miljoenen is. Door één komma in de notatiereeks in te voegen, kunnen we die getallen delen door 1.000 laten zien.
| 1 | Spreadsheets ("Blad1").Bereik ("A1:A6").NumberFormat = "\$#,##0.00,\m" |
Hierdoor worden de getallen gedeeld door 1.000 weergegeven, hoewel het oorspronkelijke getal nog steeds op de achtergrond in de cel staat.
Als je twee komma's in de opmaakreeks plaatst, worden de getallen gedeeld door een miljoen
| 1 | Sheets("Blad1").Range("A1:A6").NumberFormat = "\$#,##0.00,,\m" |
Dit is het resultaat met slechts één komma (delen door 1.000):
Voorwaardelijke opmaak maken binnen de opmaakreeks
U kunt voorwaardelijke opmaak instellen aan de voorkant van Excel, maar u kunt dit ook doen binnen uw VBA-code, wat betekent dat u de opmaakreeks programmatisch kunt manipuleren om wijzigingen aan te brengen.
U kunt maximaal vier secties gebruiken binnen uw opmaakreeks. Elke sectie wordt begrensd door een puntkomma (;). De vier secties komen overeen met positief, negatief, nul en tekst
| 1 | Range("A1:A7").NumberFormat = "#,##0.00;[Rood]-#,##0.00;[Groen] #,##0.00;[Blauw]” |
In dit voorbeeld gebruiken we dezelfde hash-, komma- en nultekens als scheidingstekens voor duizendtallen en twee decimalen, maar we hebben nu verschillende secties voor elk type waarde.
De eerste sectie is voor positieve getallen en verschilt qua formaat niet van wat we al eerder hebben gezien.
Het tweede gedeelte voor negatieve getallen introduceert een kleur (rood) die tussen een paar vierkante haken wordt gehouden. Het formaat is hetzelfde als voor positieve getallen, behalve dat er een minteken (-) vooraan is toegevoegd.
Het derde gedeelte voor nulgetallen gebruikt een kleur (Groen) tussen vierkante haken, waarbij de numerieke reeks hetzelfde is als voor positieve getallen.
Het laatste gedeelte is voor tekstwaarden en het enige dat hiervoor nodig is, is weer een kleur (blauw) tussen vierkante haken
Dit is het resultaat van het toepassen van deze opmaakreeks:
U kunt verder gaan met voorwaarden binnen de opmaakreeks. Stel dat u elk positief getal boven de 10.000 als groen wilt weergeven, en elk ander getal als rood, dan kunt u deze notatiereeks gebruiken:
| 1 | Range("A1:A7").NumberFormat = "[>=10000][Groen]#,##0.00;[<10000][Rood]#,##0.00" |
Deze opmaakreeks bevat voorwaarden voor >=10000 die tussen vierkante haken staan, zodat groen alleen wordt gebruikt als het getal groter is dan of gelijk is aan 10000
Dit is het resultaat:
Breuken gebruiken bij het opmaken van tekenreeksen
Breuken worden niet vaak gebruikt in spreadsheets, omdat ze normaal gesproken gelijk staan aan decimalen die iedereen kent.
Soms dienen ze echter wel een doel. In dit voorbeeld worden dollars en centen weergegeven:
| 1 | Range("A1:A7").NumberFormat = "#,##0 "" dollars en "" 00/100 "" cent """ |
Dit is het resultaat dat zal worden geproduceerd:
Onthoud dat, ondanks dat de getallen als tekst worden weergegeven, ze er nog steeds op de achtergrond zijn als getallen en dat alle Excel-formules er nog steeds op kunnen worden gebruikt.
Datum- en tijdnotaties
Datums zijn eigenlijk getallen en u kunt er op dezelfde manier notaties op gebruiken als voor getallen. Als u een datum opmaakt als een numeriek getal, ziet u links van de komma een groot getal en een aantal decimalen. Het getal links van de komma geeft het aantal dagen aan vanaf 01-jan-1900, en de decimalen geven de tijd weer op basis van 24 uur
| 1 | MsgBox-indeling (Now(), "dd-mmm-yyyy") |
Hiermee wordt de huidige datum opgemaakt om '08-Jul-2020' weer te geven. Als u 'mmm' voor de maand gebruikt, worden de eerste drie tekens van de maandnaam weergegeven. Als je de naam van de volledige maand wilt, gebruik je 'mmmm'
U kunt tijden opnemen in uw notatietekenreeks:
| 1 | MsgBox-indeling (Now(), "dd-mmm-jjjj uu:mm AM/PM") |
Dit toont ’08-Jul-2020 01:25 PM’
'uu:mm' staat voor uren en minuten en AM/PM gebruikt een 12-uurs klok in plaats van een 24-uurs klok.
U kunt teksttekens opnemen in uw opmaakreeks:
| 1 | MsgBox-indeling (Now(), "dd-mmm-yyyy uu:mm AM/PM"" vandaag""") |
Dit toont ’08-Jul-2020 01:25 PM today’
U kunt ook letterlijke tekens gebruiken met een backslash ervoor, op dezelfde manier als voor tekenreeksen met numerieke notatie.
Vooraf gedefinieerde formaten
Excel heeft een aantal ingebouwde formaten voor zowel getallen als datums die u in uw code kunt gebruiken. Deze weerspiegelen voornamelijk wat beschikbaar is op de front-end voor nummeropmaak, hoewel sommige verder gaan dan wat normaal beschikbaar is in het pop-upvenster. U hebt ook niet de flexibiliteit over het aantal decimalen of het gebruik van scheidingstekens voor duizendtallen.
Algemeen nummer
Dit formaat geeft het nummer precies weer zoals het is
| 1 | MsgBox-indeling (1234567.89, "Algemeen nummer") |
Het resultaat is 1234567.89
Munteenheid
| 1 | MsgBox-indeling (123467.894, "Valuta") |
Dit formaat voegt een valutasymbool toe voor het nummer, b.v. $, £ afhankelijk van uw landinstelling, maar het zal het getal ook opmaken tot 2 decimalen en de duizenden scheiden met komma's.
Het resultaat is $ 1.234.567,89
Gemaakt
| 1 | MsgBox-indeling (123467.894, "Vast") |
Dit formaat geeft ten minste één cijfer aan de linkerkant weer, maar slechts twee cijfers aan de rechterkant van de komma.
Het resultaat is 1234567.89
Standaard
| 1 | MsgBox-indeling (123467.894, "Standaard") |
Dit geeft het getal weer met de duizend-scheidingstekens, maar alleen tot op twee decimalen.
Het resultaat is 1.234.567,89
procent
| 1 | MsgBox-indeling (123467.894, "Procent") |
Het getal wordt vermenigvuldigd met 100 en aan het einde van het getal wordt een percentagesymbool (%) toegevoegd. Het formaat wordt weergegeven tot op 2 decimalen
Het resultaat is 123456789,40%
Wetenschappelijk
| 1 | MsgBox-indeling (123467.894, "wetenschappelijk") |
Dit converteert het getal naar exponentieel formaat
Het resultaat is 1.23E+06
Ja nee
| 1 | MsgBox-indeling (123467.894, "Ja/Nee") |
Dit geeft 'Nee' weer als het nummer nul is, anders wordt 'Ja' weergegeven
Het resultaat is 'Ja'
Waar onwaar
| 1 | MsgBox-indeling (123467.894, "True/False") |
Dit geeft 'False' weer als het getal nul is, anders wordt 'True' weergegeven
Het resultaat is 'True'
Aan uit
| 1 | MsgBox-indeling (123467.894, "Aan/Uit") |
Dit geeft 'Uit' weer als het getal nul is, anders wordt 'Aan' weergegeven
Het resultaat is 'Aan'
Algemene datum
| 1 | MsgBox-indeling (Now(), "Algemene datum") |
Hierdoor wordt de datum weergegeven als datum en tijd in AM/PM-notatie. Hoe de datum wordt weergegeven, hangt af van uw instellingen in het Configuratiescherm van Windows (Klok en regio | Regio). Het kan worden weergegeven als 'mm/dd/jjjj' of 'dd/mm/jjjj'
Het resultaat is ‘7/7/2020 3:48:25 PM’
Lange datum
| 1 | MsgBox-indeling (Now(), "Lange datum") |
Hierdoor wordt een lange datum weergegeven zoals gedefinieerd in het Configuratiescherm van Windows (Klok en regio | Regio). Houd er rekening mee dat de tijd niet is inbegrepen.
Het resultaat is ‘dinsdag 7 juli 2022’
Middellange datum
| 1 | MsgBox-indeling (Now(), "Medium Date") |
Dit geeft een datum weer zoals gedefinieerd in de instellingen voor korte datums zoals gedefinieerd door de landinstelling in het Configuratiescherm van Windows.
Het resultaat is ’07-Jul-20’
Korte datum
| 1 | MsgBox-indeling (Now(), "Korte datum") |
Geeft een korte datum weer zoals gedefinieerd in het Configuratiescherm van Windows (Klok en regio | Regio). Hoe de datum wordt weergegeven, hangt af van uw landinstelling. Het kan worden weergegeven als 'mm/dd/jjjj' of 'dd/mm/jjjj'
Het resultaat wordt ‘7/7/2020’
Lange tijd
| 1 | MsgBox-indeling (Now(), "Lange tijd") |
Geeft een lange tijd weer zoals gedefinieerd in het Configuratiescherm van Windows (Klok en regio | Regio).
Het resultaat is '4:11:39 PM'
Gemiddelde tijd
| 1 | MsgBox-indeling (Now(), "Medium Time") |
Geeft een gemiddelde tijd weer zoals gedefinieerd door uw landinstelling in het Configuratiescherm van Windows. Dit wordt meestal ingesteld als 12-uursformaat met uren, minuten en seconden en het AM/PM-formaat.
Het resultaat is ’16:15 uur’
Korte tijd
| 1 | MsgBox-indeling (Now(), "Korte tijd") |
Geeft een gemiddelde tijd weer zoals gedefinieerd in het Configuratiescherm van Windows (Klok en regio | Regio). Dit wordt meestal ingesteld als 24-uursnotatie met uren en minuten
Het resultaat is ’16:18’
Gevaren van het gebruik van de vooraf gedefinieerde formaten van Excel in datums en tijden
Het gebruik van de vooraf gedefinieerde formaten voor datums en tijden in Excel VBA is erg afhankelijk van de instellingen in het Windows Configuratiescherm en ook van wat de landinstelling is ingesteld
Gebruikers kunnen deze instellingen eenvoudig wijzigen, en dit heeft invloed op hoe uw datums en tijden worden weergegeven in Excel
Als u bijvoorbeeld een Excel-toepassing ontwikkelt die gebruikmaakt van vooraf gedefinieerde indelingen binnen uw VBA-code, kunnen deze volledig veranderen als een gebruiker zich in een ander land bevindt of een andere landinstelling gebruikt dan u. Het kan zijn dat de kolombreedten niet passen bij de datumdefinitie, of dat bij een gebruiker het Active X-besturingselement, zoals een keuzelijst met invoervak, te smal is om de datums en tijden correct weer te geven.
U moet rekening houden met waar het publiek zich geografisch bevindt wanneer u uw Excel-toepassing ontwikkelt
Door de gebruiker gedefinieerde notaties voor getallen
Er zijn een aantal verschillende parameters die u kunt gebruiken bij het definiëren van uw opmaakreeks:
| Karakter | Beschrijving |
| Null-tekenreeks | Geen opmaak |
| 0 | Tijdelijke aanduiding voor cijfers. Geeft een cijfer of een nul weer. Als er een cijfer voor die positie is, wordt het cijfer weergegeven, anders wordt er 0 weergegeven. Als er minder cijfers dan nullen zijn, krijgt u voorloop- of volgnullen. Als er meer cijfers achter de komma staan dan nullen, dan wordt het getal afgerond op het aantal decimalen dat door de nullen wordt weergegeven. Als er meer cijfers voor de komma dan nullen staan, worden deze normaal weergegeven. |
| # | Tijdelijke aanduiding voor cijfers. Dit geeft een cijfer of niets weer. Het werkt hetzelfde als de tijdelijke aanduiding voor nul hierboven, behalve dat voorloop- en volgnullen niet worden weergegeven. Bijvoorbeeld 0,75 zou worden weergegeven met nul tijdelijke aanduidingen, maar dit zou 0,75 zijn met # tijdelijke aanduidingen. |
| . Decimale punt. | Slechts één toegestaan per formaattekenreeks. Dit teken is afhankelijk van de instellingen in het Configuratiescherm van Windows. |
| % | Percentage tijdelijke aanduiding. Vermenigvuldigt getal met 100 en plaatst % teken waar het voorkomt in de opmaakreeks |
| , (komma) | Duizend scheidingsteken. Dit wordt gebruikt als 0 of # tijdelijke aanduidingen worden gebruikt en de opmaakreeks een komma bevat. Een komma links van de komma geeft de afronding op het dichtstbijzijnde duizendtal aan. bijv. ##0, Twee aangrenzende komma's links van het scheidingsteken voor duizendtallen geven afronding op het dichtstbijzijnde miljoen aan. bijv. ##0,, |
| E-E+ | Wetenschappelijk formaat. Dit geeft het getal exponentieel weer. |
| : (dikke darm) | Tijdscheidingsteken - wordt gebruikt bij het formatteren van een tijd om uren, minuten en seconden te splitsen. |
| / | Datumscheidingsteken - dit wordt gebruikt bij het specificeren van een formaat voor een datum |
| - + £ $ ( ) | Geeft een letterlijk teken weer.Als u een ander teken wilt weergeven dan hier wordt vermeld, laat u dit voorafgaan door een backslash (\) |
Door de gebruiker gedefinieerde formaten voor datums en tijden
Deze tekens kunnen allemaal worden gebruikt in uw opmaakreeks bij het opmaken van datums en tijden:
| Karakter | Betekenis |
| C | Geeft de datum weer als ddddd en de tijd als ttttt |
| NS | Geef de dag weer als een getal zonder voorloopnul |
| dd | Geef de dag weer als een getal met voorloopnul |
| ddd | Geef de dag weer als afkorting (zon - za) |
| dddd | Toon de volledige naam van de dag (zondag - zaterdag) |
| dddd | Geef een datumserienummer weer als een volledige datum volgens Korte datum in de internationale instellingen van het Windows Configuratiescherm |
| dddddd | Geeft een datumserienummer weer als een volledige datum volgens Lange datum in de internationale instellingen van het Windows Configuratiescherm. |
| met wie | Geeft de dag van de week weer als een getal (1 = zondag) |
| ww | Geeft de week van het jaar weer als een getal (1-53) |
| m | Geeft de maand weer als een getal zonder voorloopnul |
| mm | Geeft de maand weer als een getal met voorloopnullen |
| mmm | Geeft maand als afkorting weer (jan-dec) |
| mmm | Geeft de volledige naam van de maand weer (januari - december) |
| Q | Geeft het kwartaal van het jaar weer als een getal (1-4) |
| ja | Geeft de dag van het jaar weer als een getal (1-366) |
| yy | Geeft het jaar weer als een tweecijferig getal |
| yyyy | Geeft het jaar weer als een viercijferig nummer |
| H | Geeft het uur weer als een getal zonder voorloopnul |
| hh | Geeft het uur weer als een getal met voorloopnul |
| N | Geeft de minuut weer als een getal zonder voorloopnul |
| nn | Geeft de minuut weer als een getal met voorloopnul |
| s | Geeft de seconde weer als een getal zonder voorloopnul |
| ss | Geeft de tweede weer als een getal met voorloopnul |
| ttttt | Geef een tijdserienummer weer als een volledige tijd. |
| AM PM | Gebruik een 12-uursklok en geef AM of PM weer om voor of na de middag aan te geven. |
| AM PM | Gebruik een 12-uurs klok en gebruik am of pm om voor of na de middag aan te geven |
| A/P | Gebruik een 12-uurs klok en gebruik A of P om voor of na de middag aan te geven |
| een/p | Gebruik een 12-uurs klok en gebruik a of p om voor of na de middag aan te geven |
