Objekter i Excel

Excel (og de øvrige programmer i Office pakken) er objektorienterede. Det betyder, at man kan arbejde med "enkeltdele" af programmerne (programmernes objekter) i sin kode. At gennemgå objektorienteret programmering i detaljer, eller bare gennemgå alle objekterne i Excel er ikke denne artikels hensigt. Jeg vil se på nogle af de mest brugte objekter, og hvordan man kan bruge VBA til at håndtere disse objekter. Men først en kort forklaring på, hvad objekter egentlig er.

Hvad er et objekt?
Brug af objekter i kode
Objektvariabler
Egenskaben Count og Metoden Add
Programmering med objekter
Workbook
Worksheet
Range

Hvad er et objekt?

Et objekt er et element eller en helhed. Et sådant objekt kan indeholde/bestå af andre objekter, som igen kan indeholde andre objekter og så fremdeles. En bondegård kan fx være et objekt. Bondegården kan indeholde andre objekter, så som stalde, lader, hønsegårde osv. Hønsegården kan indeholde hønsehuse, som igen indeholder redekasser, og sådan kan man fortsætte.

Objekter er karakteriseret ved, at de har forskellige egenskaber. Et personobjekt har fx egenskaber så som højde, vægt, skostørrelse, hårfarve, øjenfarve osv. Desuden kan der knyttes såkaldte metoder til objektet. Metoder er handlinger, som objektet kan udføre. Personobjektet kan fx lytte, se, sove, løbe osv. Endelig kan objekter have hændelser, det vil sige begivenheder, som indtræffer i forbindelse med objekterne, og som kan betyde en ændring af objektets egenskaber, iværksættelse af en metode osv. Når hændelsen "forskrække" indtræffer på personobjektet, ændres egenskaben "ansigtskulør" til bleg, og metoden "øg puls" går i gang.

I Excel er der mange objekter. Mest overordnet er Excel selv, eller applikationen (Application), som rummer alle de andre objekter. Inde i dette objekt findes så andre objekter som værktøjslinier, projektmapper mm., som så igen kan indeholde objekter i form af knapper, ark osv. I alt er der mere end 100 forskellige objekter i Excel. Du kan se en oversigt over Excels objektmodel ved at starte hjælpen fra VBA editoren og skrive Microsoft Excel Object Model i søgeruden. Her vil jeg dog nøjes med at se på de tre mest anvendte af disse objekter: Workbook objektet, Worksheet objektet og Range objektet.

Objekter grupperes i objektsamlinger. Fx kan man tale om Worksheets (i flertal), som samlingen af objekter af typen Worksheet, der findes i et objekt af typen Worksbook. Alle samlinger har en fælles egenskab og en fælles metode. Egenskaben er "antal objekter i samlingen" og metoden er "Tilføj nyt objekt til samlingen". Se nedenfor.

- Til Top -

Bruge objekter i koden

