Excel makroer & VBA - tips, tricks og eksempler

Dette er mine sider om programmering i Excel med makroer, eller VBA (Visual Basic for Applications) som det også hedder.

Excel makroer er et stærkt værktøj til at automatisere opgaver, som ellers kræver megen tid, og man kan udvikle applikationer med dialogbokse, og hvad ved jeg, til andre brugere.

Dette er ikke en lærebog - dem er der nok af. Nej, jeg videregiver blot erfaringer med eksempler på kode, jeg har haft gavn af.

Der er ikke meget begynderstof - det meste er for brugere, som er kommet i gang, og som måske spekulerer over, hvordan de lige løser et bestemt problem.

Alligevel kan der sagtens være inspiration at hente for de Excel-brugere, som leger med makro-optageren og gerne vil gøre koden mere strømlinet. Søger du noget specielt, kan søgefunktionen på toppen af siden fortælle, om det behandles på disse sider.

På de fleste sider er der kode, som du kan kopiere ind i et VBA-modul. Du kan også downloade nogle regneark med eksempler. På små skærme kan kodelinjer være tekstombrudte, men linjeskiftene bliver OK igen, når du indsætter det kopierede.

Alle eksemplerne er lavet og afprøvet i enten Excel 2000 eller Excel 2003. Hvis de ikke virker i nyere versioner, så bebrejd venligst Microsoft og ikke mig ;-)

På alle sider gemmer menuen sig i "burgeren" i øverste venstre hjørne. Nu er det ikke hver og hveranden dag, disse sider opdateres, så vil du underrettes, når det sker, kan du abonnere på mit RSS feed.

Om mig?

Jeg har primært arbejdet med programmering i Excel på mit arbejde. Det er især automatisk import af procesdata og dataudtræk til forskellige formål.

I den tunge ende ligger programmer, som kan blande råstoffer i det rette kemiske forhold ved at bruge Excels problemløser til løsning af ligninger med flere ubekendte.

Jeg har hentet megen hjælp og inspiration på nettet og følte, at det var tid at give noget tilbage. Derfor disse sider.

Automation error

Med Excel 2016 kom en ny bug: Når du åbner (nogle) regneark med makroer lavet i Excel 2003 eller ældre, får du en fejlmeddelelse, "Automation error".

Makroerne kan køres uden problemer, men vil du den irriterende fejlmeddelse til livs, så gem i det nye format som et regneark med makroer (*.xlsm).

Kontakt

Du er velkommen til at sende mig en mail, hvis du har spørgsmål eller forslag til nye emner, jeg kan tage op.

Nu er der grænser for, hvor meget tid jeg kan bruge på dette, så hav venligst forståelse for, at jeg ikke kan love dig et svar eller en ny webside; men jeg læser alle mails!

Opdateringer

18. februar 2018. Ny side om at tilføje tekst til en tekstfil. Eksemplet er en tekstfil, hvori dato, tid og brugernavn logges, når regnearket åbnes, hvis brugernavnet er forskelligt fra XXX.

21. august 2017. Jeg har opdateret siden om at lave en kalender i Excel. Makroerne kan nu lave flere layouts/kalendertyper, påskedagenes placering beregnes, der er nogle helligdage med faste datoer, og brugeren kan definere op til 30 af sine egne. Regneark med makroer og userform kan downloades.

11. juni 2017. Ny side om at bruge makroer til betinget formatering af celler.

Det kan have sine fordele at bruge makroer i stedet for Excels indbyggede, betingede formatering - især hvis man vil tælle de celler, man gør noget ved (som fx får rød baggrundsfarve). Regneark med eksemplet kan downloades.

30. oktober 2016. Ny side om at lave en kalender i Excel. Laver en ny workbook med en fane for hver måned og et brugerdefineret antal rækker.

Fin til fx at holde styr på, hvor mange timer man bruger på forskellige opgaver, forskellige ansatte eller hvad ved jeg.

24. januar 2016. Ny side om afhængige ComboBoxe, og arrays i en class collection. Hvordan valget i én combobox kan styre indholdet i en anden.

Eksemplet bruger et dynamisk antal arrays, som opbevares i en class collection. Det er ikke så indviklet, som det måske lyder! Regneark kan downloades.

16. oktober 2015. En ny side: Kopier formler til andet regneark uden links. Hvis du kopierer formler fra et regneark til et andet, vil den kopierede formel få en reference til det originale regneark.

Det kan man fikse med søg og erstat, men hvis det er noget, man gør ofte, kan man med fordel bruge en makro.

10. oktober 2015. En ny side: Betinget kopiering af rækker. Et ofte stillet spørgsmål går på, hvordan man kopierer rækker fra en tabel til en anden (fx i et andet regneark), hvis rækken matcher et eller andet kriterium som fx kundenummer.

