Excel VBA-veiledning - Hvordan skrive kode i et regneark ved hjelp av Visual Basic

Introduksjon

Dette er en veiledning om å skrive kode i Excel-regneark ved hjelp av Visual Basic for Applications (VBA).

Excel er et av Microsofts mest populære produkter. I 2016 sa konsernsjefen "Tenk på en verden uten Excel. Det er bare umulig for meg." Vel, kanskje ikke verden kan tenke uten Excel.

  • I 1996 var det over 30 millioner brukere av Microsoft Excel (kilde).
  • I dag er det anslagsvis 750 millioner brukere av Microsoft Excel. Det er litt mer enn befolkningen i Europa og 25 ganger flere brukere enn det var i 1996.

Vi er en stor lykkelig familie!

I denne opplæringen lærer du om VBA og hvordan du skriver kode i et Excel-regneark ved hjelp av Visual Basic.

Forutsetninger

Du trenger ingen tidligere programmeringserfaring for å forstå denne opplæringen. Du trenger imidlertid:

  • Grunnleggende til middels kjennskap til Microsoft Excel
  • Hvis du vil følge med VBA-eksemplene i denne artikkelen, trenger du tilgang til Microsoft Excel, helst den siste versjonen (2019), men Excel 2016 og Excel 2013 vil fungere helt fint.
  • En vilje til å prøve nye ting

Læringsmål

I løpet av denne artikkelen vil du lære:

  1. Hva VBA er
  2. Hvorfor du vil bruke VBA
  3. Hvordan bli satt opp i Excel for å skrive VBA
  4. Hvordan løse noen virkelige problemer med VBA

Viktige begreper

Her er noen viktige konsepter som du bør være kjent med for å forstå denne opplæringen.

Objekter : Excel er objektorientert, noe som betyr at alt er et objekt - Excel-vinduet, arbeidsboken, et ark, et diagram, en celle. VBA lar brukerne manipulere og utføre handlinger med objekter i Excel.

Hvis du ikke har noen erfaring med objektorientert programmering og dette er et helt nytt konsept, kan du ta et sekund for å la det synke inn!

Prosedyrer : en prosedyre er en del av VBA-koden, skrevet i Visual Basic Editor, som utfører en oppgave. Noen ganger blir dette også referert til som en makro (mer om makroer nedenfor). Det er to typer prosedyrer:

  • Underrutiner: en gruppe VBA-uttalelser som utfører en eller flere handlinger
  • Funksjoner: en gruppe VBA-setninger som utfører en eller flere handlinger og returnerer en eller flere verdier

Merk: du kan ha funksjoner som opererer inne i underrutiner. Du får se det senere.

Makroer : Hvis du har brukt noe tid på å lære mer avansert Excel-funksjonalitet, har du sannsynligvis møtt konseptet med en "makro". Excel-brukere kan ta opp makroer, bestående av brukerkommandoer / tastetrykk / klikk, og spille dem av lynraskt for å utføre repetitive oppgaver. Registrerte makroer genererer VBA-kode, som du deretter kan undersøke. Det er faktisk ganske morsomt å spille inn en enkel makro og deretter se på VBA-koden.

Vær oppmerksom på at det noen ganger kan være enklere og raskere å registrere en makro i stedet for å håndkode en VBA-prosedyre.

For eksempel, kanskje du jobber i prosjektledelse. En gang i uken må du gjøre en rå eksportert rapport fra prosjektledelsessystemet til en vakkert formatert, ren rapport for ledelse. Du må formatere navnene på overbudsjettprosjektene i fet rød tekst. Du kan registrere formateringsendringene som en makro og kjøre det når du trenger å gjøre endringen.

Hva er VBA?

Visual Basic for Applications er et programmeringsspråk utviklet av Microsoft. Hvert program i Microsoft Office-pakken leveres med VBA-språket uten ekstra kostnad. VBA lar Microsoft Office-brukere lage små programmer som fungerer i Microsoft Office-programmer.

Tenk på VBA som en pizzaovn i en restaurant. Excel er restauranten. Kjøkkenet leveres med standard kommersielle apparater, som store kjøleskap, ovner og vanlige oleovner - alt dette er Excels standardfunksjoner.

Men hva om du vil lage vedfyrt pizza? Kan ikke gjøre det i en vanlig kommersiell stekeovn. VBA er pizzaovnen.

