Momwe mungagwiritsire ntchito Personal Macro Book

Ngati simunadziwe bwino ma macros mu Excel, ndiye kuti ndimakusilirani pang'ono. Kumva kuti ndinu wamphamvuyonse komanso kuzindikira kuti Microsoft Excel yanu ikhoza kukwezedwa mopanda malire yomwe ingabwere kwa inu mutadziwa macros ndikumverera kosangalatsa.

Komabe, nkhaniyi ndi ya iwo omwe "adaphunzira kale mphamvu" ndipo ayamba kugwiritsa ntchito macros (achilendo kapena olembedwa okha - ziribe kanthu) pa ntchito yawo ya tsiku ndi tsiku.

Macro ndi kachidindo (mizere ingapo) m'chinenero cha Visual Basic chomwe chimapangitsa Excel kuchita zomwe mukufuna: sungani deta, kupanga lipoti, kukopera-kumata matebulo obwerezabwereza, ndi zina zotero. Kupatula apo, komwe ma macro amasungidwa zimatengera komwe angagwire (kapena sangathe) kugwira ntchito.

Если макрос решает небольшую локальную проблему в отдельно взятом файле (monga chitsanzo обрабатывает внесенную проблему в отдельно взятом файле) то логично хранить код внутри этого же файла. Без вопросов.

Ndipo ngati macro ayenera kukhala achilengedwe chonse komanso ofunikira m'buku lililonse la Excel - monga, mwachitsanzo, macro osinthira mafomu kukhala ofunika? Bwanji osatengera code yake ya Visual Basic m'buku lililonse nthawi zonse? Kuphatikiza apo, posachedwa, pafupifupi wogwiritsa ntchito aliyense amafika poganiza kuti zingakhale bwino kuyika ma macros onse mubokosi limodzi, mwachitsanzo, kukhala nawo pafupi nthawi zonse. Ndipo mwina ngakhale kuthamanga osati pamanja, koma ndi njira zazifupi za kiyibodi? Apa ndipamene Personal Macro Workbook ikhoza kukhala yothandiza kwambiri.

Momwe mungapangire Personal Macro Book

Pamenepo, Personal Book of Macros (LMB) ndi fayilo yanthawi zonse ya Excel mumtundu wamabuku a binary (Personal.xlsb), yomwe imatsegulidwa yokha mumayendedwe obisika nthawi yomweyo monga Microsoft Excel. Iwo. mukangoyamba Excel kapena kutsegula fayilo iliyonse kuchokera ku disk, mafayilo awiri amatsegulidwa kwenikweni - anu ndi Personal.xlsb, koma sitikuwona yachiwiri. Chifukwa chake, ma macros onse omwe amasungidwa mu LMB amapezeka kuti ayambitsidwe nthawi iliyonse pomwe Excel imatsegulidwa.

Ngati simunagwiritsepo ntchito LMB, ndiye kuti fayilo ya Personal.xlsb kulibe. Njira yosavuta yopangira ndikujambulitsa macro opanda tanthauzo ndi chojambulira, koma tchulani Bukhu Lanu ngati malo osungira - ndiye kuti Excel idzakakamizika kukupangirani. Za ichi:

1. Dinani Woyambitsa (Wolemba Mapulogalamu). Ngati ma tabo Woyambitsa sichikuwoneka, ndiye kuti ikhoza kuthandizidwa muzokonda kudzera Fayilo - Zosankha - Kukhazikitsa Riboni (Kunyumba - Zosankha - Sinthani Mwamakonda Anu Riboni).
2. Pa Advanced tabu Woyambitsa pitani Kujambula kwa Macro (Rekodi Macro). Pazenera lomwe limatsegulidwa, sankhani Personal Macro Book (Personal Macro Workbook) monga malo osungirako code yolembedwa ndikusindikiza OK:

   

3. Siyani kujambula ndi batani Lekani Kujambula (Imani Kujambula) tsamba Woyambitsa (Wolemba Mapulogalamu)

Mutha kuyang'ana zotsatira podina batani Zooneka Basic pomwepo pa tabu. Woyambitsa - pa zenera lotseguka la mkonzi pakona yakumanzere pagawo Project - VBA Project fayilo yathu iyenera kuwonekera MUNTHU. Zithunzi za XLSB. Nthambi yake yomwe imatha kukulitsidwa ndi chizindikiro chowonjezera kumanzere, kufikira Module 1, pomwe khodi ya macro yopanda tanthauzo yomwe tangojambula imasungidwa:

Zabwino zonse, mwangopanga Personal Macro Book yanu! Ingokumbukirani kuti dinani batani losunga ndi floppy disk pakona yakumanzere kwa toolbar.

Momwe mungagwiritsire ntchito Personal Macro Book

