Cuprins
Chiar dacă nu știți să programați, există multe locuri (cărți, site-uri web, forumuri) unde puteți găsi cod macro VBA gata făcut pentru un număr mare de sarcini tipice în Excel. Din experiența mea, majoritatea utilizatorilor își colectează mai devreme sau mai târziu colecția personală de macrocomenzi pentru a automatiza procesele de rutină, fie că este vorba de traducerea formulelor în valori, afișarea sumelor în cuvinte sau însumarea celulelor după culoare. Și aici apare problema - codul macro din Visual Basic trebuie să fie stocat undeva pentru a fi folosit mai târziu în muncă.
Cea mai ușoară opțiune este să salvați codul macro direct în fișierul de lucru, accesând editorul Visual Basic folosind comanda rapidă de la tastatură Alt+F11 și adăugarea unui nou modul gol prin meniu Inserare – Modul:
Cu toate acestea, există mai multe dezavantaje cu această metodă:
- Dacă există o mulțime de fișiere de lucru și este nevoie de o macrocomandă peste tot, cum ar fi o macrocomandă pentru conversia formulelor în valori, atunci va trebui să copiați codul în fiecare carte.
- Nu trebuie uitat salvați fișierul în format macro-activat (xlsm) sau în format de carte binar (xlsb).
- La deschiderea unui astfel de fișier protecția macro va emite de fiecare dată un avertisment care trebuie confirmat (bine, sau dezactivează complet protecția, ceea ce poate să nu fie întotdeauna de dorit).
O soluție mai elegantă ar fi crearea propriul program de completare (Excel Add-in) – un fișier separat cu un format special (xlam) care conține toate macrocomenzile dumneavoastră „favorite”. Avantajele acestei abordări:
- Va fi suficient conectați suplimentul o dată în Excel – și puteți utiliza procedurile și funcțiile sale VBA în orice fișier de pe acest computer. Prin urmare, nu este necesară resalvarea fișierelor de lucru în formatele xlsm și xlsb, deoarece. codul sursă nu va fi stocat în ele, ci în fișierul de completare.
- Protecţie nici macro-urile nu te vor deranja. suplimentele sunt, prin definiție, surse de încredere.
- Pot face filă separată pe panglica Excel cu butoane frumoase pentru a rula macrocomenzi de completare.
- Suplimentul este un fișier separat. A lui ușor de transportat de la computer la computer, împărtășește-l cu colegii sau chiar vinde-l 😉
Să parcurgem pas cu pas întregul proces de creare a propriului program de completare Microsoft Excel.
Pasul 1. Creați un fișier de completare
Deschideți Microsoft Excel cu un registru de lucru gol și salvați-l sub orice nume adecvat (de exemplu MyExcelAddin) în format de completare cu comanda Fișier – Salvare ca sau chei F12, specificând tipul fișierului Add-in Excel:
Vă rugăm să rețineți că, în mod implicit, Excel stochează suplimente în folderul C:UsersYour_nameAppDataRoamingMicrosoftAddIns, dar, în principiu, puteți specifica orice alt folder care vă este convenabil.
Pasul 2. Conectam add-in-ul creat
Acum, programul de completare pe care l-am creat la ultimul pas MyExcelAddin trebuie să fie conectat la Excel. Pentru a face acest lucru, accesați meniul Fișier – Opțiuni – Suplimente (Fișier — Opțiuni — Suplimente), faceți clic pe butonul Despre Noi (Merge) în partea de jos a ferestrei. În fereastra care se deschide, faceți clic pe butonul Recenzie (Naviga) și specificați locația fișierului nostru de completare.
Dacă ai făcut totul bine, atunci nostru MyExcelAddin ar trebui să apară în lista de suplimente disponibile:
Pasul 3. Adăugați macrocomenzi la programul de completare
Suplimentul nostru este conectat la Excel și funcționează cu succes, dar încă nu există o singură macrocomandă în el. Hai să-l umplem. Pentru a face acest lucru, deschideți editorul Visual Basic cu comanda rapidă de la tastatură Alt+F11 sau prin buton Visual Basic fila dezvoltator (Dezvoltator). Dacă file dezvoltator nu este vizibil, poate fi afișat prin intermediul Fișier – Opțiuni – Configurare panglică (Fișier — Opțiuni — Personalizare panglică).
Ar trebui să existe o fereastră în colțul din stânga sus al editorului Proiect (dacă nu este vizibil, atunci porniți-l prin meniu Vizualizare — Explorator de proiecte):
Această fereastră afișează toate registrele de lucru deschise și completările care rulează Microsoft Excel, inclusiv ale noastre. VBAProject (MyExcelAddin.xlam) Selectați-l cu mouse-ul și adăugați un nou modul la el prin meniu Inserare – Modul. În acest modul, vom stoca codul VBA al macrocomenzilor noastre de completare.
Puteți fie să tastați codul de la zero (dacă știți să programați), fie să îl copiați de undeva gata făcut (ceea ce este mult mai ușor). Să introducem, pentru testare, codul unei macrocomenzi simple, dar utile în modulul gol adăugat:
După introducerea codului, nu uitați să faceți clic pe butonul de salvare (dischetă) din colțul din stânga sus.
Macro-ul nostru FormuleLaValori, așa cum vă puteți imagina cu ușurință, convertește formulele în valori într-un interval preselectat. Uneori, aceste macrocomenzi sunt numite și Proceduri. Pentru a-l rula, trebuie să selectați celule cu formule și să deschideți o casetă de dialog specială Macrocomenzi din filă dezvoltator (Dezvoltator – Macrocomenzi) sau scurtătură de la tastatură Alt+F8. În mod normal, această fereastră arată macrocomenzi disponibile din toate registrele de lucru deschise, dar macrocomenzile suplimentare nu sunt vizibile aici. În ciuda acestui fapt, putem introduce numele procedurii noastre în câmp nume macro (nume macro)apoi faceți clic pe buton Alerga (alerga) – și macro-ul nostru va funcționa:
| |
Aici puteți, de asemenea, să atribuiți o comandă rapidă de la tastatură pentru a lansa rapid o macrocomandă - butonul este responsabil pentru aceasta parametrii (Opțiuni) în fereastra anterioară Macro:
Când atribuiți taste, rețineți că acestea sunt sensibile la majuscule și la aspectul tastaturii. Deci, dacă atribuiți o combinație like Ctrl+Й, apoi, de fapt, pe viitor va trebui să vă asigurați că aveți aspectul activat și apăsați suplimentar Schimbapentru a obține litera mare.
Pentru comoditate, putem adăuga și un buton pentru macrocomandă în bara de instrumente cu acces rapid din colțul din stânga sus al ferestrei. Pentru a face acest lucru, selectați Fișier – Opțiuni – Bara de instrumente Acces rapid (Fișier — Opțiuni — Personalizare bară de instrumente de acces rapid), iar apoi în lista derulantă din partea de sus a ferestrei opțiunea Macrocomenzi. După aceea, macro-ul nostru FormuleLaValori poate fi plasat pe panou cu butonul Adăuga (Adăugați) și selectați o pictogramă pentru aceasta cu butonul Schimba (Editați | ×):
Pasul 4. Adăugați funcții la supliment
dar macro-proceduri, există, de asemenea macrofuncții sau cum se numesc UDF (Funcție definită de utilizator = funcție definită de utilizator). Să creăm un modul separat în suplimentul nostru (comandă de meniu Inserare – Modul) și lipiți acolo codul următoarei funcții:
Este ușor de observat că această funcție este necesară pentru a extrage TVA din sumă inclusiv TVA. Nu binomul lui Newton, desigur, dar ne va ajuta ca exemplu pentru a arăta principiile de bază.
Rețineți că sintaxa unei funcții este diferită de o procedură:
- se foloseste constructia Funcția…. Funcția de sfârșit in schimb Sub... End Sub
- după numele funcției, argumentele acesteia sunt indicate între paranteze
- în corpul funcției, se efectuează calculele necesare și apoi rezultatul este atribuit unei variabile cu numele funcției
De asemenea, rețineți că această funcție nu este necesară și este imposibil să rulați ca procedura anterioară macro prin caseta de dialog Macrocomenzi și buton Alerga. O astfel de funcție macro ar trebui să fie folosită ca o funcție standard de foaie de lucru (SUM, IF, VLOOKUP...), adică doar introduceți în orice celulă, specificând valoarea sumei cu TVA ca argument:
… sau intrați prin caseta de dialog standard pentru inserarea unei funcții (buton fx în bara de formule), selectând o categorie Definit de utilizator (Definit de utilizator):
Singurul moment neplăcut aici este absența descrierii obișnuite a funcției din partea de jos a ferestrei. Pentru a-l adăuga, va trebui să faceți următoarele:
- Deschideți Visual Basic Editor cu o comandă rapidă de la tastatură Alt+F11
- Selectați programul de completare în panoul Proiect și apăsați tasta F2pentru a deschide fereastra Object Browser
- Selectați proiectul de completare din lista derulantă din partea de sus a ferestrei
- Faceți clic dreapta pe funcția care apare și selectați comanda Proprietăţi.
- Introduceți o descriere a funcției în fereastră Descriere
- Salvați fișierul de completare și reporniți excel.
După repornire, funcția ar trebui să afișeze descrierea pe care am introdus-o:
Pasul 5. Creați o filă de supliment în interfață
Atingerea finală, deși nu obligatorie, dar plăcută va fi crearea unei file separate cu un buton pentru a rula macrocomandă, care va apărea în interfața Excel după conectarea suplimentului nostru.
Informațiile despre filele care sunt afișate implicit sunt conținute în carte și trebuie formatate într-un cod XML special. Cel mai simplu mod de a scrie și edita un astfel de cod este cu ajutorul unor programe speciale – editori XML. Unul dintre cele mai convenabile (și gratuite) este programul lui Maxim Novikov Editor XML de panglică.
Algoritmul de lucru cu acesta este următorul:
- Închideți toate ferestrele Excel, astfel încât să nu existe un conflict de fișiere atunci când edităm codul XML al suplimentului.
- Lansați programul Ribbon XML Editor și deschideți fișierul nostru MyExcelAddin.xlam în el
- Cu buton file în colțul din stânga sus, adăugați fragmentul de cod pentru noua filă:
- Trebuie să puneți ghilimele goale id fila și grupul nostru (orice identificatori unici) și în etichetă – numele filei noastre și un grup de butoane de pe ea:
- Cu buton buton în panoul din stânga, adăugați un cod gol pentru buton și adăugați etichete la acesta:
- eticheta este textul de pe buton
- imagineMso — acesta este numele condiționat al imaginii de pe buton. Am folosit o pictogramă cu butonul roșu numit AnimationCustomAddExitDialog. Numele tuturor butoanelor disponibile (și există câteva sute de ele!) pot fi găsite pe un număr mare de site-uri de pe Internet dacă căutați cuvintele cheie „imageMso”. Pentru început, puteți merge aici.
- onAction – acesta este numele procedurii de apel invers – o macrocomandă scurtă specială care va rula macrocomandă principală FormuleLaValori. Puteți numi această procedură cum doriți. O vom adauga putin mai tarziu.
- Puteți verifica corectitudinea a tot ceea ce s-a făcut folosind butonul cu o bifă verde în partea de sus a barei de instrumente. În același loc, faceți clic pe butonul cu o dischetă pentru a salva toate modificările.
- Închideți Editorul XML de panglică
- Deschideți Excel, accesați editorul Visual Basic și adăugați o procedură de apel invers la macrocomandă KillFormulasastfel încât să ruleze macrocomandă principală pentru înlocuirea formulelor cu valori.
- Salvăm modificările și, revenind la Excel, verificăm rezultatul:
Asta e tot – suplimentul este gata de utilizare. Completați-l cu propriile proceduri și funcții, adăugați butoane frumoase - și va deveni mult mai ușor să utilizați macrocomenzi în munca dvs.
- Ce sunt macrocomenzile, cum să le folosiți în munca dvs., de unde să obțineți codul macro în Visual Basic.
- Cum să faci un ecran de deschidere când deschideți un registru de lucru în Excel
- Ce este o carte personală cu macro-uri și cum să o folosești