Jeg har lavet et eksempel, som gør netop det. Makroen bruger Like-operatoren til at sammenligne - den tillader brugen af "jokere" og mønstre.

22. august 2015. En ny side: Userform med kalender og datovælger. Ingen ActiveX som kan give problemer mellem forskellige versioner af MS Office. Eksempler på brug af adskillige datofunktioner samt et simpelt klassemodul til at håndtere hændelser ved at deklarere Public WithEvents. Regneark med den fulde kode kan downloades.

9. august 2015. To nye sider om regnearksfunktioner: Lav dine egne regnearksfunktioner og addins og SUM.HVIS som ignorerer skjulte celler

En læser spurgte, hvordan man kunne få Excels SUM.HVIS-funktion til at ignorere skjulte celler. Det satte mig igang med at lave skræddersyede regnearksfunktioner.

22. juli 2015. En ny side om brug af regnearksfunktioner på arrays. Det er lynhurtigt at kopiere data, som opfylder forskellige betingelser, fra et array til et andet, og så er det praktisk, at man kan bruge regnearksfunktioner på de udtrukne data.

Siden giver nogle simple eksempler på syntaksen, og du kan downloade en zip-komprimeret fil med to regneark og et noget mere avanceret eksempel. Makroer og konfiguration er i det ene regneark, data i det andet.

6. juni 2015. En ny side om betinget sletning af rækker i en tabel. Eksempel på hvordan man kan slette rækker i en tabel, hvis værdier i en brugervalgt kolonne opfylder et brugerdefineret kriterium - fx at værdien er mindre end 300.

Et zip-komprimeret regneark med eksemplet kan downloades. Makroerne gør bl.a. brug af Ranges, Arrays, UserForms (også modeless), en ListBox, en funktion og løkker.

21. februar 2015. En ny side om beregning af stop- og driftstid m.m.. Et eksempel på hvordan man kan beregne antal stop, start, stoptid, driftstid, gennemsnitlig tid mellem stop m.m. ved bl.a. at bruge VBA-funkttionen DateDiff.

Makroerne bruger også løkker, ranges, arrays og en collection. Eksemplet kan sagtens bruges som udgangspunkt til at beregne tider for andet end lige stop og start. Regneark med eksemplet kan downloades.

30. november 2014. En ny side om Animerede diagrammer. Det er ikke så indviklet at lave animationer i Excel. Se her hvordan.

Der er en video med eksempler, og du kan downloade regnearket med koden, som blev brugt til videoen.

16. november 2014. Så er der en ny side: Rund op til nærmeste hundrede, tusinde osv.

Forleden fik jeg brug for en funktion, som kunne finde en værdi til automatisk at skalere et diagrams Y-akse. Hvis udgangsværdien var 2, skulle funktionen returnere 3, 9 skulle blive til 10, 11 til 20, 72 til 80, 117 til 200, 1700 til 2000, 14999 til 20000 osv.

4. oktober 2014. Et Pareto-diagram er et søjlediagram, hvor data er sorteret, aå de højeste søjler står til venstra, og så er der en kurve, som viser den akkumulerede procent.De bruges til at visualisere, hvilke faktorer der har størst betydning.

Det er besværligt at lave dem manuelt, så hvorfor ikke gøre det i et snuptag med makroer? Se koden eller download et zip-komprimeret regneark. Diagrammets kildedata kan være i samme regneark som makroerne eller i et andet.

Histogram

28. august 2014. Fiksede en bug i eksempelregnearket om histogrammer - man kunne ikke altid vælge fanebladet med kildedata. Koden på websiden er også rettet til.

25. august 2014. Siden om at lave histogrammer i Excel med VBA har fået en kraftig overhaling.

Der er kode til at lave et søjlediagram med 8 søjler/intervaller sammen med en klokkeformet, normalfordelt kurve baseret på datasættets standardafvigelse og middelværdi.

Det går lynhurtigt sammenlignet med den manuelle metode, hvor man skal bruge Analysis Toolpak.

Det andet eksempel er med brugerdefineret antal søjler. Regneark med eksemplerne kan downloades.

19. marts 2014. Jeg har tilføjet en side, om hvordan man bruger Excels Solver (Problemløser) med VBA. Du kan også downloade et regneark, som bruger Solver med VBA makroer.

Med VBA kan man lave en applikation med brugerinterface, og i eksemplet går det ud på at vælge råmaterialer, sætte betingelser og få råmaterialerne blandet til den rette kemiske sammensætning og til billigste pris.

1. marts 2014. Egne class collections (klassesamlinger) er et stærkt værktøj til struktureret programmering, hvor man ikke skal holde styr på en hulens bunke variabler.

Der er bare den hage, at man ikke kan bruge den hurtige For Each...Next-løkke til at gennemløbe sin collection - medmindre man kender tricket! Se her, hvordan: For Each Next-løkker i egne class collections.

