Excel VBA apmācība - kā rakstīt kodu izklājlapā, izmantojot Visual Basic

Ievads

Šī ir apmācība par koda rakstīšanu Excel izklājlapās, izmantojot Visual Basic for Applications (VBA).

Excel ir viens no populārākajiem Microsoft produktiem. 2016. gadā Microsoft izpilddirektors teica: "Padomājiet par pasauli bez Excel. Tas man vienkārši nav iespējams." Nu, varbūt pasaule nevar domāt bez Excel.

  • 1996. gadā Microsoft Excel (avots) bija vairāk nekā 30 miljoni lietotāju.
  • Mūsdienās tiek lēsts, ka Microsoft Excel lietotāji ir 750 miljoni. Tas ir nedaudz vairāk nekā Eiropas iedzīvotāju skaits un 25 reizes vairāk lietotāju nekā 1996. gadā.

Mēs esam viena liela laimīga ģimene!

Šajā apmācībā jūs uzzināsiet par VBA un to, kā rakstīt kodu Excel izklājlapā, izmantojot Visual Basic.

Priekšnoteikumi

Lai saprastu šo apmācību, jums nav nepieciešama iepriekšēja programmēšanas pieredze. Tomēr jums būs nepieciešams:

  • Pamata un vidēja līmeņa zināšanas par Microsoft Excel
  • Ja vēlaties sekot līdzi šajā rakstā sniegtajiem VBA piemēriem, jums būs nepieciešama piekļuve Microsoft Excel, vēlams, jaunākajai versijai (2019), taču Excel 2016 un Excel 2013 darbosies lieliski.
  • Vēlme izmēģināt jaunas lietas

Mācību mērķi

Šī raksta laikā jūs uzzināsiet:

  1. Kas ir VBA
  2. Kāpēc jūs izmantojat VBA
  3. Kā iestatīt programmā Excel, lai rakstītu VBA
  4. Kā atrisināt dažas reālās problēmas ar VBA

Svarīgi jēdzieni

Šeit ir daži svarīgi jēdzieni, kas jums jāpārzina, lai pilnībā izprastu šo apmācību.

Objekti : Excel ir orientēts uz objektu, kas nozīmē, ka viss ir objekts - Excel logs, darbgrāmata, lapa, diagramma, šūna. VBA ļauj lietotājiem manipulēt un veikt darbības ar objektiem programmā Excel.

Ja jums nav pieredzes ar objektorientētu programmēšanu un šī ir pavisam jauna koncepcija, veltiet sekundi, lai ļautu tai nogrimt!

Procedūras : procedūra ir daļa no VBA koda, kas ierakstīts Visual Basic redaktorā un izpilda uzdevumu. Dažreiz to sauc arī par makro (vairāk par makro zemāk). Ir divu veidu procedūras:

  • Apakšprogrammas: VBA paziņojumu grupa, kas veic vienu vai vairākas darbības
  • Funkcijas: VBA paziņojumu grupa, kas veic vienu vai vairākas darbības un atgriež vienu vai vairākas vērtības

Piezīme: jums var būt funkcijas, kas darbojas apakšprogrammās. Jūs redzēsiet vēlāk.

Makro : ja esat pavadījis kādu laiku, apgūstot modernākas Excel funkcionalitātes, iespējams, esat saskāries ar “makro” jēdzienu. Excel lietotāji var ierakstīt makro, kas sastāv no lietotāja komandām / taustiņsitieniem / klikšķiem, un tos var atskaņot zibens ātrumā, lai veiktu atkārtotus uzdevumus. Ierakstītie makro ģenerē VBA kodu, kuru pēc tam varat pārbaudīt. Faktiski ir diezgan jautri ierakstīt vienkāršu makro un pēc tam aplūkot VBA kodu.

Lūdzu, ņemiet vērā, ka dažreiz makro ierakstīšana var būt vienkāršāka un ātrāka, nevis VBA procedūras kodēšana ar roku.

