Ungazakha kanjani isengezo sakho seMicrosoft Excel

Ngisho noma ungazi ukuthi uhlela kanjani, kunezindawo eziningi (izincwadi, amawebhusayithi, izinkundla) lapho ungathola khona amakhodi e-VBA enziwe ngomumo ngenani elikhulu lemisebenzi ejwayelekile ku-Excel. Ngokuhlangenwe nakho kwami, abasebenzisi abaningi ngokushesha noma kamuva baqoqa iqoqo labo lomuntu siqu lama-macros ukuze benze izinqubo zesimiso ngokuzenzakalela, noma ngabe ukuhumusha amafomula abe amanani, ukubonisa izibalo ngamagama, noma ukufingqa amaseli ngombala. Futhi lapha inkinga iphakama - ikhodi enkulu ku-Visual Basic idinga ukugcinwa ndawana thize ukuze isetshenziswe kamuva emsebenzini.

Inketho elula ukugcina ikhodi ye-macro ngqo efayeleni elisebenzayo ngokuya kumhleli we-Visual Basic usebenzisa isinqamuleli sekhibhodi. Alt+F11 futhi wengeza imojuli entsha engenalutho ngemenyu Faka – Imojula:

Nokho, kukhona ukungalungi okuningi ngale ndlela:

  • Uma kunamafayela amaningi asebenzayo, futhi i-macro iyadingeka yonke indawo, njenge-macro yokuguqula amafomula abe amanani, kuzodingeka ukopishe ikhodi. kuzo zonke izincwadi.
  • Akumele kukhohlakale gcina ifayela ngefomethi enikwe amandla amakhulu (xlsm) noma ngefomethi yencwadi kanambambili (xlsb).
  • Lapho uvula ifayela elinjalo isivikelo esikhulu isikhathi ngasinye izokhipha isexwayiso esidinga ukwamukelwa (kahle, noma vimbela ukuvikela ngokuphelele, okungenzeka kungafiseleki ngaso sonke isikhathi).

Isixazululo esihle kakhulu kungaba ukudala isengezo sakho (Isengezo se-Excel) - ifayela elihlukile lefomethi ekhethekile (xlam) equkethe wonke amamakhro "owathandayo". Izinzuzo zale ndlela:

  • Kuzokwanela xhuma isengezo kanye ku-Excel - futhi ungasebenzisa izinqubo nemisebenzi yayo ye-VBA kunoma yiliphi ifayela kule khompyutha. Ukulondoloza kabusha amafayela akho asebenzayo kumafomethi we-xlsm- kanye ne-xlsb, ngakho-ke, akudingekile, ngoba. ikhodi yomthombo ngeke igcinwe kuzo, kodwa kufayela lokungeza.
  • Protection ngeke ukhathazwe ngama-macros, futhi. izengezo, ngokwencazelo, ziyimithombo ethenjwayo.
  • Ungakwenza ithebhu ehlukile kuribhoni ye-Excel enezinkinobho ezinhle zokuqalisa ama-add-in macros.
  • Isengezo siyifayela elihlukile. Okwakhe kulula ukuthwala kusuka kukhompuyutha kuya kukhompuyutha, yabelana nozakwenu noma uyithengise 😉

Ake sihambe kuyo yonke inqubo yokudala isengezo sakho se-Microsoft Excel isinyathelo ngesinyathelo.

Isinyathelo 1. Dala ifayela elengeziwe

Vula i-Microsoft Excel ngencwadi yokusebenza engenalutho bese uyigcina ngaphansi kwanoma yiliphi igama elifanelekile (isibonelo I-MyExcelAddin) ngefomethi yokwengeza ngomyalo Ifayela – Gcina Njenge noma okhiye F12, ecacisa uhlobo lwefayela Isengezo se-Excel:

Sicela uqaphele ukuthi ngokuzenzakalelayo i-Excel igcina izengezo kufolda ethi C:UsersYour_nameAppDataRoamingMicrosoftAddIns, kodwa, ngokomthetho, ungacacisa noma iyiphi enye ifolda elungele wena.

