Exceli VBA õpetus - kuidas kirjutada koodi arvutustabelisse Visual Basicu abil

Sissejuhatus

See on õpetus koodi kirjutamise kohta Exceli arvutustabelites Visual Basic for Applications (VBA) abil.

Excel on Microsofti üks populaarsemaid tooteid. 2016. aastal ütles Microsofti tegevjuht "Mõelge ilma Exceli maailmale. See on minu jaoks lihtsalt võimatu." Noh, võib-olla ei suuda maailm ilma Excelita mõelda.

  • 1996. aastal oli Microsoft Exceli (allikas) kasutajaid üle 30 miljoni.
  • Praegu on hinnanguliselt 750 miljonit Microsofti Exceli kasutajat. See on veidi rohkem kui Euroopa elanikkond ja 25x rohkem kasutajaid kui 1996. aastal.

Oleme üks suur õnnelik pere!

Selles õpetuses saate teada VBA-st ja sellest, kuidas Visual Basicu abil Exceli arvutustabelisse koodi kirjutada.

Eeldused

Selle õpetuse mõistmiseks ei vaja te eelnevat programmeerimiskogemust. Siiski vajate:

  • Microsoft Exceli põhi- ja kesktaseme tundmine
  • Kui soovite järgida selles artiklis toodud VBA näiteid, vajate juurdepääsu Microsoft Excelisse, eelistatavalt uusimale versioonile (2019), kuid Excel 2016 ja Excel 2013 töötavad suurepäraselt.
  • Valmisolek proovida uusi asju

Õppe-eesmärgid

Selle artikli käigus saate teada:

  1. Mis on VBA
  2. Miks te kasutaksite VBA-d?
  3. Kuidas Excelis VBA kirjutamiseks seadistada
  4. Kuidas lahendada VBA-ga mõned reaalsed probleemid

Olulised mõisted

Siin on mõned olulised mõisted, mida peaksite selle õpetuse täielikuks mõistmiseks tundma.

Objektid : Excel on objektorienteeritud, mis tähendab, et kõik on objekt - Exceli aken, töövihik, leht, diagramm, lahter. VBA võimaldab kasutajatel Excelis objektidega manipuleerida ja toiminguid teha.

Kui teil pole objektorienteeritud programmeerimisega kogemusi ja see on täiesti uus kontseptsioon, laske mõni sekund lasta sellel vajuda!

Protseduurid : protseduur on tükike VBA-koodi, mis on kirjutatud Visual Basic Editori ja täidab ülesande. Mõnikord nimetatakse seda ka makroks (rohkem makrode kohta allpool). Menetlusi on kahte tüüpi:

  • Alamprogrammid: VBA-lausete rühm, mis täidab ühte või mitut toimingut
  • Funktsioonid: VBA-lausete rühm, mis sooritab ühe või mitu toimingut ja tagastab ühe või mitu väärtust

Märkus. Alamprogrammides võivad toimida funktsioonid. Näete hiljem.

Makrod : kui olete mõnda aega kulutanud Exceli täpsemate funktsioonide õppimisele, olete tõenäoliselt kohanud „makro” mõistet. Exceli kasutajad saavad korduvate ülesannete täitmiseks makrosid, mis koosnevad kasutaja käskudest / klahvivajutustest / klõpsudest, salvestada ja välkkiirelt taasesitada. Salvestatud makrod genereerivad VBA-koodi, mida saate seejärel uurida. Tegelikult on üsna lõbus salvestada lihtne makro ja siis vaadata VBA koodi.

Pidage meeles, et mõnikord võib makro salvestamine olla lihtsam ja kiirem kui VBA-protseduuri käsitsi kodeerimine.

Näiteks töötate võib-olla projektijuhtimisega. Kord nädalas peate oma projektijuhtimissüsteemist toore eksporditud aruande muutma juhtimiseks kaunilt vormistatud ja puhtaks aruandeks. Üleeelarveliste projektide nimed peate vormistama rasvase punase tekstina. Saate vorminduse muudatused salvestada makrona ja käivitada see alati, kui teil on vaja muudatusi teha.

Mis on VBA?

Visual Basic for Applications on Microsofti välja töötatud programmeerimiskeel. Iga Microsoft Office'i paketi tarkvaraprogramm on komplektis VBA-keelega ja seda ilma lisatasuta. VBA võimaldab Microsoft Office'i kasutajatel luua väikseid programme, mis töötavad Microsoft Office'i tarkvaraprogrammides.

Mõelge VBA-st nagu pitsaahi restoranis. Excel on restoran. Köögis on tavalised kaubandusseadmed, nagu suured külmikud, pliidid ja tavalised õli-ahjud - need on kõik Exceli standardfunktsioonid.

