Contents
Ko da ba ku san yadda ake tsarawa ba, akwai wurare da yawa (littattafai, gidajen yanar gizo, tarurruka) inda zaku iya samun shirye-shiryen VBA macro code don adadi mai yawa na ayyuka na yau da kullun a cikin Excel. A cikin gwaninta, yawancin masu amfani ba dade ko ba dade suna tattara tarin macro na kansu don sarrafa ayyukan yau da kullun, ko fassarar ƙididdiga zuwa ƙima, nuna jimla a cikin kalmomi, ko tara sel ta launi. Kuma a nan matsalar ta taso - lambar macro a cikin Visual Basic yana buƙatar adana wani wuri don amfani da shi daga baya a cikin aiki.
Zaɓin mafi sauƙi shine adana lambar macro kai tsaye a cikin fayil ɗin aiki ta zuwa ga Editan Kayayyakin Kayayyakin gani ta amfani da gajeriyar hanyar keyboard. alt+F11 da ƙara sabon tsarin komai ta hanyar menu Saka - Module:
Koyaya, akwai rashin amfani da yawa tare da wannan hanyar:
- Idan akwai fayilolin aiki da yawa, kuma ana buƙatar macro a ko'ina, kamar macro don canza ƙididdiga zuwa ƙima, to dole ne ku kwafi lambar. a kowane littafi.
- Kada a manta Ajiye fayil a tsarin macro-enabled (xlsm) ko a tsarin littafin binary (xlsb).
- Lokacin buɗe irin wannan fayil ɗin macro kariya kowane lokaci zai ba da gargaɗin da ke buƙatar yarda (da kyau, ko kuma kashe kariya gaba ɗaya, wanda ƙila ba koyaushe ake so ba).
Mafi kyawun bayani shine ƙirƙirar add-in ku (Excel Add-in) - fayil daban na tsari na musamman (xlam) wanda ya ƙunshi duk macros "fi so". Amfanin wannan hanyar:
- Zai isa haɗa add-on sau ɗaya a cikin Excel - kuma zaka iya amfani da hanyoyin VBA da ayyuka a kowane fayil akan wannan kwamfutar. Sake adana fayilolin aiki a cikin xlsm- da xlsb-tsara, don haka, ba a buƙata ba, saboda. Ba za a adana lambar tushe a cikinsu ba, amma a cikin fayil ɗin ƙarawa.
- kariya Ba za ku damu da macros ba, ma. add-ons sune, ta ma'ana, amintattun tushe.
- Iya yi daban tab a kan ribbon na Excel tare da maɓalli masu kyau don gudanar da ƙara-in macros.
- Add-in fayil daban ne. Nasa mai sauƙin ɗauka daga kwamfuta zuwa kwamfuta, a raba ta ga abokan aiki ko ma sayar da ita 😉
Bari mu yi tafiya cikin dukkan tsarin ƙirƙirar ƙarawar Microsoft Excel naku mataki-mataki.
Mataki 1. Ƙirƙiri fayil ɗin ƙarawa
Bude Microsoft Excel tare da littafin aiki mara komai kuma adana shi a ƙarƙashin kowane suna mai dacewa (misali MyExcelAddin) a cikin tsarin ƙarawa tare da umarni Fayil - Ajiye azaman ko makullai F12, ƙayyade nau'in fayil ɗin Excel Add-in:
Da fatan za a lura cewa ta hanyar tsoho na Excel yana adana add-ins a cikin babban fayil ɗin C: UsersYour_nameAppDataRoamingMicrosoftAddIns, amma, bisa ƙa'ida, zaku iya saka kowane babban fayil ɗin da ya dace da ku.
Mataki 2. Mun haɗu da ƙara-in da aka ƙirƙira
Yanzu add-in da muka ƙirƙira a mataki na ƙarshe MyExcelAddin dole ne a haɗa zuwa Excel. Don yin wannan, je zuwa menu Fayil - Zaɓuɓɓuka - Ƙara-kan (Fayil - Zaɓuɓɓuka - Ƙara-Ins), danna maballin Game da (Tafi) a gindin taga. A cikin taga da ya buɗe, danna maɓallin review (Bincike) kuma saka wurin fayil ɗin add-in mu.
Idan kun yi komai daidai, to namu MyExcelAddin yakamata ya bayyana a cikin jerin abubuwan da ake samu:
Mataki 3. Ƙara macros zuwa add-in
Add-in mu yana da alaƙa da Excel kuma yana aiki cikin nasara, amma babu macro ɗaya a ciki tukuna. Mu cika shi. Don yin wannan, buɗe editan Visual Basic tare da gajeriyar hanyar madannai alt+F11 ko kuma ta hanyar maballin Kayayyakin aikin Basic tab developer (Mai haɓakawa). Idan tabs developer ba a bayyane ba, ana iya nunawa ta hanyar Fayil - Zaɓuɓɓuka - Saitin Ribbon (Fayil - Zaɓuɓɓuka - Keɓance Ribbon).
Ya kamata a sami taga a saman kusurwar hagu na editan Project (idan ba a gani ba, to kunna shi ta hanyar menu Duba - Project Explorer):
Wannan taga yana nuna duk buɗaɗɗen littattafan aiki da abubuwan ƙarawa na Microsoft Excel, gami da namu. VBAProject (MyExcelAddin.xlam) Zaɓi shi tare da linzamin kwamfuta kuma ƙara sabon tsari zuwa gare shi ta menu Saka - Module. A cikin wannan tsarin, za mu adana lambar VBA na macro-in mu.
Kuna iya ko dai rubuta lambar daga karce (idan kun san yadda ake tsarawa), ko kwafe shi daga wani wuri da aka shirya (wanda ya fi sauƙi). Bari mu, don gwaji, shigar da lambar macro mai sauƙi amma mai fa'ida a cikin ƙaramar komai a ciki:
Bayan shigar da lambar, kar a manta da danna maɓallin ajiyewa (diskette) a kusurwar hagu na sama.
Macronmu FormulasToValues, kamar yadda zaku iya tunani cikin sauƙi, yana canza ƙididdiga zuwa ƙima a cikin kewayon da aka zaɓa. Wani lokaci ana kiran waɗannan macro hanyoyin. Don gudanar da shi, kuna buƙatar zaɓar sel masu tsari kuma buɗe akwatin tattaunawa na musamman Macros daga tab developer (Developer - Macros) ko gajeriyar hanyar keyboard alt+F8. A al'ada, wannan taga yana nuna samuwan macros daga duk buɗaɗɗen littattafan aiki, amma ƙara-in macro ba a bayyane a nan. Duk da wannan, za mu iya shigar da sunan tsarin mu a cikin filin sunan macro (Macro name)sa'an nan kuma danna maɓallin Run (gudu) - kuma macronmu zai yi aiki:
| |
Anan zaka iya sanya gajeriyar hanyar madannai don ƙaddamar da macro da sauri - maɓallin shine alhakin wannan Siga (Zaɓuɓɓuka) a baya taga Macro:
Lokacin sanya maɓallai, ka tuna cewa suna da hankali kuma suna da mahimmancin shimfidar madannai. Don haka idan kun sanya haɗin gwiwa kamar Ctrl+Й, to, a zahiri, a nan gaba dole ne ku tabbatar kun kunna shimfidar wuri kuma latsa ƙari Motsidon samun babban harafi.
Don saukakawa, za mu iya ƙara maɓalli don macro ɗin mu zuwa madaidaicin kayan aiki da sauri a kusurwar hagu na sama na taga. Don yin wannan, zaɓi Fayil - Zaɓuɓɓuka - Bar Toolbar Samun Sauri (Fayil - Zaɓuɓɓuka - Ƙirƙirar Kayan aikin Samun Sauri), sannan a cikin jerin abubuwan da aka saukar a saman taga zaɓin Macros. Bayan haka mu macro FormulasToValues za a iya sanya a kan panel tare da button Add (Ƙara) kuma zaɓi gunki don shi tare da maɓallin Change (Edit):
Mataki 4. Ƙara ayyuka zuwa add-in
amma macro-tsari, akwai kuma aiki macros ko kuma kamar yadda ake kiran su UDF (Ƙayyadaddun aikin mai amfani = ƙayyadadden aikin mai amfani). Bari mu ƙirƙiri keɓantaccen tsari a cikin add-on mu (umurnin menu Saka - Module) kuma liƙa lambar aikin mai zuwa a can:
Yana da sauƙi a ga cewa ana buƙatar wannan aikin don cire VAT daga adadin ciki har da VAT. Ba Newton's binomial ba, ba shakka, amma zai yi mana a matsayin misali don nuna ƙa'idodin asali.
Lura cewa haɗin haɗin aikin ya bambanta da hanya:
- ana amfani da gini Aiki…. Ƙarshen Aiki maimakon Sub… Ƙarshe Sub
- bayan sunan aikin, ana nuna hujjojinsa a cikin maƙallan
- a cikin jikin aikin, ana yin lissafin da ake buƙata sannan a sanya sakamakon zuwa wani maɓalli mai sunan aikin.
Hakanan lura cewa ba a buƙatar wannan aikin, kuma ba shi yiwuwa a yi aiki kamar tsarin macro na baya ta cikin akwatin maganganu Macros da maballin Run. Irin wannan macro aikin ya kamata a yi amfani da shi azaman daidaitaccen aikin takardar aiki (SUM, IF, VLOOKUP…), watau kawai shigar da kowane tantanin halitta, yana ƙayyade ƙimar adadin tare da VAT azaman hujja:
… ko shigar da ta daidaitattun akwatin maganganu don saka aiki (button fx a cikin dabara bar), zabar wani category Ma'anar Mai amfani (An bayyana mai amfani):
Lokaci mara kyau kawai a nan shine rashin bayanin da aka saba yi na aikin a kasan taga. Don ƙara shi za ku yi masu zuwa:
- Bude Editan Kayayyakin Kaya tare da gajeriyar hanyar madannai alt+F11
- Zaɓi add-in a cikin panel Project kuma danna maɓallin F2don buɗe taga Object Browser
- Zaɓi aikin ƙara ɗinku daga jerin abubuwan da aka saukar a saman taga
- Danna dama akan aikin da ya bayyana kuma zaɓi umarni Properties.
- Shigar da bayanin aikin a cikin taga description
- Ajiye fayil ɗin ƙarawa kuma zata sake farawa Excel.
Bayan sake farawa, aikin yakamata ya nuna bayanin da muka shigar:
Mataki 5. Ƙirƙiri wani add-on tab a cikin dubawa
Na ƙarshe, kodayake ba dole ba ne, amma taɓawa mai daɗi shine ƙirƙirar shafin daban tare da maɓalli don gudanar da macro ɗin mu, wanda zai bayyana a cikin ƙirar Excel bayan haɗa add-in mu.
Bayani game da shafukan da aka nuna ta tsohuwa yana ƙunshe a cikin littafin kuma dole ne a tsara su a cikin lambar XML ta musamman. Hanya mafi sauƙi don rubutawa da gyara irin wannan lambar shine tare da taimakon shirye-shirye na musamman - masu gyara XML. Ɗaya daga cikin mafi dacewa (kuma kyauta) shine shirin Maxim Novikov Ribbon XML Editan.
Algorithm don aiki tare da shi shine kamar haka:
- Rufe duk windows na Excel don kada a sami rikici lokacin da muka gyara lambar XML ta ƙara.
- Kaddamar da shirin Ribbon XML Editan kuma buɗe fayil ɗin MyExcelAddin.xlam a ciki
- Tare da maballin shafuka a kusurwar hagu na sama, ƙara snippet code don sabon shafin:
- Kuna buƙatar shigar da fakitin fanko id shafinmu da rukuninmu (kowane abubuwan ganowa na musamman), da kuma cikin lakabin - sunayen shafin mu da rukunin maɓallai akan sa:
- Tare da maballin button a gefen hagu, ƙara lambar sarari don maballin kuma ƙara tags zuwa gare shi:
- lakabi shine rubutun akan maballin
- imageMso - wannan shine sunan sharadi na hoton akan maballin. Na yi amfani da gunkin maɓalli mai suna AnimationCustomAddExitDialog. Sunayen duk maɓallan da ake da su (kuma akwai ɗaruruwan ɗaruruwan su!) Ana iya samun su akan ɗimbin rukunin rukunin yanar gizo idan kun nemo kalmomin “imageMso”. Don farawa, zaku iya zuwa nan.
- onAction - wannan shine sunan hanyar dawo da kira - gajeriyar macro na musamman wanda zai gudanar da babban macro na mu FormulasToValues. Kuna iya kiran wannan hanya duk abin da kuke so. Za mu ƙara shi kaɗan kaɗan.
- Kuna iya bincika daidaiton duk abin da aka yi ta amfani da maɓallin tare da alamar rajistan koren a saman mashaya kayan aiki. A wuri guda, danna maɓallin tare da faifan floppy don adana duk canje-canje.
- Rufe Editan XML Ribbon
- Bude Excel, je zuwa Editan Kayayyakin Kayayyakin Kayayyakin Kayayyakin Kayayyakin Kayayyakin Kayayyakin Kayayyakin Kayayyakin Kira zuwa macro KillFormulasta yadda zai gudanar da babban macro na mu don maye gurbin dabara da ƙima.
- Muna adana canje-canje kuma, komawa zuwa Excel, duba sakamakon:
Wannan ke nan – add-in yana shirye don amfani. Cika shi tare da hanyoyin ku da ayyukan ku, ƙara kyawawan maɓalli - kuma zai zama mafi sauƙi don amfani da macros a cikin aikinku.
- Menene macros, yadda ake amfani da su a cikin aikinku, inda zaku sami macro code a cikin Visual Basic.
- Yadda ake yin allo lokacin buɗe littafin aiki a Excel
- Menene Littafin Macro na Keɓaɓɓen da yadda ake amfani da shi