Piemēram, varbūt jūs strādājat projektu vadībā. Reizi nedēļā jums jāpārvērš neapstrādāts eksportēts pārskats no jūsu projekta vadības sistēmas par skaisti noformētu, tīru ziņojumu vadībai. Jums jāpārformulē pārbudžeta projektu nosaukumi treknā un sarkanā krāsā. Varat ierakstīt izmaiņas formatējumā kā makro un palaist to vienmēr, kad nepieciešams veikt izmaiņas.

Kas ir VBA?

Visual Basic for Applications ir programmēšanas valoda, kuru izstrādājusi Microsoft. Katra programmatūra, kas atrodas Microsoft Office komplektā, tiek komplektēta ar VBA valodu bez papildu maksas. VBA ļauj Microsoft Office lietotājiem izveidot nelielas programmas, kas darbojas Microsoft Office programmatūras ietvaros.

Padomājiet par VBA kā picas krāsni restorānā. Excel ir restorāns. Virtuvē ir standarta komerciālas ierīces, piemēram, lieli ledusskapji, krāsnis un parastās cepeškrāsnis - tās visas ir Excel standarta funkcijas.

Bet ko tad, ja vēlaties pagatavot malkas picu? To nevar izdarīt standarta komerciālā cepeškrāsnī. VBA ir picu krāsns.

Yum.

Kāpēc Excel izmantot VBA?

Jo vislabākā ir malkas picas!

Bet nopietni.

Daudzi cilvēki daudz laika pavada programmā Excel kā daļu no sava darba. Arī laiks programmā Excel pārvietojas atšķirīgi. Atkarībā no apstākļiem 10 minūtes programmā Excel var justies kā mūžība, ja nevarat izdarīt to, kas jums nepieciešams, vai arī 10 stundas var paiet ļoti ātri, ja viss notiek lieliski. Kurā brīdī jums jāuzdod sev jautājums, kāpēc es uz zemes pavadu 10 stundas programmā Excel?

Dažreiz šīs dienas ir neizbēgamas. Bet, ja jūs katru dienu pavadāt 8-10 stundas programmā Excel, veicot atkārtotus uzdevumus, atkārtojot daudzus un tos pašus procesus, mēģinot iztīrīt citus faila lietotājus vai pat atjauninot citus failus pēc izmaiņu veikšanas Excel failā, VBA procedūra var būt risinājums jums.

Jums vajadzētu apsvērt iespēju izmantot VBA, ja jums nepieciešams:

  • Automatizējiet atkārtotus uzdevumus
  • Izveidojiet vienkāršus veidus, kā lietotāji var mijiedarboties ar jūsu izklājlapām
  • Manipulēt ar lielu datu apjomu

Darba sākšana VBA rakstīšanai programmā Excel

Izstrādātāja cilne

Lai rakstītu VBA, lentei būs jāpievieno cilne Izstrādātājs, lai jūs redzētu šādu lenti.

Lai lentē pievienotu cilni Izstrādātājs:

  1. Cilnē Fails dodieties uz Opcijas> Pielāgot lenti.
  2. Sadaļā Pielāgot lenti un sadaļā Galvenās cilnes atzīmējiet izvēles rūtiņu Izstrādātājs.

Pēc cilnes parādīšanas cilne Izstrādātājs paliek redzama, ja vien neesat notīrījis izvēles rūtiņu vai ja jums ir jāpārinstalē Excel. Papildinformāciju skatiet Microsoft palīdzības dokumentācijā.

VBA redaktors

Pārejiet uz cilni Izstrādātājs un noklikšķiniet uz pogas Visual Basic. Tiks parādīts jauns logs - tas ir Visual Basic redaktors. Šīs apmācības vajadzībām jums vienkārši jāpārzina rūts Project Explorer un rūts Properties Properties.

Excel VBA piemēri

Vispirms izveidosim failu, kurā varēsim spēlēties.

  1. Atveriet jaunu Excel failu
  2. Saglabājiet to kā makro iespējotu darbgrāmatu (.xlsm)
  3. Atlasiet cilni Izstrādātājs
  4. Atveriet VBA redaktoru

Pieņemsim rokenrolu ar dažiem vienkāršiem piemēriem, lai jūs varētu rakstīt kodu izklājlapā, izmantojot Visual Basic.

1. piemērs: parādiet ziņojumu, kad lietotāji atver Excel darbgrāmatu

VBA redaktorā atlasiet Ievietot -> Jauns modulis

Ierakstiet šo kodu moduļa logā (neielīmējiet!):

Sub Auto_Open ()

MsgBox ("Laipni lūdzam XYZ darbgrāmatā.")

Beigu apakšnodaļa

Saglabājiet, aizveriet darbgrāmatu un atkārtoti atveriet darbgrāmatu. Šim dialoglodziņam vajadzētu parādīties.

Ta da!

Kā tas to dara?

Atkarībā no jūsu zināšanas par programmēšanu, jums var būt daži minējumi. Tas nav īpaši sarežģīts, taču notiek diezgan daudz:

  • Apakšsadaļa (saīsne “Subroutine”): no sākuma atcerieties “VBA paziņojumu grupu, kas veic vienu vai vairākas darbības”.
  • Auto_Open: šī ir konkrētā apakšprogramma. Tas automātiski palaiž jūsu kodu, kad tiek atvērts Excel fails - tas ir notikums, kas izraisa procedūru. Auto_Open darbosies tikai tad, kad darbgrāmata tiks atvērta manuāli; tas nedarbosies, ja darbgrāmata tiks atvērta, izmantojot kodu no citas darbgrāmatas (to izdarīs Workbook_Open, uzziniet vairāk par abu atšķirību).
  • Pēc noklusējuma apakšprogrammas piekļuve ir publiska. Tas nozīmē, ka jebkurš cits modulis var izmantot šo apakšprogrammu. Visi šīs apmācības piemēri būs publiskas apakšprogrammas. Ja nepieciešams, varat pasludināt apakšprogrammas par privātajām. Tas var būt vajadzīgs dažās situācijās. Uzziniet vairāk par apakšprogrammas piekļuves modifikatoriem.
  • msgBox: šī ir funkcija - VBA priekšrakstu grupa, kas veic vienu vai vairākas darbības un atgriež vērtību. Atgrieztā vērtība ir ziņojums “Laipni lūdzam XYZ darbgrāmatā.”

Īsāk sakot, šī ir vienkārša apakšprogramma, kas satur funkciju.

Kad es to varētu izmantot?

Varbūt jums ir ļoti svarīgs fails, kuram piekļūst reti (teiksim, reizi ceturksnī), bet katru dienu automātiski atjaunina cita VBA procedūra. Kad tam piekļūst, to veic daudzi cilvēki vairākās nodaļās visā uzņēmumā.

  • Problēma: Lielāko daļu laika, kad lietotāji piekļūst failam, viņi ir neizpratnē par šī faila mērķi (kāpēc tas pastāv), par to, kā tas tiek tik bieži atjaunināts, kas to uztur un kā viņiem ar to būtu jādarbojas. Jaunajiem darbiniekiem vienmēr ir daudz jautājumu, un jums šie jautājumi ir jāliek vēl un vēl un vēl.
  • Risinājums: izveidojiet lietotāja ziņojumu, kurā ir īsa atbilde uz katru no šiem bieži atbildētajiem jautājumiem.

Piemēri reālajā pasaulē

  • Izmantojiet MsgBox funkciju, lai parādītu ziņojumu, ja ir kāds notikums: lietotājs aizver Excel darbgrāmatu, lietotājs izdrukā, darbgrāmatai tiek pievienota jauna lapa utt.
  • Izmantojiet MsgBox funkciju, lai parādītu ziņojumu, kad lietotājam pirms Excel darbgrāmatas aizvēršanas ir jāizpilda kāds nosacījums
  • Izmantojiet funkciju InputBox, lai iegūtu informāciju no lietotāja

2. piemērs: Ļaujiet lietotājam veikt citu procedūru

VBA redaktorā atlasiet Ievietot -> Jauns modulis

Ierakstiet šo kodu moduļa logā (neielīmējiet!):

Sub UserReportQuery ()

Dim UserInput tik ilgi

Blāva atbilde kā veselais skaitlis

UserInput = vbYesNē

Atbilde = MsgBox ("Vai apstrādāt XYZ ziņojumu?", UserInput)

Ja Atbilde = vbJā, tad ProcessReport

Beigu apakšnodaļa

Apstrādes atskaite ()