Aga mis siis, kui soovite teha puuküttega pitsa? Tavalises kaubanduslikus küpsetusahjus seda teha ei saa. VBA on pitsaahi.

Yum.

Miks kasutada VBA-d Excelis?

Sest puuküttega pitsa on parim!

Aga tõsiselt.

Paljud inimesed veedavad oma töö osana Excelis palju aega. Ka aeg Excelis liigub teisiti. Olenevalt oludest võib 10 minutit Excelis tunduda igavikuna, kui te ei suuda seda, mida vajate, või 10 tundi võib mööduda väga kiiresti, kui kõik sujub suurepäraselt. Millal peaksite endalt küsima, miks ma veedan 10 tundi Excelis?

Mõnikord on need päevad vältimatud. Aga kui kulutate Excelis iga päev 8–10 tundi korduvate toimingute tegemiseks, paljude samade protsesside kordamiseks, teiste faili kasutajate järel koristamiseks või isegi muude failide värskendamiseks pärast Exceli faili muudatuste tegemist, VBA protseduur võib olla just teie jaoks lahendus.

Kaaluge VBA kasutamist, kui peate:

  • Automatiseerige korduvaid ülesandeid
  • Looge kasutajatele lihtsad viisid teie arvutustabelitega suhtlemiseks
  • Manipuleerida suures koguses andmeid

Häälestamine VBA kirjutamiseks Excelis

Arendaja vahekaart

VBA kirjutamiseks peate lindile lisama vahekaardi Arendaja, nii et näete sellist linti.

Vahekaardi Arendaja lisamiseks lindile tehke järgmist.

  1. Valige vahekaardil Fail Valikud> Kohanda linti.
  2. Jaotises Kohanda linti ja jaotises Peamised vahelehed märkige ruut Arendaja.

Pärast vahekaardi kuvamist jääb vahekaart Arendaja nähtavaks, välja arvatud juhul, kui tühjendate märkeruudu või peate Exceli uuesti installima. Lisateavet leiate Microsofti abidokumentatsioonist.

VBA toimetaja

Liikuge vahekaardile Arendaja ja klõpsake nuppu Visual Basic. Avaneb uus aken - see on Visual Basicu redaktor. Selle õpetuse jaoks peate lihtsalt tundma paani Project Explorer ja paani Atribuudid.

Exceli VBA näited

Kõigepealt loome faili, milles saaksime mängida.

  1. Avage uus Exceli fail
  2. Salvestage see makrotoega töövihikuna (. Xlsm)
  3. Valige vahekaart Arendaja
  4. Avage VBA redaktor

Rakendame Visual Basicu abil arvutustabelisse koodi kirjutamise lihtsate näidete abil.

Näide nr 1: kuvage teade, kui kasutajad avavad Exceli töövihiku

Valige VBA redaktoris Lisa -> Uus moodul

Kirjutage see kood aknas moodul (ärge kleepige!):

Alamautomaatne_avatud ()

MsgBox ("Tere tulemast XYZ töövihikusse.")

Lõpeta alam

Salvestage, sulgege töövihik ja avage töövihik uuesti. See dialoog peaks ilmuma.

Ta da!

Kuidas see seda teeb?

Sõltuvalt programmeerimise tundlikkusest võib teil olla mõningaid oletusi. See pole eriti keeruline, kuid toimub üsna palju:

  • Alam (lühidalt „Alamprogramm”): pidage algusest peale meeles „VBA-lausete rühma, mis täidab ühte või mitut toimingut”.
  • Automaatne_avatud: see on konkreetne alamprogramm. See käivitab teie koodi Exceli faili avanemisel automaatselt - see on protseduuri käivitav sündmus. Automaatne_avamine töötab ainult siis, kui töövihik on käsitsi avatud; see ei tööta, kui töövihik avatakse teise töövihiku koodi kaudu (Workbook_Open teeb seda, vaadake lisateavet nende kahe erinevuse kohta).
  • Vaikimisi on alamprogrammi juurdepääs avalik. See tähendab, et seda alamprogrammi saab kasutada mõni muu moodul. Kõik selle õpetuse näited on avalikud alamprogrammid. Vajadusel võite kuulutada alamprogrammid privaatseteks. Seda võib mõnes olukorras vaja minna. Lisateave alamprogrammi juurdepääsu modifikaatorite kohta.
  • msgBox: see on funktsioon - VBA-lausete rühm, mis sooritab ühe või mitu toimingut ja tagastab väärtuse. Tagastatud väärtus on teade „Tere tulemast XYZ töövihikusse“.

Lühidalt, see on lihtne alamprogramm, mis sisaldab funktsiooni.

