Excel VBA-vejledning - Sådan skriver du kode i et regneark ved hjælp af Visual Basic

Introduktion

Dette er en tutorial om at skrive kode i Excel-regneark ved hjælp af Visual Basic for Applications (VBA).

Excel er et af Microsofts mest populære produkter. I 2016 sagde administrerende direktør for Microsoft "Tænk på en verden uden Excel. Det er bare umuligt for mig." Nå, måske kan verden ikke tænke uden Excel.

  • I 1996 var der over 30 millioner brugere af Microsoft Excel (kilde).
  • I dag er der anslået 750 millioner brugere af Microsoft Excel. Det er lidt mere end befolkningen i Europa og 25 gange flere brugere, end der var i 1996.

Vi er en stor lykkelig familie!

I denne vejledning lærer du om VBA og hvordan man skriver kode i et Excel-regneark ved hjælp af Visual Basic.

Forudsætninger

Du har ikke brug for nogen tidligere programmeringserfaring for at forstå denne tutorial. Du skal dog have:

  • Grundlæggende til mellemliggende fortrolighed med Microsoft Excel
  • Hvis du vil følge med VBA-eksemplerne i denne artikel, skal du have adgang til Microsoft Excel, helst den nyeste version (2019), men Excel 2016 og Excel 2013 fungerer fint.
  • En vilje til at prøve nye ting

Læringsmål

I løbet af denne artikel vil du lære:

  1. Hvad VBA er
  2. Hvorfor ville du bruge VBA
  3. Sådan oprettes i Excel til at skrive VBA
  4. Sådan løses nogle virkelige problemer med VBA

Vigtige begreber

Her er nogle vigtige begreber, som du bør være fortrolig med for at forstå denne tutorial fuldt ud.

Objekter : Excel er objektorienteret, hvilket betyder, at alt er et objekt - Excel-vinduet, projektmappen, et ark, et diagram, en celle. VBA giver brugerne mulighed for at manipulere og udføre handlinger med objekter i Excel.

Hvis du ikke har nogen erfaring med objektorienteret programmering, og dette er et helt nyt koncept, skal du tage et øjeblik for at lade det synke ind!

Procedurer : en procedure er et stykke VBA-kode, skrevet i Visual Basic Editor, der udfører en opgave. Nogle gange kaldes dette også en makro (mere om makroer nedenfor). Der er to typer procedurer:

  • Underrutiner: en gruppe VBA-udsagn, der udfører en eller flere handlinger
  • Funktioner: en gruppe VBA-udsagn, der udfører en eller flere handlinger og returnerer en eller flere værdier

Bemærk: du kan have funktioner, der fungerer inde i underrutiner. Du får se senere.

Makroer : Hvis du har brugt nogen tid på at lære mere avanceret Excel-funktionalitet, har du sandsynligvis stødt på begrebet "makro". Excel-brugere kan optage makroer, der består af brugerkommandoer / tastetryk / klik, og afspille dem lynhurtigt for at udføre gentagne opgaver. Optagede makroer genererer VBA-kode, som du derefter kan undersøge. Det er faktisk ret sjovt at optage en simpel makro og derefter se på VBA-koden.

Husk, at det undertiden kan være lettere og hurtigere at optage en makro i stedet for at håndkode en VBA-procedure.

For eksempel, måske arbejder du i projektledelse. En gang om ugen skal du omdanne en rå eksporteret rapport fra dit projektstyringssystem til en smukt formateret, ren rapport til lederskab. Du skal formatere navnene på de overbudgette projekter med fed rød tekst. Du kan registrere formateringsændringerne som en makro og køre det, når du har brug for at foretage ændringen.

Hvad er VBA?

Visual Basic for Applications er et programmeringssprog udviklet af Microsoft. Hvert softwareprogram i Microsoft Office-pakken leveres med VBA-sprog uden ekstra omkostninger. VBA giver Microsoft Office-brugere mulighed for at oprette små programmer, der fungerer inden for Microsoft Office-softwareprogrammer.

Tænk på VBA som en pizzaovn i en restaurant. Excel er restauranten. Køkkenet leveres med almindelige kommercielle apparater som store køleskabe, komfurer og almindelige oleovne - det er alle Excels standardfunktioner.

Men hvad nu hvis du vil lave træfyret pizza? Kan ikke gøre det i en standard kommerciel bageovn. VBA er pizzaovnen.

