stijlen in Excel

Inhoud

Inleiding

Dit artikel is in het Engels verschenen op de MSDN site van Microsoft:

Using Styles to Dress Up Your Worksheets in Excel 2007

Dit artikel laat zien hoe je gebruik kunt maken van Excel's stijlen om het onderhoud aan een spreadsheet te vergemakkelijken.

Het is maar al te gemakkelijk om een werkblad te verfraaien met allerlei kleurtjes, kadertjes en andere tierelantijntjes. Omdat deze formatterings werkzaamheden vaak ad-hoc gebeuren, veroorzaakt dit nogal eens een rommelig geheel.

Door consistent gebruik te maken van stijlen in plaats van cel voor cel onderdelen van de formattering aan te passen, wordt je gedwongen gestructureerder te werken. Consistent toepassen van een (zo berperkt mogelijk aantal) stijlen blijkt er zelfs toe te leiden dat er beter over de algehele structuur van de werkmap wordt nagedacht: de kwaliteit van het rekenmodel kan er door verbeteren.

stijlen zijn daarom onderbelicht en verdienen meer aandacht.

De werking van stijlen

Een stijl is niets anders dan een set celopmaken waaraan een naam gegeven is. Alle cellen waarop dit profiel wordt toegepast zien er (deels) hetzelfde uit en wanneer je een element van het stijl aangepast, dan krijgen alle cellen deze veranderde opmaak mee.

Het gebruik van stijlen vergt enige gewenning, maar kan zeer grote voordelen bieden. Je kan je wellicht de situatie voorstellen waarbij je een fraai geformatteerde spreadsheet toont aan je leidinggevende, waarop deze vriendelijk verzoekt of je alle invoercellen misschien een licht gele achtergrond kleur kunt geven in plaats van een donker gele. Bij een groot bestand kan dit een aanzienlijke hoeveelheid werk betekenen. Had je een stijl voor deze cellen gebruikt, dan was deze wijziging een kwestie van seconden.

Bij stijlen is de celopmaak in feite een optelsom. Deze kan uit zowel een stijl als uit aangepaste onderdelen bestaan. Welke onderdelen binnen het stijl vallen bepaal je tijdens de definitie van het stijl (zie onderstaande afbeelding). Je krijgt dit venster via het lint: Start tab, Cel stijlen gallerij, Nieuwe celstijl:

stijlen venster voor Excel 2010

Daarna opent het nieuwe stijl venster:

stijlen venster voor Excel 2010

stijl venster voor Excel

Als op een cel het ene stijl wordt toegepast na het andere, dan zal het resultaat een optelsom van beide profielen zijn. Wat het resultaat van deze optelsom precies is, hangt af van welke onderdelen van beide stijlen "actief" zijn (hierover later meer). Op deze manier zouden "cascading styles" (getrapte stijlen) kunnen worden toegepast. Helaas "vergeet" Excel welke voorgaande stijlen op een cel zijn toegepast.

Een stijl maken

Een handige methode om een nieuw stijl op te stellen is om een cel te selecteren die reeds de juiste opmaak heeft. Daarna op de Start tab, groep Stijlen, Cel stijlen uitklappen, onderaan Nieuwe celstijl... kiezen.

Dialoogvenster stijl

Dialoogvenster stijl.

Om een nieuw stijl te maken typ je simpelweg de naam van het profiel in in het vakje "Naam stijl". Standaard worden vervolgens alle opties die een stijl kan bevatten aangevinkt. Haal de vinkjes weg bij de formattering die je niet in het stijl wilt hebben (in het voorbeeld van hierboven dus Getal en Uitlijning).

Stel alle overige onderdelen in zoals je die wenst. Dit kan je doen via de knop "Wijzigen". Het standaard venster "celeigenschappen" wordt dan getoond:

Venster celopmaak

Het venster celeigenschappen, dat verschijnt na klikken op "wijzigen" vanuit het venster stijl

Merk op, dat de rubrieken in het venster "stijl" overeenkomen met de tabjes van het dialoogvenster celeigenschappen.

Let op: Mocht je een eigenschap wijzigen op één van de tabjes die je oorspronkelijk in het venster stijl had uitgevinkt, dan zal deze optie automatisch aangevinkt worden.

Als je alle instellingen hebt gemaakt en de stijl is "opgeslagen", dan is de nieuwe stijl nog niet op de selectie toegepast, dat moet je vervolgens alsnog doen.

stijlen toepassen

Het is erg eenvoudig om een stijl toe te passen op een reeks cellen.

Klik op de Start tab en zoek de Stijlen groep. KLap de Cel Stijlen lijst uit en klik een stijl aan.

Afwijken van onderdelen van een stijl

Wanneer je van een cel met een bepaald stijl een eigenschap wijzigt die deel uitmaakt van het stijl, dan zullen wijzigingen in dat deel van het stijl niet langer aan die cel doorgegeven worden.

Als je dus het lettertype van bepaalde cellen hebt aangepast, terwijl het lettertype deel uitmaakt van het stijl, dan zal het lettertype van deze cellen niet wijzigen indien je dit in het stijl verandert:

Reeks cellen met stijl, 1 wijkt af.

Reeks cellen met stijl, 1 wijkt af.

Je kan de stijl van een cel opnieuw instellen door eenvoudigweg deze stijl opnieuw aan te klikken in de cel stijlen gallerij.

Tips voor het gebruik van stijlen

Beheren van stijlen

Om een goed overzicht te hebben van de stijlen die in de werkmap aanwezig zijn, is het aan te bevelen om een extra werkblad in te voegen met daarin een lijst met aanwezige stijlen. Plaats in kolom A de namen van de profielen en in kolom B een voorbeeld van het eindresultaat:

Tabel met stijlen in een werkblad

Tabel met stijlen in een werkblad

Als dan een stijl moet worden aangepast, selecteer dan de voorbeeldcel in kolom B en pas het profiel aan.

Het maken van een profiel dat lijkt op een bestaand profiel is dan tevens eenvoudig: kopieer de rij met het profiel dat goed past en plak deze rij elders in het werkblad. Selecteer de cel in kolom B van de nieuwe rij en kies Opmaak, stijl... . Voer de naam van het nieuwe profiel in en kies toevoegen en kies vervolgens wijzigen om het profiel aan te passen. Wijzig ook de naam in kolom A.

Omgang met stijlen

Gebruik stijlen zo strict mogelijk: vermijdt het aanpassen van 1 onderdeel van de opmaak van een cel waarop reeds een bepaald profiel is toegepast. Als er bijvoorbeeld een profiel is dat waarden in cellen in procenten met twee cijfers achter de komma weergeeft en je wil in een bepaalde (reeks) cel(len) 3 cijfers, maak dan een apart stijl aan voor deze situatie.

Deze werkwijze zal je ertoe dwingen goed na te denken over de verschillende celopmaken die je in jouw bestand wilt gebruiken. Door dit gedisciplineerd en gestruktureerd toe te passen zal je merken dat je bestand er beter uit gaat zien.

Onderscheid stijlen naar functie

Door na te denken over de struktuur van je Excel bestand kan je al gauw onderscheid maken tussen verschillende onderdelen van het bestand:

  • Invoer cellen
    Worden gebruikt om de variabele gegevens van uw model in te voeren
  • Parameter cellen
    Worden gebruikt om aangenomen grootheden van het model in te stellen, zoals grenswaarden.
  • Uitvoer cellen
    Cellen in een bereik dat voor uitvoer bedoeld is (presentatie, al dan niet om af te drukken).
  • Reken cellen
    De cellen waarin het eigenlijke rekenwerk gebeurt
  • Cellen bedoeld ter afbakening
    Door juist gebruik van gekleurde cellen kunnen duidelijke gebieden in het werkblad worden afgebakend.

Overweeg om stijlen te maken voor elk van deze soorten, elk met bijvoorbeeld een eigen achtergrond kleur.

VBA voorbeelden en hulpprogrammaatjes

