Amfani ga mai tsara shirye-shiryen VBA

Idan ka furta kalmar "macros" tare da numfashi mai ban tsoro da kuma lafazi a cikin sauti na biyu, kuma kalmar "Visual Basic for Applications" tana kama da sihiri a gare ku, to wannan labarin ba na ku ba ne. A kowane hali, a yanzu 🙂

Idan kuna da aƙalla wasu gogewa a cikin shirye-shiryen macro a cikin VBA a cikin Excel, kuma ba ku shirya tsayawa ba, to zaɓin add-ins masu amfani da shirye-shiryen da ke ƙasa yakamata su kasance masu amfani a gare ku.

MZ-Tools – “wukar Swiss” don mai tsara shirye-shirye

Bayan shigarwa a cikin editan VBE a cikin menu kayayyakin aiki, ƙaramin menu zai bayyana MZ-Kayan aiki da sabon kayan aiki don saurin samun dama ga ayyuka iri ɗaya:

Ya san yadda zai yi da yawa. Daga cikin mafi daraja, a ganina:

• Ƙara "kifi mara kyau" ta atomatik don ƙirƙirar hanyoyi, ayyuka, taron da masu sarrafa kuskure tare da daidaitaccen suna na masu canji bisa ga tsarin Hungarian.
  
• Kwafi sarrafawa akan fom ɗin masu amfani tare da lambar su.
  
• Yi alamun shafi (Fiyayyen) don matakai kuma da sauri matsar da su a cikin babban aiki.
• Raba dogayen layukan lamba zuwa da yawa kuma a haɗa baya (raga ku haɗa layi).
  
• Ba da cikakkun ƙididdiga akan aikin (yawan layukan lamba, hanyoyin, abubuwa akan fom, da sauransu)
• Bincika aikin don masu canji da hanyoyin da ba a yi amfani da su ba (Tsarin Bita)
• Ƙirƙiri tushe na samfuran lambar ku (Tsarin Lambobi) don al'amuran al'ada kuma da sauri saka su cikin sabbin macros daga baya.
• Ƙirƙiri dogon igiya mai ban tsoro ta atomatik don haɗawa zuwa tushen bayanan waje ta hanyar ADO.
  
• Haɗa hotkeys zuwa kowane aiki daga ƙarawa.
  

Dole ne mara ma'ana ga mai shirye-shirye na kowane mataki. Idan kana da sabon sigar Office, to tabbas zazzage sabuwar sigar MZ-Tools 3.00.1218 mai kwanan wata 1 ga Maris, saboda. ya gyara kwaro lokacin aiki tare da Excel 2013.  

download link MZ-Kayan aiki

Smart Indenter – shigarwa ta atomatik a cikin lambar

Yana yin aiki mai sauƙi amma mai matukar mahimmanci sosai - yana shigar da shafuka ta atomatik a cikin lambar VBA, yana nuna madaidaicin madaukai, duba yanayin, da sauransu.

Yana da matukar dacewa don sanya wannan aikin ga kowane gajeriyar hanyar madannai mai dacewa a cikin sashin Zaɓuɓɓukan Indenting kuma ku yi shi da taɓawa ɗaya.

Abin baƙin cikin shine, marubucin shirin ya watsar da shi a cikin 2005 (me yasa, Carl!?) Kuma sabon sigar akan shafin shine don Excel 97-2003. Koyaya, shirin yana aiki da kyau tare da sabbin sigogin. Maganar kawai: idan kuna da Excel 2013, to, kafin shigar da Smart Indenter, dole ne ku fara shigar da sabon sigar MZ-Tools, saboda. yana ƙunshe da ɗakin karatu mai ƙarfi da ake buƙata don aikin Indenter.

download link Smart Indenter

VBE Tools – micro-tuning abubuwa a cikin siffofin

Daidaita sarrafawa (maɓallai, filayen shigarwa, lakabin rubutu, da dai sauransu) akan nau'i mai rikitarwa na iya zama ciwo a cikin jaki. Daidaitaccen ɗaure ga grid edita ta menu Kayan aiki - Zaɓuɓɓuka - Gabaɗaya - Daidaita Sarrafa zuwa Grid wani lokacin ba ya taimaka da yawa har ma ya fara shiga hanya, musamman ma idan kuna buƙatar motsawa, misali, maɓallin kawai dan kadan. Ƙarin kayan aikin VBE zai taimaka a cikin wannan al'amari, wanda, bayan shigarwa, yana nuna kwamiti mai sauƙi inda za ku iya daidaita girman da matsayi a kan nau'i na abin da aka zaɓa:

Hakanan za'a iya canza matsayi tare da kiban Alt, da kuma sake girman kiban Shift+Alt+ da Ctrl+Alt+ kibiyoyi.

Hakanan, ta danna dama akan wani abu, zaku iya sake suna nan da nan tare da lambar.

download link Kayan aikin VBE

VBA Diff - Neman Bambance-bambance a cikin Lambobi

Wataƙila wannan kayan aikin zai zama mafi amfani ga ƙwararrun masu shirye-shiryen VBA lokacin ƙirƙirar manyan ayyuka masu rikitarwa ko haɓaka haɗin gwiwa. Babban aikinsa shine kwatanta ayyuka guda biyu kuma a gani na nuna bambancin lamba tsakanin su:

Akwai lokacin kyauta na kwanaki 30, sannan ƙari zai tambaye ku ku biya fam 39 don shi (kimanin 3.5 dubu rubles a farashin canji na yanzu).

Gaskiya magana, ya zo a cikin rayuwata kawai sau 3-4 a kan manyan ayyuka, amma sai ya cece ni kwanaki da yawa da yawa jijiya Kwayoyin 🙂 To, akwai ko da yaushe, ba shakka, wani free madadin: fitarwa da lambar zuwa fayil ɗin rubutu (danna dama-dama modulo - Export) kuma kwatanta su daga baya a cikin Microsoft Word ta amfani da umarnin Bita - Kwatanta Takardu, amma tare da taimakon VBA Diff tsari ne na girman da ya fi dacewa.

download link VBA Diff

Moqups da Wireframe Sketcher - ƙirar ƙirar keɓancewa

Lokacin ƙirƙirar hadaddun mu'amala don mu'amalar mai amfani, yana da matukar dacewa don ƙirƙira a gaba da kusan bayyanar akwatunan maganganu, watau aiwatar da prototyping. A zahiri, ya zama mafi sauƙi fiye da sake yin shirye-shiryen shirye-shiryen da lambar su daga baya. Na tuna sau ɗaya a cikin ɗayan ayyukan abokin ciniki ya nemi yin "menu", ma'ana "shafukan". Rabin rana na aiki saukar da magudanar ruwa 🙁

Akwai adadi mai yawa na shirye-shiryen biya da kyauta na matakai daban-daban na rikitarwa da iko don waɗannan ayyuka. Na gwada kusan dozin irin waɗannan shirye-shirye da ayyuka, kuma kwanan nan na fi yawan amfani da su moqups:

Wannan editan kan layi ne wanda:

• Baya buƙatar shigar da shirye-shirye daban. Kullum kuna iya zuwa ofishin abokin ciniki kuma ku buɗe-show-gyara ƙirar da aka ƙirƙira daidai akan rukunin yanar gizon.
• Ya ƙunshi duk manyan abubuwan akwatunan maganganu (lakabi, maɓalli, jeri, da sauransu) a cikin nau'ikan Windows da Mac.
• Yana ba ku damar fitar da ƙirar ƙirƙira a cikin tsarin PNG ko PDF ko aika hanyar haɗi zuwa abokin ciniki don kallo akan layi.
  
• A gaskiya kyauta. Akwai iyaka akan adadin abubuwa masu hoto, amma ban taɓa yin nasarar wuce su ba. Idan sarari ya ƙare ko kuna son adana manyan ayyuka da yawa a lokaci ɗaya, koyaushe kuna iya haɓakawa zuwa sigar ƙima akan $99 kowace shekara.

Gabaɗaya, don ayyukan mai haɓakawa a cikin VBA - fiye da isa, ina tsammanin.

Idan wani yana buƙatar ainihin zaɓi na layi (don yin aiki ba tare da samun damar Intanet a bakin teku ba, alal misali), to ina ba da shawarar Wireframe Sketcher:

Bayan lokacin demo kyauta na makonni 2, zai nemi ku saya akan $99 iri ɗaya.

link to moqups

download link Wireframe Sketcher

Ganuwa Basic – code obfuscator

Abin takaici, ba zai yiwu a kulle lambar tushe na macros ɗinku tare da kalmar sirri a cikin Microsoft Excel ba. Koyaya, akwai duka nau'ikan shirye-shiryen da ake kira obfuscators (Daga Turanci. ruɗe - ruɗe, ruɗe), wanda ke canza bayyanar lambar VBA ta yadda zai yi wahala sosai don karantawa da fahimtarsa, wato:

• ana maye gurbin sunaye masu canji, matakai da ayyuka tare da saiti masu tsayi marasa ma'ana ko, akasin haka, tare da gajerun haruffa marasa fahimta.
  
• Ana cire indents na gani na gani
• an cire ko, akasin haka, ana sanya raƙuman layi ba da gangan ba, da sauransu.

A gaskiya, ni ba mai sha'awar amfani da waɗannan hanyoyin ba ne. Musamman, tare da PLEX, Na yanke shawarar cewa zai fi kyau a ba masu siyan cikakken sigar buɗewa, mai sauƙin fahimta da lambar tushe mai sharhi - wannan alama a gare ni mafi daidai. Duk da haka, 'yan'uwana masu shirye-shirye sun sha samun lokuta lokacin da irin wannan shirin zai kasance da amfani sosai (mai shirye-shiryen ya yi aikin, amma abokin ciniki bai biya ba, da dai sauransu) Don haka idan kuna buƙatar shi, san inda za ku samu. "Mu mutane ne masu zaman lafiya, amma jirgin mu masu sulke..." da duk wannan.

Download Basic Ganuwa

Mai tsabtace lamba - tsaftacewa na lamba

A cikin aiwatar da aiki a kan aikin (musamman idan yana da girma da tsawo), "datti" ya fara tarawa a cikin nau'o'in code da nau'i-nau'i na bayanin sabis na editan VBE wanda zai iya haifar da rashin tsammani da maras so. Amfani Mai tsabtace Code yana tsaftace wannan muck a hanya mai sauƙi amma abin dogaro: fitar da lambar daga kayayyaki zuwa fayilolin rubutu, sannan a shigo da shi da tsabta. Ina ba da shawarar sosai cewa lokacin aiki akan manyan ayyuka, aiwatar da irin wannan "tsaftacewa" lokaci-lokaci.

download link Mai tsabtace Code

Ribbon XML Editan

Idan kuna son ƙirƙirar shafin ku tare da kyawawan maɓalli a kan ribbon na Excel don gudanar da macros ɗin ku, to ba za ku iya yin ba tare da editan fayil ɗin XML na dubawa ba. Tabbas, mafi dacewa da ƙarfi a yau shine shirin cikin gida dangane da wannan. Ribbon XML EditanMaxim Novikov ya halitta.

Cikakken software mai ban mamaki wanda:

• zai ba ku damar ƙara shafuka, maɓallai, jerin abubuwan da aka saukar da sauran abubuwan sabon ƙirar Office zuwa kintinkiri.
• cikakken goyon bayan harshe
• yana taimakawa tare da gyara ta hanyar nuna alamun mahallin
• ana iya samun sauƙin sarrafa darussa
  
• gaba daya kyauta

download link Ribbon XML Editan

PS

Shekaru da yawa, Microsoft ya yi watsi da masu haɓaka VBA a fili, suna la'akari da shi, a fili, ya zama ƙaramin shirye-shirye. Jita-jita lokaci-lokaci suna zamewa cewa sigar Office ta gaba ba za ta ƙara samun Visual Basic ba ko kuma za a maye gurbinsa da JavaScript. Sabbin nau'ikan Kayayyakin Kayayyakin Kayayyakin Kayayyakin Kayayyakin Kayayyakin Kayayyaki suna fitowa akai-akai tare da sabbin abubuwa masu kyau, kuma editan VBE ya makale a cikin 1997, har yanzu bai iya shigar da lambar tare da daidaitattun kayan aikin ba.

A zahiri, dubban mutane suna adana sa'o'i da kwanaki godiya ga masu shirye-shiryen VBA da ke ƙirƙirar macros don sarrafa ayyukan sarrafa bayanan ofis na yau da kullun. Duk wanda ya ga yadda macro a cikin layin code 10 ya aika fayiloli zuwa abokan ciniki 200 a cikin rabin minti daya, ya maye gurbin sa'o'i uku na aikin wauta, zai fahimce ni 🙂

Kuma more. 

Duk shirye-shiryen da ke sama zabi na ne kawai da shawarwarin kaina bisa gwaninta na kaina. Babu wani daga cikin marubutan da ya nemi tallata kuma bai biya shi ba (kuma ba zan ɗauka ba, bisa ka'ida). Idan kuna da wani abu don ƙarawa zuwa jerin da ke sama - maraba da sharhi, ɗan adam mai godiya ba zai kasance cikin bashi ba.