„Excel VBA“ mokymo programa - kaip rašyti kodą skaičiuoklėje naudojant „Visual Basic“

Įvadas

Tai pamoka apie kodo rašymą „Excel“ skaičiuoklėse naudojant „Visual Basic for Applications“ (VBA).

„Excel“ yra vienas populiariausių „Microsoft“ produktų. 2016 m. „Microsoft“ generalinis direktorius pasakė: „Pagalvok apie pasaulį be„ Excel “. Man tai tiesiog neįmanoma“. Na, galbūt pasaulis negali mąstyti be „Excel“.

  • 1996 m. „Microsoft Excel“ (šaltinis) buvo daugiau nei 30 milijonų vartotojų.
  • Apskaičiuota, kad „Microsoft Excel“ yra maždaug 750 milijonų vartotojų. Tai yra šiek tiek daugiau nei Europos gyventojų ir 25 kartus daugiau vartotojų nei 1996 m.

Mes esame viena didelė laiminga šeima!

Šioje pamokoje sužinosite apie VBA ir kaip rašyti kodą „Excel“ skaičiuoklėje naudojant „Visual Basic“.

Būtinos sąlygos

Norėdami suprasti šią pamoką, jums nereikia ankstesnės programavimo patirties. Tačiau jums reikės:

  • Pagrindinis ir vidutinis „Microsoft Excel“ žinojimas
  • Jei norite sekti kartu su VBA pavyzdžiais šiame straipsnyje, jums reikės prieigos prie „Microsoft Excel“, pageidautina naujausios versijos (2019), tačiau „Excel 2016“ ir „Excel 2013“ veiks puikiai.
  • Noras išbandyti naujus dalykus

Mokymosi tikslai

Per šį straipsnį sužinosite:

  1. Kas yra VBA
  2. Kodėl naudosite VBA
  3. Kaip nustatyti „Excel“, kad būtų galima rašyti VBA
  4. Kaip išspręsti kai kurias realias problemas, susijusias su VBA

Svarbios sąvokos

Štai keletas svarbių sąvokų, kurias turėtumėte žinoti, kad galėtumėte visiškai suprasti šią pamoką.

Objektai : „Excel“ yra orientuota į objektą, o tai reiškia, kad viskas yra objektas - „Excel“ langas, darbaknygė, lapas, diagrama, langelis. VBA leidžia vartotojams manipuliuoti ir atlikti veiksmus su „Excel“ objektais.

Jei neturite objektyvaus programavimo patirties ir tai yra visiškai nauja koncepcija, skirkite sekundę, kad tai įsimestų!

Procedūros : procedūra yra „VBA“ kodo dalis, parašyta „Visual Basic“ redaktoriuje, atliekanti užduotį. Kartais tai taip pat vadinama makrokomanda (toliau apie makrokomandas). Yra dviejų tipų procedūros:

  • Potvarkiai: VBA sakinių grupė, atliekanti vieną ar daugiau veiksmų
  • Funkcijos: VBA sakinių grupė, atliekanti vieną ar daugiau veiksmų ir pateikianti vieną ar daugiau reikšmių

Pastaba: galite turėti funkcijas, veikiančias paprogramėse. Pamatysi vėliau.

Makrokomandos : jei praleidote laiką mokydamiesi pažangesnių „Excel“ funkcijų, tikriausiai susidūrėte su „makrokomandos“ sąvoka. „Excel“ vartotojai gali įrašyti makrokomandas, susidedančias iš vartotojo komandų / klavišų paspaudimų / paspaudimų, ir žaibiškai jas atkurti, kad atliktų pasikartojančias užduotis. Įrašytos makrokomandos sugeneruoja VBA kodą, kurį galite išnagrinėti. Iš tikrųjų labai smagu įrašyti paprastą makrokomandą ir tada pažvelgti į VBA kodą.

Atminkite, kad kartais gali būti lengviau ir greičiau įrašyti makrokomandą, o ne rankiniu būdu koduoti VBA procedūrą.

Pavyzdžiui, galbūt jūs dirbate projektų valdyme. Kartą per savaitę jūs turite paversti neapdorotą eksportuotą ataskaitą iš savo projekto valdymo sistemos į gražiai suformatuotą, švarią ataskaitą vadovybei. Turite suformuoti perteklinio biudžeto projektų pavadinimus paryškintu raudonu tekstu. Galite įrašyti formatavimo pakeitimus kaip makrokomandą ir paleisti, kai tik reikia atlikti pakeitimus.

