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:
Daarna opent het nieuwe stijl venster:
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.
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:
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.
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
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:
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!
