Tai yra mano laidos užrašai iš mano pasirodymo „Call for Help“ 33 serijoje „TechTV Canada“.
„VBA Makro Recorder“
Kiekvienoje nuo 1995 m. Parduotoje „Excel“ kopijoje yra neįtikėtinai galingas „Visual Basic for Applications“ (VBA), slepiantis už langelių. Jei turite „Excel“, turite VBA.
VBA leidžia automatizuoti viską, ką galite padaryti programoje „Excel“, be to, atlikti daugiau dalykų, kurių „Excel“ paprastai neįmanoma.
Mūsų laimei, „Microsoft“ su „Excel“ įtraukė makrokomandą. Tai leidžia jums rašyti VBA kodą nebūnant programuotoju. Deja, „Macro Recorder“ yra ydingas. Jis nesukurs kodo, kuris veiks kiekvieną kartą. Makrokomandos įrašytuvas priartins jus, tačiau daugeliu atvejų reikia šiek tiek pataisyti kodą, kad jis patikimai veiktų. Kitais atvejais turite žinoti, kada naudoti santykinį įrašymą, kad makrokomanda veiktų.
Tracy Syrstad ir aš parašėme VBA ir makrokomandas „Microsoft Excel 2016“, kad galėtume lengviau suprasti įrašyto kodo apribojimus ir išmokyti, kaip pataisyti įrašytą kodą viskam tinkamam.
Laidoje aš pademonstravau makrokomandos įrašymo procesą, kuris veiks puikiai, jei mokėsite naudoti mygtuką „Santykinė nuoroda“. Kažkas man davė „Excel“ darbalapį su šimtais vardų ir adresų. Jie norėjo pašto etikečių. Jei kada nors naudojote „Microsoft Word“ pašto sujungimo funkciją, žinote, kad kiekvienas laukas reikalingas naujame darbalapio stulpelyje. Vietoje šio konkretaus darbalapio duomenys buvo nukreipti į A stulpelį.
Vienos etiketės taisymo procesas yra gana varginantis.
- Pradėkite nuo langelio žymeklio ant A stulpelio pavadinimo.
- Paspauskite rodyklę žemyn, kad pereitumėte į adresą
- „Ctrl“ + x, jei norite iškirpti
- Rodyklė aukštyn, dešinė rodyklė, kad pereitumėte į B stulpelį šalia pavadinimo
- „Ctrl“ + v, kad įklijuotumėte
- Rodyklė žemyn du kartus, kairėn rodyklė vieną kartą, kad pereitumėte į miestą
- „Ctrl“ + x, jei norite iškirpti
- Rodyklė aukštyn du kartus, dešinė rodyklė tris kartus
- „Ctrl“ + v, kad įklijuotumėte
- Kairė rodyklė du kartus, rodyklė žemyn vieną kartą, kad pereitumėte prie tuščios eilutės.
- Laikykite nuspaudę „Shift“ klavišą, kai du kartus paspausite rodyklę žemyn
- Alt + edr ištrinti tuščias eilutes
- Rodyklė aukštyn ir žemyn, kad vėl pasirinktumėte tik vardą.
Čia yra animacija, rodanti šiuos veiksmus:
„Excel“ nustatymas leisti makrokomandas
Nors kiekvienoje „Excel“ kopijoje yra VBA, pagal numatytuosius nustatymus „Microsoft“ išjungia galimybę paleisti makrokomandas. Norėdami leisti makrokomandas, turite atlikti vienkartinį koregavimą. Atidarykite „Excel“. Meniu pasirinkite Įrankiai> Makrokomanda> Sauga. Jei makrokomandos saugumo lygis šiuo metu nustatytas aukštas, pakeiskite jį į vidutinį.
Pasirengimas įrašyti makrokomandą
Pagalvokite apie veiksmus, būtinus užduočiai pasiekti. Norite įsitikinti, kad pradėjote langelio žymeklį ant pavadinimo ir baigiate jį kitu vardu. Tai leis daug kartų pakartotinai paleisti makrokomandą. Kai atliksite visus veiksmus, įjunkite makrofoną. Meniu pasirinkite Įrankiai> Makrokomanda> Įrašyti naują makrokomandą.
Jei daugelį dienų vykdysite šią makrokomandą, turėtumėte jai suteikti naudingą pavadinimą. Jei automatizuoti vienkartinę užduotį yra vienkartinė makrokomanda, tikriausiai gerai, jei makrokomandos pavadinimą paliksite kaip „Makro1“. Svarbus laukas čia yra sparčiųjų klavišų laukas. Jei kuriate makrokomandą vienkartiniam naudojimui, priskirkite makrokomandą sparčiam klavišui, pvz., „Ctrl + a“ - gana paprasta pakartotinai paspausti „Ctrl + a“, nes klavišai yra arti vienas kito. Jei ketinate sukurti makrokomandą, kurią naudosite kiekvieną dieną, makrokomandą norite priskirti klavišui, kuris dar nėra svarbus sparčiųjų klavišų derinys. „Ctrl + j“ arba „Ctrl + k“ yra geras pasirinkimas.
Vienkartinio naudojimo makrokomandos turėtų būti saugomos „ThisWorkbook“. Jei makrokomandą reikia naudoti daugelyje skirtingų darbaknygių, makrokomandą galite išsaugoti „Personal Macro Workbook“.
Dabar jums pateikiama mažiausia įrankių juosta visoje „Excel“; sustabdyti įrašymo įrankių juostą. Jame yra tik dvi piktogramos ir jis atrodo taip:
Jei ši įrankių juosta jums trukdo, nespauskite X, kad ją uždarytumėte. Vietoj to, patraukite mėlyną juostą ir nuvilkite įrankių juostą į naują vietą. Įrankių juostos perkėlimo veiksmas nėra įrašomas į makrokomandą. Jei netyčia uždarote įrankių juostą, ją grąžinkite naudodami „View“> „Toolbars“> „Stop Recording“. Tačiau šis veiksmas bus įrašytas.
Pirmasis įrankių juostos mygtukas yra mygtukas „Sustabdyti įrašymą“. Tai savaime suprantama. Baigę įrašyti makrokomandą, paspauskite sustabdymo įrašymo įrankių juostą.
Svarbesnis (ir retai suprantamas) mygtukas yra mygtukas „Santykinė nuoroda“.
Mūsų dabartiniame pavyzdyje prieš pradėdami turite paspausti mygtuką „Santykinė nuoroda“. Jei įrašote makrokomandą su įjungtomis santykinėmis nuorodomis, „Excel“ įrašys tokius veiksmus:
- Perkelkite vieną langelį žemyn
- Iškirpkite esamą langelį
- Perkelkite vieną langelį aukštyn
- Perkelkite vieną langelį į dešinę
- Įklijuoti
- ir kt.
Jei vietoj to įrašytumėte makrokomandą be santykinių nuorodų, makrokomanda įrašytų šiuos veiksmus:
- Pereiti į langelį A4
- Iškirpti langelį A4
- Pereiti į langelį A3
- Pereikite į langelį B3
- Įklijuoti į B3 langelį
- ir kt.
Tai būtų siaubingai neteisinga - mums reikia, kad makrokomanda veiktų ląstelėse, kurios yra 1 ir 2 ląstelės nuo makrokomandos pradžios. Vykdant makrokomandą vėl ir vėl, pradinis taškas bus 4, 5, 6 eilutės ir tt. Įrašius makrokomandą be įjungtų santykinių nuorodų, makrokomanda visada bus vykdoma 3 eilutėje.
Atlikite šiuos veiksmus:
- „Stop Recording“ įrankių juostoje pasirinkite „Relative Reference“ mygtuką
- Langelio žymeklis jau turėtų būti ant vardo A stulpelyje.
- Paspauskite rodyklę žemyn, kad pereitumėte į adresą
- „Ctrl“ + x, jei norite iškirpti
- Rodyklė aukštyn, dešinė rodyklė, kad pereitumėte į B stulpelį šalia pavadinimo
- „Ctrl“ + v, kad įklijuotumėte
- Rodyklė žemyn du kartus, kairėn rodyklė vieną kartą, kad pereitumėte į miestą
- „Ctrl“ + x, jei norite iškirpti
- Rodyklė aukštyn du kartus, dešinė rodyklė tris kartus
- „Ctrl“ + v, kad įklijuotumėte
- Kairė rodyklė du kartus, rodyklė žemyn vieną kartą, kad pereitumėte prie tuščios eilutės.
- Laikykite nuspaudę „Shift“ klavišą, kai du kartus paspausite rodyklę žemyn
- Alt + edr ištrinti tuščias eilutes
- Rodyklė aukštyn ir žemyn, kad pasirinktumėte kitą vardą sąraše.
- Paspauskite „Stop Recording“ įrankių juostą
- Išsaugokite darbaknygę
- Išbandykite makrokomandą paspausdami aukščiau priskirtą spartųjį klavišą. Tai turėtų puikiai nustatyti kitą vardą sąraše
Kai pamatysite, kad makrokomanda veikia kaip norima, galite palaikyti „Ctrl“ + a, kad greitai nustatytumėte kelis vardus per sekundę. Visą 500 vardų sąrašą galima pataisyti per minutę ar dvi.
Premijos patarimas - ne šou
Galite lengvai suvynioti makrokomandą į paprastą kilpą, kad ji sutvarkytų visus 500 vardų, jums nereikėtų paspausti „Ctrl“ + kelis šimtus kartų.
Paspauskite klavišą Alt + F11, kad atidarytumėte makrokomandą.
Jei nematote srities „Projektas - VBAProject“, paspauskite „Ctrl“ + r.
Dešiniuoju pelės mygtuku spustelėkite „Module1“ ir pasirinkite Peržiūrėti kodą. Pamatysite kodą, kuris atrodo taip:
Dabar jūs neturite suprasti, ką daro šis kodas, bet jūs žinote, kad mes norime viską paleisti toje makrokomandoje vieną kartą kiekvienam mūsų turimam vardui. Jei žinote, kad yra 462 vardai, galite pridėti 2 eilutes, kad kodas būtų paleistas 462 kartus. Makrokomandos viršuje įterpkite naują eilutę su žodžiais „ For i = 1 to 462“. Makrokomandos apačioje įterpkite eilutę su užrašu „ Next i“. Tai liepia VBA paleisti kodą 462 kartus.
Išvada
Po šio paprasto makro aš parodžiau labai sudėtingos makrokomandos pavyzdį. Ši makrokomanda sukūrė 46 įmonės padalinių suvestines lenteles ir diagramas. Ši ataskaita kiekvieną mėnesį truko 40 valandų. VBA makrokomanda veikia ir sukuria visas 46 ataskaitas per mažiau nei 2 minutes.
Knyga VBA ir makrokomandos „Microsoft Excel 2016“ padės jums mokymosi kreivę, kad galėtumėte įrašyti tokias paprastas makrokomandas iki labai sudėtingų ataskaitų, kurios gali sutaupyti šimtus valandų per metus. Žinoma, jei nenorite mokytis VBA, samdykite „Excel“ konsultantą, kuris sukurs jums ataskaitą.