Kas yra VBA?

„Visual Basic for Applications“ yra „Microsoft“ sukurta programavimo kalba. Kiekviena programinės įrangos, esančios „Microsoft Office“ pakete, komplektacija su VBA kalba yra be papildomų išlaidų. VBA leidžia „Microsoft Office“ vartotojams kurti mažas programas, veikiančias „Microsoft Office“ programinės įrangos programose.

Pagalvokite apie VBA kaip apie picos krosnį restorane. „Excel“ yra restoranas. Virtuvėje yra standartiniai komerciniai prietaisai, tokie kaip dideli šaldytuvai, viryklės ir įprastos orkaitės - tai visos „Excel“ funkcijos.

Bet ką daryti, jei norite gaminti malkomis kūrenamą picą? Negalite to padaryti įprastoje komercinėje kepimo krosnyje. VBA yra picos orkaitė.

Yum.

Kodėl verta naudoti VBA „Excel“?

Nes malkomis kūrenama pica yra geriausia!

Bet rimtai.

Daugybė žmonių daug laiko praleidžia „Excel“ kaip savo darbo dalį. „Excel“ laikas taip pat juda kitaip. Priklausomai nuo aplinkybių, 10 minučių „Excel“ programoje gali atrodyti amžinybė, jei negalite padaryti to, ko jums reikia, arba 10 valandų gali praeiti labai greitai, jei viskas klostosi puikiai. Štai kada turėtumėte savęs paklausti, kodėl aš žemėje praleidžiu 10 valandų „Excel“?

Kartais tos dienos neišvengiamos. Bet jei kasdien praleidžiate 8–10 valandų „Excel“ atlikdami pasikartojančias užduotis, pakartodami daugybę tų pačių procesų, bandydami išvalyti kitus failo vartotojus ar net atnaujindami kitus failus atlikę „Excel“ failo pakeitimus, VBA procedūra gali būti sprendimas jums.

Turėtumėte apsvarstyti galimybę naudoti VBA, jei jums reikia:

  • Automatizuokite pasikartojančias užduotis
  • Sukurkite paprastus būdus, kaip vartotojai gali sąveikauti su jūsų skaičiuoklėmis
  • Manipuliuoti dideliais duomenų kiekiais

Kaip pradėti rašyti VBA „Excel“

Kūrėjo skirtukas

Norėdami parašyti VBA, prie juostos turėsite pridėti skirtuką Kūrėjas, kad pamatytumėte tokią juostelę.

Norėdami prie juostos pridėti skirtuką Kūrėjas:

  1. Skirtuke Failas eikite į Parinktys> Tinkinti juostelę.
  2. Dalyje Tinkinti juostelę ir skiltyje Pagrindiniai skirtukai pažymėkite žymės langelį Kūrėjas.

Parodžius skirtuką, skirtukas Kūrėjas lieka matomas, nebent išvalysite žymimąjį laukelį arba turėsite iš naujo įdiegti „Excel“. Norėdami gauti daugiau informacijos, žr. „Microsoft“ pagalbos dokumentaciją.

VBA redaktorius

Eikite į skirtuką „Kūrėjas“ ir spustelėkite mygtuką „Visual Basic“. Pasirodys naujas langas - tai „Visual Basic“ redaktorius. Šioje pamokoje tiesiog turite būti susipažinę su „Project Explorer“ ir „Properties Properties“ sritimis.

„Excel VBA“ pavyzdžiai

Pirmiausia sukurkime failą, kuriame galėtume žaisti.

  1. Atidarykite naują „Excel“ failą
  2. Išsaugokite jį kaip makrokomandą turinčią darbaknygę (.xlsm)
  3. Pasirinkite skirtuką Kūrėjas
  4. Atidarykite VBA redaktorių

Peržiūrėkime keletą paprastų pavyzdžių, kurie padės jums rašyti kodą į skaičiuoklę naudodami „Visual Basic“.

1 pavyzdys: Rodyti pranešimą, kai vartotojai atidaro „Excel“ darbaknygę

