Momwe mungapangire zowonjezera zanu za Microsoft Excel

Ngakhale simukudziwa kukonza, pali malo ambiri (mabuku, mawebusayiti, mabwalo) komwe mungapezeko ma VBA macro code okonzeka amitundu yambiri yantchito mu Excel. M'chidziwitso changa, ogwiritsa ntchito ambiri posakhalitsa amasonkhanitsa ma macros awo kuti asinthe machitidwe achizolowezi, kaya ndikumasulira mafomu kukhala ofunika, kusonyeza kuchuluka m'mawu, kapena kusonkhanitsa maselo ndi mtundu. Ndipo apa pali vuto - macro code mu Visual Basic iyenera kusungidwa kwinakwake kuti idzagwiritsidwe ntchito pambuyo pake.

Njira yosavuta ndiyo kusunga ma macro code mwachindunji mufayilo yogwira ntchito popita ku Visual Basic mkonzi pogwiritsa ntchito njira yachidule ya kiyibodi. alt+F11 ndikuwonjezera gawo latsopano lopanda kanthu kudzera pa menyu Ikani - Module:

Komabe, pali zovuta zingapo ndi njira iyi:

  • Ngati pali mafayilo ambiri omwe akugwira ntchito, ndipo ma macro amafunikira kulikonse, monga macro kuti asinthe mafomu kukhala mfundo, ndiye kuti muyenera kukopera kachidindo. m'buku lililonse.
  • Siyenera kuyiwalika sungani fayilo mumtundu wothandizidwa ndi macro (xlsm) kapena m'mabuku a binary (xlsb).
  • Mukatsegula fayilo yotere chitetezo chachikulu nthawi iliyonse idzapereka chenjezo lomwe liyenera kuvomerezedwa (chabwino, kapena kuletsa chitetezo kwathunthu, zomwe sizingakhale zofunika nthawi zonse).

Yankho lokongola kwambiri lingakhale kupanga chowonjezera chanu (chowonjezera cha Excel) - fayilo ina yamtundu wapadera (xlam) wokhala ndi macros anu onse "okonda". Ubwino wa njirayi:

  • Zidzakhala zokwanira gwirizanitsani zowonjezera kamodzi mu Excel - ndipo mutha kugwiritsa ntchito njira ndi machitidwe ake a VBA mufayilo iliyonse pakompyuta iyi. Kusunganso mafayilo anu ogwirira ntchito mu xlsm- ndi xlsb-formats, motero, sikofunikira, chifukwa. code source sichidzasungidwa mwa iwo, koma mu fayilo yowonjezera.
  • Protection simudzavutitsidwa ndi macros, mwina. zowonjezera ndi, mwa tanthawuzo, magwero odalirika.
  • Ndikhoza kuchita osiyana tabu pa riboni ya Excel yokhala ndi mabatani abwino kuti muthamangitse ma macros owonjezera.
  • Chowonjezera ndi fayilo yosiyana. Ake zosavuta kunyamula kuchokera pa kompyuta kupita pa kompyuta, gawani ndi anzanu kapena mugulitse 😉

Tiyeni tidutse njira yonse yopangira zowonjezera zanu za Microsoft Excel pang'onopang'ono.

Gawo 1. Pangani fayilo yowonjezera

Tsegulani Microsoft Excel ndi bukhu lopanda kanthu ndikulisunga pansi pa dzina lililonse loyenera (mwachitsanzo MyExcelAddin) mumtundu wowonjezera ndi lamulo Fayilo - Sungani Monga kapena makiyi F12, kufotokoza mtundu wa fayilo Zowonjezera za Excel:

Chonde dziwani kuti mwachisawawa Excel imasunga zowonjezera mufoda ya C:UsersYour_nameAppDataRoamingMicrosoftAddIns, koma, kwenikweni, mutha kutchula foda ina iliyonse yomwe ili yabwino kwa inu.

Gawo 2. Timagwirizanitsa chowonjezera chopangidwa

Tsopano chowonjezera chomwe tidapanga mu gawo lomaliza MyExcelAddin iyenera kulumikizidwa ndi Excel. Kuti muchite izi, pitani ku menyu Fayilo - Zosankha - Zowonjezera (Fayilo - Zosankha - Zowonjezera), dinani batani About (Pita) pansi pawindo. Pazenera lomwe limatsegulidwa, dinani batani Review (Sakatulani) ndikufotokozerani komwe kuli fayilo yathu yowonjezera.

Ngati mwachita zonse bwino, ndiye athu MyExcelAddin ziyenera kuwonekera pamndandanda wazowonjezera zomwe zilipo:

Khwerero 3. Onjezerani ma macros ku zowonjezera

Zowonjezera zathu zimalumikizidwa ndi Excel ndipo zimagwira ntchito bwino, koma palibe macro imodzi yokha. Tiyeni tidzaze. Kuti muchite izi, tsegulani Visual Basic mkonzi ndi njira yachidule ya kiyibodi alt+F11 kapena pa batani Zooneka Basic tsamba Woyambitsa (Wolemba Mapulogalamu). Ngati ma tabo Woyambitsa zosawoneka, zitha kuwonetsedwa kudzera Fayilo - Zosankha - Kukhazikitsa Riboni (Fayilo - Zosankha - Sinthani Riboni).