1. feb. 2014. En ny side, Opsplitning af tekstfiler, viser, hvordan en tekstfil kan opdeles i mindre tekstfiler med et brugerdefineret antal linjer/rækker. Det kan også være csv-filer, så skal man blot ændre "txt" til "csv" i koden.

Jeg havde brug for faciliteten, da jeg skulle importere en stor tekstfil til Excel 2003, som "kun" har 65.536 rækker, men jeg fandt ikke noget på nettet, jeg kunne få til at fungere, og så skrev jeg selv makroen.

30. nov. 2013. Siden om ListBoxe er opdateret. Jeg har tilføjet eksempler på, hvordan man kan sætte en ListBox' liste = et array (1 eller flere kolonner).

24. november 2013. Det er nemt at importere csv-filer til Excel med en VBA-makro, men hvis filen er semikolonsepareret, kan det godt være drilsk, hvis man ikke ved, hvordan man får VBA til at makke ret.

På siden Import af csv-filer til Excel viser jeg hvordan, og der er også et eksempel på en makro, man kan bruge, hvis man af en eller anden grund ikke vil bruge Excels indbyggede importfunktioner.

9. november 2013. Jeg har lavet en ny side, som beskriver, hvordan man kan bruge dobbeltklik som alternativ til hyperlinks, når man skal navigere mellem celler, faneblade og regneark.

Fidusen er at bruge et faneblads eller regnearks BeforeDoubleclick-event og tilføje en smule kode. Du kan også downloade et zipkomprimeret regneark med eksempler.

4. november 2013. Jeg har nu lavet et RSS feed til disse sider om Excel VBA makroer. Giv det til din feed reader for at abonnere på nyheder og opdateringer af disse sider. Snart kommer en ny side om et alternativ til hyperlinks, som kan bruges til at navigere i og mellem regneark og faneblade.

17. august 2013. Jeg har opdateret siden om ListBoxe med eksempler på, hvordan man kan forvælge værdier på en liste, så de er valgt, når listen vises.

28. juli 2013 med Flette og kombinere data og tabeller. En læser bad om hjælp til at kombinere 2 tabeller i 2 forskellige regneark ved at bruge et firmanavn som "nøgle".

Efterfølgende lavede jeg denne side med eksempler på, hvordan man kan flette eller kombinere data, lister og tabeller, hvad enten de er i ét eller flere regneark. Der er også eksempler på, hvordan output kan skrives til et nyt regneark. Eksemplerne kan downloades som zip-komprimerede regneark.

13. juli 2013 med Hurtig import af tekstfiler. Hvis man ofte bruger Excels guide til import af tekstfiler, bliver man hurtigt træt af hver eneste gang at skulle klikke sig gennem alle valgmulighederne.

Hvis man på forhånd ved, at feltadskilleren er semikolon, tabulator osv. kan man automatisere importen, så man kun skal vælge filen og slipper for at bladre sig gennem guiden.

På siden viser jeg hvordan, og man kan også downloade et zip-komprimeret regneark med eksemplet samt en semikolon-separeret tekstfil, man kan lege med.

9. februar 2013 med Proces- og massebalancer med VBA. Hvem har ikke oplevet Excel brokke sig over en cirkulær reference? Som fx B1 = A1 og A1 = B1 + 2.

I industrielle processer er der meget ofte "cirkulære referencer," hvor fx en delstrøm af et materiale ledes tilbage til et tidligere procesafsnit, så man netop får "A1 = B1 +2" effekten.

Det kan man komme udenom ved at lave en model af processen i VBA og så gennemløbe den X gange, til der er balance mellem input og output.

Det har jeg skrevet en side om, og der er et tilhørende regneark, hvor VBA-koden er udførligt kommenteret.

Regnearket kan modellere mange processer, hvor strømme går frem og tilbage, og det illustrerer, hvordan man ved at bruge klassemoduler og class collections kan gøre noget meget indviklet enkelt og struktureret. Det med klasser er ikke så svært, og der er power i at bruge dem!

2. februar 2013 med Billet- eller nummerkontrol med VBA. En festivalarrangør spurgte, om jeg kunne hjælpe med at lave et regneark, der ved adgangskontrol tjekker, om et indskannet eller indtastet billetnummer er gyldigt og ubrugt.

For en gangs skyld en webside helt uden kodeeksempler, men du kan downloade regnearket, hvor makroerne er udførligt kommenteret - et eksempel på en applikation til et bestemt formål.

Regnearket kan selvfølgelig bruges til andet end blot billetkontrol, og det bruger flere af de tricks og rutiner, jeg har beskrevet på andre sider.

Ved samme lejlighed har layoutet af disse sider fået en ansigtsløftning, og links til regneark med eksempler på VBA-kode er nu samlet på én side.

Der er flere og ældre opdateringer, men listen kan også blive for lang.