Onderstaande hulp programmaatjes kunnen jouw dagelijks gebruik van stijlen vergemakkelijken en tonen tevens hoe het gebruik van stijlen in VBA in zijn werk gaat.

Opzoeken van cellen met een bepaald profiel

Onderstaande code zoekt naar cellen die een stijl hebben met "demo" in de naam:

Sub FindaStyle()
    Dim oSh As Worksheet
    Dim oCell As Range
    For Each oSh In ThisWorkbook.Worksheets
        For Each oCell In oSh.UsedRange.Cells
            If oCell.Style Like "*demo*" Then
                Application.GoTo oCell
                Stop
            End If
        Next
    Next
End Sub

Zodra een cel hieraan voldoet stopt de uitvoering van de code ("Stop") en krijg je de mogelijkheid de cel te bekijken.

Een lijst van stijlen maken

Onderstaande code maakt op een werkblad genaamd "Config - Styles" een lijst met de aanwezige stijlen:

Sub ListStyles()
    Dim oSt As Style
    Dim oCell As Range
    Dim lCount As Long
    Dim oStylesh As Worksheet
    Set oStylesh = ThisWorkbook.Worksheets("Config - Styles")
    With oStylesh
        lCount = oStylesh.UsedRange.Rows.Count + 1
        For Each oSt In ThisWorkbook.Styles
            On Error Resume Next
            Set oCell = Nothing
            Set oCell = Intersect(oStylesh.UsedRange, oStylesh.Range("A:A")).Find(oSt.Name, _
                oStylesh.Range("A1"), xlValues, xlWhole, , , False)
            If oCell Is Nothing Then
            lCount = lCount + 1
            .Cells(lCount, 1).Style = oSt.Name
            .Cells(lCount, 1).Value = oSt.NameLocal
            .Cells(lCount, 2).Style = oSt.Name
            End If
        Next
    End With
End Sub

Opmaak van cellen verwijderen en stijlen opnieuw instellen

Onderstaande code verwijdert alle opmaak van alle cellen van uw bestand en past vervolgens het stijl opnieuw op de cellen toe.

Pas op: je raakt dus veel opmaak kwijt als je geen zorgvuldig gebruik hebt gemaakt van stijlen!!!

Sub ReApplyStyles()
'Resets styles of cells to their original style (resets all formatting done on top of ANY style)
    Dim oCell As Range
    Dim oSh As Worksheet
    If MsgBox("Proceed with care:" & vbNewLine & vbNewLine & _
              "This routine will erase all formatting done on top of the existing cell styles." & vbNewLine & _
              "Continue?", vbCritical + vbOKCancel + vbDefaultButton2, GSAPPNAME) = vbOK Then
        For Each oSh In ActiveWindow.SelectedSheets
            For Each oCell In oSh.UsedRange.Cells
                If oCell.MergeArea.Cells.Count = 1 Then
                    oCell.Style = CStr(oCell.Style)
                End If
            Next
        Next
    End If
End Sub

Stijlen vervangen door een ander profiel

Onderstaande code gebruikt een lijst met twee kolommen; in de linker kolom staat het huidige stijl, in de rechter een vervangend stijl. de code zoekt vervolgens alle cellen met het linker stijl en vervangt dit door het rechter. Ideaal om opruiming te houden. Selecteer voor uitvoeren van de code de cellen in de linker kolom die je wil laten verwerken.

