Maitiro ekugadzira yako yekuwedzera-mukati yeMicrosoft Excel

Kunyangwe iwe usingazive kuronga, kune nzvimbo dzakawanda (mabhuku, mawebhusaiti, maforamu) kwaunogona kuwana yakagadzirira-yakagadzirwa VBA macro kodhi yenhamba hombe yeakajairwa mabasa muExcel. Mune ruzivo rwangu, vashandisi vazhinji munguva pfupi vanotora yavo yekuunganidza macros kuti iite otomatiki maitiro, kungave kuri kushandura mafomula kuita kukosha, kuratidza masamu mumashoko, kana kupfupisa masero neruvara. Uye pano dambudziko rinomuka - iyo macro kodhi muVisual Basic inoda kuchengetwa pane imwe nzvimbo kuitira kuti ishandiswe gare gare mubasa.

Iyo iri nyore sarudzo ndeye kuchengetedza iyo macro kodhi zvakananga mufaira rekushanda nekuenda kuVisual Basic mupepeti uchishandisa iyo keyboard nzira. alt+F11 uye nekuwedzera nyowani isina chinhu module kuburikidza nemenyu Pinza – Module:

Zvisinei, pane zvipingamupinyi zvakawanda nenzira iyi:

  • Kana paine akawanda ekushanda mafaera, uye macro inodiwa kwese kwese, senge macro yekushandura mafomula kuita hunhu, ipapo uchafanirwa kukopa iyo kodhi. mubhuku rimwe nerimwe.
  • Hatifaniri kukanganwa chengetedza faira mune macro-enabled fomati (xlsm) kana mumabhinari bhuku fomati (xlsb).
  • Pakuvhura faira rakadaro macro protection nguva imwe neimwe ichapa yambiro inoda kubvumwa (zvakanaka, kana kudzima kudzivirira zvachose, izvo zvingave zvisingadiwi nguva dzose).

Imwe sarudzo yakanakisa ingave yekugadzira yako yekuwedzera-mukati (Excel Wedzera-in) - faira rakaparadzana remhando yakakosha (xlam) ine ese ako "aunofarira" macros. Zvakanakira nzira iyi:

  • Zvinenge zvakwana batanidza add-on kamwe chete muExcel - uye unogona kushandisa maitiro ayo eVBA uye mabasa mune chero faira pane iyi komputa. Kuchengetedza mafaera ako ekushanda mu xlsm- uye xlsb-mafomati, saka, hazvidiwe, nekuti. iyo kodhi kodhi haizochengetwe mavari, asi mune yekuwedzera-in faira.
  • Protection iwe hauzonetswa nema macros, kana. ma-add-ons ari, netsanangudzo, anovimbwa masosi.
  • Anogona kuita zvakasiyana tab paExcel ribhoni ine mabhatani akanaka ekumhanyisa maadd-in macros.
  • Iyo yekuwedzera-iyi faira rakasiyana. Yake nyore kutakura kubva pakombuta kuenda pakombuta, igovane nevamwe kana kutotengesa 😉

Ngatifambei mukati memaitiro ese ekugadzira yako Microsoft Excel yekuwedzera-mukati nhanho nhanho.

Danho 1. Gadzira faira rekuwedzera

Vhura Microsoft Excel nebhuku rebasa risina chinhu uye richengetedze pasi pezita rakakodzera (semuenzaniso MyExcelAddin) mune yekuwedzera-mufomati ine murairo Faira - Save As kana makiyi F12, ichitsanangura rudzi rwefaira Excel Wedzera-in:

Ndokumbira utarise kuti nekusarudzika Excel zvitoro zvinowedzera muC:UsersYour_nameAppDataRoamingMicrosoftAddIns folda, asi, mumusimboti, unogona kutsanangura chero imwe folda yakakunakira iwe.

Danho 2. Isu tinobatanidza iyo yakagadzirwa-add-in

Iye zvino iyo yekuwedzera isu takagadzira mudanho rekupedzisira MyExcelAddin inofanira kunge yakabatana neExcel. Kuti uite izvi, enda kumenyu Faira - Sarudzo - Mawedzero (Faira - Sarudzo - Wedzera-Ins), tinya bhatani About (Enda) pazasi pehwindo. Pahwindo rinovhurwa, tinya bhatani ongorora (Bhurawuza) uye tsanangura nzvimbo yedu yekuwedzera-in faira.

Kana iwe wakaita zvese nemazvo, saka yedu MyExcelAddin inofanira kuoneka mune runyorwa rweanowanikwa ekuwedzera:

Danho 3. Wedzera macros kune-add-in

Yedu yekuwedzera-yakabatana neExcel uye inoshanda zvinobudirira, asi hapana kana imwe macro mairi parizvino. Ngatizadze. Kuti uite izvi, vhura iyo Visual Basic edhita neiyo keyboard shortcut alt+F11 kana nebhatani Visual Basic Tab yokuvaka (Mugadziri). Kana ma tabo yokuvaka isingaonekwe, inogona kuratidzwa kuburikidza Faira - Sarudzo - Ribhoni Setup (Faira - Sarudzo - Gadzirisa Ribhoni).

Panofanira kuva nehwindo mukona yepamusoro kuruboshwe rwemupepeti purojekiti (kana isingaonekwe, wobva wabatidza kuburikidza nemenyu Tarisa - Project Explorer):

Iri hwindo rinoratidza ese akavhurika mabhuku ebasa uye rinomhanyisa Microsoft Excel yekuwedzera, kusanganisira yedu. VBAProject (MyExcelAddin.xlam) Sarudza iyo negonzo uye wedzera module nyowani kwairi kuburikidza nemenyu Pinza – Module. Mune ino module, isu tichachengeta iyo VBA kodhi yeadd-in macros edu.

Iwe unogona kana kunyora kodhi kubva kutanga (kana iwe uchiziva kuronga), kana kuikopa kubva kune imwe nzvimbo yakagadzirira-yakagadzirwa (iyo iri nyore kwazvo). Ngatitorei, yekuyedza, isa kodhi yeyakareruka asi inobatsira macro mune yakawedzera isina chinhu module:

Mushure mekupinda iyo kodhi, usakanganwa kudzvanya bhatani rekuchengetedza (diskette) mukona yekumusoro kuruboshwe.

Yedu macro FormulasToValues, sezvaunogona kufungidzira zviri nyore, inoshandura mafomula kune kukosha mune yakafanosarudzwa renji. Dzimwe nguva macros aya anonziwo mitemo. Kuti uiite, unofanirwa kusarudza maseru ane mafomula uye wovhura yakakosha dialog box macro kubva kune tab yokuvaka (Mugadziri - Macros) kana nzira yekudimbudzira keyboard alt+F8. Kazhinji, hwindo iri rinoratidza macros anowanikwa kubva kumabhuku ese akavhurika ebasa, asi ekuwedzera-mukati macros haaonekwe pano. Pasinei neizvi, tinogona kuisa zita remaitiro edu mumunda macro name (Macro zita)wobva wadzvanya bhatani manya (mhanya) - uye macro yedu ichashanda:

    

Pano iwe unogona zvakare kugovera nzira yekudimbudzira keyboard kuti ikurumidze kuvhura macro - bhatani rine chekuita neizvi Parameters (Sarudzo) mufafitera rakapfuura Macro:

Paunenge uchigovera makiyi, ramba uchifunga kuti iwo ane hunyanzvi uye dhizaini dhizaini. Saka kana iwe ukagovera musanganiswa wakafanana Ctrl+Й, then, in fact, in the future you will have to make sure that you have the layout turned on and press additionally kusudurukakuwana vara guru.

Kuti zvive nyore, isu tinogona zvakare kuwedzera bhatani remacro yedu kune yekukurumidza yekuwana toolbar mukona yekumusoro kuruboshwe kwehwindo. Kuti uite izvi, sarudza Faira - Sarudzo - Kurumidza Kupinda Toolbar (Faira - Sarudzo - Gadzirisa Kurumidza Kupinda Toolbar), uyezve mune yekudonha-pasi runyorwa kumusoro kwehwindo sarudzo macro. Mushure meizvozvo macro yedu FormulasToValues inogona kuiswa pane panel nebhatani wedzera (Wedzera) uye sarudza chiratidzo chayo nebhatani chinja (Hodha):

Danho 4. Wedzera mabasa kune-add-in

asi macro-procedures, variko zvakare basa macros kana sokudanwa kwavanoitwa UDF (Mushandisi Anotsanangurwa Basa = mushandisi anotsanangurwa basa). Ngatigadzirei module yakaparadzana mune yedu yekuwedzera (menu command Pinza – Module) uye unamate kodhi yeinotevera basa ipapo:

Zviri nyore kuona kuti basa iri rinodiwa kubvisa VAT kubva muhuwandu hunosanganisira VAT. Kwete Binomial yaNewton, hongu, asi ichatiitira semuenzaniso kuratidza misimboti yekutanga.

Ziva kuti syntax yebasa rakasiyana nemaitiro:

  • kugadzirwa kunoshandiswa Basa…. End Function panzvimbo Sub… End Sub
  • mushure mezita rebasa, nharo dzayo dzinoratidzwa mumabhuraketi
  • mumutumbi webasa racho, maverengero anodiwa anoitwa uyezve mhedzisiro yacho inopihwa kune inoshanduka ine zita rebasa.

Ziva zvakare kuti basa iri haridiwe, uye hazvigoneke kumhanya senge yapfuura macro maitiro kuburikidza nebhokisi rekutaura macro uye bhatani manya. Iro macro function inofanirwa kushandiswa seyakajairwa pepa rebasa (SUM, IF, VLOOKUP…), kureva kungopinda mune chero sero, uchitsanangura kukosha kwehuwandu neVAT senharo:

