Përmbajtje
Edhe nëse nuk dini si të programoni, ka shumë vende (libra, faqe interneti, forume) ku mund të gjeni kodin makro të gatshëm VBA për një numër të madh detyrash tipike në Excel. Në përvojën time, shumica e përdoruesve herët a vonë mbledhin koleksionin e tyre personal të makrove për të automatizuar proceset rutinë, pavarësisht nëse bëhet fjalë për përkthimin e formulave në vlera, shfaqjen e shumave në fjalë ose përmbledhjen e qelizave sipas ngjyrës. Dhe këtu lind problemi - kodi makro në Visual Basic duhet të ruhet diku në mënyrë që të përdoret më vonë në punë.
Mundësia më e lehtë është të ruani kodin makro direkt në skedarin e punës duke shkuar te redaktori i Visual Basic duke përdorur shkurtoren e tastierës Ndalet+F11 dhe duke shtuar një modul të ri bosh nëpërmjet menysë Insert – Moduli:
Sidoqoftë, kjo metodë ka disa disavantazhe:
- Nëse ka shumë skedarë pune dhe kudo nevojitet një makro, siç është një makro për konvertimin e formulave në vlera, atëherë do të duhet të kopjoni kodin në çdo libër.
- Nuk duhet harruar ruaj skedarin në format të aktivizuar makro (xlsm) ose në formatin binar të librit (xlsb).
- Kur hapni një skedar të tillë mbrojtje makro do të lëshojë çdo herë një paralajmërim që duhet pranuar (mirë, ose çaktivizoni plotësisht mbrojtjen, gjë që mund të mos jetë gjithmonë e dëshirueshme).
Një zgjidhje më elegante do të ishte krijimi shtesa juaj (Shtesa Excel) – një skedar i veçantë i një formati të veçantë (xlam) që përmban të gjitha makrot tuaja "të preferuara". Përparësitë e kësaj qasjeje:
- Do të jetë e mjaftueshme lidhni shtesën një herë në Excel – dhe mund të përdorni procedurat dhe funksionet e tij VBA në çdo skedar në këtë kompjuter. Prandaj, nuk kërkohet ri-ruajtja e skedarëve tuaj të punës në formatet xlsm- dhe xlsb, sepse. kodi burimor nuk do të ruhet në to, por në skedarin shtesë.
- mbrojtje nuk do të shqetësoheni as nga makro. Shtesat janë, sipas përkufizimit, burime të besueshme.
- Mund te bej skedë e veçantë në shiritin e Excel-it me butona të këndshëm për të ekzekutuar makro shtesë.
- Shtesa është një skedar i veçantë. E tij lehtë për të kryer nga kompjuteri në kompjuter, ndajeni me kolegët apo edhe shisni 😉
Le të ecim në të gjithë procesin e krijimit të shtesës tuaj Microsoft Excel hap pas hapi.
Hapi 1. Krijoni një skedar shtesë
Hapni Microsoft Excel me një libër pune bosh dhe ruajeni nën çdo emër të përshtatshëm (për shembull MyExcelAddin) në format shtesë me komandën Skedari – Ruaj si ose çelësat F12, duke specifikuar llojin e skedarit Shtesa Excel:
Ju lutemi vini re se Excel ruan si parazgjedhje shtesa në dosjen C:UsersYour_nameAppDataRoamingMicrosoftAddIns, por, në parim, mund të specifikoni çdo dosje tjetër që është e përshtatshme për ju.
Hapi 2. Ne lidhim shtesën e krijuar
Tani shtesa që krijuam në hapin e fundit MyExcelAddin duhet të jetë i lidhur me Excel. Për ta bërë këtë, shkoni te menyja Skedar – Opsione – Shtesa (Skedar - Opsione - Shtesa), klikoni në butonin Rreth Nesh (Shko) në fund të dritares. Në dritaren që hapet, klikoni butonin Rishikim (Shfleto) dhe specifikoni vendndodhjen e skedarit tonë shtesë.
Nëse keni bërë gjithçka siç duhet, atëherë jonë MyExcelAddin duhet të shfaqet në listën e shtesave të disponueshme:
Hapi 3. Shtoni makro në shtesë
Shtesa jonë është e lidhur me Excel dhe funksionon me sukses, por nuk ka ende një makro të vetme në të. Le ta mbushim atë. Për ta bërë këtë, hapni redaktorin e Visual Basic me shkurtoren e tastierës Ndalet+F11 ose me buton Visual Basic tab zhvillues (Zhvilluesi). Nëse skedat zhvillues nuk shihet, mund të shfaqet përmes Skedari – Opsionet – Konfigurimi i shiritit (Skedar - Opsione - Personalizo shiritin).
Duhet të ketë një dritare në këndin e sipërm të majtë të redaktuesit Projekt (nëse nuk është e dukshme, atëherë ndizni përmes menysë Shiko - Projekt Explorer):
Kjo dritare shfaq të gjithë librat e hapur të punës dhe ekzekutimin e shtesave të Microsoft Excel, duke përfshirë tonat. Projekti VBA (MyExcelAddin.xlam) Zgjidhni atë me miun dhe shtoni një modul të ri në të nëpërmjet menysë Insert – Moduli. Në këtë modul, ne do të ruajmë kodin VBA të makrove tona shtesë.
Ju ose mund të shkruani kodin nga e para (nëse dini të programoni), ose ta kopjoni atë nga diku gati (gjë që është shumë më e lehtë). Le të futim, për testim, kodin e një makro të thjeshtë por të dobishme në modulin bosh të shtuar:
Pasi të keni futur kodin, mos harroni të klikoni në butonin e ruajtjes (disketa) në këndin e sipërm të majtë.
Makroja jonë FormulatToValues, siç mund ta imagjinoni lehtësisht, konverton formulat në vlera në një gamë të parazgjedhur. Ndonjëherë këto makro quhen gjithashtu Procedurat. Për ta ekzekutuar, duhet të zgjidhni qelizat me formula dhe të hapni një kuti të veçantë dialogu macros nga skeda zhvillues (Zhvilluesi - Makro) ose shkurtore të tastierës Ndalet+F8. Normalisht, kjo dritare tregon makrot e disponueshme nga të gjithë librat e hapur të punës, por makrot shtesë nuk janë të dukshme këtu. Pavarësisht kësaj, ne mund të fusim emrin e procedurës sonë në terren emër makro (Emri makro)dhe pastaj kliko butonin run (vrapim) – dhe makroja jonë do të funksionojë:
| |
Këtu mund të caktoni gjithashtu një shkurtore të tastierës për të nisur shpejt një makro - butoni është përgjegjës për këtë Parameters (Opsione) në dritaren e mëparshme Macro:
Kur caktoni tastet, mbani në mend se ato janë të ndjeshme ndaj shkronjave të vogla dhe të ndjeshme ndaj paraqitjes së tastierës. Pra, nëse caktoni një kombinim si Ctrl+Й, atëherë, në fakt, në të ardhmen do të duhet të siguroheni që ta keni të aktivizuar paraqitjen dhe të shtypni shtesë Ndryshimpër të marrë shkronjën e madhe.
Për lehtësi, ne gjithashtu mund të shtojmë një buton për makro tonë në shiritin e veglave të aksesit të shpejtë në këndin e sipërm të majtë të dritares. Për ta bërë këtë, zgjidhni Skedari – Opsionet – Shiriti i veglave me akses të shpejtë (Skedar - Opsione - Personalizo shiritin e veglave të aksesit të shpejtë), dhe më pas në listën rënëse në krye të dritares opsionin macros. Pas kësaj makroja jonë FormulatToValues mund të vendoset në panel me butonin Shtoj (Add) dhe zgjidhni një ikonë për të me butonin Ndryshim (Edit):
Hapi 4. Shtoni funksione te shtesa
Por makro-procedurat, ka edhe makro funksionet ose siç quhen UDF (Funksioni i përcaktuar nga përdoruesi = funksioni i përcaktuar nga përdoruesi). Le të krijojmë një modul të veçantë në shtesën tonë (komandën e menysë Insert – Moduli) dhe ngjisni kodin e funksionit të mëposhtëm atje:
Është e lehtë të shihet se ky funksion nevojitet për të nxjerrë TVSH-në nga shuma që përfshin TVSH-në. Jo binomi i Njutonit, sigurisht, por do të na bëjë si shembull për të treguar parimet bazë.
Vini re se sintaksa e një funksioni është e ndryshme nga një procedurë:
- përdoret ndërtimi Funksioni…. Funksioni i Fundit në vend Nën … Fund Nën
- pas emrit të funksionit, argumentet e tij tregohen në kllapa
- në trupin e funksionit kryhen llogaritjet e nevojshme dhe më pas rezultati i caktohet një ndryshoreje me emrin e funksionit.
Gjithashtu vini re se ky funksion nuk është i nevojshëm dhe është e pamundur të ekzekutohet si procedura e mëparshme makro përmes kutisë së dialogut macros dhe butonin run. Një funksion i tillë makro duhet të përdoret si funksion standard i fletës së punës (SUM, IF, VLOOKUP…), dmth thjesht futni në çdo qelizë, duke specifikuar vlerën e shumës me TVSH-në si argument:
… ose futeni përmes kutisë standarde të dialogut për futjen e një funksioni (butoni fx në shiritin e formulës), duke zgjedhur një kategori Perdorues i percaktuar (Perdorues i percaktuar):
Momenti i vetëm i pakëndshëm këtu është mungesa e përshkrimit të zakonshëm të funksionit në fund të dritares. Për ta shtuar atë, duhet të bëni sa më poshtë:
- Hapni Redaktuesin Visual Basic me një shkurtore të tastierës Ndalet+F11
- Zgjidhni shtesën në panelin e Projektit dhe shtypni tastin F2për të hapur dritaren e Shfletuesit të Objekteve
- Zgjidhni projektin tuaj shtesë nga lista rënëse në krye të dritares
- Klikoni me të djathtën në funksionin që shfaqet dhe zgjidhni komandën Pronat.
- Vendosni një përshkrim të funksionit në dritare Përshkrim
- Ruani skedarin shtesë dhe rinisni Excel.
Pas rinisjes, funksioni duhet të shfaqë përshkrimin që kemi futur:
Hapi 5. Krijo një skedë shtesë në ndërfaqe
Prekja përfundimtare, megjithëse jo e detyrueshme, por e këndshme do të jetë krijimi i një skede të veçantë me një buton për të ekzekutuar makronë tonë, e cila do të shfaqet në ndërfaqen Excel pasi të lidhni shtesën tonë.
Informacioni rreth skedave që shfaqen si parazgjedhje përmbahen brenda librit dhe duhet të formatohen në një kod të veçantë XML. Mënyra më e lehtë për të shkruar dhe modifikuar një kod të tillë është me ndihmën e programeve speciale - redaktorët XML. Një nga më të përshtatshmet (dhe falas) është programi i Maxim Novikov Redaktori i Ribbon XML.
Algoritmi për të punuar me të është si më poshtë:
- Mbyllni të gjitha dritaret e Excel në mënyrë që të mos ketë konflikt skedari kur modifikojmë kodin shtesë XML.
- Hapni programin Ribbon XML Editor dhe hapni skedarin tonë MyExcelAddin.xlam në të
- Me buton tabs në këndin e sipërm të majtë, shtoni copëzën e kodit për skedën e re:
- Duhet të vendosni thonjëza boshe id skeda dhe grupi ynë (ndonjë identifikues unik), dhe në etiketë – emrat e skedës sonë dhe një grup butonash në të:
- Me buton buton në panelin e majtë, shtoni një kod bosh për butonin dhe shtoni etiketa në të:
- etiketë është teksti në buton
- imazh Mso — ky është emri i kushtëzuar i figurës në buton. Kam përdorur një ikonë të butonit të kuq të quajtur AnimationCustomAddExitDialog. Emrat e të gjithë butonave të disponueshëm (dhe ka disa qindra prej tyre!) mund të gjenden në një numër të madh faqesh në internet nëse kërkoni për fjalët kyçe "imageMso". Për fillestarët, mund të shkoni këtu.
- mbiVeprim – ky është emri i procedurës së kthimit të thirrjes – një makro speciale e shkurtër që do të ekzekutojë makronë tonë kryesore FormulatToValues. Ju mund ta quani këtë procedurë si të dëshironi. Do ta shtojmë pak më vonë.
- Ju mund të kontrolloni korrektësinë e gjithçkaje të bërë duke përdorur butonin me një shenjë të gjelbër në krye të shiritit të veglave. Në të njëjtin vend, klikoni në butonin me një disketë për të ruajtur të gjitha ndryshimet.
- Mbyllni Redaktorin XML të Ribbon
- Hapni Excel, shkoni te redaktori i Visual Basic dhe shtoni një procedurë të kthimit të thirrjes në makro tonë KillFormulatnë mënyrë që të ekzekutojë makronë tonë kryesore për zëvendësimin e formulave me vlera.
- Ne i ruajmë ndryshimet dhe, duke u kthyer në Excel, kontrollojmë rezultatin:
Kjo është e gjitha - shtesa është gati për t'u përdorur. Plotësoni atë me procedurat dhe funksionet tuaja, shtoni butona të bukur - dhe do të bëhet shumë më e lehtë të përdorni makro në punën tuaj.
- Çfarë janë makro, si t'i përdorni ato në punën tuaj, ku të merrni kodin makro në Visual Basic.
- Si të krijoni një ekran spërkatës kur hapni një libër pune në Excel
- Çfarë është një Libër Personal Makro dhe si ta përdorim atë