Yum.

Hvorfor bruge VBA i Excel?

Fordi træfyret pizza er det bedste!

Men seriøst.

En masse mennesker bruger meget tid i Excel som en del af deres job. Tid i Excel bevæger sig også anderledes. Afhængigt af omstændighederne kan 10 minutter i Excel føles som evigheden, hvis du ikke er i stand til at gøre, hvad du har brug for, eller 10 timer kan gå meget hurtigt, hvis alt går godt. Hvilket er, når du skal spørge dig selv, hvorfor i alverden bruger jeg 10 timer i Excel?

Nogle gange er disse dage uundgåelige. Men hvis du bruger 8-10 timer hver dag i Excel på at udføre gentagne opgaver, gentage mange af de samme processer, forsøge at rydde op efter andre brugere af filen eller endda opdatere andre filer efter ændringer i Excel-filen, en VBA-procedure kan bare være løsningen for dig.

Du bør overveje at bruge VBA, hvis du har brug for:

  • Automatiser gentagne opgaver
  • Opret nemme måder, hvorpå brugere kan interagere med dine regneark
  • Manipuler store mængder data

Kom godt i gang med at skrive VBA i Excel

Fanen Udvikler

For at skrive VBA skal du tilføje fanen Udvikler til båndet, så du ser båndet som dette.

Sådan tilføjes fanen Udvikler til båndet:

  1. Gå til fanen Fil, og gå til Indstillinger> Tilpas bånd.
  2. Marker afkrydsningsfeltet Udvikler under Tilpas båndet og under Hovedfaner.

Når du har vist fanen, forbliver fanen Udvikler synlig, medmindre du fjerner afkrydsningsfeltet eller er nødt til at geninstallere Excel. For yderligere oplysninger, se Microsoft-hjælpedokumentation.

VBA Editor

Naviger til fanen Udvikler, og klik på Visual Basic-knappen. Et nyt vindue vil dukke op - dette er Visual Basic Editor. Med henblik på denne vejledning skal du bare være fortrolig med Project Explorer-ruden og egenskabsruden.

Excel VBA eksempler

Lad os først oprette en fil, som vi kan lege i.

  1. Åbn en ny Excel-fil
  2. Gem den som en makroaktiveret projektmappe (. Xlsm)
  3. Vælg fanen Udvikler
  4. Åbn VBA Editor

Lad os rocke og rulle med nogle nemme eksempler for at få dig til at skrive kode i et regneark ved hjælp af Visual Basic.

Eksempel 1: Vis en besked, når brugere åbner Excel-projektmappen

I VBA Editor skal du vælge Indsæt -> Nyt modul

Skriv denne kode i modulvinduet (indsæt ikke!):

Sub Auto_ Åbn ()

MsgBox ("Velkommen til XYZ-projektmappen.")

Afslut sub

Gem, luk projektmappen, og åbn projektmappen igen. Denne dialogboks skal vises.

Ta da!

Hvordan gør det det?

