Makro ierakstīšana programmā Excel - soli pa solim

Pat ja esat pilnīgi iesācējs Excel VBA pasaulē, varat viegli ierakstīt makro un automatizēt kādu savu darbu.

Šajā detalizētajā rokasgrāmatā es apskatīšu visu, kas jums jāzina, lai sāktu ierakstīt un izmantot makro programmā Excel.

Ja jūs interesē vienkāršs VBA apguves veids, apskatiet manu Tiešsaistes Excel VBA apmācība.

Kas ir makro?

Ja esat iesācējs VBA, vispirms ļaujiet man pastāstīt, kas ir makro - galu galā es turpināšu lietot šo terminu visā apmācībā.

Makro ir kods, kas rakstīts VBA (Visual Basic for Applications), kas ļauj palaist koda gabalu ikreiz, kad tas tiek izpildīts.

Bieži vien cilvēki (ieskaitot mani) atsaucas uz VBA kodu kā makro - neatkarīgi no tā, vai tas ir ģenerēts, izmantojot makro ierakstītāju, vai arī ir rakstīts manuāli.

Ierakstot makro, Excel rūpīgi seko jūsu veiktajām darbībām un pieraksta to saprotamā valodā - kas ir VBA.

Un tā kā Excel ir patiešām labs piezīmju pieņēmējs, tas rada ļoti detalizētu kodu (kā mēs redzēsim vēlāk šajā apmācībā).

Tagad, pārtraucot ierakstīšanu, saglabājot makro un palaižot to, Excel vienkārši atgriežas pie tā izveidotā VBA koda un veic tieši tādas pašas darbības.

Tas nozīmē, ka, pat ja jūs neko nezināt par VBA, varat automatizēt dažus uzdevumus, vienkārši ļaujot programmai Excel vienreiz ierakstīt jūsu darbības un pēc tam tās atkārtoti izmantot.

Tagad iedziļināsimies un redzēsim, kā ierakstīt makro programmā Excel.

Cilnes Izstrādātājs iegūšana lentē

Pirmais makro ierakstīšanas solis ir lentē iegūt cilni Izstrādātājs.

Ja lentē jau redzat izstrādātāja cilni, pārejiet uz nākamo sadaļu, pretējā gadījumā veiciet tālāk norādītās darbības.

• Ar peles labo pogu noklikšķiniet uz jebkuras esošās lentes cilnes un noklikšķiniet uz opcijas "Pielāgot lenti". Tiks atvērts dialoglodziņš Excel opcijas.
• Dialoglodziņā Excel opcijas jums būs opcijas Pielāgot lenti. Labajā pusē galvenajā cilnē pārbaudiet opciju Izstrādātājs.
• Noklikšķiniet uz Labi.

Iepriekš minētās darbības padarītu cilni Izstrādātājs pieejamu lentes apgabalā.

Makro ierakstīšana programmā Excel

Tagad, kad mums ir viss vietā, iemācīsimies ierakstīt makro programmā Excel.

Ierakstīsim ļoti vienkāršu makro - tādu, kas izvēlas šūnu un tajā ievada tekstu “Excel”. Ierakstot šo makro, es izmantoju tekstu “Excel”, taču nekautrējieties ievadīt savu vārdu vai jebkuru citu tekstu, kas jums patīk.

Tālāk ir norādītas šīs makro ierakstīšanas darbības.