Payenera kukhala zenera kumtunda kumanzere ngodya ya mkonzi Project (ngati sichikuwoneka, tsegulani kudzera pa menyu Onani - Project Explorer):

Zenerali likuwonetsa mabuku onse otseguka ndikugwiritsa ntchito zowonjezera za Microsoft Excel, kuphatikiza zathu. VBAProject (MyExcelAddin.xlam) Sankhani ndi mbewa ndi kuwonjezera gawo latsopano kwa izo kudzera menyu Ikani - Module. Mu gawoli, tisunga khodi ya VBA ya ma macros athu owonjezera.

Mutha kuyika kachidindo kuyambira poyambira (ngati mukudziwa kuyitanitsa), kapena kukopera kuchokera kwinakwake kokonzekera (komwe kuli kosavuta). Tiyeni, poyesa, tilowetse nambala ya macro yosavuta koma yothandiza mu gawo lopanda kanthu lowonjezera:

Mukalowetsa kachidindo, musaiwale kudina batani losunga (diskete) pakona yakumanzere yakumanzere.

Macro athu Ma formulasToValues, monga momwe mungaganizire mosavuta, amasintha mafomula kukhala ma values ​​omwe adasankhidwa kale. Nthawi zina macros awa amatchedwanso Njira. Kuti muyendetse, muyenera kusankha ma cell omwe ali ndi mafomu ndikutsegula bokosi lapadera Macros kuchokera ku tabu Woyambitsa (Wopanga - Macros) kapena njira yachidule ya kiyibodi alt+F8. Nthawi zambiri, zenerali likuwonetsa ma macros omwe akupezeka m'mabuku onse otseguka, koma ma macro owonjezera sakuwoneka pano. Ngakhale izi, tikhoza kulowa dzina la ndondomeko yathu m'munda macro dzina (Macro dzina)ndiyeno dinani batani Thamangani (thamanga) - ndipo macro athu adzagwira ntchito:

    

Apa mutha kugawanso njira yachidule ya kiyibodi kuti mutsegule macro mwachangu - batani ndi lomwe limayambitsa izi magawo (Zosankha) pawindo lapitalo Macro:

Mukamapereka makiyi, dziwani kuti ndizovuta kwambiri komanso mawonekedwe a kiyibodi. Ndiye ngati mupereka kuphatikiza ngati Ctrl+Й, ndiye, m'tsogolomu mudzayenera kuwonetsetsa kuti mwatsegula ndikusindikizanso kosangalatsakuti atenge chilembo chachikulu.

Kuti zitheke, titha kuwonjezeranso batani la macro athu pazida zofikira mwachangu pakona yakumanzere kwazenera. Kuti muchite izi, sankhani Fayilo - Zosankha - Chida Chofikira Mwamsanga (Fayilo - Zosankha - Sinthani Mwamakonda Anu Chida Chofikira Mwamsanga), ndiyeno pamndandanda wotsitsa pamwamba pa zenera njira Macros. Pambuyo pake macro athu Ma formulasToValues akhoza kuikidwa pa gulu ndi batani kuwonjezera (Onjezani) ndikusankha chithunzi chake ndi batani Change (Sinthani):

Gawo 4. Add ntchito kwa kuwonjezera-mu

koma njira zazikulu, ziliponso ntchito macros kapena monga amatchedwa UDF (Ntchito Yofotokozedwa ndi Wogwiritsa = ntchito yofotokozedwa ndi wogwiritsa). Tiyeni tipange gawo lapadera pazowonjezera zathu (menu command Ikani - Module) ndikuimitsa kachidindo kotsatirako:

Ndizosavuta kuwona kuti ntchitoyi ikufunika kuti muchotse VAT pamtengowo kuphatikiza VAT. Osati Binomial ya Newton, ndithudi, koma idzatichitira monga chitsanzo kusonyeza mfundo zoyambirira.

Dziwani kuti kalembedwe ka ntchito ndi kosiyana ndi kachitidwe:

  • zomangamanga zimagwiritsidwa ntchito Ntchito…. Ntchito Yomaliza m'malo mwake Sub ... Mapeto gawo
  • pambuyo pa dzina la ntchitoyo, mfundo zake zimawonetsedwa m'mabulaketi
  • m'thupi la ntchitoyo, kuwerengera kofunikira kumachitika ndipo zotsatira zake zimaperekedwa ku kusintha komwe kuli ndi dzina la ntchitoyo.

Komanso dziwani kuti ntchitoyi siyofunika, ndipo ndizosatheka kuthamanga ngati njira yachikale ya macro kudzera mu bokosi la zokambirana Macros ndi batani Thamangani. Ntchito yayikulu yotereyi iyenera kugwiritsidwa ntchito ngati ntchito yokhazikika yamasamba (SUM, IF, VLOOKUP…), mwachitsanzo, ingolowetsani mu selo iliyonse, kufotokoza mtengo wa ndalamazo ndi VAT monga mtsutso:

… fx mu formula bar), kusankha gulu Kumasulira Womasulira (Kutanthauzira Wogwiritsa):

Mphindi yokha yosasangalatsa apa ndi kusowa kwa kufotokozera mwachizolowezi ntchito pansi pawindo. Kuti muwonjezere, muyenera kuchita izi:

  1. Tsegulani Visual Basic Editor ndi njira yachidule ya kiyibodi alt+F11
  2. Sankhani chowonjezera mu Project gulu ndi akanikizire kiyi F2kuti mutsegule zenera la Object Browser
  3. Sankhani pulojekiti yanu yowonjezera kuchokera pamndandanda wotsitsa pamwamba pa zenera
  4. Dinani kumanja pa ntchito yomwe ikuwoneka ndikusankha lamulo Zida.
  5. Lowetsani kufotokozera za ntchitoyi pawindo Kufotokozera
  6. Sungani fayilo yowonjezera ndi kuyambitsanso Excel.

Pambuyo poyambiranso, ntchitoyi iyenera kuwonetsa kufotokozera komwe tidalowa:

Gawo 5. Pangani kuwonjezera-pa tabu mu mawonekedwe

Chomaliza, ngakhale sichiyenera, koma kukhudza kosangalatsa kudzakhala kupangidwa kwa tabu yosiyana ndi batani loyendetsa macro athu, omwe adzawonekere mu mawonekedwe a Excel pambuyo polumikiza zowonjezera zathu.

Zambiri zokhudzana ndi ma tabo omwe amawonetsedwa mwachisawawa zili m'bukuli ndipo ziyenera kusinthidwa mu code yapadera ya XML. Njira yosavuta yolembera ndikusintha kachidindo kotere ndi thandizo la mapulogalamu apadera - osintha a XML. Chimodzi mwazosavuta (komanso chaulere) ndi pulogalamu ya Maxim Novikov Riboni XML Editor.

Ma algorithm ogwiritsira ntchito ndi awa:

  1. Tsekani mazenera onse a Excel kuti pasakhale kusamvana kwamafayilo tikasintha kachidindo ka XML.
  2. Yambitsani pulogalamu ya Ribbon XML Editor ndi kutsegula fayilo yathu ya MyExcelAddin.xlam mmenemo
  3. Ndi batani totsegulira pakona yakumanzere, yonjezerani kachidutswa kakang'ono ka tabu yatsopano:
  4. Muyenera kuyika mawu opanda kanthu id tabu yathu ndi gulu (zizindikiritso zilizonse zapadera), ndi in chizindikiro - mayina a tabu yathu ndi gulu la mabatani omwe ali pamenepo:
  5. Ndi batani batani kumanzere, onjezani nambala yopanda kanthu pa batani ndikuwonjezera ma tag kwa iyo:

    - chizindikiro ndi mawu pa batani

    - imageMso - ili ndi dzina lokhazikika la chithunzi chomwe chili pa batani. Ndinagwiritsa ntchito batani lofiira lotchedwa AnimationCustomAddExitDialog. Mayina a mabatani onse omwe alipo (ndipo pali mazana angapo a iwo!) Angapezeke pamasamba ambiri pa intaneti ngati mufufuza mawu ofunika "imageMso". Poyamba, mukhoza kupita apa.

    - paAction - ili ndi dzina la njira yoyimbira foni - macro apadera apadera omwe angayendetse ma macro athu akulu Ma formulasToValues. Mutha kuyitcha njirayi chilichonse chomwe mungafune. Tiziwonjezera pambuyo pake.

  6. Mutha kuwona kulondola kwa chilichonse chomwe chachitika pogwiritsa ntchito batani lokhala ndi cholembera chobiriwira pamwamba pazida. Pamalo omwewo, dinani batani ndi floppy disk kuti musunge zosintha zonse.
  7. Tsekani Riboni XML Editor
  8. Tsegulani Excel, pitani ku Visual Basic mkonzi ndikuwonjezera njira yobwereza ku macro athu KillFormulaskotero kuti imayendetsa ma macro athu akulu m'malo mwa ma formula ndi ma values.
  9. Timasunga zosinthazo, ndikubwerera ku Excel, onani zotsatira zake:

Ndizo zonse - chowonjezera chakonzeka kugwiritsidwa ntchito. Lembani ndi machitidwe anu ndi ntchito zanu, onjezani mabatani okongola - ndipo zidzakhala zosavuta kugwiritsa ntchito macros pa ntchito yanu.

  • Kodi macro ndi chiyani, momwe mungagwiritsire ntchito pantchito yanu, komwe mungapeze ma macro code mu Visual Basic.
  • Momwe mungapangire skrini ya splash mukatsegula buku lantchito mu Excel
  • Kodi Personal Macro Book ndi momwe mungagwiritsire ntchito

Siyani Mumakonda