Gudun macro akan lokaci

Shari'ar gama gari a aikace: kuna buƙatar gudanar da ɗaya ko fiye na macros ɗin ku a wani lokaci ko a wani mitar. Misali, kuna da babban rahoto mai nauyi wanda ke sabunta rabin sa'a kuma kuna son gudanar da sabuntawar rabin sa'a kafin ku isa wurin aiki da safe. Ko kuna da macro wanda yakamata ya aika imel ta atomatik zuwa ma'aikata a ƙayyadadden mitar. Ko, lokacin aiki tare da PivotTable, kuna son sabuntawa akan tashi kowane sakan 10, da sauransu.

Bari mu ga abin da Excel da Windows ke da ikon aiwatar da wannan.

Gudun macro a mitar da aka bayar

Hanya mafi sauƙi don yin wannan ita ce amfani da ginanniyar hanyar VBA Aikace-aikace.LokaciWanda ke gudanar da ƙayyadadden macro a ƙayyadadden lokacin. Bari mu fahimci wannan da misali mai amfani.

Bude editan Kayayyakin Kayayyakin Kaya tare da maballin suna iri ɗaya akan shafin developer (Mai haɓakawa) ko gajeriyar hanyar keyboard alt+F11, saka sabon tsari ta cikin menu Saka - Module sannan ka kwafi wannan code din can:

Dim TimeToRun 'mai canzawa na duniya inda aka adana lokacin gudu na gaba' wannan shine babban macro Sub MyMacro() Application.Lissafta' sake lissafin littafin Range("A1").Interior.ColorIndex = Int(Rnd() * 56) 'cika cell A1 tare da launi bazuwar :) Kira NextRun 'gudu da macro na gaba Run don saita lokacin gudu na gaba Ƙarshen Sub' wannan macro yana saita lokaci don gudu na gaba na babban macro Sub NextRun () TimeToRun = Yanzu + TimeValue("00: 00:03") 'ƙara daƙiƙa 3 zuwa aikace-aikacen lokaci na yanzu.OnTime TimeToRun, "MyMacro"'tsara tsari na gaba Ƙarshen Sub 'macro don fara maimaita jerin Sub Start() Kira NextRun Ƙarshen Sub' macro don dakatar da maimaita jerin. Ƙarshen Ƙarshe () Aikace-aikacen.Lokacin LokaciToRun, "MyMacro", , Ƙarshen Ƙarshen Ƙarshen  

Bari mu gano menene a nan.

Na farko, muna buƙatar mai canzawa wanda zai adana lokacin gudu na gaba na macro - na kira shi TimeToRun. Lura cewa abubuwan da ke cikin wannan canjin dole ne su kasance samuwa ga duk macro na gaba, don haka muna buƙatar yin shi. duniya, watau bayyana a farkon tsarin kafin na farko sub.

Na gaba ya zo babban macro MyMacro, wanda zai yi babban aiki - don sake lissafin littafin ta amfani da hanyar Aikace-aikace.Lissafi. Don ƙarin bayani, na ƙara da dabara = TDATE() a cikin takardar a cikin cell A1, wanda ke nuna kwanan wata da lokaci - idan aka sake ƙididdige shi, za a sabunta abubuwan da ke ciki a gaban idanunmu (kawai kunna nunin seconds a cikin tantanin halitta. format). Don ƙarin jin daɗi, Na kuma ƙara zuwa macro umarnin don cika tantanin halitta A1 tare da launi da aka zaɓa (launi mai launi shine lamba a cikin kewayon 0..56, wanda aikin ke samarwa. Rnd kuma yana zagaye har zuwa aikin lamba Int).

Macro NextRun yana ƙara darajar baya TimeToRun 3 ƙarin daƙiƙa sannan kuma tsara jadawalin gudu na gaba na babban macro MyMacro don wannan sabon lokaci. Tabbas, a aikace, zaku iya amfani da kowane tazara na lokaci da kuke buƙata ta hanyar kafa hujjar aikin TimeValue a cikin tsari hh:mm:ss.

Kuma a ƙarshe, don dacewa kawai, an ƙara ƙarin jerin ƙaddamar da macros. Gida da kammalawarsa Gama. Na ƙarshe yana amfani da hujjar hanya ta huɗu don karya jerin. A lokacin daidai arya.

Jimlar idan kuna gudanar da macro Gida, to wannan duka carousel zai juya, kuma za mu ga hoto mai zuwa a kan takardar:

Kuna iya dakatar da jerin ta hanyar gudu, bi da bi, macro Gama. Don saukakawa, zaku iya sanya gajerun hanyoyin keyboard zuwa duka macros ta amfani da umarnin Macros - Zabuka tab developer (Mai haɓakawa - Macros - Zaɓuɓɓuka).

Gudun macro akan jadawali

Tabbas, duk abin da aka bayyana a sama yana yiwuwa ne kawai idan kuna da Microsoft Excel yana gudana kuma fayil ɗinmu yana buɗewa a ciki. Yanzu bari mu kalli shari'ar da ta fi rikitarwa: kuna buƙatar gudanar da Excel bisa ga jadawalin da aka ba ku, misali, kowace rana da ƙarfe 5:00, buɗe babban rahoto mai rikitarwa a ciki kuma sabunta duk haɗin gwiwa da tambayoyin da ke cikinsa ta yadda zai iya. ku kasance cikin shiri har zuwa lokacin da muka isa wurin aiki 🙂

A irin wannan yanayin, yana da kyau a yi amfani da shi Mai tsara Windows – shirin da aka gina musamman a cikin kowane sigar Windows wanda zai iya aiwatar da takamaiman ayyuka akan jadawali. A gaskiya ma, kun riga kun yi amfani da shi ba tare da saninsa ba, saboda PC ɗin ku akai-akai yana bincika abubuwan sabuntawa, zazzage sabbin bayanai na anti-virus, daidaita manyan fayilolin girgije, da dai sauransu. Duk aikin Mai tsarawa ne. Don haka aikinmu shi ne mu ƙara wa ayyukan da ke akwai wani wanda zai ƙaddamar da Excel kuma ya buɗe takamaiman fayil a cikinsa. Kuma za mu rataya macronmu akan taron Littafin Aiki_Bude wannan fayil - kuma an warware matsalar.

Ina so in yi muku gargaɗi nan da nan cewa yin aiki tare da Mai tsarawa na iya buƙatar ci gaba da haƙƙoƙin mai amfani, don haka idan ba za ku iya samun umarni da ayyukan da aka siffanta a ƙasa akan kwamfutar aikinku a ofis ba, tuntuɓi ƙwararrun IT don taimako.

Ƙaddamar da Jadawalin

Don haka bari mu fara Jadawalin. Don yin wannan, zaku iya ko dai:

• Dama danna maɓallin Fara kuma zaɓi Computer Management (Gudanar da Kwamfuta)
• Zaɓi a cikin Control Panel: Gudanarwa - Jadawalin Aiki (Tsarin Gudanarwa - Kayan Gudanarwa - Mai tsara Aiki)
• Zaɓi daga babban menu Fara - Na'urorin haɗi - Kayan aikin Tsarin - Mai tsara Aiki
• Danna maɓallin gajeren hanya Win+R, shiga daikkumar.msc kuma latsa Shigar

Ya kamata taga mai zuwa ya bayyana akan allon (Ina da sigar Ingilishi, amma kuna iya samun sigar):

Createirƙiri aiki

Don ƙirƙirar sabon ɗawainiya ta amfani da mayen mataki-mataki mai sauƙi, danna mahaɗin Ƙirƙiri aiki mai sauƙi (Ƙirƙiri Babban Aiki) a cikin dama panel.

A mataki na farko na maye, shigar da suna da bayanin aikin da za a ƙirƙira:

Latsa maballin Next (Na gaba) kuma a mataki na gaba za mu zaɓi abin faɗakarwa - mitar ƙaddamarwa ko taron da zai ƙaddamar da aikinmu (misali, kunna kwamfutar):

