8.4 Macro's wijzigen - zelf macro's schrijven
Voorbeeld:
Je wil een macro maken om in een cel de volgende opmaakkenmerken in te stellen: lettertype Arial, puntgrootte 14, datumopmaak in de vorm 'maandag, 7 februari 2000', vet en cursief. Noem de macro 'Datumopmaak' en voorzie de sneltoetscombinatie CTRL+SHIFT+D.
Neem de macro op. Kies daarbij voor 'Relatieve verwijzing'. Ga naar het modulevenster:
| De macro ziet er oorspronkelijk uit zoals hieronder. Je kan een aantal lijnen die toch allen maar standaardinstellingen aanduiden verwijderen. Ze zijn vet weergegeven | Je kan bovendien wijzigingen aanbrengen. De puntgrootte breng je op 16 en de tekstkleur moet rood zijn. |
| Sub Datumopmaak() ' ' Datumopmaak Macro ' De macro is opgenomen op ... door ... ' ' Sneltoets: CTRL+SHIFT+D ' With Selection.Font .Name = "Arial" .Size = 14 .Strikethrough = False .Superscript = False .Subscript = False .OutlineFont = False .Shadow = False .Underline = xlUnderlineStyleNone .ColorIndex = xlAutomatic End With Selection.NumberFormat = "dddd, d mmm yyyy" Selection.Font.Bold = True Selection.Font.Italic = True End Sub |
Sub Datumopmaak() ' ' Datumopmaak Macro ' De macro is opgenomen op ... door ... ' ' Sneltoets: CTRL+SHIFT+D ' With Selection.Font .Name = "Arial" .Size = 16 .Color = RGB(255, 0, 0) End With Selection.NumberFormat = "dddd, d mmm yyyy" Selection.Font.Bold = True Selection.Font.Italic = True End Sub |
. In tegenstelling tot de macro 'Vetrood' werkt de macro Datumopmaak in op de geselecteerde cel(len). 'Selection' is een eigenschap van een object van het type 'Window' die als resultaat een object van het type 'Range' oplevert indien de selectie uit één of meerdere cellen bestaat. Vervolgens wijst de eigenschap 'Font' naar een 'Font-object'. Zo'n object stelt het lettertype van het betreffende object voor, hier de geselecteerde cel(len). Een 'Font-object' heeft een aantal kenmerken: lettertypenaam, tekengrootte, kleur, ... Met de eigenschappen Name, Size en Color kan je deze een waarde geven. Heb je meerdere eigenschappen van een object in te stellen dan kan je best de instructie With ... End With gebruiken. Dit levert een kortere schrijfwijze op. De eigenschap Colorindex kan een waarde tussen 1 en 56 krijgen (of de overeenkomstige symbolische constante). Bij de eigenschap Color kan je de waarde aangeven met de functie RGB(x,y,z) waarbij 0<= x,y,z <= 255. Uiteraard heb je op die manier meer combinatiemogelijkheden.
| De kortste notatie van de macro Datumopmaak wordt uiteindelijk: |
| Sub Datumopmaak() ' Sneltoets: CTRL+SHIFT+D With Selection.Font .Name = "Arial" .Size = 16 .Color = RGB(255, 0, 0) .Bold = True .Italic = True End With Selection.NumberFormat = "dddd, d mmm yyyy" End Sub |
Voorbeeld 2:
Plaats in A1:A7 de middagtemperaturen van één week. Zet het gemiddelde eronder. Positieve temperaturen worden in het groen, negatieve in het rood weergeven. Beeld alle getallen met 2 decimalen af. A8 krijgt een gele achtergrond.
| Sub Weekgemiddelde() ' ' Weekgemiddelde Macro ' De macro is opgenomen op ... door ... ' ' Sneltoets: CTRL+SHIFT+W ' ActiveCell.FormulaR1C1 = "5.5" Range("A2").Select ActiveCell.FormulaR1C1 = "4.6" Range("A3").Select ActiveCell.FormulaR1C1 = "7" Range("A4").Select ActiveCell.FormulaR1C1 = "2" Range("A5").Select ActiveCell.FormulaR1C1 = "-0.5" Range("A6").Select ActiveCell.FormulaR1C1 = "2" Range("A7").Select ActiveCell.FormulaR1C1 = "4" Range("A8").Select ActiveCell.FormulaR1C1 = "=AVERAGE(R[-7]C:R[-1]C)" Range("A1:A8").Select Selection.NumberFormat = "[Green]+0.00;[Red]-0.00" Range("A8").Select With Selection.Interior .ColorIndex = 6 .Pattern = xlSolid End With End Sub |
De eigenschap ActiveCell verwijst
naar de actieve cel in het huidige of in het aangeduide venster. Activecell is een
eigenschap van een Window-object. Met de eigenschap FormulaR1C1 kan je de formule voor een cel instellen of lezen. Voor numerieke waarden mogen de aanhalingstekens weggelaten worden. Bevat de cel een constante, dan wordt deze constante weergegeven bij het lezen. Er bestaan 2 zgn. verwijzingstypes: het R1C1-type en het A1-type. Bij het opnemen van macro's wordt het type R1C1 gebruikt. Onder dit kader vind je enkele voorbeelden ter verduidelijkng. De methode Select selecteert een object. Dit kan ook één cel zijn. In dat geval kan je ook de methode Activate gebruiken. De formule voor A8 nl. "=AVERAGE(R[-7]C:R[-1]C)" maakt het gemiddelde van het celbereik met begincel R[-7]C. Dit is de cel die 7 rijen boven de actieve cel in dezelfde kolom ligt. Zo ligt de eindcel R[-1]C juist boven de actieve. |
Celverwijzingstypes:
| De celverwijzingstypes A1 en R1K1 in Excel: Via Extra, Opties, Algemeen kan je het celverwijzingstype instellen. Standaard is er gekozen voor het type 'A1'. Bij het R1K1 verwijzingstype worden de kolommen zoals de rijen ook genummerd. |
De celverwijzingstypes A1 en R1K1 in VBA: Om naar een celbereik te verwijzen met de eigenschap 'Range' moet je het A1-verwijzingstype gebruiken terwijl celverwijzingen in formules enkel in het R1C1-verwijzingstype kunnen. |
Bij beide types is relatieve en absolute adressering mogelijk. Bij een relatief adres wordt naar een cel werwezen vertrekkende van de huidige of actieve cel daar waar bij een absoluut adres de plaats van de cursor of de huidige selectie geen invloed heeft.
In de verwijzingsstijl A1 gebruik je het '$'-teken om een (deel van) een adres absoluut te maken. Het adres $B$5 heet absoluut, $B5 en B$5 zijn gemengde adressen terwijl B5 een relatief adres is.
Met een verwijzing in de vorm van R1K1 wordt de locatie van een cel aangeduid met een 'R', gevolgd door een rijnummer en een 'K', gevolgd door een kolomnummer. Staat het rij- en/of kolomnummer tussen vierkante haakjes, dan gaat het om een relatieve verwijzing anders om een absolute.
| Stel dat B5 de actieve cel is, dan verwijst | naar |
| R[2]K[2] | D7 |
| R[-2]K | B3 |
| R2K2 | B2 |
| R[-1] | rij 4 |
| R | huidige rij (absolute verwijzing) |
| R1K1 | A1 |
Bij het verwijzingstype R1K1 zie je bij relatieve adressen dus rechtstreeks waar de bedoelde cel zich bevindt t.o.v. de actieve.
| De macro Weekgemiddelde wordt aangepast zodat met relatieve adressen kan gewerkt worden. Ook worden de temperaturen in een invoervenster ingetikt. Het volstaat dus om de cursor bij het begin van de uitvoering van de macro op de gewenste plaats te zetten. |
| Sub Weekgemiddelde() ' ' Weekgemiddelde Macro ' De macro is opgenomen op ... door ... ' ' Sneltoets: CTRL+SHIFT+W ' ActiveCell.FormulaR1C1 = InputBox("Maandag", "Weekgemiddelde") ActiveCell.Offset(1, 0).Activate ActiveCell.FormulaR1C1 = InputBox("Dinsdag", "Weekgemiddelde") ActiveCell.Offset(1, 0).Activate ActiveCell.FormulaR1C1 = InputBox("Woensdag", "Weekgemiddelde") ActiveCell.Offset(1, 0).Activate ActiveCell.FormulaR1C1 = InputBox("Donderdag", "Weekgemiddelde") ActiveCell.Offset(1, 0).Activate ActiveCell.FormulaR1C1 = InputBox("Vrijdag", "Weekgemiddelde") ActiveCell.Offset(1, 0).Activate ActiveCell.FormulaR1C1 = InputBox("Zaterdag", "Weekgemiddelde") ActiveCell.Offset(1, 0).Activate ActiveCell.FormulaR1C1 = InputBox("Zondag", "Weekgemiddelde") ActiveCell.Offset(1, 0).Activate ActiveCell.FormulaR1C1 = "=AVERAGE(R[-7]C:R[-1]C)" Selection.Interior.ColorIndex = 6 Range(ActiveCell.Offset(-7, 0).Address & ":" & ActiveCell.Offset(0, 0).Address).Select Selection.NumberFormat = "[Green]+0.00;[Red]-0.00" End Sub |
Inputbox(prompt,titel): functie die een aanwijzing weergeeft in een dialoogvenster, wacht tot de gebruiker tekst invoert of een knop kiest en geeft een string met de inhoud van het tekstvak als resultaat. Voor meer (optionele) parameters: zie het helpscherm.
Offset(aantal rijen, aantal kolommen): eigenschap waarmeeje de verplaatsing aangeeft t.o.v. de aangeduide cel. Eerst het aantal rijen dat moet opgeschoven worden, dan het aantal kolommen. Beide getallen kunnen negatief zijn.
Address: bij de eigenschap Range(cel1:cel2) wordt via de eigenschap Address het adres van een cel gevraagd. Verder wordt met het ampersand-teken (&) een expressie gevormd om de parameters van de eigenschap Range aan elkaar te kunnen rijgen.
Oefeningen: