Ukwenza i-macro ngesikhathi

Icala elivame kakhulu ekusebenzeni: udinga ukusebenzisa i-macros yakho eyodwa noma ngaphezulu ngesikhathi esinikeziwe noma ngemvamisa ethile. Isibonelo, unombiko omkhulu nosindayo obuyekeza isigamu sehora futhi ungathanda ukuqalisa isibuyekezo isigamu sehora ngaphambi kokuba ufike emsebenzini ekuseni. Noma une-macro okufanele ithumele ngokuzenzakalelayo ama-imeyili kubasebenzi ngemvamisa ethile. Noma, lapho usebenza ne-PivotTable, ufuna ukuthi ibuyekeze empukaneni njalo ngemizuzwana eyi-10, njalo njalo.

Ake sibheke ukuthi yiziphi i-Excel neWindows ezinekhono lokusebenzisa lokhu.

Ukusebenzisa i-macro ngemvamisa enikeziwe

Indlela elula yokwenza lokhu ukusebenzisa indlela ye-VBA eyakhelwe ngaphakathi Isicelo.NgesikhathiAn esebenzisa imakhro eshiwo ngesikhathi esibekiwe. Ake sikuqonde lokhu ngesibonelo esisebenzayo.

Vula i-Visual Basic editor ngenkinobho yegama elifanayo kuthebhu Unjiniyela (Unjiniyela) noma isinqamuleli sekhibhodi Alt+F11, faka imojuli entsha ngemenyu Faka – Imojula bese ukopisha ikhodi elandelayo lapho:

Dim TimeToRun 'okuguquguqukayo kwembulunga yonke lapho kugcinwa khona isikhathi esilandelayo sokugijima 'lena uhlelo lokusebenza oluyinhloko lwe-Sub MyMacro().Bala 'bala kabusha Uhlu Lwencwadi("A1").Interior.ColorIndex = Int(Rnd() * 56) 'gcwalisa iseli A1 elinombala ongahleliwe :) Shayela i-NextRun 'sebenzisa i-NextRun macro ukuze usethe isikhathi sokusebenza esilandelayo End Sub 'le macro isetha isikhathi sokugijima okulandelayo kwe-macro enkulu Sub NextRun() TimeToRun = Now + TimeValue("00: 00:03") 'engeza imizuzwana emi-3 esikhathini samanje Isicelo.OnTime TimeToRun, "MyMacro" 'shejula ukugijima okulandelayo End Sub 'macro ukuze uqale ukulandelana okuphindaphindiwe I-Sub Start() Shayela i-NextRun End Sub 'macro ukuze umise ukulandelana okuphindaphindiwe I-Sub Finish() Isicelo.OnTime TimeToRun, "MyMacro", , Isiphetho Samanga Samanga  

Ake sithole ukuthi yini lapha.

Okokuqala, sidinga okuguquguqukayo okuzogcina isikhathi sokugijima okulandelayo kwe-macro yethu - ngikubizile I-TimeToRun. Sicela uqaphele ukuthi okuqukethwe kwalokhu kuguquguquka kufanele kutholakale kuwo wonke ama-macros ethu alandelayo, ngakho-ke sidinga ukukwenza global, okungukuthi memezela ekuqaleni kwemojuli ngaphambi kweyokuqala Okungaphansi.

Okulandelayo kuza i-macro yethu eyinhloko I-MyMacro, okuzokwenza umsebenzi oyinhloko - ukubala kabusha incwadi usebenzisa indlela Isicelo.Bala. Ukuze kucace kakhudlwana, ngengeze ifomula =TDATE() eshidini elikuseli A1, elibonisa usuku nesikhathi - uma kubalwa kabusha, okuqukethwe kulo kuzobuyekezwa phambi kwamehlo ethu (vele uvule isibonisi semizuzwana esitokisini. ifomethi). Ukuze ngijabulele okwengeziwe, ngiphinde ngengeza ku-macro umyalo wokugcwalisa iseli A1 ngombala okhethwe ngokungahleliwe (ikhodi yombala iyinani eliphelele kububanzi 0..56, okhiqizwa umsebenzi Rnd futhi ifinyeza umsebenzi ophelele Int).