1. Noklikšķiniet uz cilnes Izstrādātājs.
2. Grupā Kods noklikšķiniet uz pogas Makro. Tiks atvērts dialoglodziņš “Ierakstīt makro”.
3. Dialoglodziņā Makro ierakstīšana ievadiet makro nosaukumu. Es lietoju nosaukumu EnterText. Nosaucot makro, ir jāievēro daži nosaukšanas nosacījumi. Piemēram, jūs nevarat izmantot atstarpes starp tām. Es parasti labprātāk saglabāju savus makro nosaukumus kā vienu vārdu ar dažādām daļām ar lielo burtu. Varat arī izmantot pasvītrojumu, lai atdalītu divus vārdus, piemēram, Enter_Text.
4. (Neobligāts solis) Ja vēlaties, varat piešķirt īsinājumtaustiņu. Šajā gadījumā mēs izmantosim īsinājumtaustiņu Control + Shift + N. Atcerieties, ka šeit piešķirtā saīsne ignorēs visus esošos īsinājumtaustiņus jūsu darbgrāmatā. Piemēram, ja piešķirat īsinājumtaustiņu Control + S, jūs to nevarēsit izmantot darbgrāmatas saglabāšanai (tā vietā, kad to izmantosit, tā izpildīs makro).
5. Opcijā “Saglabāt makro” pārliecinieties, vai ir atlasīta opcija “Šī darbgrāmata”. Šis solis nodrošina, ka makro ir daļa no darbgrāmatas. Tas būs pieejams, kad to saglabāsit un atkal atvērsit, vai pat ja kopīgosit to ar kādu citu.
6. (Neobligāts solis) Ievadiet aprakstu. Es parasti to nedaru, bet, ja esat ārkārtīgi organizēts, varat pievienot makro.
7. Noklikšķiniet uz Labi. Tiklīdz jūs noklikšķināt uz Labi, tas sāk ierakstīt jūsu darbības programmā Excel. Cilnē Izstrādātājs varat redzēt pogu “Apturēt ierakstīšanu”, kas norāda, ka notiek makro ierakstīšana.
8. Atlasiet šūnu A2.
9. Ievadiet tekstu Excel (vai arī varat izmantot savu vārdu).
10. Nospiediet taustiņu Enter. Tas atlasīs šūnu A3.
11. Cilnē Izstrādātājs noklikšķiniet uz pogas Apturēt ierakstīšanu.

Apsveicam!

Jūs tikko ierakstījāt savu pirmo makro programmā Excel. Jūs vairs neesat makro jaunava.

Lai gan makro nedara neko noderīgu, tas kalpos savam mērķim, izskaidrojot, kā makro ierakstītājs darbojas programmā Excel.

Tagad turpināsim un pārbaudīsim šo makro.

Lai pārbaudītu makro, veiciet tālāk norādītās darbības.

1. Izdzēsiet tekstu šūnā A2. Tas ir, lai pārbaudītu, vai makro ievieto tekstu šūnā A2 vai nē.
2. Atlasiet jebkuru šūnu, izņemot A2. Tas ir, lai pārbaudītu, vai makro izvēlas šūnu A2 vai nē.
3. Noklikšķiniet uz cilnes Izstrādātājs.
4. Grupā Kods noklikšķiniet uz pogas Makro.
5. Dialoglodziņā Makro noklikšķiniet uz makro Nosaukums - EnterText.
6. Noklikšķiniet uz pogas Palaist.

Jūs pamanīsit, ka, tiklīdz noklikšķināt uz pogas Palaist, šūna A2 tiek ievietota teksts “Excel” un tiek atlasīta šūna A3.

Tagad tas viss var notikt sekundes daļā, taču patiesībā makro - gluži kā paklausīgs elfs - sekoja precīzām darbībām, kuras parādījāt, ierakstot makro.

Tātad makro vispirms izvēlas šūnu A2, pēc tam ievada tekstu Excel tajā un pēc tam izvēlas šūnu A3.

Piezīme. Makro var palaist arī, izmantojot īsinājumtaustiņu Control + Shift + N (turiet nospiestu taustiņu Control un Shift un pēc tam nospiediet N taustiņu). Tas ir tas pats īsinājumtaustiņš, ko mēs piešķīrām makro, to ierakstot.

Ko makro ierakstīšana dara aizmugurē