Millal ma saaksin seda kasutada?

Võib-olla on teil mõni väga oluline fail, millele pääseb juurde harva (näiteks kord kvartalis), kuid mida värskendatakse iga päev mõne muu VBA-protseduuri abil. Kui sellele juurde pääsete, saavad seda paljud ettevõtted mitmes osakonnas kogu ettevõttes.

  • Probleem: Enamasti on kasutajad failile juurdepääsemisel segaduses selle faili eesmärgi (miks see olemas on), kuidas seda nii sageli värskendatakse, kes seda hooldab ja kuidas peaksid failiga suhtlema. Uutel töötajatel on alati palju küsimusi ja peate neid küsimusi esitama ikka ja jälle.
  • Lahendus: looge kasutaja sõnum, mis sisaldab lühidalt vastust kõigile neile sageli vastatud küsimustele.

Näited reaalsest maailmast

  • Funktsiooni MsgBox abil saate kuvada teate, kui on mõni sündmus: kasutaja sulgeb Exceli töövihiku, kasutaja prindib, töövihikule lisatakse uus leht jne.
  • Funktsiooni MsgBox abil saate kuvada teate, kui kasutaja peab enne Exceli töövihiku sulgemist tingimust täitma
  • Kasutajalt teabe saamiseks kasutage funktsiooni InputBox

Näide # 2: lubage kasutajal täita mõni teine ​​protseduur

Valige VBA redaktoris Lisa -> Uus moodul

Kirjutage see kood aknas moodul (ärge kleepige!):

Sub UserReportQuery ()

Hämar kasutaja sisend nii kaua

Hämar vastus täisarvuna

UserInput = vbYesNo

Answer = MsgBox ("Kas töödelda XYZ-i aruannet?", UserInput)

Kui vastus = vbJah, siis töötlege aruandega

Lõpeta alam

Alamprotsessi aruanne ()

MsgBox ("Täname XYZ-i aruande töötlemise eest.")

Lõpeta alam

Salvestage ja navigeerige Exceli vahekaardile Arendaja ja valige suvand „Nupp”. Klõpsake lahtrit ja määrake nupule makro UserReportQuery.

Nüüd klõpsake nuppu. See teade peaks kuvama järgmist:

Klõpsake "jah" või vajutage sisestusklahvi.

Veelkord, tada!

Pange tähele, et sekundaarne alamprogramm ProcessReport võib olla mis tahes . Näitan rohkem võimalusi näites nr 3. Aga esmalt...

Kuidas see seda teeb?

See näide tugineb eelmisele näitele ja sellel on üsna palju uusi elemente. Vaatame uue asja üle:

  • Dim UserInput As Long: Dim on lühike “dimension” ja võimaldab deklareerida muutujate nimesid. Sel juhul on UserInput muutuja nimi ja Long andmetüüp. Selges inglise keeles tähendab see rida "Siin on muutuja nimega" UserInput "ja see on pikk muutuja tüüp."
  • Hämar vastus täisarvuna: deklareerib teise muutuja nimega “Vastus”, mille andmetüüp on täisarv. Lisateavet andmetüüpide kohta leiate siit.
  • UserInput = vbYesNo: määrab muutujale väärtuse. Sel juhul vbYesNo, mis kuvab nupud Jah ja Ei. Nuputüüpe on palju . Lisateavet leiate siit.
  • Answer = MsgBox („Kas töödelda XYZ-i aruannet?”, UserInput): määrab muutuja Answer väärtuseks MsgBox-funktsiooni ja UserInput-muutuja. Jah, muutuja muutuja sees.
  • Kui Answer = vbYes, siis ProcessReport: see on lause „If”, tingimuslik lause, mis võimaldab meil öelda, kas x on tõene, siis tehke y. Sel juhul, kui kasutaja on valinud "Jah", siis käivitage alamprogramm ProcessReport.

Millal ma saaksin seda kasutada?

Seda saaks kasutada mitmel, mitmel viisil. Selle funktsionaalsuse väärtus ja mitmekülgsus on rohkem määratletud sekundaarse alamprogrammi tegevusega.

Näiteks võib-olla teil on fail, mida kasutatakse kolme erineva nädalaaruande loomiseks. Need aruanded on vormistatud dramaatiliselt erineval viisil.

  • Probleem: iga kord, kui üks neist aruannetest tuleb luua, avab kasutaja faili ning muudab vormingut ja diagramme; nii edasi ja nii edasi. Seda faili redigeeritakse ulatuslikult vähemalt 3 korda nädalas ja iga kord, kui see redigeeritakse, kulub vähemalt 30 minutit.
  • Lahendus: looge 1 nupp aruande tüübi kohta, mis vormindab aruannete vajalikud komponendid automaatselt ümber ja loob vajalikud diagrammid.