Nam.

Hvorfor bruke VBA i Excel?

Fordi vedfyrt pizza er best!

Men seriøst.

Mange bruker mye tid i Excel som en del av jobbene sine. Tid i Excel beveger seg også annerledes. Avhengig av omstendighetene kan 10 minutter i Excel føles som evigheten hvis du ikke er i stand til å gjøre det du trenger, eller 10 timer kan gå veldig raskt hvis alt går bra. Det er da du bør spørre deg selv, hvorfor i all verden bruker jeg 10 timer i Excel?

Noen ganger er disse dagene uunngåelige. Men hvis du bruker 8-10 timer hver dag i Excel på å gjøre repeterende oppgaver, gjenta mange av de samme prosessene, prøve å rydde opp etter andre brukere av filen, eller til og med oppdatere andre filer etter at endringer er gjort i Excel-filen, en VBA-prosedyre kan bare være løsningen for deg.

Du bør vurdere å bruke VBA hvis du trenger å:

  • Automatiser gjentatte oppgaver
  • Lag enkle måter for brukere å samhandle med regnearkene dine
  • Manipuler store datamengder

Bli klar for å skrive VBA i Excel

Utviklerfane

For å skrive VBA, må du legge til kategorien Developer i båndet, slik at du ser båndet slik.

Slik legger du til kategorien Utvikler i båndet:

  1. Gå til Fil-fanen, og gå til Alternativer> Tilpass bånd.
  2. Merk av for Utvikler under Tilpass båndet og under Hovedfaner.

Etter at du har vist fanen, forblir utviklerfanen synlig, med mindre du fjerner avmerkingsboksen eller må installere Excel på nytt. For mer informasjon, se Microsoft hjelpedokumentasjon.

VBA Editor

Naviger til utviklerfanen, og klikk på Visual Basic-knappen. Et nytt vindu vil dukke opp - dette er Visual Basic Editor. I forbindelse med denne opplæringen trenger du bare å være kjent med Project Explorer-ruten og Property Properties-ruten.

Excel VBA eksempler

La oss først lage en fil for oss å leke i.

  1. Åpne en ny Excel-fil
  2. Lagre den som en makroaktivert arbeidsbok (. Xlsm)
  3. Velg kategorien Utvikler
  4. Åpne VBA Editor

La oss rocke og rulle med noen enkle eksempler for å få deg til å skrive kode i et regneark ved hjelp av Visual Basic.

Eksempel 1: Vis en melding når brukere åpner Excel-arbeidsboken

I VBA Editor velger du Sett inn -> Ny modul

Skriv denne koden i modulvinduet (ikke lim inn!):

Sub Auto_Open ()

MsgBox ("Velkommen til XYZ arbeidsbok.")

Avslutt sub

Lagre, lukk arbeidsboken og åpne arbeidsboken på nytt. Denne dialogboksen skal vises.

Ta da!

Hvordan gjør det?