I-Macro NextRun yengeza kunani langaphambilini I-TimeToRun 3 ngaphezulu imizuzwana bese uhlela ukugijima okulandelayo kwe-macro eyinhloko I-MyMacro ngalesi sikhathi esisha. Yebo, ekusebenzeni, ungasebenzisa noma yiziphi ezinye izikhawu ozidingayo ngokusetha izimpikiswano zomsebenzi IsikhathiValue ngefomethi hh:mm:ss.

Futhi ekugcineni, ukuze kube lula, kungezwe ama-macro okuqalisa alandelanayo. Ikhaya kanye nokuqedwa kwayo Qeda. Owokugcina usebenzisa i-agumenti yendlela yesine ukuze aphule ukulandelana. Ngesikhathi alinganayo Amanga.

Isamba uma usebenzisa i-macro Ikhaya, khona-ke le-carousel yonke izophenduka, futhi sizobona isithombe esilandelayo eshidini:

Ungamisa ukulandelana ngokugijima, ngokulandelana, i-macro Qeda. Ukuze kube lula, unganikeza izinqamuleli zekhibhodi kuwo womabili amamakhro usebenzisa umyalo Amakhrosi - Izinketho ithebhu Unjiniyela (Unjiniyela - Amakhrosi - Izinketho).

Ukwenza imakhro ngeshejuli

Yiqiniso, konke okuchazwe ngenhla kungenzeka kuphela uma une-Microsoft Excel esebenzayo futhi ifayela lethu livuliwe kuyo. Manje ake sibheke icala eliyinkimbinkimbi kakhulu: udinga ukusebenzisa i-Excel ngokweshejuli enikeziwe, isibonelo, nsuku zonke ngo-5:00, vula umbiko omkhulu futhi oyinkimbinkimbi kuwo futhi ubuyekeze konke ukuxhumana nemibuzo kuyo ukuze ikwazi. ube usulungile ngesikhathi sifika emsebenzini 🙂

Esimweni esinjalo, kungcono ukusebenzisa I-Windows Scheduler – uhlelo olwakhelwe ngokukhethekile kunoma iyiphi inguqulo ye-Windows engenza izenzo ezithile kushejuli. Eqinisweni, usuvele uyisebenzisa ngaphandle kokwazi, ngoba i-PC yakho ihlola njalo izibuyekezo, ilanda isizindalwazi esisha sokulwa namagciwane, ivumelanisa amafolda amafu, njll. Wonke umsebenzi woMhleli. Ngakho-ke umsebenzi wethu uwukwengeza emisebenzini ekhona omunye ozokwethula i-Excel futhi uvule ifayela elishiwo kuyo. Futhi sizolenga i-macro yethu emcimbini Incwadi Yokusebenzela_Vula leli fayela - futhi inkinga ixazululiwe.

Ngifuna ukukuxwayisa ngaso leso sikhathi ukuthi ukusebenza noHluli kungase kudinge amalungelo athuthukile omsebenzisi, ngakho-ke uma ungakwazi ukuthola imiyalo nemisebenzi echazwe ngezansi kukhompyutha yakho yomsebenzi ehhovisi, thintana nochwepheshe bakho be-IT ukuze uthole usizo.

Kwethulwa uMhleli

Ngakho-ke ake siqale i-Scheduler. Ukuze wenze lokhu, unga:

• Chofoza kwesokudla inkinobho Qala bese ukhetha Ukuphathwa kwekhompyutha (Ukuphathwa kwekhompyutha)
• Khetha Kuphaneli Yokulawula: Ukuphatha – Isihleli Somsebenzi (Iphaneli yokulawula — Amathuluzi Okuphatha — Isihleli Somsebenzi)
• Khetha kumenyu enkulu Qala – Izesekeli – Amathuluzi Esistimu – Isihleli Somsebenzi
• Cindezela isinqamuleli sekhibhodi Win+R, ngena imisebenzichd.msc bese ucindezela Faka

Iwindi elilandelayo kufanele livele esikrinini (nginenguqulo yesiNgisi, kodwa ungaba nenguqulo):

Dala umsebenzi

Ukudala umsebenzi omusha usebenzisa iwizadi elula yesinyathelo nesinyathelo, chofoza isixhumanisi Dala umsebenzi olula (Dala Umsebenzi Oyisisekelo) iphaneli elingakwesokudla.

Esinyathelweni sokuqala sewizadi, faka igama nencazelo yomsebenzi ozokwenziwa:

Chofoza inkinobho Olandelayo (Olandelayo) futhi esinyathelweni esilandelayo sikhetha i-trigger - imvamisa yokuqalisa noma umcimbi ozokwethula umsebenzi wethu (ngokwesibonelo, ukuvula ikhompyutha):

Uma ukhethe Daily (Nsuku zonke), bese esinyathelweni esilandelayo uzodinga ukukhetha isikhathi esithile, idethi yokuqala yokulandelana nesinyathelo (njalo ngosuku lwesi-2, osukwini lwesi-5, njll.):

Isinyathelo esilandelayo ukukhetha isenzo - Qalisa uhlelo (Qala uhlelo):

Futhi ekugcineni, into ethakazelisa kakhulu ukuthi yini okufanele ivulwe:

In the Uhlelo noma umbhalo (Uhlelo/isikripthi) udinga ukufaka indlela eya ku-Microsoft Excel njengohlelo, okungukuthi ngqo ku-Excel esebenzisekayo. Kumakhompyutha ahlukene anezinguqulo ezahlukene ze-Windows ne-Office, leli fayela lingase libe kumafolda ahlukene, ngakho nazi izindlela ezimbalwa zokuthi uthole indawo yalo:

• Chofoza kwesokudla kusithonjana (isinqamuleli) ukuze uvule i-Excel kudeskithophu noma kubha yomsebenzi bese ukhetha umyalo Materials (Izakhiwo), bese efasiteleni elivulayo, kopisha indlela emgqeni Target:

                       

• Vula noma iyiphi incwadi yokusebenzela ye-Excel, bese uvula Isiphathi Somsebenzi (Umphathi Womsebenzi) ukusunduza Ctrl+Alt+Kusukela futhi ngokuchofoza kwesokudla kulayini I-Microsoft Excel, khetha umyalo Materials (Izakhiwo). Ewindini elivulayo, ungakopisha indlela, ungakhohlwa ukungeza i-backslash kuyo kanye ne-EXCEL.EXE ekugcineni:

               

• Vula i-Excel, vula i-Visual Basic editor ngesinqamuleli sekhibhodi Alt+F11, vula iphaneli ngokushesha inhlanganisela ye- Ctrl+G, faka umyalo kuyo:

  ? Isicelo.Umzila

  ... bese uchofoza Faka

  

  Kopisha indlela ewumphumela, ungakhohlwa ukungeza i-backslash kuyo kanye ne-EXCEL.EXE ekugcineni.

In the Ngeza izimpikiswano (kuyakhetheka) (Engeza izimpikiswano (uyazikhethela)) udinga ukufaka indlela egcwele yencwadi nge-macro esifuna ukuyivula.

Uma yonke into ifakiwe, bese uchofoza Olandelayo bese Qeda (Qeda). Umsebenzi kufanele wengezwe ohlwini olujwayelekile:

Kulula ukuphatha umsebenzi odaliwe usebenzisa izinkinobho ezingakwesokudla. Lapha ungakwazi ukuhlola umsebenzi ngokuwenza ngokushesha (gijima)ngaphandle kokulinda isikhathi esibekiwe. Ungakwazi ukuvala umsebenzi okwesikhashana (Khubaza)ukuze iyeke ukusebenza isikhathi esithile, njengeholide lakho. Nokho, ungakwazi njalo ukushintsha imingcele (izinsuku, isikhathi, igama lefayela) ngokusebenzisa inkinobho Materials (Izakhiwo).

Engeza imakhro ukuze uvule ifayela

Manje kusele ukulenga encwadini yethu ukwethulwa kwe-macro esiyidingayo kumcimbi ovuliwe wefayela. Ukuze wenze lokhu, vula ibhuku bese uya kusihleli se-Visual Basic usebenzisa isinqamuleli sekhibhodi Alt+F11 noma izinkinobho Visual Basic ithebhu Unjiniyela (Unjiniyela). Efasiteleni elivuleka ekhoneni eliphezulu kwesokunxele, udinga ukuthola ifayela lethu esihlahleni bese uchofoza kabili ukuze uvule imojula. Le ncwadi (Lencwadi Yokusebenzela).

Uma ungaliboni leli windi kusihleli se-Visual Basic, ungalivula ngemenyu Buka - I-Project Explorer.

Kuwindi lemojula elivulekayo, engeza isibambi somcimbi sencwadi ngokusikhetha ohlwini oludonsela phansi phezulu. Incwadi yokusebenzela и Vula, ngokulandelana:

Isifanekiso senqubo kufanele sivele esikrinini. Incwadi Yokusebenzela_Vula, lapho phakathi kwemigqa I-Private Sub и Qeda i-Sub futhi udinga ukufaka leyo miyalo ye-VBA okufanele isebenze ngokuzenzakalelayo lapho le ncwadi yokusebenzela ye-Excel ivulwa, lapho uShejuli eyivula ngokweshejuli. Nazi ezinye izinketho eziwusizo ze-overclocking:

• LeNcwadi Yokusebenzela.Vuselela Konke - Ivuselela yonke imibuzo yedatha yangaphandle, imibuzo yemibuzo yamandla, namaPivotTables. Inketho enemisebenzi eminingi. Ungakhohlwa ukuvumela ukuxhumeka kudatha yangaphandle ngokuzenzakalelayo futhi ubuyekeze izixhumanisi nge Ifayela - Izinketho - Isikhungo Sokuthemba - Izinketho Zesikhungo Sokuthemba - Okuqukethwe Kwangaphandle, ngaphandle kwalokho, uma uvula incwadi, kuzovela isixwayiso esivamile futhi i-Excel, ngaphandle kokubuyekeza noma yini, izolinda isibusiso sakho ngendlela yokuchofoza inkinobho. Nika amandla okuqukethwe (Vumela okuqukethwe):

  

• I-ActiveWorkbook.Connections(“Igama_lokuxhunywa”).Vuselela — ibuyekeza idatha ekuxhumekeni kwe-Connection_Name.
• Amashidi(“Ishidi5“).Amathebula ePivot(“PivotTable1«).PivotCache.Vuselela – ukubuyekeza ithebula le-pivot elilodwa eliqanjwe I-PivotTable1 eshidini Ishidi5.
• Isicelo.Bala - ukubalwa kabusha kwawo wonke amabhuku okusebenzela e-Excel avuliwe.
• Isicelo.BalaFullRebuild - ukubalwa kabusha okuphoqelelwe kwawo wonke amafomula nokwakhiwa kabusha kwakho konke ukuncika phakathi kwamaseli kuwo wonke amabhuku okusebenzela avuliwe (okulingana nokufaka kabusha wonke amafomula).
• Amashidi okusebenzela(“Bika”).PrintOut – phrinta ishidi Izithombe.
• Shayela i-MyMacro – sebenzisa imakhro eqanjwe I-MyMacro.
• LeNcwadi Yokusebenzela.Londoloza – gcina incwadi yamanje
  
• Lezincwadi Zokusebenza.Londoloza njenge-“D:ArchiveReport” & Faka esikhundleni(Manje, “:", “-“) & “.xlsx” – gcina incwadi kwifolda D:Faka kungobo yomlando ngaphansi kwegama Izithombe nosuku nesikhathi okufakwe egameni.

Uma ufuna ukuthi i-macro yenziwe kuphela lapho ifayela livulwa nguMhleli ngo-5:00 ekuseni, futhi hhayi ngaso sonke isikhathi lapho umsebenzisi evula incwadi yokusebenza phakathi nosuku lokusebenza, khona-ke kunengqondo ukwengeza isheke lesikhathi, isibonelo:

Uma Ifomethi(Manje, "hh:mm") = "05:00" Bese kuba Lencwadi Yokusebenzela.RefreshAll  

Yilokho kuphela. Ungakhohlwa ukulondoloza ibhuku lakho lokusebenza ngefomethi enikwe amandla amakhulu (xlsm noma xlsb) futhi ungakwazi ukuvala i-Excel ngokuphephile uye ekhaya, ushiye ikhompuyutha yakho ivuliwe. Esikhathini esithile (ngisho noma i-PC ikhiyiwe), i-Scheduler izovula i-Excel futhi ivule ifayela elishiwo kuyo, futhi i-macro yethu izokwenza izenzo ezihleliwe. Futhi uzonethezeka embhedeni ngenkathi umbiko wakho osindayo ubalwa kabusha ngokuzenzakalela - ubuhle! 🙂

• Ayini ama-macros, asetshenziswa kanjani, lapho ungafaka khona ikhodi ye-Visual Basic ku-Excel
• Ungazakha kanjani isengezo sakho esikhulu se-Excel
• Ungayisebenzisa kanjani i-Personal Macro Workbook njengomtapo wezincwadi wama-macros akho ku-Excel