Ndiye chirichonse chiri chophweka. Macro iliyonse yomwe mungafune (mwachitsanzo, kachidindo koyambira Sub ndi kutha mapeto Sub) akhoza kukopera motetezeka ndikumata mu Module 1, kapena mu gawo lina, kuwonjezera pa menyu Ikani - Module. Kusunga ma macros onse mu gawo limodzi kapena kuwayika mosiyanasiyana ndi nkhani ya kukoma. Iyenera kuwoneka motere:

Mutha kuyendetsa macro owonjezera mu bokosi la zokambirana lotchedwa ndi batani Macros (Makiro) tsamba Woyambitsa:

Mu zenera lomwelo, mwa kuwonekera batani magawo (Zosankha), mutha kukhazikitsa njira yachidule ya kiyibodi kuti muthamangitse mwachangu macro kuchokera pa kiyibodi. Samalani: njira zazifupi za kiyibodi zama macros zimasiyanitsa pakati pa masanjidwe (kapena Chingerezi) ndi chikwama.

Kuphatikiza pazotsatira zanthawi zonse mu Bukhu Lanu, mutha kusunganso makonda macro ntchito (UDF = Ntchito Yofotokozedwa ndi Wogwiritsa). Mosiyana ndi ndondomeko, ntchito code imayamba ndi mawu ntchitoor Ntchito Yapagulu, ndikumaliza ndi Kutsiriza Ntchito:

Khodiyo iyenera kukopera mofanana ndi gawo lililonse la bukhu la PERSONAL.XLSB, ndiyeno zidzatheka kuyitana ntchitoyi mwachizolowezi, monga ntchito iliyonse ya Excel, podutsa batani. f_x mu bar ya formula ndikusankha ntchito pawindo Ntchito Wizards mu gulu Kumasulira Womasulira (Kutanthauzira Wogwiritsa):

Zitsanzo zantchito zotere zitha kupezeka mochulukira pa intaneti kapena pano patsamba (kuchuluka kwa mawu, kusaka mawu pafupifupi, VLOOKUP 2.0, kutembenuza Cyrillic kukhala matanthauzidwe, ndi zina zambiri.)

Kodi Personal Macro Book imasungidwa kuti?

Ngati mugwiritsa ntchito Personal Book of Macros, ndiye posachedwa mudzakhala ndi chikhumbo:

• gawani ma macros omwe mwasonkhanitsidwa ndi ogwiritsa ntchito ena
• kukopera ndi kusamutsa Personal Book ku kompyuta ina
• kupanga kopi yosunga

Kuti muchite izi, muyenera kupeza fayilo ya PERSONAL.XLSB pa diski yanu ya kompyuta. Mwachikhazikitso, fayiloyi imasungidwa mufoda yapadera yoyambira ya Excel yotchedwa XLSTART. Chifukwa chake chomwe chikufunika ndikufika ku foda iyi pa PC yathu. Ndipo apa ndipamene pali vuto pang'ono, chifukwa malo a fodayi amadalira mtundu wa Windows ndi Office ndipo akhoza kusiyana. Nthawi zambiri iyi ndi imodzi mwazosankha izi:

• C: Mafayilo a PulogalamuMicrosoft OfficeOffice12XLSTART
• C:Documents and SettingsComputerApplication DataMicrosoftExcelXLSTART
• C: Ogwiritsadzina la akaunti yanuAppDataRoamingMicrosoftExcelXLSTART

Kapenanso, mutha kufunsa Excel yokha malo a fodayi pogwiritsa ntchito VBA. Kuti muchite izi, mu Visual Basic mkonzi (batani Zooneka Basic tsamba Woyambitsa) нужно открыть окно mwamsanga njira yowomba Ctrl + G, lembani lamulo ? Application.StartupPath ndipo dinani Lowani:

Njira yotsatila ikhoza kukopera ndikuyika pamzere wapamwamba wawindo la Explorer mu Windows ndikudina Lowani - ndipo tiwona chikwatu chokhala ndi fayilo yathu ya Personal Book of Macros:

PS

Ndipo ma nuances angapo othandiza pakutsata:

• Mukamagwiritsa ntchito Personal Macro Book, Excel idzayenda pang'onopang'ono, makamaka pa ma PC ofooka
  
• ndikofunikira nthawi ndi nthawi kuchotsa Bukhu Lanu ku zinyalala zazidziwitso, ma macro akale komanso osafunikira, ndi zina zambiri.
• ogwiritsa ntchito makampani nthawi zina amakhala ndi zovuta kugwiritsa ntchito Personal Book, tk. ili ndi fayilo mufoda yobisika yadongosolo
  

• Kodi ma macro ndi chiyani komanso momwe mungagwiritsire ntchito pantchito yanu
• Zothandiza pa pulogalamu ya VBA
• Maphunziro a "Programming macros mu VBA mu Microsoft Excel"