... kana kupinda kuburikidza neyakajairwa dialog box yekuisa basa (bhatani fx mune fomula bar), uchisarudza chikamu Mushandisi Anotsanangurwa (Mushandisi Anotsanangurwa):

Nguva chete isingafadzi pano kusavapo kwekutsanangurwa kwakajairwa kwebasa iri pasi pehwindo. Kuti uwedzere iwe unofanirwa kuita zvinotevera:

  1. Vhura iyo Visual Basic Mharidzo ine keyboard yekudimbudzira alt+F11
  2. Sarudza iyo yekuwedzera muPaneru yeProjekti uye tinya kiyi F2kuvhura hwindo reObject Browser
  3. Sarudza yako yekuwedzera-mupurojekiti kubva pane yekudonha-pasi runyorwa kumusoro kwehwindo
  4. Tinya-kurudyi pane basa rinoonekwa uye sarudza kuraira Properties.
  5. Isa tsananguro yebasa iri pahwindo tsananguro
  6. Sevha faira rekuwedzera uye tangazve excel.

Mushure mekutangazve, basa rinofanira kuratidza tsananguro yatakaisa:

Nhanho 5. Gadzira tabhu yekuwedzera-mune interface

Yekupedzisira, kunyangwe isiri yekumanikidza, asi inonakidza kubata kuchave kusikwa kwetabhu yakaparadzana ine bhatani rekumhanyisa macro yedu, inozoonekwa muExcel interface mushure mekubatanidza yedu yekuwedzera.

Ruzivo rwematabo anoratidzwa nekusarudzika ari mukati mebhuku uye anofanirwa kuumbwa mune yakakosha XML kodhi. Nzira iri nyore yekunyora uye kugadzirisa kodhi yakadai ndeyerubatsiro rwezvirongwa zvakakosha - XML ​​vagadziri. Imwe yeakanyanya nyore (uye yemahara) chirongwa chaMaxim Novikov Ribhoni XML Mupepeti.

Iyo algorithm yekushanda nayo ndeyotevera:

  1. Vhara ese maExcel windows kuitira kuti pasave nekupokana kwefaira patinogadzirisa iyo yekuwedzera-muXML kodhi.
  2. Tangisa chirongwa cheRibhoni XML Edhinda wovhura faira redu reMyExcelAddin.xlam mariri
  3. Nebhatani Tabs mukona yekumusoro kuruboshwe, wedzera kodhi snippet yetabhu nyowani:
  4. Iwe unofanirwa kuisa mazwi asina chinhu id yedu tab neboka (chero zviziviso zvakasiyana), uye mukati chitaridzi -mazita etabhu yedu uye neboka remabhatani pairi:
  5. Nebhatani bhatani papaneru yekuruboshwe, wedzera kodhi isina chinhu yebhatani uye wedzera ma tag kwairi:

    - label ndiwo mashoko ari pabhatani

    - mufananidzoMso -Iri ndiro zita rine chirevo chemufananidzo uri pabhatani. Ndakashandisa dzvuku bhatani icon inonzi AnimationCustomAddExitDialog. Mazita emabhatani ese anowanikwa (uye kune mazana emazana awo!) Inogona kuwanikwa pane nhamba huru yemasaiti paInternet kana iwe ukatsvaga mazwi makuru "imageMso". Kutanga, unogona kuenda pano.

    - onAction -Iri ndiro zita rekuita callback - yakakosha macro macro inomhanyisa macro yedu huru FormulasToValues. Unogona kudaidza iyi nzira chero yaunoda. Tichawedzera zvishoma gare gare.

  6. Unogona kutarisa kurongeka kwezvese zvakaitwa uchishandisa bhatani rine cheki chegirini chiri pamusoro petururira. Munzvimbo imwe chete, tinya bhatani rine floppy disk kuchengetedza shanduko dzese.
  7. Vhara iyo Ribhoni XML Mharidzo
  8. Vhura Excel, enda kuVisual Basic edhita uye wedzera maitiro ekudzokera kumashure kune yedu macro KillFormulaskuitira kuti inomhanya yedu huru macro yekutsiva mafomula nehunhu.
  9. Isu tinochengetedza shanduko uye, kudzokera kuExcel, tarisa mhedzisiro:

Ndizvo chete - iyo yekuwedzera yakagadzirira kushandiswa. Izadze nemaitiro ako uye mabasa, wedzera mabhatani akanaka - uye zvichava nyore kushandisa macros mubasa rako.

  • Chii macros, maitiro ekuashandisa mubasa rako, kupi kwekuwana macro kodhi muVisual Basic.
  • Maitiro ekugadzira splash skrini kana uchivhura bhuku rebasa muExcel
  • Chii chinonzi Personal Macro Book uye nzira yekuishandisa

Leave a Reply