Mukati
Kana iwe usati wajaira macros muExcel, saka ini ndinokuda iwe zvishoma. Manzwiro ekuva nesimba rose uye kuziva kuti Microsoft Excel yako inogona kukwidziridzwa kusvika kukusingaperi iyo ichauya kwauri mushure mekuziva macros manzwiro anofadza.
Zvisinei, chinyorwa ichi ndechevaya "vakatodzidza simba" uye vakatanga kushandisa macros (kunze kwenyika kana kunyorwa ivo pachavo - hazvina basa) mubasa ravo rezuva nezuva.
A macro ikodhi (mitsetse yakati wandei) mumutauro weVisual Basic inoita kuti Excel iite zvaunoda: gadzira data, gadzira mushumo, kopi-namira matafura akawanda anodzokororwa, nezvimwewo. Mubvunzo ndewokuti ndekupi kwekuchengetedza iyi mitsetse mishoma yekodhi? Mushure mezvose, uko iyo macro inochengetwa inobva yabva pane iyo inogona (kana isingakwanise) kushanda.
Если макрос решает небольшую локальную проблему в отдельно взятом файле (например обрабатывает внесенную проблему в отдельно взятом файле) то логично хранить код внутри этого же файла. Без вопросов.
Uye kana macro ichifanirwa kuve yakasarudzika uye ichidikanwa mune chero bhuku rebasa reExcel - senge, semuenzaniso, macro ekushandura mafomula kuita maitiro? Wadii kukopa Visual Basic kodhi yake mubhuku rega rega? Mukuwedzera, nokukurumidza kana kuti gare gare, anenge chero mushandisi anouya kumhedziso yokuti zvingava zvakanaka kuisa macros ose mubhokisi rimwe chete, kureva kuva navo nguva dzose. Uye pamwe kunyange kumhanya kwete nemaoko, asi ne keyboard mapfupi? Apa ndipo panogona kuve nerubatsiro rukuru iyo Personal Macro Workbook.
Maitiro ekugadzira Yako Macro Book
Saizvozvo, Personal Book of Macros (LMB) inguva yeExcel faira mune bhinari bhuku rekushanda fomati (Personal.xlsb), iyo inovhura otomatiki mune yakavanzika modhi panguva imwe chete seMicrosoft Excel. Avo. paunongotanga Excel kana kuvhura chero faira kubva kudhisiki, mafaira maviri anovhurwa chaizvo - yako uye Personal.xlsb, asi isu hationi yechipiri. Nekudaro, ese macros akachengetwa muLMB anowanikwa kuvhurwa chero nguva apo Excel yakavhurika.
Kana usati wamboshandisa LMB, saka pekutanga Personal.xlsb faira haripo. Nzira iri nyore yekuigadzira ndeyekurekodha mamwe macro asina zvazvinoreva nerekodha, asi tsanangura Bhuku Rako senzvimbo yekurichengeta - ipapo Excel inozomanikidzwa kuti igadzirire iwe pachako. Nokuda kweizvi:
- Baya yokuvaka (Mugadziri). Kana ma tabo yokuvaka haisi kuoneka, saka inogona kugoneswa muzvirongwa kuburikidza Faira - Sarudzo - Ribhoni Setup (Kumba - Sarudzo - Gadzirisa iyo Ribhoni).
- Pa Advanced tabo yokuvaka watinya Macro kurekodha (Rekodha Macro). Muhwindo rinovhurwa, sarudza Personal Macro Book (Personal Macro Workbook) senzvimbo yekuchengetedza kodhi yakanyorwa uye wodzvanya OK:
- Rega kurekodha nebhatani Mira Kurekodha (Misa kurekodha) Tab yokuvaka (Mugadziri)
Unogona kutarisa mhinduro nekudzvanya bhatani Visual Basic ipapo pane tab. yokuvaka - muhwindo rakavhurwa remupepeti mukona yepamusoro kuruboshwe pane pani Chirongwa - VBA Project faira yedu inofanira kuoneka MUNHU. XLSB. Bazi rayo rinogona kuwedzerwa nechiratidzo chekuwedzera kuruboshwe, kusvika Module1, uko kodhi yeasina zvazvinoreva macro yatichangobva kurekodha inochengetwa:
Makorokoto, iwe uchangobva kugadzira yako Personal Macro Book! Ingo usakanganwa kudzvanya pane bhatani rekuchengetedza nefloppy disk mukona yekumusoro kuruboshwe yebhari.
Maitiro ekushandisa Personal Macro Book
Zvadaro zvinhu zvose zviri nyore. Chero macro aunoda (kureva chidimbu chekodhi chinotanga nacho Sub uye kuguma End Sub) inogona kukopwa uye kunamirwa zvakachengeteka mukati Module1, kana mune yakaparadzana module, uchiiwedzera kare kuburikidza nemenyu Pinza – Module. Kuchengeta macros ese mune imwe module kana kuaisa kunze mune akasiyana ingori nyaya yekuravira. Inofanira kutaridzika seizvi:
Iwe unogona kumhanya yakawedzera macro mubhokisi rebhokisi rakadaidzwa nebhatani macro (Makororo) Tab yokuvaka:
Muhwindo rimwe chete, nekudzvanya bhatani Parameters (Sarudzo), unogona kuseta nzira yekudimbudzira yekhibhodi kuti ikurumidze kumhanyisa macro kubva pane keyboard. Ngwarira: mapfupi ekhibhodi e macros anosiyanisa pakati pemagadzirirwo (kana Chirungu) nekesi.
Kuwedzera kune akajairwa macro-maitiro muBhuku Rega, iwe unogona zvakare kuchengeta tsika macro mabasa (UDF = Mushandisi Inotsanangurwa Basa). Kusiyana nematanho, kodhi yebasa inotanga nekutaura basaor Public Function, uye kuguma ne Pedza Basa:
Kodhi yacho inofanira kukopwa nenzira imwechete kune chero module ye PERSONAL.XLSB bhuku, uye zvino zvichave zvichiita kudana basa racho nenzira yakajairika, seyese chiyero cheExcel basa, nekudzvanya bhatani. fx mune formula bar uye kusarudza basa muhwindo Basa Wizards muchikwata Mushandisi Anotsanangurwa (Mushandisi Anotsanangurwa):
Mienzaniso yemabasa akadai inogona kuwanikwa muhuwandu hukuru paInternet kana pano pasaiti (huwandu mumashoko, fungidziro yekutsvaga zvinyorwa, VLOOKUP 2.0, kushandura Cyrillic kuita dudziro, nezvimwewo)
Iyo Personal Macro Book yakachengetwa kupi?
Kana iwe ukashandisa iro Personal Book reMacros, ipapo nekukurumidza kana gare gare iwe unenge uine chishuwo:
- govera macros ako akaunganidzwa nevamwe vashandisi
- kopira uye endesa Bhuku Rako kune imwe komputa
- ita kopi yekuchengetedza
Kuti uite izvi, unozofanirwa kutsvaga PERSONAL.XLSB faira padisiki rekombuta yako. Nekumisikidza, faira iri rinochengetwa mune yakakosha Excel yekutanga folda inonzi XLSTART. Saka zvese zvinodikanwa kusvika kune iyi folda paPC yedu. Uye apa ndipo panomuka dambudziko diki, nekuti nzvimbo yefolda iyi zvinoenderana neshanduro yeWindows neHofisi uye inogona kusiyana. Iyi inowanzova imwe yeinotevera sarudzo:
- C: Mafaira eChirongwaMicrosoft OfficeOffice12XLSTART
- C: Zvinyorwa uye ZvirongwaComputerApplication DataMicrosoftExcelXLSTART
- C: Vashandisiyako-akaundi-zitaAppDataRoamingMicrosoftExcelXLSTART
Neimwe nzira, unogona kubvunza Excel pachayo nezvenzvimbo yeiyi folda uchishandisa VBA. Kuti uite izvi, mune Visual Basic edhita (bhatani Visual Basic Tab yokuvaka) нужно открыть окно Pakarepo keyboard shortcut CTRL+G, nyora murayiro ? Application.StartupPath uye tinya pinda:
Iyo inoguma nzira inogona kukopwa uye kunamirwa mumutsara wepamusoro weExplorer hwindo muWindows uye tinya pinda - uye isu tichaona folda ine Yedu Bhuku reMacros faira:
PS
Uye mashoma anoshanda nuances mukutsvaga:
- paunenge uchishandisa Personal Macro Book, Excel inomhanya zvishoma zvishoma, kunyanya pamaPC asina simba
- zvakakodzera nguva nenguva kujekesa Bhuku Remunhu kubva kumarara eruzivo, ekare uye asina kufanira macros, nezvimwe.
- vashandisi vemakambani dzimwe nguva vane matambudziko ekushandisa Bhuku reMunhu, tk. iyi ifaira mune system yakavanzika folda
- Chii chinonzi macros uye kuti ungashandise sei mubasa rako
- Inobatsira kune VBA programmer
- Kudzidzisa "Programming macros muVBA muMicrosoft Excel"