Avhengig av din fortrolighet med programmering, kan du ha noen gjetninger. Det er ikke spesielt komplisert, men det skjer ganske mye:

  • Sub (forkortelse for "Subrutine): husk fra begynnelsen" en gruppe VBA-uttalelser som utfører en eller flere handlinger. "
  • Auto_Open: dette er den spesifikke underrutinen. Den kjører automatisk koden din når Excel-filen åpnes - dette er hendelsen som utløser prosedyren. Auto_Open vil bare kjøres når arbeidsboken åpnes manuelt; den kjører ikke hvis arbeidsboken åpnes via kode fra en annen arbeidsbok (Workbook_Open vil gjøre det, lære mer om forskjellen mellom de to).
  • Som standard er tilgang til en underrutine offentlig. Dette betyr at enhver annen modul kan bruke denne underrutinen. Alle eksempler i denne opplæringen vil være offentlige underrutiner. Om nødvendig kan du erklære underrutiner som private. Dette kan være nødvendig i noen situasjoner. Lær mer om tilgangsendringsmoduler for underrutiner.
  • msgBox: dette er en funksjon - en gruppe VBA-setninger som utfører en eller flere handlinger og returnerer en verdi. Den returnerte verdien er meldingen "Velkommen til XYZ arbeidsbok."

Kort sagt, dette er en enkel underrutine som inneholder en funksjon.

Når kunne jeg bruke dette?

Kanskje du har en veldig viktig fil som er sjelden tilgjengelig (for eksempel en gang i kvartalet), men automatisk oppdatert daglig av en annen VBA-prosedyre. Når det er tilgjengelig, er det av mange mennesker i flere avdelinger, over hele selskapet.

  • Problem: Mesteparten av tiden når brukere får tilgang til filen, er de forvirrede om formålet med denne filen (hvorfor den eksisterer), hvordan den oppdateres så ofte, hvem som vedlikeholder den og hvordan de skal samhandle med den. Nyansettelser har alltid mange spørsmål, og du må stille disse spørsmålene om og om og om igjen.
  • Løsning: lag en brukermelding som inneholder et kortfattet svar på hvert av disse ofte besvarte spørsmålene.

Ekte verdenseksempler

  • Bruk MsgBox-funksjonen til å vise en melding når det er noen hendelse: bruker lukker en Excel-arbeidsbok, bruker skriver ut, et nytt ark blir lagt til arbeidsboken osv.
  • Bruk MsgBox-funksjonen til å vise en melding når en bruker trenger å oppfylle en betingelse før du lukker en Excel-arbeidsbok
  • Bruk InputBox-funksjonen for å få informasjon fra brukeren

Eksempel 2: Tillat at brukeren utfører en annen prosedyre

I VBA Editor velger du Sett inn -> Ny modul

Skriv denne koden i modulvinduet (ikke lim inn!):

Sub UserReportQuery ()

Demp brukerinngang så lenge

Dimmt svar som heltall

UserInput = vbYesNo

Svar = MsgBox ("Behandle XYZ-rapporten?", Brukerinngang)

Hvis svar = vbJa, deretter ProcessReport

Avslutt sub

Sub ProcessReport ()

MsgBox ("Takk for behandlingen av XYZ-rapporten.")

Avslutt sub

Lagre og naviger tilbake til utviklerfanen i Excel, og velg alternativet “Knapp”. Klikk på en celle og tilordne UserReportQuery-makroen til knappen.

Klikk nå på knappen. Denne meldingen skal vise:

Klikk "ja" eller trykk Enter.

Nok en gang, tada!

Vær oppmerksom på at den sekundære underrutinen, ProcessReport, kan være hva som helst . Jeg vil demonstrere flere muligheter i eksempel 3. Men først...

Hvordan gjør det?

Dette eksemplet bygger på forrige eksempel og har ganske mange nye elementer. La oss gå gjennom de nye tingene:

  • Dim UserInput As Long: Dim er en forkortelse for "dimensjon" og lar deg erklære variabelnavn. I dette tilfellet er UserInput variabelnavnet og Long er datatypen. På vanlig engelsk betyr denne linjen "Her er en variabel kalt" UserInput ", og det er en lang variabel type."
  • Dimmt svar som heltall: erklærer en annen variabel kalt “Svar”, med datatypen Heltall. Lær mer om datatyper her.
  • UserInput = vbYesNo: tilordner en verdi til variabelen. I dette tilfellet vbYesNo, som viser knappene Ja og Nei. Det er mange typer knapper, lær mer her.
  • Svar = MsgBox (“Behandle XYZ-rapporten?”, UserInput): tilordner verdien av variabelen Answer til å være en MsgBox-funksjon og UserInput-variabelen. Ja, en variabel i en variabel.
  • Hvis svar = vbJa, så er ProcessReport: dette er en "If-setning", en betinget uttalelse, som lar oss si om x er sant, så gjør y. I dette tilfellet, hvis brukeren har valgt "Ja", så utfør ProcessReport-underrutinen.

Når kunne jeg bruke dette?

Dette kan brukes på mange, mange måter. Verdien og allsidigheten av denne funksjonaliteten er mer definert av hva den sekundære underrutinen gjør.

For eksempel, kanskje du har en fil som brukes til å generere 3 forskjellige ukentlige rapporter. Disse rapportene er formatert på dramatisk forskjellige måter.

  • Problem: Hver gang en av disse rapportene må genereres, åpner en bruker filen og endrer formatering og diagrammer; også videre. Denne filen redigeres mye minst 3 ganger per uke, og det tar minst 30 minutter hver gang den redigeres.
  • Løsning: Lag 1 knapp per rapporttype, som automatisk omformaterer de nødvendige komponentene i rapportene og genererer de nødvendige diagrammer.

Ekte verdenseksempler

  • Opprett en dialogboks slik at brukeren automatisk kan fylle ut viss informasjon på flere ark
  • Bruk InputBox-funksjonen for å få informasjon fra brukeren, som deretter fylles ut over flere ark

Eksempel nr. 3: Legg til tall i et område med en For-Next-løkke

For sløyfer er veldig nyttige hvis du trenger å utføre repeterende oppgaver på et bestemt verdiområde - matriser eller celleområder. På vanlig engelsk sier en løkke “for hver x, gjør y.”

I VBA Editor velger du Sett inn -> Ny modul

Skriv denne koden i modulvinduet (ikke lim inn!):

Sub LoopExample ()

Dim X som heltal

For X = 1 til 100

Område ("A" og X). Verdi = X

Neste X

Avslutt sub

Lagre og gå tilbake til utviklerfanen i Excel, og velg Makro-knappen. Kjør LoopExample-makroen.

Dette skal skje:

Etc, til 100. rad.

Hvordan gjør det?

  • Dim X As Integer: erklærer variabelen X som en datatype for Integer.
  • For X = 1 til 100: dette er starten på For-sløyfen. Enkelt sagt, den forteller sløyfen å fortsette å gjenta til X = 100. X er telleren . Sløyfen fortsetter å kjøre til X = 100, kjør en siste gang og stopp deretter.
  • Område ("A" og X). Verdi = X: Dette erklærer rekkevidden til sløyfen og hva du skal sette i det området. Siden X = 1 i utgangspunktet, vil den første cellen være A1, på hvilket tidspunkt sløyfen vil sette X i den cellen.
  • Neste X: dette forteller at løkken skal kjøre igjen

Når kunne jeg bruke dette?

For-Next-løkken er en av de kraftigste funksjonene i VBA; det er mange potensielle brukssaker. Dette er et mer komplekst eksempel som vil kreve flere lag med logikk, men det kommuniserer mulighetenes verden i For-Next-løkker.

Kanskje har du en liste over alle produktene som selges på bakeriet ditt i kolonne A, typen produkt i kolonne B (kaker, smultringer eller muffins), kostnadene for ingredienser i kolonne C, og markedets gjennomsnittlige kostnad for hver produkttype i et annet ark.

Du må finne ut hva som skal være utsalgsprisen for hvert produkt. Du tenker at det burde være kostnaden for ingredienser pluss 20%, men også 1,2% under markedsgjennomsnittet hvis mulig. En For-Next-løkke vil tillate deg å gjøre denne typen beregninger.

Ekte verdenseksempler

  • Bruk en sløyfe med en nestet if-setning for å legge til bestemte verdier i en egen matrise bare hvis de oppfyller visse betingelser
  • Utfør matematiske beregninger på hver verdi i et område, for eksempel beregne tilleggsbelastninger og legg dem til verdien
  • Snu gjennom hvert tegn i en streng og trekk ut alle tallene
  • Velg tilfeldig antall verdier fra en matrise

Konklusjon

Nå som vi har snakket om pizza og muffins og oh-yeah, hvordan vi skriver VBA-kode i Excel-regneark, la oss gjøre en læringskontroll. Se om du kan svare på disse spørsmålene.

  • Hva er VBA?
  • Hvordan blir jeg satt opp for å begynne å bruke VBA i Excel?
  • Hvorfor og når vil du bruke VBA?
  • Hva er noen problemer jeg kan løse med VBA?

Hvis du har en god ide om hvordan du kan svare på disse spørsmålene, var dette vellykket.

Enten du er en og annen bruker eller en kraftbruker, håper jeg at denne opplæringen ga nyttig informasjon om hva som kan oppnås med bare litt kode i Excel-regnearkene dine.

God koding!

Læringsressurser

  • Excel VBA Programming for Dummies, John Walkenbach
  • Kom i gang med VBA, Microsoft Documentation
  • Lære VBA i Excel, Lynda

Litt om meg

Jeg er Chloe Tucker, en kunstner og utvikler i Portland, Oregon. Som tidligere lærer søker jeg kontinuerlig etter skjæringspunktet mellom læring og undervisning, eller teknologi og kunst. Ta kontakt med meg på Twitter @_chloetucker og sjekk ut nettstedet mitt på chloe.dev.