Tagad dosimies uz Excel aizmuguri - VB redaktoru - un redzēsim, ko patiesībā dara makro ierakstīšana.

Lai atvērtu VB redaktoru programmā Excel, veiciet tālāk norādītās darbības.

1. Noklikšķiniet uz cilnes Izstrādātājs.
2. Grupā Kods noklikšķiniet uz pogas Visual Basic.

Vai arī varat izmantot īsinājumtaustiņu - ALT + F11 (turiet nospiestu ALT taustiņu un nospiediet F11), nevis divas iepriekš minētās darbības. Šī saīsne atver arī to pašu VB redaktoru.

Tagad, ja VB redaktoru redzat pirmo reizi, neesiet satriekts.

Ļaujiet man ātri iepazīstināt jūs ar VB redaktora anatomiju.

• Ēdienkarte: Šeit jums ir visas VB redaktora iespējas. Uzskatiet to par VBA lenti. Tajā ir komandas, kuras varat izmantot, strādājot ar VB redaktoru.
• Rīkjosla - Tas ir kā VB redaktora ātrās piekļuves rīkjosla. Tam ir dažas noderīgas iespējas, un jūs varat tam pievienot papildu iespējas. Tās priekšrocība ir tāda, ka rīkjoslā esošā opcija ir tikai viena klikšķa attālumā.
• Project Explorer logs - Šeit Excel uzskaita visas darbgrāmatas un visus objektus katrā darbgrāmatā. Piemēram, ja mums ir darbgrāmata ar 3 darblapām, tā tiktu parādīta Project Explorer. Šeit ir daži papildu objekti, piemēram, moduļi, lietotāja veidlapas un klases moduļi.
• Koda logs - Šeit tiek ierakstīts vai uzrakstīts VBA kods. Katram Project Explorer uzskaitītajam objektam ir koda logs, piemēram, darblapas, darbgrāmatas, moduļi utt. Šajā apmācībā mēs redzēsim, ka ierakstītais makro nonāk moduļa koda logā.
• Rekvizītu logs - Šajā logā varat redzēt katra objekta īpašības. Es bieži izmantoju šo logu, lai nosauktu objektus vai mainītu slēptos rekvizītus. Atverot VB redaktoru, iespējams, neredzēsit šo logu. Lai to parādītu, noklikšķiniet uz cilnes skats un atlasiet Rekvizītu logs.
• Tūlītējs logs - Es bieži izmantoju tūlītējo logu, rakstot kodu. Tas ir noderīgi, ja vēlaties pārbaudīt dažus paziņojumus vai atkļūdošanas laikā. Pēc noklusējuma tas var nebūt redzams, un jūs varat to parādīt, noklikšķinot uz cilnes Skats un atlasot opciju Tūlītējs logs.

Kad mēs ierakstījām makro - EnterText, VB redaktorā notika šādas lietas:

• Tika ievietots jauns modulis.
• Tika ierakstīts makro ar mūsu norādīto nosaukumu - EnterText
• Kods tika ierakstīts moduļa koda logā.

Tātad, ja jūs veicat dubultklikšķi uz moduļa (šajā gadījumā 1. modulis), parādīsies koda logs, kā parādīts zemāk.

Šeit ir kods, ko mums piešķīra makro ierakstītājs:

Sub EnterText () '' EnterText Macro '' Īsinājumtaustiņi: Ctrl+Shift+N 'diapazons ("A2"). Atlasiet ActiveCell.FormulaR1C1 = "Excel" diapazons ("A3"). Atlasiet Beigt apakšdaļu

VBA jebkurā rindā, kas seko ‘ (apostrofa zīme) netiek izpildīts. Tas ir komentārs, kas paredzēts tikai informatīvam nolūkam. Ja noņemsiet pirmās piecas šī koda rindiņas, makro joprojām darbosies, kā paredzēts.

Tagad ļaujiet man ātri aprakstīt katras koda rindas darbību:

Kods sākas ar Sub, kam seko makro nosaukums un tukša iekava. Sub ir saīsinājums no Subroutine. Katra VBA apakšprogramma (saukta arī par procedūru) sākas ar Sub un beidzas ar End Sub.

• Diapazons (“A2”). Izvēlieties - Šī līnija izvēlas šūnu A2.
• ActiveCell.FormulaR1C1 = “Excel” - šī rinda aktīvajā šūnā ievada tekstu Excel. Tā kā mēs kā pirmo soli izvēlējāmies A2, tā kļūst par mūsu aktīvo šūnu.
• Diapazons (“A3”). Izvēlieties - Tiek atlasīta šūna A3. Tas notiek, kad mēs nospiežam taustiņu Enter pēc teksta ievadīšanas, kā rezultātā tika izvēlēta šūna A3.

Es ceru, ka līdz šim jums ir pamatzināšanas par makro ierakstīšanu programmā Excel.

Paturiet prātā, ka makro ierakstītāja kods nekādā ziņā nav efektīvs kods.

Makro ierakstītājs dažreiz kodam pievieno daudz pūka, kas reizēm nav nepieciešams. Bet tas nenozīmē, ka tas nav lietderīgi. Kādam, kurš apgūst VBA, makro ierakstītājs var būt lielisks veids, kā analizēt, kā lietas darbojas VBA.

Absolūtais salīdzinājumā ar relatīvo makro ierakstīšanu

Jūs jau zināt par absolūtām un relatīvām atsaucēm programmā Excel … vai ne?

Ja jums tā nav - vispirms izlasiet šo pamācību par atsaucēm.

Izlasi? Ejam tālāk.

Šajā sadaļā mēs redzēsim, kā ierakstīt makro absolūtās un relatīvās atsaucēs. Bet pirms tam ļaujiet man ātri apkopot atšķirību starp absolūto un relatīvo atsauci VBA (ja esat kļuvis slinks un neizlasījāt saiti, kuru es sniedzu pirms dažām rindām):

Ja makro ierakstīšanai izmantojat absolūtu atsauces opciju, VBA kods vienmēr atsaucas uz tām pašām šūnām, kuras izmantojāt. Piemēram, ja izvēlaties šūnu A2, katru reizi ievadiet tekstu Excel un nospiediet taustiņu Enter - neatkarīgi no tā, kur atrodaties darblapā un neatkarīgi no tā, kura šūna ir atlasīta, jūsu kods vispirms atlasīs šūnu A2, ievadīs tekstu Excel un pēc tam pārejiet uz šūnu A3.

Ja makro ierakstīšanai izmantojat relatīvās atsauces opciju, VBA šifrētu šūnu atsauces. Tas drīzāk koncentrētos uz kustību, salīdzinot ar aktīvo šūnu. Piemēram, pieņemsim, ka jums jau ir atlasīta šūna A1 un sākat makro ierakstīšanu relatīvā atsauces režīmā.

Tagad jūs izvēlaties šūnu A2, ievadiet tekstu Excel un nospiediet ievadīšanas taustiņu. Tagad, palaižot šo makro, tas neatgriezīsies šūnā A2, tā vietā tas pārvietosies attiecībā pret aktīvo šūnu. Piemēram, ja ir atlasīta šūna K3, tā tiks pārvietota uz K4, ievadīs tekstu Excel un pēc tam beidzot atlasīs šūnu K5.

Tagad ļaujiet man jums pastāstīt, kā ierakstīt makro relatīvo atsauču režīmā:

1. Atlasiet šūnu A1.
2. Noklikšķiniet uz cilnes Izstrādātājs.
3. Grupā Kods noklikšķiniet uz pogas “Izmantot relatīvās atsauces”. Tas kļūs zaļš, norādot, ka tas ir ieslēgts.
4. Noklikšķiniet uz pogas Ierakstīt makro.
5. Dialoglodziņā Makro ierakstīšana ievadiet makro nosaukumu. Es lietoju nosaukumu EnterTextRelRef.
6. Opcijā Veikala makro veikalā pārliecinieties, vai ir atlasīta opcija “Šī darbgrāmata”.
7. Noklikšķiniet uz Labi.
8. Atlasiet šūnu A2.
9. Ievadiet tekstu Excel (vai arī varat ievadīt savu vārdu).
10. Nospiediet taustiņu Enter. Tas novirzīs kursoru uz šūnu A3.
11. Cilnē Izstrādātājs noklikšķiniet uz pogas Apturēt ierakstīšanu.

Tas ierakstītu makro relatīvā atsauces režīmā.

Tagad dariet to.

1. Atlasiet jebkuru šūnu (izņemot A1).
2. Noklikšķiniet uz cilnes Izstrādātājs.
3. Grupā Kods noklikšķiniet uz pogas Makro.
4. Dialoglodziņā Makro noklikšķiniet uz makro nosaukuma - EnterTextRelRef.
5. Noklikšķiniet uz pogas Palaist.

Kas notiek? Vai kursors atgriezās šūnā A3.

Tā nebūtu - tāpēc, ka esat ierakstījis makro relatīvā atsauces režīmā. Tātad kursors pārvietotos attiecībā pret aktīvo šūnu. Piemēram, ja jūs to darāt, kad ir atlasīta šūna K3, tā ievadīs tekstu Excel ir šūna K4 un galu galā atlasīs šūnu K5.

Šeit ir kods, kas tiek ierakstīts aizmugurē (VB redaktora moduļa koda logs):

Sub EnterTextRelRef () '' EnterTextRelRef Macro '' ActiveCell.Offset (1, 0). Range ("A1"). Atlasiet ActiveCell.FormulaR1C1 = "Excel" ActiveCell.Offset (1, 0). Range ("A1"). Atlasiet Beigt apakš

Ņemiet vērā, ka viss šis kods nekur neattiecas uz šūnām K3 vai K4. Tā vietā tā izmanto Activecell, lai atsauktos uz atlasīto šūnu, un Offset, lai pārvietotos attiecībā pret aktīvo šūnu.

Neuztraucieties par diapazona (“A1”) daļu, kas ir kodā. Tas ir viens no tiem nevajadzīgajiem kodiem, ko makro ierakstītājs piebilst, ka tam nav nekāda mērķa un to var noņemt. Kods bez tā darbotos lieliski.

Poga Izmantot relatīvo atsauci cilnē Izstrādātājs ir pārslēgšanas poga. Varat to izslēgt (un pārslēgties atpakaļ uz absolūtu atsauci), noklikšķinot uz tā.

Ko nevar izdarīt makro ierakstītājs

Makro ierakstītājs lieliski sekos jums programmā Excel un ierakstīs jūsu precīzās darbības, taču tas var neizdoties, kad tas ir jādara vairāk.

