Nilalaman
Kahit na hindi mo alam kung paano mag-program, maraming mga lugar (mga libro, website, forum) kung saan makakahanap ka ng yari na VBA macro code para sa isang malaking bilang ng mga tipikal na gawain sa Excel. Sa aking karanasan, ang karamihan sa mga user ay maaga o huli nangongolekta ng kanilang personal na koleksyon ng mga macro upang i-automate ang mga nakagawiang proseso, ito man ay pagsasalin ng mga formula sa mga halaga, pagpapakita ng mga kabuuan sa mga salita, o pagsusuma ng mga cell ayon sa kulay. At dito lumitaw ang problema - ang macro code sa Visual Basic ay kailangang maimbak sa isang lugar upang magamit sa ibang pagkakataon sa trabaho.
Ang pinakamadaling opsyon ay ang direktang i-save ang macro code sa gumaganang file sa pamamagitan ng pagpunta sa Visual Basic editor gamit ang keyboard shortcut Alt+F11 at pagdaragdag ng bagong walang laman na module sa pamamagitan ng menu Ipasok – Module:
Gayunpaman, mayroong ilang mga kawalan sa pamamaraang ito:
- Kung maraming gumaganang file, at kailangan ng macro sa lahat ng dako, gaya ng macro para sa pag-convert ng mga formula sa mga value, kakailanganin mong kopyahin ang code sa bawat libro.
- Hindi dapat kalimutan i-save ang file sa macro-enabled na format (xlsm) o sa binary book na format (xlsb).
- Kapag binubuksan ang naturang file proteksyon ng macro ay sa bawat oras na magbibigay ng babala na kailangang kilalanin (mabuti, o ganap na huwag paganahin ang proteksyon, na maaaring hindi palaging kanais-nais).
Ang isang mas eleganteng solusyon ay ang lumikha iyong sariling add-in (Excel Add-in) – isang hiwalay na file ng isang espesyal na format (xlam) na naglalaman ng lahat ng iyong "paboritong" macro. Ang mga bentahe ng diskarteng ito:
- Ito ay sapat na ikonekta ang add-on nang isang beses sa Excel – at maaari mong gamitin ang mga pamamaraan at function ng VBA nito sa anumang file sa computer na ito. Ang muling pag-save ng iyong gumaganang mga file sa xlsm- at xlsb-format, sa gayon, ay hindi kinakailangan, dahil. ang source code ay hindi maiimbak sa kanila, ngunit sa add-in na file.
- proteksyon hindi ka rin maaabala ng mga macro. Ang mga add-on ay, ayon sa kahulugan, mga pinagkakatiwalaang mapagkukunan.
- Kaya hiwalay na tab sa Excel ribbon na may magagandang button para magpatakbo ng mga add-in na macro.
- Ang add-in ay isang hiwalay na file. Ang kanyang madaling dalhin mula sa computer patungo sa computer, ibahagi ito sa mga kasamahan o kahit na ibenta ito 😉
Maglakad tayo sa buong proseso ng paggawa ng sarili mong Microsoft Excel add-in nang hakbang-hakbang.
Hakbang 1. Gumawa ng add-in na file
Buksan ang Microsoft Excel gamit ang isang blangkong workbook at i-save ito sa ilalim ng anumang angkop na pangalan (halimbawa MyExcelAddin) sa add-in na format na may command File – I-save Bilang o mga susi F12, na tumutukoy sa uri ng file Excel Add-in:
Pakitandaan na bilang default, nag-iimbak ang Excel ng mga add-in sa folder na C:UsersYour_nameAppDataRoamingMicrosoftAddIns, ngunit, sa prinsipyo, maaari mong tukuyin ang anumang ibang folder na maginhawa para sa iyo.
Hakbang 2. Ikinonekta namin ang nilikhang add-in
Ngayon ang add-in na ginawa namin sa huling hakbang MyExcelAddin dapat na konektado sa Excel. Upang gawin ito, pumunta sa menu File – Mga Opsyon – Mga Add-on (File — Mga Pagpipilian — Mga Add-In), i-click ang button tungkol sa (Go) sa ibaba ng bintana. Sa window na bubukas, i-click ang button Pagsusuri (Browse) at tukuyin ang lokasyon ng aming add-in na file.
Kung ginawa mo ang lahat ng tama, ang aming MyExcelAddin dapat lumabas sa listahan ng mga available na add-on:
Hakbang 3. Magdagdag ng mga macro sa add-in
Ang aming add-in ay konektado sa Excel at matagumpay na gumagana, ngunit wala pang isang macro dito. Punan natin ito. Upang gawin ito, buksan ang Visual Basic editor gamit ang keyboard shortcut Alt+F11 o sa pamamagitan ng pindutan Visual Basic tab developer (Developer). Kung tabs developer hindi nakikita, maaari itong ipakita sa pamamagitan ng File – Opsyon – Ribbon Setup (File — Mga Pagpipilian — I-customize ang Ribbon).
Dapat mayroong isang window sa itaas na kaliwang sulok ng editor proyekto (kung hindi ito nakikita, pagkatapos ay i-on ito sa pamamagitan ng menu Tingnan — Project Explorer):
Ipinapakita ng window na ito ang lahat ng bukas na workbook at nagpapatakbo ng mga add-in ng Microsoft Excel, kabilang ang sa amin. VBAProject (MyExcelAddin.xlam) Piliin ito gamit ang mouse at magdagdag ng bagong module dito sa pamamagitan ng menu Ipasok – Module. Sa modyul na ito, iimbak namin ang VBA code ng aming mga add-in na macro.
Maaari mong i-type ang code mula sa simula (kung alam mo kung paano mag-program), o kopyahin ito mula sa isang lugar na handa na (na mas madali). Tayo, para sa pagsubok, ipasok ang code ng isang simple ngunit kapaki-pakinabang na macro sa idinagdag na walang laman na module:
Matapos ipasok ang code, huwag kalimutang mag-click sa pindutan ng pag-save (disket) sa kaliwang sulok sa itaas.
Ang aming macro FormulasToValues, gaya ng madali mong maiisip, nagko-convert ng mga formula sa mga halaga sa isang preselected range. Minsan tinatawag din ang mga macro na ito pamamaraan. Upang patakbuhin ito, kailangan mong pumili ng mga cell na may mga formula at magbukas ng isang espesyal na dialog box Macros mula sa tab developer (Developer — Macros) o keyboard shortcut Alt+F8. Karaniwan, ang window na ito ay nagpapakita ng mga available na macro mula sa lahat ng bukas na workbook, ngunit ang mga add-in na macro ay hindi makikita dito. Sa kabila nito, maaari naming ipasok ang pangalan ng aming pamamaraan sa field macro name (Macro name)at pagkatapos ay i-click ang pindutan Tumakbo (tumakbo) – at gagana ang aming macro:
| |
Dito maaari ka ring magtalaga ng keyboard shortcut upang mabilis na maglunsad ng macro – ang button ay responsable para dito parameter (Mga Opsyon) sa nakaraang window Macro:
Kapag nagtatalaga ng mga key, tandaan na ang mga ito ay case sensitive at keyboard layout sensitive. Kaya kung magtatalaga ka ng isang kumbinasyon tulad ng Ctrl+Й, pagkatapos, sa katunayan, sa hinaharap ay kailangan mong tiyakin na naka-on ang layout at pindutin ang karagdagan Ilipatpara makuha ang malaking titik.
Para sa kaginhawahan, maaari din kaming magdagdag ng isang button para sa aming macro sa quick access toolbar sa kaliwang sulok sa itaas ng window. Upang gawin ito, piliin File – Mga Opsyon – Mabilis na Access Toolbar (File — Mga Opsyon — I-customize ang Quick Access Toolbar), at pagkatapos ay sa drop-down na listahan sa tuktok ng window ang opsyon Macros. Pagkatapos nito ang aming macro FormulasToValues maaaring ilagay sa panel gamit ang pindutan Idagdag (Magdagdag) at pumili ng icon para dito gamit ang button Baguhin (I-edit):
Hakbang 4. Magdagdag ng mga function sa add-in
pero macro-procedures, meron din mga function na macro o gaya ng tawag sa kanila UDF (Function na Tinukoy ng User = function na tinukoy ng gumagamit). Gumawa tayo ng hiwalay na module sa ating add-on (menu command Ipasok – Module) at i-paste ang code ng sumusunod na function doon:
Madaling makita na ang function na ito ay kailangan upang kunin ang VAT mula sa halaga kasama ang VAT. Hindi binomial ni Newton, siyempre, ngunit ito ay gagawin para sa atin bilang isang halimbawa upang ipakita ang mga pangunahing prinsipyo.
Tandaan na ang syntax ng isang function ay iba sa isang procedure:
- ginagamit ang konstruksiyon Function…. End Function sa halip Sub ... End Sub
- pagkatapos ng pangalan ng function, ang mga argumento nito ay ipinahiwatig sa mga bracket
- sa katawan ng function, ang mga kinakailangang kalkulasyon ay isinasagawa at pagkatapos ay ang resulta ay itinalaga sa isang variable na may pangalan ng function.
Tandaan din na hindi kailangan ang function na ito, at imposibleng tumakbo tulad ng nakaraang macro procedure sa pamamagitan ng dialog box Macros at pindutan Tumakbo. Ang naturang macro function ay dapat gamitin bilang isang standard worksheet function (SUM, IF, VLOOKUP...), ibig sabihin, ipasok lamang sa anumang cell, na tumutukoy sa halaga ng halaga na may VAT bilang argumento:
… o pumasok sa karaniwang dialog box para sa pagpasok ng isang function (button fx sa formula bar), pagpili ng kategorya Tinukoy ng Gumagamit (Tukoy ng User):
Ang tanging hindi kasiya-siyang sandali dito ay ang kawalan ng karaniwang paglalarawan ng pag-andar sa ibaba ng window. Upang idagdag ito kailangan mong gawin ang sumusunod:
- Buksan ang Visual Basic Editor gamit ang isang keyboard shortcut Alt+F11
- Piliin ang add-in sa Project panel at pindutin ang key F2upang buksan ang window ng Object Browser
- Piliin ang iyong add-in na proyekto mula sa drop-down na listahan sa itaas ng window
- Mag-right-click sa function na lilitaw at piliin ang command Mga Katangian.
- Maglagay ng paglalarawan ng function sa window paglalarawan
- I-save ang add-in na file at i-restart ang excel.
Pagkatapos mag-restart, dapat ipakita ng function ang paglalarawan na ipinasok namin:
Hakbang 5. Gumawa ng add-on na tab sa interface
Ang pangwakas, bagaman hindi sapilitan, ngunit kaaya-ayang pagpindot ay ang paglikha ng isang hiwalay na tab na may isang pindutan upang patakbuhin ang aming macro, na lilitaw sa interface ng Excel pagkatapos ikonekta ang aming add-in.
Ang impormasyon tungkol sa mga tab na ipinapakita bilang default ay nasa loob ng aklat at dapat na naka-format sa isang espesyal na XML code. Ang pinakamadaling paraan upang magsulat at mag-edit ng naturang code ay sa tulong ng mga espesyal na programa - mga editor ng XML. Ang isa sa mga pinaka-maginhawa (at libre) ay ang programa ni Maxim Novikov Ribbon XML Editor.
Ang algorithm para sa pagtatrabaho dito ay ang mga sumusunod:
- Isara ang lahat ng Excel windows para walang conflict sa file kapag na-edit namin ang add-in na XML code.
- Ilunsad ang Ribbon XML Editor program at buksan ang aming MyExcelAddin.xlam file dito
- Gamit ang pindutan tabs sa kaliwang sulok sa itaas, idagdag ang code snippet para sa bagong tab:
- Kailangan mong maglagay ng walang laman na mga panipi id aming tab at pangkat (anumang natatanging identifier), at sa etiketa – ang mga pangalan ng aming tab at isang pangkat ng mga button dito:
- Gamit ang pindutan butones sa kaliwang panel, magdagdag ng blangkong code para sa button at magdagdag ng mga tag dito:
- label ay ang teksto sa pindutan
— larawanMso — ito ang kondisyong pangalan ng larawan sa button. Gumamit ako ng icon na pulang button na tinatawag na AnimationCustomAddExitDialog. Ang mga pangalan ng lahat ng magagamit na mga pindutan (at mayroong ilang daang mga ito!) ay matatagpuan sa isang malaking bilang ng mga site sa Internet kung hahanapin mo ang mga keyword na "imageMso". Para sa simula, maaari kang pumunta dito.
- onAction – ito ang pangalan ng callback procedure – isang espesyal na maikling macro na magpapatakbo sa aming pangunahing macro FormulasToValues. Maaari mong tawagan ang pamamaraang ito kahit anong gusto mo. Idagdag namin ito mamaya.
- Maaari mong suriin ang kawastuhan ng lahat ng ginawa gamit ang button na may berdeng check mark sa tuktok ng toolbar. Sa parehong lugar, mag-click sa pindutan na may floppy disk upang i-save ang lahat ng mga pagbabago.
- Isara ang Ribbon XML Editor
- Buksan ang Excel, pumunta sa Visual Basic editor at magdagdag ng callback procedure sa aming macro KillFormulasupang patakbuhin nito ang aming pangunahing macro para sa pagpapalit ng mga formula ng mga halaga.
- Ise-save namin ang mga pagbabago at, pagbalik sa Excel, suriin ang resulta:
Iyon lang – handa nang gamitin ang add-in. Punan ito ng sarili mong mga procedure at function, magdagdag ng magagandang button – at magiging mas madali itong gumamit ng mga macro sa iyong trabaho.
- Ano ang mga macro, kung paano gamitin ang mga ito sa iyong trabaho, kung saan makakakuha ng macro code sa Visual Basic.
- Paano gumawa ng splash screen kapag nagbubukas ng workbook sa Excel
- Ano ang Personal Macro Book at kung paano ito gamitin