Sub FixStyles()
'-------------------------------------------------------------------------
' Procedure : FixStyles
' Company   : JKP Application Development Services (c)
' Author    : Jan Karel Pieterse
' Created   : 4-10-2007
' Purpose   : Replaces styles with the replacement style as defined by a two column list.
'             column 1 should contain the existing style, col 2 the replacing style
'-------------------------------------------------------------------------
    Dim sOldSt As String
    Dim sNewSt As String
    Dim oSh As Worksheet
    Dim oCell As Range
    Dim oSourceCell As Range
    Set oSourceCell = ActiveCell
    While oSourceCell.Value <> ""
        sOldSt = oSourceCell.Value
        sNewSt = InputBox("Please enter replacement style for:" & sOldSt, "Style changer", oSourceCell.Offset(, 1).Value)
        If sNewSt = "" Then Exit Sub
        If sNewSt <> "" And sNewSt <> sOldSt Then
            For Each oSh In ThisWorkbook.Worksheets
                For Each oCell In oSh.UsedRange
                    If oCell.Style = sOldSt Then
                        Application.GoTo oCell
                        On Error Resume Next
                        oCell.Style = sNewSt
                    End If
                Next
            Next
        End If
        Set oSourceCell = oSourceCell.Offset(1)
    Wend
End Sub

Formattering verwijderen van een tabel

Stel dat je net een bereik naar een tabel hebt omgezet (Zie dit artikel), maar het oorspronkelijke bereik had je voorzien van allerlei opmaak zoals randen en opvulkleuren. Tabellen hebben hun eigen tabel stijlen, maar die overschrijven formattering die je zelf hebt gedaan niet. Wat je kunt doen is de Standaard stijl toepassen op de tabel, maar dat zorgt ervoor dat al je getalsopmaak verdwijnt. Onderstaand macrootje maakt eerst een nieuwe stijl en zet het getalsopmaak gedeelte van die stijl uit. Als die stijl op de tabel wordt toegepast behoud je de getalsopmaak.

Sub RemoveFormattingOfTable()
    Dim oStNormalNoNum As Style
    On Error Resume Next
    Set oStNormalNoNum = ActiveWorkbook.Styles("NormalNoNum")
    On Error GoTo 0
    If oStNormalNoNum Is Nothing Then
        ActiveWorkbook.Styles.Add "NormalNoNum"
        Set oStNormalNoNum = ActiveWorkbook.Styles("NormalNoNum")
        oStNormalNoNum.IncludeNumber = False
    End If
    With ActiveSheet.ListObjects(1)
        .Range.Style = "NormalNoNum"
        'Now apply tablestyle:
        .TableStyle = "TableStyleLight1"
    End With
    ActiveWorkbook.Styles("NormalNoNum").Delete
End Sub

Internationale problemen

Het Style object in VBA heeft zowel een Name als een NameLocal eigenschap. Je zou dus kunnen denken dat als jouw Excel in het Nederlands is ingesteld je toch code kunt schrijven zoals dit:

    ActiveCell.Style = "Heading 1"
    

Of misschien dit:

    ActiveCell.Style = ActiveWorkbook.Styles("Heading 1")    

Maar met Excel in het Nederlands levert het uitvoeren van deze code deze foutmelding op:

Error 450; "Onjuist aantal argumenten of ongeldige eigenschappentoewijzing"

Dit komt dus omdat de NameLocal eigenschap van de Heading 1 stijl in het Nederlands is hetzelfde is als de Name eigenschap: Kop 1.

Erger nog, als je door de Styles collectie loopt, dan is de index van de Kop 1 stijl niet dezelfde als die van de Heading 1 style in Engels Excel. De stijlen zijn namelijk alfabetisch gesorteerd op hun vertaalde namen. Bovendien, zolang je in je huidige Excel sessie nog geen snelkoppeling gebruikt hebt, dan bestaan de hyperlink stijlen niet eens.

Zo zien de Engelse en Nederlandse lijst eruit:

Tabel met stijlen in een werkblad

Je ziet duidelijk dat de Kop en Heading stijlen niet op dezelfde rijen staan.

Voor werkmappen die in verschillende taalversies van Excel moeten werken (of invoegtoepassingen) gebruik ik een truc. Ik voeg een werkblad toe aan het bestand (of de invoegtoepassing) waarop ik cellen van een stijl voorzie. Als het bestand laadt, dan lees ik de (dan vertaalde) namen van de stijlen zodat ik die in de code foutloos kan gebruiken.

Conclusie