• Jūs nevarat izpildīt kodu, neizvēloties objektu. Ja vēlaties, lai makro ierakstītājs pārietu uz nākamo darblapu un iezīmētu visas aizpildītās šūnas A slejā, neatstājot pašreizējo darblapu, tas to nevarēs izdarīt. Tas ir tāpēc, ka, ja es jums lūdzu to darīt, pat jūs to nevarēsit izdarīt (neatstājot pašreizējo lapu). Un, ja jūs pats to nevarat izdarīt, kā makro ierakstītājs fiksēs jūsu darbības. Šādos gadījumos jums manuāli jāiet un jāizveido/jārediģē kods.
• Jūs nevarat izveidot pielāgotu funkciju ar makro ierakstītāju. Izmantojot VBA, varat izveidot pielāgotas funkcijas, kuras varat izmantot darblapā kā parastās funkcijas. To var izveidot, rakstot kodu manuāli.
• Jūs nevarat palaist kodus, pamatojoties uz notikumiem: VBA varat izmantot daudzus notikumus, piemēram, darbgrāmatas atvēršanu, darblapas pievienošanu, dubultklikšķi uz šūnas utt., Lai palaistu kodu, kas saistīts ar šo notikumu. Lai to izdarītu, varat izmantot makro ierakstītāju.
• Ar makro ierakstītāju nevar izveidot cilpas. Ievadot kodu manuāli, varat izmantot VBA cilpu spēku (piemēram, Nākamais, Katram nākamajam, Darīt, Darīt līdz). Bet jūs to nevarat izdarīt, ierakstot makro.
• Jūs nevarat analizēt nosacījumus: Izmantojot makro ierakstītāju, varat pārbaudīt koda nosacījumus. Ja rakstāt VBA kodu manuāli, varat izmantot IF IF Else paziņojumus, lai analizētu stāvokli un palaistu kodu, ja tas ir patiess (vai citu kodu, ja tas ir nepareizs).
• Makro procedūrā nevar nodot argumentus: Ierakstot makro, tam nekad nebūs argumentu. Apakšprogramma var izmantot ievades argumentus, kurus makro var izmantot uzdevuma veikšanai. Ierakstot makro, to nevar izdarīt, jo ierakstītie makro ir neatkarīgi un nav savienoti ar citiem esošiem makro.

Makro iespējoti failu paplašinājumi

Ierakstot makro vai manuāli rakstot VBA kodu programmā Excel, fails jāsaglabā ar faila paplašinājumu, kuram ir iespējota makro (.xlsm).

Pirms Excel 2007 bija pietiekams tikai viens faila formāts - .xls.

Bet no 2007. gada .xlsx tika ieviests kā standarta faila paplašinājums. Faili, kas tiek saglabāti kā .xlsx, nevar saturēt tajā makro. Tātad, ja jums ir fails ar paplašinājumu .xlsx un jūs ierakstāt/rakstāt makro un to saglabājat, tas brīdinās jūs to saglabāt makro iespējotā formātā un parādīs dialoglodziņu (kā parādīts zemāk):

Ja izvēlaties Nē, programma Excel ļauj to saglabāt makro iespējotā formātā. Bet, noklikšķinot uz Jā, programma Excel automātiski noņems visu kodu no jūsu darbgrāmatas un saglabā to kā .xlsx darbgrāmatu.

Tātad, ja darbgrāmatā ir makro, tas jāsaglabā .xlsm formātā, lai saglabātu šo makro.

Dažādi makro palaišanas veidi programmā Excel

Līdz šim mēs esam redzējuši tikai vienu veidu, kā programmā Excel palaist makro - izmantojot dialoglodziņu Makro.

Bet ir vairāki veidi, kā palaist makro.

1. Palaist makro no lentes (cilne Izstrādātājs)
2. Izmantojot īsinājumtaustiņu (kas jums jāpiešķir)
3. Piešķiriet makro formai
4. Piešķiriet pogai makro
5. Palaidiet makro no VB redaktora

Secinājums - ierakstiet makro, kad iestrēdzis

Es jau minēju, ka makro ierakstītājs ir noderīgs rīks ikvienam, kas strādā ar VBA programmā Excel.

Dažas reizes izmantojot makro ierakstītāju, pamanīsit, ka tas izspiež daudz nevajadzīga koda. Tomēr tas joprojām ir noderīgs un sniedz dažas idejas, ar ko sākt. Piemēram, ja es lūdzu filtrēt šūnu kolonnu, izmantojot VBA, un jums nav ne jausmas, kas ir sintakse, varat ātri ierakstīt makro un pārbaudīt kodu.

Makro ierakstītājs ir neaizstājams rīks, un pat pēc vairāku gadu VBA kodēšanas pieredzes manā jostā es bieži vēršos pēc palīdzības pie makro ierakstītāja.