Contidos
Aínda que non sabes como programar, hai moitos lugares (libros, sitios web, foros) onde podes atopar código de macro VBA preparado para un gran número de tarefas típicas en Excel. Segundo a miña experiencia, a maioría dos usuarios recollen tarde ou cedo a súa colección persoal de macros para automatizar os procesos rutineiros, xa sexa traducir fórmulas en valores, mostrar sumas en palabras ou sumando celas por cores. E aquí xorde o problema: o código macro en Visual Basic debe almacenarse nalgún lugar para poder ser usado máis tarde no traballo.
A opción máis sinxela é gardar o código macro directamente no ficheiro de traballo indo ao editor de Visual Basic usando o atallo de teclado alt+F11 e engadir un novo módulo baleiro a través do menú Inserir – Módulo:
Non obstante, este método ten varias desvantaxes:
- Se hai moitos ficheiros de traballo e se necesita unha macro en todas partes, como unha macro para converter fórmulas en valores, entón terás que copiar o código en cada libro.
- Non hai que esquecer gardar o ficheiro en formato habilitado para macros (xlsm) ou en formato de libro binario (xlsb).
- Ao abrir un ficheiro deste tipo protección macro emitirá cada vez un aviso que debe ser recoñecido (ben, ou desactivar completamente a protección, o que pode non ser sempre desexable).
Unha solución máis elegante sería crear o teu propio complemento (complemento de Excel) – un ficheiro separado dun formato especial (xlam) que contén todas as túas macros "favoritas". As vantaxes deste enfoque:
- Será suficiente conectar o complemento unha vez en Excel, e pode usar os seus procedementos e funcións VBA en calquera ficheiro deste ordenador. Polo tanto, non é necesario gardar os ficheiros de traballo en formatos xlsm e xlsb. o código fonte non se almacenará neles, senón no ficheiro do complemento.
- protección tampouco che molestarán as macros. Os complementos son, por definición, fontes fiables.
- Pode facer separador separado na cinta de Excel con botóns agradables para executar macros de complementos.
- O complemento é un ficheiro separado. O seu fácil de transporte de ordenador a ordenador, compárteo cos compañeiros ou incluso véndeo 😉
Imos percorrer todo o proceso de creación do seu propio complemento de Microsoft Excel paso a paso.
Paso 1. Crea un ficheiro de complemento
Abra Microsoft Excel cun libro de traballo en branco e gárdeo con calquera nome axeitado (por exemplo MyExcelAddin) en formato de complemento co comando Ficheiro - Gardar como ou chaves F12, especificando o tipo de ficheiro Complemento de Excel:
Teña en conta que, por defecto, Excel almacena complementos no cartafol C:UsersYour_nameAppDataRoamingMicrosoftAddIns, pero, en principio, pode especificar calquera outro cartafol que lle resulte conveniente.
Paso 2. Conectamos o complemento creado
Agora o complemento que creamos no último paso MyExcelAddin debe estar conectado a Excel. Para iso, vai ao menú Ficheiro - Opcións - Complementos (Ficheiro — Opcións — Complementos), fai clic no botón sobre (Ir) na parte inferior da xanela. Na xanela que se abre, fai clic no botón revisión (Examinar) e especifique a localización do noso ficheiro de complemento.
Se fixeches todo ben, entón o noso MyExcelAddin debería aparecer na lista de complementos dispoñibles:
Paso 3. Engade macros ao complemento
O noso complemento está conectado a Excel e funciona correctamente, pero aínda non hai ningunha macro nel. Imos enchelo. Para iso, abra o editor de Visual Basic co atallo de teclado alt+F11 ou por botón Visual Basic aba revelador (Desenvolvedor). Se fichas revelador non visible, pódese mostrar a través Ficheiro - Opcións - Configuración da cinta (Ficheiro — Opcións — Personalizar cinta).
Debería haber unha xanela na esquina superior esquerda do editor Proxecto (se non está visible, actívao a través do menú Ver — Explorador de proxectos):
Esta xanela mostra todos os libros de traballo abertos e os complementos de Microsoft Excel en execución, incluído o noso. Proxecto VBA (MyExcelAddin.xlam) Selecciónao co rato e engádelle un novo módulo a través do menú Inserir – Módulo. Neste módulo, almacenaremos o código VBA das nosas macros de complementos.
Podes escribir o código desde cero (se sabes como programalo) ou copialo desde algún lugar preparado (o que é moito máis sinxelo). Imos, para probar, introducir o código dunha macro sinxela pero útil no módulo baleiro engadido:
Despois de introducir o código, non esqueza facer clic no botón gardar (disquete) na esquina superior esquerda.
A nosa macro FormulasToValuesComo podes imaxinar facilmente, converte fórmulas en valores nun intervalo preseleccionado. Ás veces tamén se chaman estas macros procedementos. Para executalo, cómpre seleccionar celas con fórmulas e abrir unha caixa de diálogo especial Macros dende a pestana revelador (Desenvolvedor - Macros) ou atallo de teclado alt+F8. Normalmente, esta xanela mostra as macros dispoñibles de todos os libros de traballo abertos, pero as macros de complementos non están visibles aquí. A pesar diso, podemos introducir o nome do noso procedemento no campo nome da macro (Nome da macro)e logo faga clic no botón Correr (correr) - e a nosa macro funcionará:
| |
Aquí tamén pode asignar un atallo de teclado para iniciar rapidamente unha macro; o botón é o responsable diso Parámetros (Opcións) na xanela anterior Macro:
Ao asignar teclas, ten en conta que distinguen entre maiúsculas e minúsculas e que distinguen a disposición do teclado. Entón, se asigna unha combinación como Ctrl+Й, then, in fact, in the future you will have to make sure that you have the layout turned on and press additionally Desprazarsepara obter a letra maiúscula.
Para maior comodidade, tamén podemos engadir un botón para a nosa macro á barra de ferramentas de acceso rápido na esquina superior esquerda da xanela. Para iso, seleccione Ficheiro - Opcións - Barra de ferramentas de acceso rápido (Ficheiro — Opcións — Personalizar barra de ferramentas de acceso rápido), e despois na lista despregábel da parte superior da xanela a opción Macros. Despois diso, a nosa macro FormulasToValues pódese colocar no panel co botón Engadir (Engadir) e seleccione unha icona para el co botón Cambiar (Editar):
Paso 4. Engade funcións ao complemento
Pero macroprocedementos, tamén os hai macros de funcións ou como se chaman UDF (Función definida polo usuario = función definida polo usuario). Imos crear un módulo separado no noso complemento (comando de menú Inserir – Módulo) e pegue alí o código da seguinte función:
É fácil ver que esta función é necesaria para extraer o IVE do importe incluído o IVE. Non é o binomio de Newton, por suposto, pero servirá como exemplo para mostrar os principios básicos.
Teña en conta que a sintaxe dunha función é diferente dun procedemento:
- utilízase a construción Función.... Función final no canto Sub... Fin Sub
- despois do nome da función, os seus argumentos indícanse entre corchetes
- no corpo da función, realízanse os cálculos necesarios e despois asígnase o resultado a unha variable co nome da función
Tamén teña en conta que esta función non é necesaria e que é imposible executala como o procedemento de macro anterior a través da caixa de diálogo Macros e botón Correr. Esta función de macro debe usarse como unha función estándar da folla de traballo (SUMA, SE, BUSCARV...), é dicir, simplemente introduza en calquera cela, especificando o valor da cantidade co IVE como argumento:
... ou entra a través da caixa de diálogo estándar para inserir unha función (botón fx na barra de fórmulas), seleccionando unha categoría Definido polo usuario (Definido polo usuario):
O único momento desagradable aquí é a ausencia da descrición habitual da función na parte inferior da xanela. Para engadilo terás que facer o seguinte:
- Abre o Editor de Visual Basic cun atallo de teclado alt+F11
- Seleccione o complemento no panel Proxecto e prema a tecla F2para abrir a xanela do Explorador de obxectos
- Seleccione o seu proxecto de complemento na lista despregable na parte superior da xanela
- Fai clic co botón dereito na función que aparece e selecciona o comando Propiedades.
- Introduza unha descrición da función na xanela descrición
- Garda o ficheiro do complemento e reinicie Excel.
Despois de reiniciar, a función debería mostrar a descrición que introducimos:
Paso 5. Crea unha pestana de complementos na interface
O toque final, aínda que non obrigatorio, pero agradable, será a creación dunha pestana separada cun botón para executar a nosa macro, que aparecerá na interface de Excel despois de conectar o noso complemento.
A información sobre as pestanas que se amosan por defecto está contida no libro e debe ter formato nun código XML especial. O xeito máis sinxelo de escribir e editar este código é coa axuda de programas especiais: editores XML. Un dos máis cómodos (e gratuítos) é o programa de Maxim Novikov Editor XML de cinta.
O algoritmo para traballar con el é o seguinte:
- Pecha todas as fiestras de Excel para que non haxa ningún conflito de ficheiros cando editemos o código XML do complemento.
- Inicie o programa Ribbon XML Editor e abra o noso ficheiro MyExcelAddin.xlam nel
- Con botón tabs na esquina superior esquerda, engade o fragmento de código para a nova pestana:
- Debe poñer comiñas baleiras id a nosa pestana e grupo (calquera identificador único), e en etiqueta – os nomes da nosa pestana e un grupo de botóns sobre ela:
- Con botón botón no panel esquerdo, engade un código en branco para o botón e engade etiquetas:
- etiqueta é o texto do botón
- imaxeMso — este é o nome condicional da imaxe no botón. Usei unha icona de botón vermello chamada AnimationCustomAddExitDialog. Os nomes de todos os botóns dispoñibles (e hai varios centos deles!) pódense atopar nun gran número de sitios en Internet se buscas as palabras clave "imageMso". Para comezar, podes ir aquí.
- onAcción – este é o nome do procedemento de devolución de chamada – unha macro curta especial que executará a nosa macro principal FormulasToValues. Podes chamar a este procedemento como queiras. Engadirémolo un pouco máis tarde.
- Podes comprobar a corrección de todo o feito usando o botón cunha marca de verificación verde na parte superior da barra de ferramentas. No mesmo lugar, fai clic no botón cun disquete para gardar todos os cambios.
- Pecha o editor XML da cinta
- Abre Excel, vai ao editor de Visual Basic e engade un procedemento de devolución de chamada á nosa macro KillFormulasde xeito que executa a nosa macro principal para substituír fórmulas por valores.
- Gardamos os cambios e, volvendo a Excel, comprobamos o resultado:
Iso é todo: o complemento está listo para usar. Enche-lo cos teus propios procedementos e funcións, engade fermosos botóns e será moito máis fácil usar macros no teu traballo.
- Que son as macros, como usalas no teu traballo, onde conseguir o código de macros en Visual Basic.
- Como facer unha pantalla de inicio ao abrir un libro de traballo en Excel
- Que é un Macro Book persoal e como usalo