contingut
Fins i tot si no sabeu programar, hi ha molts llocs (llibres, llocs web, fòrums) on podeu trobar codi de macro VBA preparat per a un gran nombre de tasques típiques a Excel. Segons la meva experiència, la majoria dels usuaris tard o d'hora recullen la seva col·lecció personal de macros per automatitzar els processos rutinaris, ja sigui traduint fórmules en valors, mostrant sumes en paraules o sumant cel·les per color. I aquí sorgeix el problema: el codi macro de Visual Basic s'ha d'emmagatzemar en algun lloc per poder utilitzar-lo més tard al treball.
L'opció més senzilla és desar el codi de macro directament al fitxer de treball anant a l'editor de Visual Basic mitjançant la drecera de teclat Alt+F11 i afegint un nou mòdul buit a través del menú Inserir – Mòdul:
Tanmateix, aquest mètode té diversos desavantatges:
- Si hi ha molts fitxers de treball i es necessita una macro a tot arreu, com ara una macro per convertir fórmules en valors, haureu de copiar el codi. en cada llibre.
- No s'ha d'oblidar desar el fitxer en format habilitat per a macros (xlsm) o en format de llibre binari (xlsb).
- En obrir aquest fitxer protecció macro cada vegada emetrà un avís que cal reconèixer (bé, o desactivarà completament la protecció, cosa que pot no ser sempre desitjable).
Una solució més elegant seria crear el vostre propi complement (complement d'Excel) – un fitxer separat d'un format especial (xlam) que conté totes les vostres macros "favorides". Els avantatges d'aquest enfocament:
- Serà suficient connecteu el complement una vegada a Excel, i podeu utilitzar els seus procediments i funcions VBA en qualsevol fitxer d'aquest ordinador. Per tant, no cal tornar a desar els fitxers de treball en formats xlsm i xlsb, perquè. el codi font no s'emmagatzemarà en ells, sinó al fitxer del complement.
- protecció les macros tampoc us molestaran. Els complements són, per definició, fonts de confiança.
- Poder fer pestanya separada a la cinta d'Excel amb botons agradables per executar macros de complement.
- El complement és un fitxer independent. Seva fàcil de portar d'ordinador a ordinador, compartir-lo amb els companys o fins i tot vendre'l 😉
Repassem tot el procés de creació del vostre propi complement de Microsoft Excel pas a pas.
Pas 1. Creeu un fitxer de complement
Obriu Microsoft Excel amb un llibre de treball en blanc i deseu-lo amb qualsevol nom adequat (per exemple MyExcelAddin) en format de complement amb l'ordre Fitxer - Desa com o claus F12, especificant el tipus de fitxer Complement d'Excel:
Tingueu en compte que, de manera predeterminada, Excel emmagatzema complements a la carpeta C:UsersYour_nameAppDataRoamingMicrosoftAddIns, però, en principi, podeu especificar qualsevol altra carpeta que us convingui.
Pas 2. Connectem el complement creat
Ara el complement que hem creat a l'últim pas MyExcelAddin ha d'estar connectat a Excel. Per fer-ho, aneu al menú Fitxer – Opcions – Complements (Fitxer — Opcions — Complements), feu clic al botó Qui som? (Vés) a la part inferior de la finestra. A la finestra que s'obre, feu clic al botó Crítica (Navega) i especifiqueu la ubicació del nostre fitxer de complement.
Si ho has fet tot bé, el nostre MyExcelAddin hauria d'aparèixer a la llista de complements disponibles:
Pas 3. Afegiu macros al complement
El nostre complement està connectat a Excel i funciona correctament, però encara no hi ha cap macro. Omplim-lo. Per fer-ho, obriu l'editor de Visual Basic amb la drecera de teclat Alt+F11 o amb botó Visual Basic llengüeta revelador (Desenvolupador). Si pestanyes revelador no visible, es pot mostrar a través Fitxer – Opcions – Configuració de la cinta (Fitxer — Opcions — Personalitza la cinta).
Hi hauria d'haver una finestra a la cantonada superior esquerra de l'editor projecte (si no és visible, activeu-lo a través del menú Visualització — Explorador de projectes):
Aquesta finestra mostra tots els llibres de treball oberts i complements de Microsoft Excel en execució, inclòs el nostre. Projecte VBA (MyExcelAddin.xlam) Seleccioneu-lo amb el ratolí i afegiu-hi un mòdul nou mitjançant el menú Inserir – Mòdul. En aquest mòdul, emmagatzemarem el codi VBA de les nostres macros de complement.
Podeu escriure el codi des de zero (si sabeu programar) o copiar-lo des d'algun lloc ja fet (que és molt més fàcil). Per provar, introduïm el codi d'una macro senzilla però útil al mòdul buit afegit:
Després d'introduir el codi, no oblideu fer clic al botó desar (disquet) a la cantonada superior esquerra.
La nostra macro FórmulesAValors, com podeu imaginar fàcilment, converteix fórmules en valors en un interval preseleccionat. De vegades, aquestes macros també s'anomenen procediments. Per executar-lo, heu de seleccionar cel·les amb fórmules i obrir un quadre de diàleg especial Macros de la pestanya revelador (Desenvolupador - Macros) o drecera de teclat Alt+F8. Normalment, aquesta finestra mostra les macros disponibles de tots els llibres de treball oberts, però les macros complementàries no són visibles aquí. Malgrat això, podem introduir el nom del nostre procediment al camp nom de la macro (Nom de la macro)i després feu clic al botó Correr (correr) – i la nostra macro funcionarà:
| |
Aquí també podeu assignar una drecera de teclat per llançar ràpidament una macro; el botó és responsable d'això Paràmetres (Opcions) a la finestra anterior Macro:
Quan assigneu tecles, tingueu en compte que distingeixen entre majúscules i minúscules i la disposició del teclat. Així que si assigneu una combinació com Ctrl+Й, llavors, de fet, en el futur hauràs d'assegurar-te que tens el disseny activat i prémer addicionalment Canviarper obtenir la majúscula.
Per comoditat, també podem afegir un botó per a la nostra macro a la barra d'eines d'accés ràpid a la cantonada superior esquerra de la finestra. Per fer-ho, seleccioneu Fitxer – Opcions – Barra d'eines d'accés ràpid (Fitxer — Opcions — Personalitza la barra d'eines d'accés ràpid), i després a la llista desplegable de la part superior de la finestra l'opció Macros. Després d'això, la nostra macro FórmulesAValors es pot col·locar al panell amb el botó Add (Afegir) i seleccioneu-ne una icona amb el botó Canviar (Edita):
Pas 4. Afegiu funcions al complement
Sinó macroprocediments, hi ha també macros de funcions o com es diuen UDF (Funció definida per l'usuari = funció definida per l'usuari). Creem un mòdul separat al nostre complement (ordre de menú Inserir – Mòdul) i enganxeu-hi el codi de la funció següent:
És fàcil veure que aquesta funció és necessària per extreure l'IVA de l'import amb IVA inclòs. No és el binomi de Newton, és clar, però ens servirà com a exemple per mostrar els principis bàsics.
Tingueu en compte que la sintaxi d'una funció és diferent d'un procediment:
- s'utilitza la construcció Funció…. Funció final en lloc Sub... Final Sub
- després del nom de la funció, els seus arguments s'indiquen entre parèntesis
- al cos de la funció, es realitzen els càlculs necessaris i després s'assigna el resultat a una variable amb el nom de la funció
Tingueu en compte també que aquesta funció no és necessària i que és impossible executar-se com el procediment de macro anterior a través del quadre de diàleg Macros i botó Correr. Aquesta funció macro s'hauria d'utilitzar com a funció de full de treball estàndard (SUMA, SI, BUSCARV...), és a dir, només cal introduir en qualsevol cel·la, especificant el valor de l'import amb l'IVA com a argument:
... o entreu a través del quadre de diàleg estàndard per inserir una funció (botó fx a la barra de fórmules), seleccionant una categoria Usuari definit (Usuari definit):
L'únic moment desagradable aquí és l'absència de la descripció habitual de la funció a la part inferior de la finestra. Per afegir-lo haureu de fer el següent:
- Obriu l'Editor de Visual Basic amb una drecera de teclat Alt+F11
- Seleccioneu el complement al tauler Projecte i premeu la tecla F2per obrir la finestra del navegador d'objectes
- Seleccioneu el vostre projecte de complement a la llista desplegable de la part superior de la finestra
- Feu clic amb el botó dret a la funció que apareix i seleccioneu l'ordre Propietats.
- Introduïu una descripció de la funció a la finestra Descripció
- Deseu el fitxer del complement i reinicieu l'excel.
Després de reiniciar, la funció hauria de mostrar la descripció que hem introduït:
Pas 5. Creeu una pestanya de complement a la interfície
El toc final, encara que no obligatori, però agradable, serà la creació d'una pestanya separada amb un botó per executar la nostra macro, que apareixerà a la interfície d'Excel després de connectar el nostre complement.
La informació sobre les pestanyes que es mostren per defecte es troba dins del llibre i s'ha de formatar en un codi XML especial. La manera més fàcil d'escriure i editar aquest codi és amb l'ajuda de programes especials: editors XML. Un dels més convenients (i gratuïts) és el programa de Maxim Novikov Editor XML de la cinta.
L'algorisme per treballar-hi és el següent:
- Tanqueu totes les finestres d'Excel perquè no hi hagi cap conflicte de fitxers quan editem el codi XML del complement.
- Inicieu el programa Ribbon XML Editor i obriu-hi el nostre fitxer MyExcelAddin.xlam
- Amb botó pestanyes a l'extrem superior esquerre, afegiu el fragment de codi per a la pestanya nova:
- Heu de posar cometes buides id la nostra pestanya i grup (qualsevol identificador únic) i a etiqueta – els noms de la nostra pestanya i un grup de botons sobre ella:
- Amb botó botó al tauler esquerre, afegiu un codi en blanc per al botó i afegiu-hi etiquetes:
- etiqueta és el text del botó
- imatgeMso — aquest és el nom condicional de la imatge del botó. Vaig utilitzar una icona de botó vermell anomenada AnimationCustomAddExitDialog. Els noms de tots els botons disponibles (i n'hi ha diversos centenars!) es poden trobar en un gran nombre de llocs a Internet si cerqueu les paraules clau "imageMso". Per començar, podeu anar aquí.
- a l'acció – aquest és el nom del procediment de devolució de trucada – una macro breu especial que executarà la nostra macro principal FórmulesAValors. Podeu trucar a aquest procediment com vulgueu. L'afegirem una mica més tard.
- Podeu comprovar la correcció de tot el fet mitjançant el botó amb una marca de verificació verda a la part superior de la barra d'eines. Al mateix lloc, feu clic al botó amb un disquet per desar tots els canvis.
- Tanqueu l'Editor XML de la cinta
- Obriu Excel, aneu a l'editor de Visual Basic i afegiu un procediment de devolució de trucada a la nostra macro KillFormulesde manera que executa la nostra macro principal per substituir fórmules per valors.
- Desem els canvis i, tornant a Excel, comprovem el resultat:
Això és tot: el complement està llest per utilitzar. Ompliu-lo amb els vostres propis procediments i funcions, afegiu botons bonics i us serà molt més fàcil utilitzar macros al vostre treball.
- Què són les macros, com utilitzar-les en el vostre treball, on obtenir el codi de macros a Visual Basic.
- Com fer una pantalla de presentació en obrir un llibre de treball a Excel
- Què és un Macro Book personal i com utilitzar-lo