Afhængigt af din fortrolighed med programmering kan du have nogle gætter. Det er ikke særlig kompliceret, men der foregår en hel del:

  • Sub (forkortelse for "Subrutine): husk fra starten," en gruppe VBA-udsagn, der udfører en eller flere handlinger. "
  • Auto_Open: dette er den specifikke subrutine. Den kører automatisk din kode, når Excel-filen åbnes - det er den begivenhed, der udløser proceduren. Auto_Open kører kun, når projektmappen åbnes manuelt; den kører ikke, hvis projektmappen åbnes via kode fra en anden projektmappe (Workbook_Open gør det, lær mere om forskellen mellem de to).
  • Som standard er en underrutins adgang offentlig. Dette betyder, at ethvert andet modul kan bruge denne underrutine. Alle eksempler i denne vejledning er offentlige underrutiner. Hvis det er nødvendigt, kan du erklære underrutiner som private. Dette kan være nødvendigt i nogle situationer. Lær mere om modifikatorer for subrutineadgang.
  • msgBox: dette er en funktion - en gruppe af VBA-sætninger, der udfører en eller flere handlinger og returnerer en værdi. Den returnerede værdi er meddelelsen "Velkommen til XYZ-projektmappen."

Kort sagt er dette en simpel subrutine, der indeholder en funktion.

Hvornår kunne jeg bruge dette?

Måske har du en meget vigtig fil, der fås sjældent (f.eks. En gang i kvartalet), men automatisk opdateres dagligt ved en anden VBA-procedure. Når det er tilgængeligt, er det af mange mennesker i flere afdelinger, hele virksomheden.

  • Problem: Det meste af tiden, når brugere får adgang til filen, er de forvirrede over formålet med denne fil (hvorfor den findes), hvordan den opdateres så ofte, hvem der vedligeholder den, og hvordan de skal interagere med den. Nyansættelser har altid mange spørgsmål, og du skal stille disse spørgsmål igen og igen og igen.
  • Løsning: Opret en brugerbesked, der indeholder et kort svar på hvert af disse ofte besvarede spørgsmål.

Eksempler på virkelige verdener

  • Brug MsgBox-funktionen til at få vist en besked, når der er en begivenhed: bruger lukker en Excel-projektmappe, bruger udskriver, et nyt ark føjes til projektmappen osv.
  • Brug MsgBox-funktionen til at få vist en besked, når en bruger skal opfylde en betingelse, før han lukker en Excel-projektmappe
  • Brug InputBox-funktionen til at få oplysninger fra brugeren

Eksempel 2: Tillad brugeren at udføre en anden procedure

I VBA Editor skal du vælge Indsæt -> Nyt modul

Skriv denne kode i modulvinduet (indsæt ikke!):

Underbrugerrapportforespørgsel ()

Dæmp brugerinput så længe

Svagt svar som heltal

UserInput = vbJaNej

Svar = MsgBox ("Behandler XYZ-rapporten?", UserInput)

Hvis svar = vbJa, derefter ProcessReport

Afslut sub

Under ProcessReport ()

MsgBox ("Tak for behandlingen af ​​XYZ-rapporten.")

Afslut sub

Gem og naviger tilbage til Excel-fanen Udvikler, og vælg indstillingen "Knap". Klik på en celle, og tildel UserReportQuery-makroen til knappen.

Klik nu på knappen. Denne meddelelse skal vise:

Klik på “ja” eller tryk Enter.

Endnu en gang, tada!

Bemærk, at den sekundære underrutine, ProcessReport, kan være hvad som helst . Jeg demonstrerer flere muligheder i eksempel # 3. Men først...

Hvordan gør det det?

Dette eksempel bygger på det foregående eksempel og har en hel del nye elementer. Lad os gå over de nye ting:

  • Dim UserInput As Long: Dim er en forkortelse for "dimension" og giver dig mulighed for at erklære variabelnavne. I dette tilfælde er UserInput variabelnavnet, og Long er datatypen. På almindelig engelsk betyder denne linje "Her er en variabel kaldet" UserInput ", og det er en lang variabel type."
  • Dæmp svar som heltal: erklærer en anden variabel kaldet "svar" med en datatype af heltal. Lær mere om datatyper her.
  • UserInput = vbYesNo: tildeler en værdi til variablen. I dette tilfælde vbYesNo, som viser knapperne Ja og Nej. Der er mange knaptyper, lær mere her.
  • Svar = MsgBox ("Behandler XYZ-rapporten?", UserInput): tildeler værdien af ​​variablen Svar til at være en MsgBox-funktion og UserInput-variablen. Ja, en variabel inden for en variabel.
  • Hvis svar = vbJa, så er ProcessReport: dette er en "Hvis-sætning", en betinget sætning, der giver os mulighed for at sige, om x er sand, skal du gøre y. I dette tilfælde, hvis brugeren har valgt “Ja”, skal du udføre ProcessReport-underrutinen.

Hvornår kunne jeg bruge dette?

Dette kunne bruges på mange, mange måder. Værdien og alsidigheden af ​​denne funktionalitet er mere defineret af, hvad den sekundære subrutine gør.

For eksempel har du måske en fil, der bruges til at generere 3 forskellige ugentlige rapporter. Disse rapporter er formateret på dramatisk forskellige måder.

  • Problem: Hver gang en af ​​disse rapporter skal genereres, åbner en bruger filen og ændrer formatering og diagrammer; så videre og så videre. Denne fil redigeres grundigt mindst 3 gange om ugen, og det tager mindst 30 minutter hver gang den redigeres.
  • Løsning: Opret 1 knap pr. Rapporttype, som automatisk omformaterer de nødvendige komponenter i rapporterne og genererer de nødvendige diagrammer.

Eksempler på virkelige verdener

  • Opret en dialogboks, så brugeren automatisk kan udfylde visse oplysninger på tværs af flere ark
  • Brug InputBox-funktionen til at få oplysninger fra brugeren, som derefter udfyldes på tværs af flere ark

Eksempel # 3: Føj numre til et område med en For-Next-løkke

For sløjfer er meget nyttige, hvis du har brug for at udføre gentagne opgaver på et bestemt værdiområde - arrays eller celleområder. På almindeligt engelsk siger en sløjfe "for hver x, gør y."

I VBA Editor skal du vælge Indsæt -> Nyt modul

Skriv denne kode i modulvinduet (indsæt ikke!):

Sub LoopExample ()

Dim X som heltal

For X = 1 til 100

Område ("A" og X) .Værdi = X

Næste X

Afslut sub

Gem og naviger tilbage til Excel-fanen Udvikler, og vælg knappen Makroer. Kør makroen LoopExample.

Dette skal ske:

Etc, indtil den 100. række.

Hvordan gør det det?

  • Dim X som heltal: erklærer variablen X som en datatype af heltal.
  • For X = 1 Til 100: dette er starten på For-sløjfen. Kort sagt, det fortæller sløjfen at gentage indtil X = 100. X er tælleren . Loop fortsætter med at udføre indtil X = 100, udfører en sidste gang og stopper derefter.
  • Område ("A" og X) .Værdi = X: Dette erklærer sløjfens rækkevidde, og hvad der skal placeres i dette interval. Da X = 1 oprindeligt, vil den første celle være A1, på hvilket tidspunkt sløjfen sætter X i den celle.
  • Næste X: dette fortæller sløjfen at køre igen

Hvornår kunne jeg bruge dette?

For-Next-sløjfen er en af ​​de mest kraftfulde funktioner i VBA; der er adskillige potentielle brugssager. Dette er et mere komplekst eksempel, der kræver flere lag af logik, men det kommunikerer verdenen af ​​muligheder i For-Next-sløjfer.

Måske har du en liste over alle produkter, der sælges i dit bageri i kolonne A, produkttypen i kolonne B (kager, donuts eller muffins), ingrediensomkostningerne i kolonne C og de gennemsnitlige markedsomkostninger for hver produkttype i et andet ark.

Du skal finde ud af, hvad der skal være detailprisen for hvert produkt. Du tænker, at det skal være prisen på ingredienser plus 20%, men også 1,2% under markedsgennemsnittet, hvis det er muligt. En For-Next-sløjfe giver dig mulighed for at foretage denne type beregning.

Eksempler på virkelige verdener

  • Brug kun en sløjfe med en indlejret if-sætning for kun at tilføje specifikke værdier til et separat array, hvis de opfylder visse betingelser
  • Udfør matematiske beregninger på hver værdi i et interval, fx beregn yderligere gebyrer og tilføj dem til værdien
  • Loop gennem hvert tegn i en streng, og træk alle tal ud
  • Vælg tilfældigt et antal værdier fra en matrix

Konklusion

Nu hvor vi har talt om pizza og boller og åh-ja, hvordan man skriver VBA-kode i Excel-regneark, lad os lave en læringskontrol. Se om du kan besvare disse spørgsmål.

  • Hvad er VBA?
  • Hvordan bliver jeg indstillet til at begynde at bruge VBA i Excel?
  • Hvorfor og hvornår vil du bruge VBA?
  • Hvad er nogle problemer, jeg kunne løse med VBA?

Hvis du har en god idé om, hvordan du kan besvare disse spørgsmål, så var dette vellykket.

Uanset om du er en lejlighedsvis bruger eller en strømbruger, håber jeg, at denne vejledning gav nyttige oplysninger om, hvad der kan opnås med bare en smule kode i dine Excel-regneark.

God kodning!

Læringsressourcer

  • Excel VBA Programmering for Dummies, John Walkenbach
  • Kom godt i gang med VBA, Microsoft Documentation
  • At lære VBA i Excel, Lynda

Lidt om mig

Jeg er Chloe Tucker, kunstner og udvikler i Portland, Oregon. Som tidligere underviser søger jeg løbende efter skæringspunktet mellem læring og undervisning eller teknologi og kunst. Kontakt mig på Twitter @_chloetucker og tjek min hjemmeside på chloe.dev.