VBA redaktoriuje pasirinkite Įterpti -> Naujas modulis

Parašykite šį kodą modulio lange (neįklijuokite!):

Pusiau automatinis_atidaryti ()

„MsgBox“ („Sveiki atvykę į XYZ darbaknygę“.)

Pabaigos sub

Išsaugokite, uždarykite darbaknygę ir vėl atidarykite darbaknygę. Šis dialogo langas turėtų būti rodomas.

Ta da!

Kaip tai daro?

Atsižvelgdami į tai, ar esate gerai susipažinę su programavimu, galite spėti. Tai nėra ypač sudėtinga, tačiau vyksta gana daug:

  • Sub (sutrumpintai „Subroutine“): nuo pat pradžių atminkite „VBA teiginių grupę, atliekančią vieną ar daugiau veiksmų“.
  • „Auto_Open“: tai yra konkretus paprogramė. Jis automatiškai paleidžia jūsų kodą, kai atsidaro „Excel“ failas - tai įvykis, kuris suaktyvina procedūrą. „Auto_Open“ bus paleista tik atidarius darbaknygę rankiniu būdu; ji nebus paleista, jei darbaknygė bus atidaryta naudojant kitos darbaknygės kodą („Workbook_Open“ tai padarys, sužinokite daugiau apie abiejų skirtumus).
  • Pagal numatytuosius nustatymus paprogramės prieiga yra vieša. Tai reiškia, kad bet kuris kitas modulis gali naudoti šią paprogramę. Visi šios pamokos pavyzdžiai bus vieši paprogramiai. Jei reikia, paprogrames galite paskelbti privačiomis. Tai gali prireikti kai kuriose situacijose. Sužinokite daugiau apie paprogramių prieigos modifikatorius.
  • msgBox: tai funkcija - VBA sakinių grupė, atliekanti vieną ar daugiau veiksmų ir grąžinanti reikšmę. Grąžinta vertė yra pranešimas „Sveiki atvykę į XYZ darbaknygę“.

Trumpai tariant, tai yra paprastas paprogramis, kuriame yra funkcija.

Kada galėčiau tai naudoti?

Galbūt turite labai svarbų failą, prie kurio prisijungiama retai (tarkime, kartą per ketvirtį), tačiau jis kasdien automatiškai atnaujinamas kita VBA procedūra. Kai prie jo prisijungia, tai daug žmonių iš kelių padalinių visoje įmonėje.

  • Problema: Dažniausiai vartotojai, pasiekdami failą, nesupranta dėl šio failo paskirties (kodėl jis egzistuoja), kaip jis taip dažnai atnaujinamas, kas jį prižiūri ir kaip turėtų su juo bendrauti. Naujiems darbuotojams visada kyla daugybė klausimų, ir jūs turite juos iš naujo pateikti vėl ir vėl.
  • Sprendimas: sukurkite vartotojo pranešimą, kuriame pateikiamas glaustas atsakymas į kiekvieną iš šių dažnai atsakomų klausimų.

Realiojo pasaulio pavyzdžiai

  • Naudokite „MsgBox“ funkciją norėdami parodyti pranešimą, kai yra įvykis: vartotojas uždaro „Excel“ darbaknygę, spausdina, prie darbaknygės pridedamas naujas lapas ir kt.
  • Norėdami uždaryti „Excel“ darbaknygę, vartotojui reikia įvykdyti sąlygą, naudokite „MsgBox“ funkciją, kad būtų rodomas pranešimas
  • Norėdami gauti informacijos iš vartotojo, naudokite „InputBox“ funkciją

2 pavyzdys: leiskite vartotojui atlikti kitą procedūrą

VBA redaktoriuje pasirinkite Įterpti -> Naujas modulis

Parašykite šį kodą modulio lange (neįklijuokite!):

Sub UserReportQuery ()

„Dim UserInput As Long“

Blankus atsakymas kaip sveikasis skaičius

UserInput = vbYesNe

Atsakymas = MsgBox („Apdoroti XYZ ataskaitą?“, „UserInput“)

Jei Atsakymas = vbTaip, tada apdorokite ataskaitą

Pabaigos sub

„Sub ProcessReport“ ()

MsgBox („Dėkojame, kad apdorojote XYZ ataskaitą.“)

Pabaigos sub