MsgBox ("Paldies, ka apstrādājāt XYZ ziņojumu.")

Beigu apakšnodaļa

Saglabājiet un pārejiet atpakaļ uz Excel cilni Izstrādātājs un atlasiet opciju “Poga”. Noklikšķiniet uz šūnas un piešķiriet pogai makro UserReportQuery.

Tagad noklikšķiniet uz pogas. Šajā ziņojumā jāparāda:

Noklikšķiniet uz "jā" vai nospiediet taustiņu Enter.

Vēlreiz, tada!

Lūdzu, ņemiet vērā, ka sekundārā apakšprogramma ProcessReport varētu būt jebkas . Es parādīšu vairāk iespēju 3. piemērā. Bet vispirms...

Kā tas to dara?

Šis piemērs balstās uz iepriekšējo piemēru, un tajā ir diezgan daudz jaunu elementu. Apskatīsim jaunās lietas:

  • Dim UserInput As Long: Dim ir saīsinājums no “dimension” un ļauj deklarēt mainīgo nosaukumus. Šajā gadījumā UserInput ir mainīgā nosaukums un Long ir datu tips. Vienkāršā angļu valodā šī rinda nozīmē “Šeit ir mainīgais ar nosaukumu“ UserInput ”, un tas ir gara mainīgā tips.”
  • Aptumšot atbildi kā veselu skaitli: paziņo citu mainīgo ar nosaukumu “Atbildēt”, kura datu tips ir vesels skaitlis. Uzziniet vairāk par datu tipiem šeit.
  • UserInput = vbYesNo: piešķir mainīgajam vērtību. Šajā gadījumā vbYesNo, kas parāda pogas Jā un Nē. Ir daudz pogu veidu, uzziniet vairāk šeit.
  • Answer = MsgBox (“Vai apstrādāt XYZ ziņojumu?”, UserInput): mainīgā Answer vērtībai piešķir funkciju MsgBox un UserInput mainīgo. Jā, mainīgais mainīgā ietvaros.
  • Ja Atbilde = vbJā, tad ProcessReport: tas ir nosacījums “Ja”, nosacīts paziņojums, kas ļauj mums pateikt, vai x ir taisnība, tad dariet y. Šajā gadījumā, ja lietotājs ir izvēlējies “Jā”, izpildiet apakšprogrammu ProcessReport.

Kad es to varētu izmantot?

To varēja izmantot daudzos un dažādos veidos. Šīs funkcionalitātes vērtību un daudzpusību vairāk nosaka tas, ko dara sekundārā apakšprogramma.

Piemēram, varbūt jums ir fails, ko izmanto, lai ģenerētu 3 dažādus nedēļas pārskatus. Šie ziņojumi ir noformēti dramatiski dažādos veidos.

  • Problēma: katru reizi, kad jāveido viens no šiem pārskatiem, lietotājs atver failu un maina formatējumu un diagrammas; tā tālāk un tā tālāk. Šis fails tiek plaši rediģēts vismaz 3 reizes nedēļā, un tas katru reizi tiek rediģēts vismaz 30 minūtes.
  • Risinājums: izveidojiet vienu pogu katram pārskata veidam, kas automātiski pārformatē nepieciešamos pārskatu komponentus un ģenerē nepieciešamās diagrammas.

Piemēri reālajā pasaulē

  • Izveidojiet dialoglodziņu, lai lietotājs varētu automātiski aizpildīt noteiktu informāciju vairākās lapās
  • Izmantojiet funkciju InputBox, lai iegūtu informāciju no lietotāja, kas pēc tam tiek aizpildīta vairākās lapās

3. piemērs: pievienojiet skaitļus diapazonam, izmantojot nākamo cilpu

Cilpas ir ļoti noderīgas, ja jums jāveic atkārtoti uzdevumi noteiktā vērtību diapazonā - masīvos vai šūnu diapazonos. Vienkāršā angļu valodā cilpa saka “katram x, dari y”.

VBA redaktorā atlasiet Ievietot -> Jauns modulis

Ierakstiet šo kodu moduļa logā (neielīmējiet!):

ApakšcilpaPiemērs ()

Dim X kā vesels skaitlis

Par X = 1 līdz 100