Isinyathelo sesi-2. Sixhuma isengezo esidaliwe

Manje isengezo esisidalile esinyathelweni sokugcina I-MyExcelAddin kufanele ixhunywe ku-Excel. Ukuze wenze lokhu, yiya kumenyu Ifayela – Izinketho – Izengezo (Ifayela — Izinketho — Izengezo), chofoza inkinobho Mayelana (Hamba) phansi kwefasitela. Ewindini elivulayo, chofoza inkinobho Buyekeza (Phequlula) futhi ucacise indawo yefayela lethu elengeziwe.

Uma wenze konke kahle, ke wethu I-MyExcelAddin kufanele kuvele ohlwini lwezengezo ezitholakalayo:

Isinyathelo sesi-3. Engeza ama-macros ku-add-in

Isengezo sethu sixhunywe ku-Excel futhi sisebenza ngempumelelo, kodwa akukabikho neyodwa i-macro kuyo okwamanje. Asigcwalise. Ukuze wenze lokhu, vula isihleli se-Visual Basic ngesinqamuleli sekhibhodi Alt+F11 noma ngenkinobho Visual Basic ithebhu Unjiniyela (Unjiniyela). Uma amathebhu Unjiniyela ayibonakali, ingavezwa nge Ifayela – Izinketho – Ukusethwa kweRibhoni (Ifayela — Izinketho — Yenza Iribhoni Ngokwezifiso).

Kufanele kube newindi ekhoneni eliphezulu kwesokunxele lomhleli Project (uma ingabonakali, yivule ngemenyu Buka - I-Project Explorer):

Leli windi libonisa zonke izincwadi zokusebenza ezivuliwe futhi isebenzisa izengezo ze-Microsoft Excel, kuhlanganisa nezethu. I-VBAProject (I-MyExcelAddin.xlam) Ikhethe ngegundane bese wengeza imojuli entsha kuyo ngemenyu Faka – Imojula. Kule mojula, sizogcina ikhodi ye-VBA yama-add-in macros.

Ungathayipha ikhodi kusukela ekuqaleni (uma wazi ukuthi uhlela kanjani), noma uyikopishe kwenye indawo eseyenziwe (okulula kakhulu). Ake, ukuze sihlole, sifake ikhodi ye-macro elula kodwa ewusizo kumojula engenalutho eyengeziwe:

Ngemva kokufaka ikhodi, ungakhohlwa ukuchofoza inkinobho yokulondoloza (i-diskette) ekhoneni eliphezulu kwesokunxele.

I-macro yethu AmafomulaToValues, njengoba ungacabanga kalula, iguqula amafomula abe amanani ebangeni elikhethwe ngaphambili. Ngezinye izikhathi lawa macros abizwa nangokuthi izinqubo. Ukuze uyiqalise, udinga ukukhetha amaseli anamafomula bese uvula ibhokisi lengxoxo elikhethekile amamakhro kusuka kuthebhu Unjiniyela (Unjiniyela - Amamakhro) noma isinqamuleli sekhibhodi Alt+F8. Imvamisa, leli windi libonisa amamakhro atholakalayo kuwo wonke ama-workbooks avuliwe, kodwa ama-add-in macros awabonakali lapha. Naphezu kwalokhu, singafaka igama lenqubo yethu ensimini igama le-macro (Igama lemakhro)bese uchofoza inkinobho Qalisa (gijima) - futhi i-macro yethu izosebenza:

    

Lapha ungakwazi futhi ukwabela isinqamuleli sekhibhodi ukuze uqalise ngokushesha i-macro - inkinobho inesibopho salokhu Amapharamitha (Izinketho) efasiteleni langaphambilini I-Macro:

Lapho unikeza okhiye, khumbula ukuthi bazwela kakhulu futhi bazwela ukwakheka kwekhibhodi. Ngakho uma ukwabela inhlanganisela efana Ctrl+Й, then, in fact, in the future you will have to make sure that you have the layout turned on and press additionally Shiftukuthola uhlamvu olukhulu.

Ukuze kube lula, singakwazi futhi ukwengeza inkinobho ye-macro yethu kubha yamathuluzi yokufinyelela ngokushesha ekhoneni eliphezulu kwesokunxele sewindi. Ukuze wenze lokhu, khetha Ifayela – Izinketho – Ibha yamathuluzi yokufinyelela Ngokushesha (Ifayela — Izinketho — Yenza ngokwezifiso ibha yamathuluzi yokufinyelela ngokushesha), bese ohlwini lokudonsela phansi phezulu efasiteleni inketho amamakhro. Ngemuva kwalokho i-macro yethu AmafomulaToValues ingafakwa kuphaneli ngenkinobho Engeza (Engeza) bese ukhetha isithonjana sayo ngenkinobho Guqula (Hlela):

Isinyathelo 4. Engeza imisebenzi esengezo

Kodwa izinqubo ezinkulu, kukhona futhi umsebenzi wama-macros noma njengoba bebizwa kanjalo UDF (Umsebenzi Ochaziwe Umsebenzi = umsebenzi ochazwe ngumsebenzisi). Ake sakhe imojuli ehlukile esengezo sethu (umyalo wemenyu Faka – Imojula) bese unamathisele ikhodi yomsebenzi olandelayo lapho:

Kulula ukubona ukuthi lo msebenzi uyadingeka ukuze kukhishwe i-VAT enanini elihlanganisa ne-VAT. Hhayi i-binomial kaNewton, yebo, kodwa izosenzela njengesibonelo ukukhombisa izimiso eziyisisekelo.

Qaphela ukuthi i-syntax yomsebenzi ihlukile kunqubo:

  • ukwakhiwa kuyasetshenziswa Umsebenzi…. Qeda Umsebenzi esikhundleni Isihloko ... Qeda Isihloko
  • ngemva kwegama lomsebenzi, izimpikiswano zawo zikhonjiswa kubakaki
  • emzimbeni womsebenzi, izibalo ezidingekayo ziyenziwa bese umphumela wabelwa oguquguqukayo ngegama lomsebenzi.

Futhi qaphela ukuthi lo msebenzi awudingeki, futhi akunakwenzeka ukusebenza njengenqubo yangaphambilini ye-macro ngokusebenzisa ibhokisi lengxoxo amamakhro nenkinobho Qalisa. Umsebenzi omkhulu onjalo kufanele usetshenziswe njengomsebenzi weshidi lokusebenzela elijwayelekile (SUM, IF, VLOOKUP…), okungukuthi vele ufake kunoma iyiphi iseli, ucacise inani lenani nge-VAT njengempikiswano:

... noma ngena ngebhokisi lengxoxo elijwayelekile lokufaka umsebenzi (inkinobho fx kubha yefomula), ukukhetha isigaba Ukuchazwa Komsebenzisi (Umsebenzisi Uchaziwe):

Umzuzu owodwa ongajabulisi lapha ukungabikho kwencazelo evamile yomsebenzi phansi kwefasitela. Ukuyengeza kuzodingeka wenze okulandelayo:

  1. Vula i-Visual Basic Editor ngesinqamuleli sekhibhodi Alt+F11
  2. Khetha isengezo kuphaneli yephrojekthi bese ucindezela inkinobho F2ukuvula iwindi leSiphequluli Sento
  3. Khetha iphrojekthi yakho yokwengeza ohlwini lokudonsela phansi phezulu efasiteleni
  4. Chofoza kwesokudla kumsebenzi ovelayo bese ukhetha umyalo Izakhiwo.
  5. Faka incazelo yomsebenzi efasiteleni Incazelo
  6. Londoloza ifayela elengeziwe bese uqala kabusha i-excel.

Ngemva kokuqala kabusha, umsebenzi kufanele ubonise incazelo esiyifakile:

Isinyathelo 5. Dala ithebhu yokwengeza kusixhumi esibonakalayo

Okokugcina, nakuba kungaphoqelekile, kodwa ukuthinta okujabulisayo kuzoba ukwakhiwa kwethebhu ehlukile enenkinobho yokusebenzisa i-macro yethu, ezovela ku-interface ye-Excel ngemva kokuxhuma isengezo sethu.

Ulwazi olumayelana namathebhu aboniswa ngokuzenzakalelayo luqukethwe ngaphakathi kwebhuku futhi kufanele lufomethwe ngekhodi ekhethekile ye-XML. Indlela elula yokubhala nokuhlela ikhodi enjalo ngosizo lwezinhlelo ezikhethekile - abahleli be-XML. Enye yezinto ezilula kakhulu (futhi mahhala) uhlelo lukaMaxim Novikov I-Ribbon XML Editor.

I-algorithm yokusebenza nayo imi kanje:

  1. Vala wonke amawindi e-Excel ukuze kungabikho ukungqubuzana kwefayela lapho sihlela ikhodi ye-XML yokungeza.
  2. Yethula uhlelo lwe-Ribbon XML Editor futhi uvule ifayela lethu le-MyExcelAddin.xlam kulo
  3. Ngenkinobho amathebhu ekhoneni eliphezulu kwesokunxele, engeza amazwibela ekhodi wethebhu entsha:
  4. Udinga ukufaka izingcaphuno ezingenalutho id ithebhu yethu neqembu (noma yiziphi izihlonzi ezihlukile), kanye ne- ilebula - amagama wethebhu yethu neqembu lezinkinobho ezikulo:
  5. Ngenkinobho inkinobho kuphaneli engakwesokunxele, engeza ikhodi engenalutho yenkinobho bese wengeza omaka kuyo:

    - ilebula umbhalo osenkinobho

    - isithombeMso — leli igama elinemibandela lesithombe esisenkinobho. Ngisebenzise isithonjana senkinobho ebomvu ebizwa nge-AnimationCustomAddExitDialog. Amagama azo zonke izinkinobho ezitholakalayo (futhi kunamakhulu amaningana awo!) angatholakala enanini elikhulu lamasayithi ku-inthanethi uma usesha amagama angukhiye “imageMso”. Okokuqala, ungaya lapha.

    - onAction - leli igama lenqubo yokuphinda ushayele emuva - i-macro emfushane ekhethekile ezosebenzisa i-macro yethu eyinhloko AmafomulaToValues. Ungashayela le nqubo noma yini oyithandayo. Sizokwengeza kamuva.

  6. Ungahlola ukulunga kwakho konke okwenziwa usebenzisa inkinobho enophawu oluluhlaza olungaphezulu kwebha yamathuluzi. Endaweni efanayo, chofoza inkinobho ene-floppy disk ukuze ulondoloze zonke izinguquko.
  7. Vala i-Ribbon XML Editor
  8. Vula i-Excel, hamba kumhleli we-Visual Basic bese wengeza inqubo yokubuyisela emuva kumakhro wethu KillFormulasukuze isebenzise i-macro yethu eyinhloko yokushintsha amafomula ngamavelu.
  9. Sigcina izinguquko futhi, sibuyela ku-Excel, hlola umphumela:

Yilokho kuphela - isengezo sesilungele ukusetshenziswa. Igcwalise ngezinqubo zakho nemisebenzi, engeza izinkinobho ezinhle - futhi kuzoba lula kakhulu ukusebenzisa ama-macros emsebenzini wakho.

  • Ayini ama-macros, ungawasebenzisa kanjani emsebenzini wakho, lapho ungathola khona ikhodi ye-macro ku-Visual Basic.
  • Ungasenza kanjani isikrini se-splash lapho uvula incwadi yokusebenzela ku-Excel
  • Iyini i-Personal Macro Book nokuthi isetshenziswa kanjani

shiya impendulo