Išsaugokite ir grįžkite į „Excel“ skirtuką „Kūrėjas“ ir pasirinkite parinktį „Mygtukas“. Spustelėkite langelį ir priskirkite makrokomandą „UserReportQuery“.

Dabar spustelėkite mygtuką. Šiame pranešime turėtų būti rodoma:

Spustelėkite „taip“ arba paspauskite „Enter“.

Dar kartą, tada!

Atkreipkite dėmesį, kad antrinis paprogramis „ProcessReport“ gali būti bet koks . Aš pademonstruosiu daugiau galimybių 3 pavyzdyje. Bet pirma...

Kaip tai daro?

Šis pavyzdys remiasi ankstesniu pavyzdžiu ir turi nemažai naujų elementų. Peržiūrėkime naujus dalykus:

  • „Dim UserInput As Long“: „Dim“ reiškia „dimensiją“ ir leidžia deklaruoti kintamųjų pavadinimus. Šiuo atveju „UserInput“ yra kintamojo pavadinimas, o „Long“ - duomenų tipas. Paprasčia anglų kalba ši eilutė reiškia „Štai kintamasis, vadinamas„ UserInput “, ir tai yra ilgojo kintamojo tipas“.
  • Blankus atsakymas kaip sveikasis skaičius: deklaruoja kitą kintamąjį, pavadintą „Atsakymas“, kurio duomenų tipas yra Sveikasis skaičius. Sužinokite daugiau apie duomenų tipus čia.
  • „UserInput = vbYesNo“: priskiria kintamajam vertę. Šiuo atveju vbYesNo, kuris rodo mygtukus Taip ir Ne. Yra daugybė mygtukų tipų. Sužinokite daugiau čia.
  • Atsakymas = MsgBox („Apdoroti XYZ ataskaitą?“, „UserInput“): kintamojo Atsakymas reikšmę priskiria funkcijai „MsgBox“ ir „UserInput“. Taip, kintamasis kintamajame.
  • Jei Atsakymas = vbTaip, tada „ProcessReport“: tai yra sakinys „If“, sąlyginis sakinys, leidžiantis pasakyti, ar x yra teisinga, tada atlikite y. Tokiu atveju, jei vartotojas pasirinko „Taip“, vykdykite paprogramę „ProcessReport“.

Kada galėčiau tai naudoti?

Tai galėtų būti naudojama daugeliu būdų. Šios funkcijos vertę ir universalumą labiau apibrėžia tai, ką daro antrinis paprogramis.

Pvz., Galbūt turite failą, kuris naudojamas 3 skirtingoms savaitinėms ataskaitoms generuoti. Šios ataskaitos yra suformuluotos labai skirtingais būdais.

  • Problema: kiekvieną kartą, kai reikia sukurti vieną iš šių ataskaitų, vartotojas atidaro failą ir keičia formatą bei diagramas; Ir taip toliau. Šis failas yra plačiai redaguojamas mažiausiai 3 kartus per savaitę ir kiekvieną kartą, kai jį redaguojate, trunka mažiausiai 30 minučių.
  • Sprendimas: sukurkite 1 mygtuką kiekvienam ataskaitos tipui, kuris automatiškai performatuoja būtinus ataskaitų komponentus ir sukuria reikiamas diagramas.

Realiojo pasaulio pavyzdžiai

  • Sukurkite dialogo langą, kad vartotojas galėtų automatiškai užpildyti tam tikrą informaciją keliuose lapuose
  • Norėdami gauti informacijos iš vartotojo, naudokite „InputBox“ funkciją, kuri tada užpildoma keliuose lapuose

3 pavyzdys: pridėkite skaičius prie diapazono naudodami „ciklo„ Kitas ““

Nes kilpos yra labai naudingos, jei reikia atlikti pasikartojančias užduotis tam tikrame reikšmių diapazone - masyvuose ar langelių diapazonuose. Paprasčia anglų kalba kilpa sako „už kiekvieną x, atlikite y“.

VBA redaktoriuje pasirinkite Įterpti -> Naujas modulis

Parašykite šį kodą modulio lange (neįklijuokite!):

Antrinė ciklo pavyzdys ()

Dim X kaip sveikasis skaičius

Jei X = nuo 1 iki 100

