To so opombe mojega nastopa ob nastopu v epizodi 33 klica na pomoč na TechTV Canada.
Makro snemalnik VBA
Vsaka kopija Excela, prodana od leta 1995, vključuje neverjetno zmogljiv Visual Basic for Applications (VBA), ki se skriva za celicami. Če imate Excel, imate VBA.
VBA vam omogoča avtomatizacijo vsega, kar lahko počnete v Excelu, poleg tega pa tudi več stvari, ki v Excelu na splošno niso mogoče.
Na našo srečo je Microsoft vključil makro snemalnik z Excelom. Tako lahko pišete kodo VBA, ne da bi bili programer. Na žalost je Makro snemalnik pomanjkljiv. Ne bo ustvaril kode, ki bo delovala vsakič. Snemalnik makrov vas bo pripeljal blizu, toda v mnogih primerih morate kodo nekoliko popraviti, da bo zanesljivo delovala. V drugih primerih morate vedeti, kdaj uporabiti relativno snemanje, da bo makro deloval.
S Tracy Syrstad sva napisala VBA in Macros Microsoft Excel 2016, da bi ti pomagala razumeti omejitve posnete kode in se naučila, kako posneto kodo popraviti v nekaj, kar bo delovalo vsakič.
V oddaji sem predstavil postopek snemanja makra, ki bo popolnoma deloval, če boste znali uporabiti gumb Relativni sklic. Nekdo mi je dal Excelov delovni list s stotinami imen in naslovov. Želeli so poštne nalepke. Če ste kdaj uporabljali funkcijo spajanja pošte Microsoft Word, veste, da potrebujete vsako polje v novem stolpcu na delovnem listu. Namesto tega so se na tem delovnem listu podatki spustili v stolpec A.
Postopek popravljanja ene nalepke je precej dolgočasen.
- Začnite s kazalcem celice na imenu v stolpcu A.
- Pritisnite puščico dol, da se premaknete na naslov
- Ctrl + x za rezanje
- Puščica gor, puščica desno, da se premaknete v stolpec B poleg imena
- Ctrl + v za lepljenje
- Dvakrat puščica dol, enkrat leva puščica, da se premaknete v mesto
- Ctrl + x za rezanje
- Dvakrat puščica gor, puščica desno trikrat
- Ctrl + v za lepljenje
- Dvakrat puščica levo, enkrat puščica dol, da se premaknete na zdaj prazno vrstico.
- Pridržite tipko Shift, medtem ko dvakrat pritisnete puščico dol
- Alt + edr za brisanje praznih vrstic
- Puščica gor in puščica dol, da znova izberete samo ime.
Tukaj je animacija, ki prikazuje te korake:
Nastavitev Excela na Dovoli makre
Čeprav vsaka kopija Excela vsebuje VBA, Microsoft privzeto izklopi možnost izvajanja makrov. Morate narediti enkratno prilagoditev, da omogočite izvajanje makrov. Odprite Excel. V meniju izberite Orodja> Makro> Varnost. Če je raven varnosti makra trenutno nastavljena na visoko, jo spremenite v Srednja.
Priprava na snemanje makra
Premislite o korakih, ki so potrebni za dosego naloge. Prepričati se morate, da začnete s kazalcem celice na ime in končate z njim pri naslednjem imenu. Tako boste lahko večkrat večkrat zagnali makro. Ko imate pripravljene korake, vklopite snemalnik makrov. V meniju izberite Tools> Macro> Record New Macro.
Če boste ta makro uporabljali več dni, mu dodajte uporabno ime. Če gre za enkratni makro za avtomatizacijo enkratnega opravila, potem je verjetno, da ime makra ostane Macro1. Tu je pomembno polje bližnjice. Če ustvarjate makro za enkratno uporabo, ga dodelite bližnjični tipki, kot je Ctrl + a - zelo enostavno je večkrat pritisniti Ctrl + a, saj sta tipki blizu. Če boste ustvarili makro, ki ga boste uporabljali vsak dan, ga želite dodeliti ključu, ki še ni pomembna kombinacija bližnjic. Ctrl + j ali Ctrl + k sta dobri izbiri.
Makroni za enkratno uporabo naj bodo shranjeni v ThisWorkbook. Če morate makro večkrat uporabljati v številnih različnih delovnih zvezkih, ga lahko shranite v osebni delovni zvezek makrov.
Zdaj ste dobili najmanjšo orodno vrstico v celotnem Excelu; ustavite snemanje orodne vrstice. Ima le dve ikoni in izgleda tako:
Če vam ta orodna vrstica pride na pot, ne pritisnite X, da jo zaprete. Namesto tega primite modro vrstico in povlecite orodno vrstico na novo mesto. Dejanje premikanja orodne vrstice v makru ni zabeleženo. Če orodno vrstico po naključju zaprete, jo vrnite v meni Pogled> Orodne vrstice> Ustavi snemanje. To dejanje pa bo zabeleženo.
Prvi gumb v orodni vrstici je gumb »Ustavi snemanje«. To je samoumevno. Ko končate s snemanjem makra, pritisnite orodno vrstico za ustavitev snemanja.
Pomembnejši (in redko razumljiv) gumb je gumb "Relativni sklic".
V našem trenutnem primeru morate pred začetkom pritisniti gumb Relativni sklic. Če posnamete makro z vklopljenimi relativnimi referencami, bo Excel zapisal naslednje korake:
- Premakni eno celico navzdol
- Izreži trenutno celico
- Premakni eno celico navzgor
- Premakni eno celico desno
- Prilepi
- itd.
Če bi namesto tega posneli makro brez relativnih referenc, bi makro zapisal te korake:
- Premakni se v celico A4
- Izreži celico A4
- Premakni se v celico A3
- Premakni se v celico B3
- Prilepite v celico B3
- itd.
To bi bilo hudo narobe - makro potrebujemo za delo na celicah, ki so od začetne točke makroja 1 in 2 celici. Med zagonom makra znova in znova bo začetna točka vrstica 4, vrstica 5, vrstica 6 itd. Če snemate makro brez vklopljenih relativnih referenc, bi se makro vedno zagnal v 3. vrstici kot izhodišče.
Sledite tem korakom:
- V orodni vrstici Ustavi snemanje izberite gumb Relativni sklic
- Kazalec celice bi že moral biti v imenu v stolpcu A.
- Pritisnite puščico dol, da se premaknete na naslov
- Ctrl + x za rezanje
- Puščica gor, puščica desno, da se premaknete v stolpec B poleg imena
- Ctrl + v za lepljenje
- Dvakrat puščica dol, enkrat leva puščica, da se premaknete v mesto
- Ctrl + x za rezanje
- Dvakrat puščica gor, puščica desno trikrat
- Ctrl + v za lepljenje
- Dvakrat puščica levo, enkrat puščica dol, da se premaknete na zdaj prazno vrstico.
- Pridržite tipko Shift, medtem ko dvakrat pritisnete puščico dol
- Alt + edr za brisanje praznih vrstic
- Puščica gor in puščica dol, da izberete naslednje ime na seznamu.
- Pritisnite orodno vrstico Ustavi snemanje
- Shranite delovni zvezek
- Preizkusite makro tako, da pritisnete zgoraj dodeljeno bližnjico. Popolnoma mora popraviti naslednje ime na seznamu
Ko vidite, da makro deluje po želji, lahko pritisnete Ctrl + a, da hitro popravite nekaj imen na sekundo. Celoten seznam 500 imen je mogoče popraviti v minuti ali dveh.
Bonus nasvet - ni na razstavi
Makro lahko enostavno zavijete v preprosto zanko, da lahko popravi vseh 500 imen, ne da bi morali nekaj stokrat pritisniti Ctrl +.
Pritisnite Alt + F11, da odprete urejevalnik makrov.
Če ne vidite podokna Project - VBAProject, pritisnite Ctrl + r.
Z desno miškino tipko kliknite Module1 in izberite View Code. Videli boste kodo, ki je videti takole:
Zdaj vam ni treba razumeti, kaj počne ta koda, vendar veste, da želimo v tem makroju zagnati enkrat za vsako ime, ki ga imamo. Če veste, da obstaja 462 imen, lahko dodate 46 vrstic, da lahko 462-krat zaženete kodo. Na vrh makra vstavite novo vrstico z besedami " For i = 1 to 462". Na dnu makra vstavite vrstico z napisom " Next i". To pove, da mora VBA zagnati kodo v 462-krat.
Zaključek
Po tem preprostem makronu sem v oddaji pokazal primer zelo zapletenega makra. Ta makro je ustvaril vrtilne tabele in grafikone za 46 oddelkov podjetja. To poročilo je trajalo 40 ur vsak mesec. Makro VBA zdaj zažene in ustvari vseh 46 poročil v manj kot 2 minutah.
Knjiga VBA in makri Microsoft Excel 2016 vas bo popeljala po krivulji učenja, tako da boste lahko od snemanja preprostih makrov, kot je ta, prišli do zelo zapletenih poročil, ki vam lahko prihranijo stotine ur na leto. Seveda, če se ne želite učiti VBA, najemite Excel-ovega svetovalca, ki bo za vas oblikoval poročilo.