Idan ka zaba Daily (Kullum), to a mataki na gaba kuna buƙatar zaɓar takamaiman lokaci, fara kwanan wata jerin da mataki (kowace rana ta 2nd, rana ta 5, da sauransu):

Mataki na gaba shine zaɓi wani aiki - Gudanar da shirin (Fara shiri):

Kuma a ƙarshe, abu mafi ban sha'awa shine ainihin abin da ake buƙatar buɗewa:

a cikin Shirin ko rubutun (Shirin/rubutun) kana buƙatar shigar da hanyar zuwa Microsoft Excel a matsayin shirin, watau kai tsaye zuwa ga Excel executable. A kan kwamfutoci daban-daban masu nau'ikan Windows da Office daban-daban, wannan fayil ɗin yana iya kasancewa cikin manyan fayiloli daban-daban, don haka ga wasu hanyoyi don gano wurinsa:

• Danna-dama akan gunkin (hanyar gajeriyar hanya) don ƙaddamar da Excel akan tebur ko a cikin taskbar kuma zaɓi umarnin Materials (Properties), sannan a cikin taga da ke buɗewa, kwafi hanyar daga layin Target:

                       

• Bude kowane littafin aikin Excel, sannan buɗe Task Manager (Task Manager) turawa Ctrl+alt+daga kuma ta danna dama akan layin Microsoft Excel, zaɓi umarni Materials (Properties). A cikin taga da ya buɗe, zaku iya kwafi hanyar, kar a manta da ƙara mayar da baya gare shi da EXCEL.EXE a ƙarshen:

               

• Bude Excel, buɗe Editan Kayayyakin Kayayyakin Kayayyakin Kayayyakin Kayayyakin Kayayyakin Kayayyakin Kayayyakin Kayayyakin Kayayyakin Kaya tare da gajeriyar hanyar madannai alt+F11, bude panel nan da nan hade da Ctrl+G, shigar da umarni a ciki:

  ? Aikace-aikace.Hanyar

  ... kuma danna Shigar

  

  Kwafi hanyar da aka samu, kar a manta da ƙara mayar da baya gare shi da EXCEL.EXE a ƙarshen.

a cikin Sanya muhawara (na zabi) (Ƙara muhawara (na zaɓi)) kana buƙatar saka cikakken hanyar zuwa littafin tare da macro da muke son buɗewa.

Lokacin da aka shigar da komai, sannan danna Next sai me Gama (Gama). Ya kamata a ƙara aikin zuwa jeri na gaba ɗaya:

Ya dace don sarrafa aikin da aka ƙirƙira ta amfani da maɓallan dama. Anan zaka iya gwada aikin ta gudanar da shi nan da nan (gudu)ba tare da jiran takamaiman lokacin ba. Kuna iya kashe wani ɗawainiya na ɗan lokaci (A kashe)ta yadda zai daina gudu na wani lokaci, kamar hutun ku. Da kyau, koyaushe kuna iya canza sigogi (kwanakin, lokaci, sunan fayil) ta maɓallin Materials (Properties).

Ƙara macro don buɗe fayil

Yanzu ya rage don rataya a cikin littafinmu ƙaddamar da macro da muke buƙata akan taron buɗe fayil ɗin. Don yin wannan, buɗe littafin kuma je zuwa editan Visual Basic ta amfani da gajeriyar hanyar madannai alt+F11 ko maɓalli Kayayyakin aikin Basic tab developer (Mai haɓakawa). A cikin taga da ke buɗe a kusurwar hagu na sama, kuna buƙatar nemo fayil ɗin mu akan bishiyar kuma danna sau biyu don buɗe tsarin. wannan littafin (Wannan Littafin Aiki).

Idan baku ga wannan taga a cikin editan Visual Basic ba, to zaku iya buɗe ta ta menu Duba - Project Explorer.

A cikin taga tsarin da ke buɗewa, ƙara mai gudanar da taron buɗaɗɗen littafi ta zaɓi shi daga jerin abubuwan da aka saukar a sama. Littafin aikin jarrabawa и Bude, bi da bi:

Samfurin tsari yakamata ya bayyana akan allon. Littafin Aiki_Bude, inda tsakanin layuka Mai zaman kansa Sub и karshen Sub kuma kana buƙatar shigar da waɗannan umarnin VBA waɗanda ya kamata a aiwatar da su ta atomatik lokacin da aka buɗe wannan littafin aikin Excel, lokacin da Mai tsarawa ya buɗe shi bisa ga jadawalin. Anan akwai wasu zaɓuɓɓuka masu amfani don overclocking:

• Wannan Littafin Aiki.RefreshAll - Yana wartsakar da duk tambayoyin bayanan waje, tambayoyin Query, da PivotTables. Zaɓin mafi dacewa. Kar a manta ba da izinin haɗi zuwa bayanan waje ta tsohuwa da sabunta hanyoyin haɗin kai ta hanyar Fayil - Zaɓuɓɓuka - Cibiyar Amincewa - Zaɓuɓɓukan Cibiyar Aminta - Abun ciki na waje, in ba haka ba, lokacin da ka bude littafin, daidaitaccen gargadi zai bayyana kuma Excel, ba tare da sabunta komai ba, zai jira albarkarka ta hanyar danna maballin. Kunna abun ciki (Kada abun ciki):

  

• ActiveWorkbook.Connections("Haɗin_Name"). Refresh - sabunta bayanai akan haɗin haɗin_Name.
• Sheets ("Tafiyar 5").PivotTables("PivotTable1«).PivotCache.Refresh – sabunta tebur pivot guda ɗaya mai suna PivotTable1 a kan takardar Takardar5.
• Aikace-aikace.Lissafi - sake kirga duk buɗaɗɗen littattafan aikin Excel.
• Aikace-aikace.LissaftaFullRebuild - sake ƙididdige ƙididdiga na tilastawa da sake gina duk abin dogaro tsakanin sel a cikin duk littattafan aiki da aka buɗe (daidai da sake shigar da duk dabarun).
• Takardun aiki ("Rahoto").PrintOut – takardar buga Photos.
• Kira MyMacro – gudanar da wani macro mai suna MyMacro.
• Wannan Littafin Aiki.Ajiye – ajiye littafin na yanzu
  
• ThisWorkbooks.AjiyeAs "D:Rahoton Archive" & Sauya (Yanzu, ":", "-") & ".xlsx" – ajiye littafin zuwa babban fayil D: Taskar Labarai a karkashin sunan Photos tare da kwanan wata da lokacin da aka rataye sunan.

Idan kuna son aiwatar da macro kawai lokacin da Mai tsarawa ya buɗe fayil ɗin a karfe 5:00 na safe, kuma ba duk lokacin da mai amfani ya buɗe littafin aiki ba yayin ranar aiki, to yana da ma'ana don ƙara rajistan lokaci, misali:

Idan Tsarin (Yanzu, "hh:mm") = "05:00" Sai Wannan Littafin Aikin.RefreshAll  

Shi ke nan. Kar a manta da adana littafin aikinku a tsarin macro-enabled (xlsm ko xlsb) kuma zaku iya rufe Excel lafiya kuma ku koma gida, ku bar kwamfutarku a kunne. A wani lokaci da aka ba (ko da PC ɗin yana kulle), Mai tsarawa zai ƙaddamar da Excel kuma ya buɗe ƙayyadaddun fayil a ciki, kuma macro ɗin mu zai yi ayyukan da aka tsara. Kuma za ku yi farin ciki a gado yayin da aka sake ƙididdige rahoton ku mai nauyi ta atomatik - kyakkyawa! 🙂

• Menene macros, yadda ake amfani da su, inda za a saka Visual Basic code a cikin Excel
• Yadda ake ƙirƙirar macro add-in don Excel
• Yadda ake amfani da Littafin Ayyukan Macro na Keɓaɓɓen azaman ɗakin karatu don macros ɗin ku a cikin Excel