Diapazonas („A“ ir X). Vertė = X

Kitas X

Pabaigos sub

Išsaugokite ir grįžkite į „Excel“ skirtuką „Kūrėjas“ ir pasirinkite mygtuką „Makrokomandos“. Paleiskite „LoopExample“ makrokomandą.

Tai turėtų atsitikti:

Ir tt, iki 100-osios eilutės.

Kaip tai daro?

  • „Dim X As Integer“: skelbia kintamąjį X kaip sveikojo skaičiaus duomenų tipą.
  • Jei X = nuo 1 iki 100: tai yra ciklo „For“ pradžia. Paprasčiau tariant, tai nurodo kilpą kartoti tol, kol X = 100. X yra skaitiklis . Kilpa bus vykdoma tol, kol X = 100, vykdys paskutinį kartą ir tada sustos.
  • Diapazonas („A“ ir X). Vertė = X: tai nurodo kilpos diapazoną ir tai, ką reikia įdėti į tą diapazoną. Kadangi iš pradžių X = 1, pirmoji ląstelė bus A1, tada kilpa įdės X į tą langelį.
  • Kitas X: tai nurodo kilpą paleisti dar kartą

Kada galėčiau tai naudoti?

„For-Next“ kilpa yra viena iš galingiausių VBA funkcijų; yra daugybė galimo naudojimo atvejų. Tai yra sudėtingesnis pavyzdys, kuriam reikalingi keli logikos sluoksniai, tačiau jis pateikia „For-Next“ kilpų galimybių pasaulį.

Gal turite visų jūsų kepykloje A skiltyje parduotų produktų sąrašą, B stulpelio produktų tipą (pyragai, spurgos ar bandelės), ingredientų kainą C stulpelyje ir vidutines kiekvieno produkto tipo rinkos kainas. kitas lapas.

Turite išsiaiškinti, kokia turėtų būti kiekvieno produkto mažmeninė kaina. Jūs galvojate, kad tai turėtų būti ingredientų kaina plius 20%, bet, jei įmanoma, taip pat 1,2% mažesnė nei rinkos vidurkis. „For-Next“ ciklas leistų atlikti tokio tipo skaičiavimus.

Realiojo pasaulio pavyzdžiai

  • Norėdami įtraukti konkrečias reikšmes į atskirą masyvą, naudokite kilpą su įdėtu „if“ sakiniu tik tada, jei jie atitinka tam tikras sąlygas
  • Atlikite matematinius skaičiavimus kiekvienai diapazono vertei, pvz., Apskaičiuokite papildomus mokesčius ir pridėkite juos prie vertės
  • Peržiūrėkite kiekvieną eilutės simbolį ir išskirkite visus skaičius
  • Atsitiktinai pasirinkite masyvo reikšmių skaičių

Išvada

Dabar, kai kalbėjome apie picas ir bandeles, o taip, kaip rašyti VBA kodą „Excel“ skaičiuoklėse, atlikime mokymosi patikrą. Pažiūrėkite, ar galite atsakyti į šiuos klausimus.

  • Kas yra VBA?
  • Kaip susikurti pradėti naudoti VBA „Excel“?
  • Kodėl ir kada naudotumėtės VBA?
  • Kokias problemas galėčiau išspręsti su VBA?

Jei turite teisingą idėją, kaip galėtumėte atsakyti į šiuos klausimus, tai pavyko.

Nesvarbu, ar esate atsitiktinis vartotojas, ar energijos vartotojas, tikiuosi, kad ši pamoka pateikė naudingos informacijos apie tai, ką galima pasiekti naudojant tik šiek tiek kodo „Excel“ skaičiuoklėse.

Laimingo kodavimo!

Mokymosi ištekliai

  • „Excel VBA“ programavimas manekenams, Johnas Walkenbachas
  • Pradėkite naudotis VBA, „Microsoft“ dokumentacija
  • Mokytis VBA „Excel“, Lynda

Šiek tiek apie mane

Esu Chloe Tucker, menininkė ir kūrėja Portlande, Oregone. Kaip buvęs pedagogas nuolat ieškau mokymosi ir mokymo, arba technologijų ir meno sankirtos. Susisiekite su manimi „Twitter“ @_chloetucker ir peržiūrėkite mano svetainę adresu chloe.dev.