Det er muligt at adressere objekterne med kode, men hvordan man gør, afhænger af, hvor man allerede "befinder sig" i forhold til de objekter, man skal adressere. Hvis jeg fx skal referere til et område i et ark, der allerede er aktiveret i en projektmappe, der er aktiveret, kan jeg nøjes med at skrive fx: Range("A1:A10). Skal jeg referer til et område i et ark, der ikke er aktiveret, må jeg have arknavnet med, fx:

Worksheets("Ark2").Range("A1:A10").

Er heller ikke projektmappen aktiveret, må man ty til

Workbooks("Mappe1.xls").Worksheets("Ark2").Range("A1:A10").

Bemærk at de enkelte objekter i objekthierarkiet adskilles med punktum, og at "navne" skrives i anførselstegn. Det er sjældent nødvendigt at sætte Application foran, men der er nogle få undtagelser.

Når man skal arbejde med objekter i sin kode, er det typisk for at hente eller ændre et objekts egenskaber, eller bruge objektets metoder. I en række sammenhænge udnytter man også et objekts hændelser til at sætte en proces i gang.

Vil jeg undersøge et objekts egenskaber, kan jeg kalde objektet, og vise egenskaberne i en meddelelsesboks, gemme dem i en variabel osv.

Sub HentArknavn()
MsgBox ActiveSheet.Name
End Sub

viser navnet på det aktive ark i meddelelsesboks. Man kan også ændre en egenskabs værdi:

Sub SkiftNavn()
Sheets("Ark2").Name = "Jan"
End Sub

ændrer navnet på Ark2 til Jan.

Jeg kan udnytte en metode til at udføre en handling på mit objekt.

Sub SletArk()
Sheets("Ark3").Delete
End Sub

udnytter arkets "slettemetode" til at slette et ark. En sletning vil øvrigt generere denne meddelelses, på samme måde som hvis man sletter arket manuelt. Hvis en kode anvendes til at slette et ark, er det imidlertid ofte helt bevidst, og så behøver man ikke at vise dialogboksen. Det kan ændres ved at anvende en af application objektets egenskaber.

Ved at ændre koden til

Sub SletArk()
    Application.DisplayAlerts = False
    Sheets("Ark4").Delete
    Application.DisplayAlerts = True
End Sub

undgås dialogboksen. Application.DisplayAlerts = False ændrer den egenskab ved applikationen, at den viser advarsler i en række sammenhænge. Men netop fordi det er en egenskab ved hele applikationen, er det vigtigt at ændre den tilbage igen. Ellers vises der heller ikke advarsler i andre sammenhænge.

- Til Top -

Objektvariabler

Når man arbejder med objekter har man, som i alle andre sammenhænge, brug for at erklære variabler. I artiklen om datatyper til variabler, kan man læse at der findes en datatype ved navn object, som kan indeholde referencer til objecter. Imidlertid ligger der lidt mere i det end bare som så.

Det er korrekt at man kan definere en variabel af typen Object, som kan indeholde referencer til alle typer af objekter. Imidlertid kan man også være mere præcis i sin variabelerklæring, hvis man på forhånd ved, hvilke objekttyper, der er tale om, fx Worksheet eller Range. Variablerne erklæres på samme måde som alle andre erklæringer, fx

Dim objMitArk As Object
Dim objNytArk As Worksheet
Dim objMinFil As Workbook
Dim objMitOmraade As Range

og så videre. Ovenfor ses eksempler på erklæringer med både den generelle datatype som de mere specifikke.

Er erklæringerne helt som sædvanligt er tildelingen lidt anderledes. Normalt ville man kunne tildele en værdi til en variabel, fx af datatypen String med

strNavn = "Ole Jensen".

Ved tildeling af referencer til objektvariabler er man imidlertid nødt til at bruge en såkaldt Set-sætning. Et eksempel på en tildeling kunne være:

Set objNytArk = Workbooks("Regnskab2009").Worksheets("Budget_2009")

Når først variablerne er oprettede kan man referere til dem i sin kode, og man kan udnytte objekternes egenskaber og metoder ved at referere til variablen i stedet:

Sub SkiftNavn2()
    Dim objNytArk As Worksheet

    Set objNytArk = Workbooks("Mappe1").Worksheets("Ark1")

    objNytArk.Name = "Budget2009"
End Sub

Mere om Set-sætningen kommer i en senere artikel om automation.

- Til Top -

Egenskaben Count og metoden Add

Ovenfor nævnte jeg, at alle samlinger har en egenskab og en metode til fælles. Egenskaben var antallet af objekter i en samling (Count). Og metoden kunne tilføje et nyt objekt til en samling (Add). Her et par eksempler på disse to:

For at få returneret antallet af regneark i en projekt mappe, kan man bruge:

Sub HvorMangeArk()
MsgBox Worksheets.Count
End Sub

Denne tæller imidlertid kun regneark. Indeholder mappen også diagramark, tælles disse ikke med. Skal de det, kan man ændre til

Sub HvorMangeArk2()
MsgBox Sheets.Count
End Sub

der tæller alle ark, uanset hvilken type ark, der er tale om.

Vil jeg tilføje et nyt ark, kan jeg gøre det med

Sub TilfoejArk()
Worksheets.Add
End Sub

eller

Sub TilfoejArk2()
Sheets.Add
End Sub

Begge varianter tilføjer et nyt regneark.

- Til Top -

Programmering med objekter

I forbindelse med programmering med objekter, er der et par programmeringsfunktioner, der er vigtige at kunne. De er begge "løkke-agtige", omend kun den ene er en rigtig løkke, nemlig For...Each...Next, der minder en hel del om den almindelige For...Next løkke, der er beskrevet her. Den anden struktur, der er interessant er With...End With strukturen. Denne struktur anvendes til at ændre flere egenskaber ved samme objekt, uden at skulle adressere objektet flere gange.

Skal jeg fx indstille en række egenskaber for et objekt, i eksemplet nedenfor Application objektet, kan jeg gøre det med

Sub IndstilExcel()
    Application.Calculation = xlManual
    Application.Iteration = True
    Application.MaxIterations = 1
    Application.MaxChange = 0.01
    Application.StandardFont = "Verdana"
End Sub

osv. for hver egenskab, der ønskes ændret. Men det kan også gøres med

Sub IndstilExcel2()
    With Application
        .Calculation = xlManual
        .Iteration = True
        .MaxIterations = 1
        .MaxChange = 0.01
        .StandardFont = "Verdana"
    End With
End Sub

Skal jeg gennemløbe alle objekter i en samling, kan jeg bruge For...Each...Next, fx

Sub RetAlleNavne()
    Dim sh As Worksheet
    Dim i As Byte

    i = 1

    For Each sh In Worksheets
        sh.Name = "Jan" & i
        i = i + 1
    Next sh
End Sub

der retter alle regneark til at at hedde Jan og en tæller, fx Jan1, Jan2, osv. Men nu til noget lidt mere praktisk med udgangspunkt i de tre valgte objekttyper.

- Til Top -

Workbook (Projektmappe)

Dette objekt repræsenterer en hel projektmappe, som jo er det samme som en fil. En sådan Workbook kan derfor gemmes, åbnes, lukkes, udskrives, der kan oprettes nye projektmapper osv. Alt dette kan også gøres med VBA. Selve Workbook-objekttypen kan adresseres på forskellig måde, afhængig af, hvilket specifikt objekt, der er tale om:

Worksbooks("Regnskab2009") for at adresse en navngiven projektmappe.
ThisWorkbook for at adressere den Projektmappe, som indeholder koden.
ActiveWorkbook for at adressere den projektmappe, som er aktiv (i fokus), når koden afspilles.

Nedenstående kode gemmer den aktive projektmappe under et nyt navn:

Sub GemNytNavn()
ActiveWorkbook.SaveAs "c:\NyProjektmappe.xls"
End Sub

Koden her udskriver den projektmappe, som indeholder koden:

Sub UdskrivDenne()
ThisWorkbook.PrintOut
End Sub

Her åbnes en projektmappe fra C-drevet:

Sub LukOp()
Workbooks.Open ("c:\rtk.xls")
End Sub

Det var altså metoder, men objekter har jo også egenskaber. Man kan fx bruge egenskaben Saved til at undersøge om en projektmappe er gemt efter de sidste ændringer og så handle i forhold til det. Dette kombineres ofte med fx logiske funktioner, som fx nedenstående

Sub RettetEllerEj()
    If ActiveWorkbook.Saved = True Then
        ActiveWorkbook.Close
    Else
        ActiveWorkbook.Save
        ActiveWorkbook.Close
    End If
End Sub

der undersøger om projektmappen er gemt inden den lukker.

- Til Top -

Worksheet (Ark)

Worksheet-objektet repræsenterer et enkelt ark. Ligesom tilfældet er med Worksbooks, kan man adresse et konkret, navngivent ark eller det aktive ark. Man kan ikke adressere ThisWorksheet, da arket ikke indeholder kode (Og dog. Jeg vender tilbage til dette i næste artikel, som kommer til at handle om om hændelser). Til gengæld kan man anvende en mere generisk form, der adresserer et ark på et bestemt sted i rækkefølge af ark eller man kan anvende arkets "kodenavn".

Sub SkiftNavn()
Sheets("Ark2").Name = "Jan"
End Sub

skifter navn på Ark2 til "Jan" Her adresseres arkets navn.

Sub SkiftNavn2()
Sheets(2).Name = "Jan"
End Sub

skifter navn på det ark, som ligger som nummer 2 i ark-rækkefølgen. Her adresseres rækkefølgen af arkene.

Sub SkiftNavn3()
ActiveSheet.Name = "Jan"
End Sub

skifter navn på det aktive ark. Her adresseres det aktive ark.

Problemet med eksempel 1 er, at såfremt, der ikke findes et Ark2, fx fordi det er omdøbt, vil koden fejle. I eksempel 2 opstår problemet, hvis det ark, man ønsker at omdøbe ikke læn gere er nummer to i rækkefølgen fordi det er flyttet eller der er indsat eller slettet ark. I så fald vil koden ikke fejle, men ændre navn på det forkerte ark. Her kunne løsningen være at bruge arkets "kodenavn".

Illustrationen til venstre viser et udsnit af objektbrowseren i Visual Basic Editoren.Jeg kan man se arketnes kodenavne, som er Ark1, Ark2 osv, altså det, der står før parentesen.Dette navn ændres ikke selv om arkene omdøbes. Omdøbes fx Per til Niels, vil det stadig være Ar4, men der vil stå Niels i parentesen. Rækkefølgen vil også altid være den samme, selv om der flyttes rundt på arkene i prejktmappen. Skalarket "Hans" omdøbes til "Hendes", kan det gøres med

Sub SkiftNavn4()
Ark3.Name = "Hendes"
End Sub

og så er det ligegyldigt, hvad arket hedder i forvejen og hvor i rækkefølgen det står.

Flere eksempler

Sub SkiftFont()
ActiveSheet.Cells.Font.Bold = True
End Sub

gør alle celler i det aktive ark fede på en gang.

For alle de regneark, der er i min projektmappe, vil jeg give cellerne fed, kursiv og grøn skrift på en rød baggrund:

Sub SkiftMere()
    Dim ws As Worksheet
    For Each ws In ActiveWorkbook.Worksheets
        With ws.Cells
            .Font.Bold = True
            .Font.Italic = True
            .Font.ColorIndex = 4
            .Interior.ColorIndex = 3
        End With
    Next ws
End Sub

- Til Top -

Range (Område)

Jeg har gemt det mest anvendte objekt til sidst. Range-objektet anvendes hver gang man skal adressere et område uanset om det består af mange eller kun en enkelt celle. Excel har ikke noget celle-objekt, så Range-objektet er den eneste måde at adressere celler på. Range-objektet kan også indeholde flere adskilte områder, en eller flere hele rækker eller kolonner, eller et område, der går igen over flere ark.

Dette objekt har utroligt mange egenskaber og metoder, og det vil simpelthen ikke være muligt at komme ind på dem alle. Du kan se eksempler på nogle af dem, i de kodeeksempler der findes under makroer. Her vil jeg bare vise nogle stykker, for at illustrere, hvordan de virker.

Sub Test1()
Range("A1").Value = 100
End Sub

giver celle A1 i det aktive ark værdien 100.

Sub Test2()
Range("A1").Value = Range("a1").Value + 100
End Sub

lægger 100 til den værdi, der allerede står i A1 i det aktive ark. Egenskaben Value tildeler altså en værdi, eller aflæser en værdi,

Sub Test3()
MsgBox Range("a1").Value
End Sub

aflæser således værdien i A1 og skriver den i en meddelelsesboks.

Sub Test4()
ActiveCell.Offset(1, 1).Value = "Jan"
End Sub

Tildeler værdien Jan til den celle, der står en kolonne til højre og en række under den række, som cellemarkøren aktuelt står i. Offset forskyder altså i forhold til et udgangspunkt. Første tal i parentesen er forskydningen i rækker. Negative tal forskyder opad, positive nedad. Det andet tal er forskydningen i kolonner. Negative tal forskyder mod venstre, positive mod højre.

Sub Test5()
MsgBox Range("a1:x234").Count
End Sub

fortæller i en meddelelsesboks, hvor mange celler, der er i det angivne område.

Sub Test6()
Range("a1:x234").Clear
End Sub

rydder indhold og format fra et område.

Sub Test7()
Range("a1:x234").ClearContents
End Sub

rydder kun indhold, men efterlader format.

Sub Test8()
Range("a1").Formula = "=Sum(a2:A10)"
End Sub

skriver den anførte formel i cellen. Pas dog på sproget. Ovenstående vil virke, fordi funktion SUM() hedder det samme på både dansk og engelsk. Dette vil imidlertid give en fejl i regnearket:

Sub Test9()
Range("a1").Formula = "=Middel(a2:A10)"
End Sub

selv om formlen, der kommer til at stå i regnearket ser rigtig ud, nemlig =Middel(A2:A10). Excel viser dog fejlkoden #NAVN!, som om den ikke genkender funktionsnavnet.

Sub Test10()
Range("a1").Formula = "=average(a2:A10)"
End Sub

vil derimod sætte MIDDEL()-formlen ind, så den bliver rigtig. Hvis man ikke kender alle de engelske betegnelser for de danske funktionsnavne, kan man i stedet bruge:

Sub Test11()
Range("a1").FormulaLocal = "=middel(a2:A10)"
End Sub

Så kan man anvende de danske funktionsnavne i stedet for de engelske.

Der er yderligere en række egenskaber, der kan være interessante at nævne, fx Address, Row og Column. De returnerer henholdsvis adressen eller rækken eller kolonnen for en given celle. De er selvfølgelig mest interessante, hvis man ikke på forhånd kender cellen, fx giver

Sub Test12()
MsgBox Range("a1").Address
End Sub

ikke megen mening, mens

Sub Test13()
MsgBox ActiveCell.Address
End Sub

gør.

Man kan også vælge eller aktivere områder, men det er sjældent nødvendigt, og koster ofte bare ekstra ressourcer. Skal jeg fx kopiere et område, kan jeg bruge følgende kode:

Sub Test14()
    Range("A2:A10").Select
    Selection.Copy
    Range("B1").Select
    Selection.PasteSpecial
End Sub

Dette er dog ikke specielt hensigtsmæssigt. En indspillet makro, vil normalt give dette resultat, men bedre er

Sub Test15()
Range("A2:A10").Copy
Range("B1").PasteSpecial
End Sub

der både er mere effektivt, og hurtigere at skrive. Selection kræver ekstra ressourcer af pcen og tager derfor længere tid. Der er dog nogle situationer, hvor Select er nødvendig, men de er få.

Til sidst et kommenteret eksempel på en For.. Each...Next og en With sætning på et Range-objekt. I cellerne B1 til B10 har jeg bogstavet A og så en værdi (et tal), fx A1. Nu vil jeg gerne af med bogstavet. Samtidigt vil jeg gerne gøre alle cellerne grønne (skriftfarve) og skrive med fed skrift. Tallene formateres med to decimaler. Hver anden række skal farves grå (baggrund). Til sidst skal der indsættes en formel i B12, der beregner gennemsnittet af tallene i området.

Sub Test16()
    'Alle celler i området gennemløbes
    For Each c In Range("B1:B10").Cells
    'Først fjernes den første karakter
        c.Value = Mid(c.Value, 2, Len(c.Value))
   'Så ændres til grøn tekst der gøres fed
        With c
            .Font.ColorIndex = 4
            .Font.Bold = True
        End With
   'Hvis rækkenummeret er deleligt med 2 farves cellen grå
        If c.Row Mod 2 = 0 Then
            c.Interior.ColorIndex = 15
        End If
    'Der gås videre til næste celle
    Next c
    'Til sidst indsættes formlen
    Range("B12").FormulaLocal = "=Middel(B1:B10)"
End Sub

Funktionerne MID() og Len() er allerede gennemgået i en tidligere artikel. Funktionen Mod, der virker noget anderledes end andre funktioner finder resten af en division. Først skrives tallet, der skal divideres, så kommer funktionsnavnet og til sidst tallet, der skal divideres med. I dette tilfælde betyder altså Rækkenummer Mod 2, at vi dividerer rækkenummeret med 2. Hvis resten er 0 farves cellen grå, ellers sker der ingenting.

Hvordan finder man så alle de egenskaber og metoder, der kan knyttes til de forskellige objekter? En måde er selvfølgelig at slå op i hjælpen. En anden måde er at indspille en makro, der ændrer en egenskab eller anvender en metode, og så se, hvilken kode, der kommer ud af det. En anden metode er simpelthen at begynde at skrive koden, så hjælper VBA editoren selv videre.

Skriver du fx Range("A1"). vil editoren vise en oversigt over de egenskaber og metoder, der er tilgængelige for objektet.

- Til Top -
- Tilbage til Programmering -
- Tilbage til makroer -
- Tilbage til Excel -