Diapazons ("A" un X). Vērtība = X

Nākamais X

Beigu apakšnodaļa

Saglabājiet un pārejiet atpakaļ uz Excel cilni Izstrādātājs un atlasiet pogu Makro. Palaidiet LoopExample makro.

Tam vajadzētu notikt:

Utt, līdz 100. rindai.

Kā tas to dara?

  • Dim X kā veselais skaitlis: deklarē mainīgo X kā veselā skaitļa datu tipu.
  • Par X = 1 līdz 100: tas ir cilnes For sākums. Vienkārši sakot, tas liek cilpu turpināt atkārtot, līdz X = 100. X ir skaitītājs . Cilpa turpinās izpildīt līdz X = 100, izpildīs pēdējo reizi un pēc tam apstāsies.
  • Diapazons ("A" un X). Vērtība = X: tas paziņo cilpas diapazonu un to, kas tajā jāievieto. Tā kā sākotnēji X = 1, pirmā šūna būs A1, un tajā brīdī cilpa ievietos X šajā šūnā.
  • Nākamais X: tas liek cilpu palaist vēlreiz

Kad es to varētu izmantot?

For-Next cilpa ir viena no spēcīgākajām VBA funkcijām; ir daudz potenciālās izmantošanas gadījumu. Šis ir sarežģītāks piemērs, kuram būtu nepieciešami vairāki loģikas slāņi, taču tas paziņo par iespēju pasauli For-Next cilpās.

Varbūt jums ir visu jūsu maiznīcā A slejā pārdoto produktu saraksts, B slejā esošo produktu veids (kūkas, virtuļi vai smalkmaizītes), sastāvdaļu izmaksas C slejā un katra produkta veida vidējās tirgus cenas vēl viena lapa.

Jums ir jāizdomā, kādai jābūt katra produkta mazumtirdzniecības cenai. Jūs domājat, ka tām vajadzētu būt sastāvdaļu izmaksām plus 20%, bet, ja iespējams, arī 1,2% zem tirgus vidējā līmeņa. For-Next cilpa ļautu jums veikt šāda veida aprēķinus.

Piemēri reālajā pasaulē

  • Izmantojiet loku ar ligzdu if, lai pievienotu īpašas vērtības atsevišķam masīvam tikai tad, ja tie atbilst noteiktiem nosacījumiem
  • Veiciet matemātiskus aprēķinus katrai diapazona vērtībai, piemēram, aprēķiniet papildu maksas un pievienojiet tās vērtībai
  • Loop caur katru rakstzīmi virknē un iegūt visus skaitļus
  • Nejauši atlasiet masīva vērtību skaitu

Secinājums

Tagad, kad mēs esam runājuši par picu un smalkmaizītēm, kā arī jā, kā rakstīt VBA kodu Excel izklājlapās, veicam mācību pārbaudi. Skatiet, vai varat atbildēt uz šiem jautājumiem.

  • Kas ir VBA?
  • Kā es varu iestatīt, lai sāktu izmantot VBA programmā Excel?
  • Kāpēc un kad jūs izmantojat VBA?
  • Kādas ir problēmas, kuras es varētu atrisināt ar VBA?

Ja jums ir pareiza ideja par to, kā jūs varētu atbildēt uz šiem jautājumiem, tas bija veiksmīgi.

Neatkarīgi no tā, vai esat neregulārs vai enerģijas lietotājs, es ceru, ka šī apmācība sniedza noderīgu informāciju par to, ko var paveikt, izmantojot tikai nelielu kodu jūsu Excel izklājlapās.

Priecīgu kodēšanu!

Mācību resursi

  • Excel VBA programmēšana manekeniem, Džons Volenbahs
  • Darba sākšana ar VBA, Microsoft dokumentāciju
  • VBA apguve programmā Excel, Lynda

Mazliet par mani

Es esmu Hloja Takere, māksliniece un attīstītāja Portlendā, Oregonā. Kā bijušais pedagogs es nepārtraukti meklēju mācīšanās un mācīšanas vai tehnoloģiju un mākslas krustojumu. Sazinieties ar mani vietnē Twitter @_chloetucker un apskatiet manu vietni vietnē chloe.dev.