Näited reaalsest maailmast

  • Looge kasutajale dialoogiboks teatud teabe automaatseks lisamiseks mitme lehe vahel
  • Kasutajalt teabe saamiseks kasutage funktsiooni InputBox, mis seejärel täidetakse mitme lehe vahel

Näide # 3: lisage järgmiseks aasaks vahemikku numbrid

Sest tsüklid on väga kasulikud, kui peate täitma korduvaid ülesandeid kindla väärtuste vahemiku - massiivide või lahtrivahemike korral. Tavalises inglise keeles on silmus „iga x jaoks tehke y”.

Valige VBA redaktoris Lisa -> Uus moodul

Kirjutage see kood aknas moodul (ärge kleepige!):

Alamtsükli näide ()

Hämar X täisarvuna

Kui X = 1 kuni 100

Vahemik ("A" ja X). Väärtus = X

Järgmine X

Lõpeta alam

Salvestage ja navigeerige Exceli vahekaardile Arendaja ja valige nupp Makrod. Käivitage makro LoopExample.

See peaks juhtuma:

Jne kuni 100. reani.

Kuidas see seda teeb?

  • Dim X täisarvuna: deklareerib muutuja X täisarvu andmetüübina.
  • X = 1 kuni 100: see on For-tsükli algus. Lihtsamalt öeldes käsib see silmus jätkata, kuni X = 100. X on loendur . Tsükkel jätkab täitmist kuni X = 100, käivitab veel viimast korda ja peatub.
  • Vahemik ("A" ja X). Väärtus = X: see deklareerib tsükli vahemiku ja selle vahemikku panemise. Kuna esialgu on X = 1, saab esimeseks lahtriks A1, siis paneb silmus X sellesse lahtrisse.
  • Järgmine X: see käsib silmusel uuesti käivitada

Millal ma saaksin seda kasutada?

For-Next silmus on VBA üks võimsamaid funktsioone; potentsiaalse kasutamise juhtumeid on palju. See on keerulisem näide, mis nõuaks mitut loogikakihti, kuid see edastab võimaluste maailma For-Next silmustes.

Võib-olla on teil nimekiri kõigist veerus A teie pagaritöökojas müüdavatest toodetest, veeru B toote tüüp (koogid, sõõrikud või kuklid), veerus C koostisosade maksumus ja iga tooteliigi keskmine turuhind veel üks leht.

Peate välja mõtlema, milline peaks olema iga toote jaehind. Te arvate, et see peaks olema koostisosade maksumus pluss 20%, aga võimaluse korral ka 1,2% turu keskmisest madalam. For-Next silmus võimaldaks teil seda tüüpi arvutust teha.

Näited reaalsest maailmast

  • Konkreetseid väärtusi eraldi massiivi lisamiseks kasutage pesaga if-lausega tsüklit ainult siis, kui need vastavad teatud tingimustele
  • Tehke vahemiku iga väärtuse jaoks matemaatilised arvutused, nt arvutage lisatasud ja lisage need väärtusele
  • Looge stringis iga märk läbi ja eraldage kõik numbrid
  • Valige massiivist juhuslikult arv väärtusi

Järeldus

Nüüd, kui oleme rääkinud pitsast ja muffinist ning oh-jah, kuidas VBA-koodi Exceli arvutustabelites kirjutada, teeme õppekontrolli. Vaadake, kas saate neile küsimustele vastata.

  • Mis on VBA?
  • Kuidas seadistada alustama VBA kasutamist Excelis?
  • Miks ja millal kasutaksite VBA-d?
  • Milliseid probleeme võiksin VBA-ga lahendada?

Kui teil on õiglane idee, kuidas saaksite neile küsimustele vastata, siis see õnnestus.

Olenemata sellest, kas olete juhukasutaja või energiatarbija, loodan, et see õpetus andis kasulikku teavet selle kohta, mida saab teie Exceli arvutustabelites lihtsalt natuke koodiga saavutada.

Head kodeerimist!

Õppevahendid

  • Exceli VBA programmeerimine mannekeenidele, John Walkenbach
  • Alustage VBA ja Microsofti dokumentatsiooniga
  • VBA õppimine Excelis, Lynda

Natuke minust

Olen Chloe Tucker, kunstnik ja arendaja Portlandis, Oregonis. Endise koolitajana otsin pidevalt õppimise ja õpetamise või tehnoloogia ja kunsti ristmikku. Võtke minuga ühendust Twitteris @_chloetucker ja vaadake minu veebisaiti aadressil chloe.dev.