Er valt veel te winnen door gebruik te maken van stijlen in een Excel bestand. Om er een paar te noemen:

  • Consistente formattering van het model
  • Eenvoudig aanpassen van de opmaak van grote delen van het bestand
  • Strikt gebruik van stijlen lijdt tot gestructureerd werken
  • Kleinere kans op problemen met het bestand (Er is een grens aan het aantal verschillende celopmaken dat Excel in een bestand kan verwerken).

Met dit artikel heb ik getracht inzicht te geven in de toepassing van stijlen. Heb je aanvullende tips en/of opmerkingen, aarzel dan niet gebruik te maken van het commentaarvak onderaan deze pagina!


Vragen, suggesties en opmerkingen

Laatste 8 commentaren van in totaal 25 (Toon ze allemaal):

 


Commentaar van: Jan Karel Pieterse (20-2-2011 11:07:46) deeplink naar dit commentaar

Hallo Guido,

Selecteer alle plaatjes door de control toets ingedrukt te haouden en erop te klikken. Heb je ze allemaal geselecteerd, druk dan op control+1 om hun eigenschappen te wijzigen.


Commentaar van: Luc Vansteenkiste (26-8-2016 11:29:43) deeplink naar dit commentaar

Hoe maak ik een macro om rijen in te voegen op een willekeurige plaats en daarna de opmaak te kopiëren van een andere selectie.


Commentaar van: Jan Karel Pieterse (26-8-2016 11:56:47) deeplink naar dit commentaar

Hallo Luc,

Heb je al iets geprobeerd? Bijvoorbeeld een macro opnemen waarbij je het handmatig doet?


Commentaar van: José (12-3-2019 15:49:40) deeplink naar dit commentaar

Hallo,

Ik heb een NAW-bestand.(voornaam, naam, adres, postcode, plaats, etc.)
Om het leesbaarder te maken zou ik graag om de 5 regels een lijn willen zien.
Ik kan dat handmatig doen, maar op dik 600 regels is dat veel werk.
Heeft Excel (lees: jullie) daar een oplossing voor?
Ik kan wel het blad opmaken als tabel, maar dan wil ik de gehele regel met o.a. aanduiding kolom1, kolom2, etc niet zien.


Commentaar van: Jan Karel Pieterse (12-3-2019 16:38:16) deeplink naar dit commentaar

Hoi José,

Je kunt een aangepaste tabelopmaak maken waarbij je voor de "eerste rijstreep" de streep grootte instelt op 4.


Commentaar van: José (12-3-2019 21:44:21) deeplink naar dit commentaar

Bedankt Jan Karel Pieterse, Het werkt.
Alleen kreeg ik in de kolommen waar de eerste regel leeg was te zien de tekst KOLOM 4, KOLOM 7, etc.
Ik heb dat opgelost door deze tekt 'wit te kleuren'.
En witte tekst op witte achtergrond.. Tja, de grote onzichtbaar truc.
Dank je voor je snelle hulp. Grtss


Commentaar van: Jan Karel Pieterse (13-3-2019 07:20:39) deeplink naar dit commentaar

Hoi José,

Inderdaad stelt Excel veldnamen verplicht in een tabel (anders kan je er ook niet naar verwijzen!). Je kan ook spaties gebruiken als kopnaam, ze moeten wel verschillend zijn. Overigens is het niet gebruikelijk om lege kolommen te hebben in een tabel.


Commentaar van: José (13-3-2019 15:07:53) deeplink naar dit commentaar

dag Jan Karel Pieterse,
Nog even. Er zijn geen lege kolommen, maar wel missende telefoonnummers o.i.d. En die kunnen bovenaan staan in een kolom. Elke kolom is geheel (naam) of gedeeltelijk (faxnummer) gevuld.
grts.


Heeft u vragen, suggesties of opmerkingen? Gebruik dan dit formulier.

Mocht uw vraag niet direct relevant zijn voor deze pagina, maar een algemene Excel vraag betreffen, dan adviseer ik om deze hier te stellen: excelexperts.nl/forum/index.php.




Als u VBA code in uw commentaar plaatst, gebruik dan [VB] tags: [VB]Uw code[/VB].