Conteúdo
Mesmo que você não saiba programar, existem muitos lugares (livros, sites, fóruns) onde você pode encontrar código de macro VBA pronto para um grande número de tarefas típicas no Excel. Na minha experiência, a maioria dos usuários, mais cedo ou mais tarde, coleta sua coleção pessoal de macros para automatizar processos de rotina, seja traduzindo fórmulas em valores, exibindo somas em palavras ou somando células por cor. E aqui surge o problema – o código de macro no Visual Basic precisa ser armazenado em algum lugar para ser usado posteriormente no trabalho.
A opção mais fácil é salvar o código da macro diretamente no arquivo de trabalho indo para o editor do Visual Basic usando o atalho de teclado outro+F11 e adicionando um novo módulo vazio através do menu Inserir - Módulo:
No entanto, existem várias desvantagens com este método:
- Se houver muitos arquivos de trabalho e uma macro for necessária em todos os lugares, como uma macro para converter fórmulas em valores, você terá que copiar o código em cada livro.
- Não deve ser esquecido salvar arquivo em formato habilitado para macro (xlsm) ou em formato de livro binário (xlsb).
- Ao abrir tal arquivo proteção macro sempre emitirá um aviso que precisa ser reconhecido (bem, ou desativará a proteção completamente, o que pode nem sempre ser desejável).
Uma solução mais elegante seria criar seu próprio suplemento (Suplemento do Excel) – um arquivo separado de um formato especial (xlam) contendo todas as suas macros “favoritas”. As vantagens desta abordagem:
- Será o suficiente conectar o complemento uma vez no Excel – e você pode usar seus procedimentos e funções VBA em qualquer arquivo neste computador. Salvar novamente seus arquivos de trabalho nos formatos xlsm e xlsb, portanto, não é necessário, porque. o código fonte não será armazenado neles, mas no arquivo add-in.
- pós-colheita você também não será incomodado por macros. add-ons são, por definição, fontes confiáveis.
- Pode fazer guia separada na faixa de opções do Excel com bons botões para executar macros suplementares.
- O suplemento é um arquivo separado. Dele fácil de transportar de computador em computador, partilhe com colegas ou até venda 😉
Vamos percorrer todo o processo de criação de seu próprio suplemento do Microsoft Excel passo a passo.
Etapa 1. Crie um arquivo de suplemento
Abra o Microsoft Excel com uma pasta de trabalho em branco e salve-a com qualquer nome adequado (por exemplo MeuExcel Addin) em formato de suplemento com o comando Arquivo - Salvar como ou chaves F12, especificando o tipo de arquivo Suplemento do Excel:
Observe que, por padrão, o Excel armazena suplementos na pasta C:UsersYour_nameAppDataRoamingMicrosoftAddIns, mas, em princípio, você pode especificar qualquer outra pasta que seja conveniente para você.
Etapa 2. Conectamos o suplemento criado
Agora, o suplemento que criamos na última etapa MeuExcel Addin deve estar conectado ao Excel. Para isso, acesse o menu Arquivo – Opções – Complementos (Arquivo — Opções — Suplementos), clique no botão Sobre (Ir) na parte inferior da janela. Na janela que se abre, clique no botão Avaliações (Navegar) e especifique o local do nosso arquivo de suplemento.
Se você fez tudo certo, então nosso MeuExcel Addin deve aparecer na lista de complementos disponíveis:
Etapa 3. Adicionar macros ao suplemento
Nosso suplemento está conectado ao Excel e funciona com sucesso, mas ainda não há uma única macro nele. Vamos encher. Para fazer isso, abra o editor do Visual Basic com o atalho de teclado outro+F11 ou por botão Visual Basic aba revelador (Desenvolvedor). Se guias revelador não visível, pode ser exibido através Arquivo – Opções – Configuração da fita (Arquivo — Opções — Personalizar Faixa de Opções).
Deve haver uma janela no canto superior esquerdo do editor Projeto (se não estiver visível, ligue-o através do menu Visualizar — Explorador de Projetos):
Essa janela exibe todas as pastas de trabalho abertas e os suplementos do Microsoft Excel em execução, incluindo os nossos. Projeto VBA (MeuExcelAddin.xlam) Selecione-o com o mouse e adicione um novo módulo a ele através do menu Inserir - Módulo. Neste módulo, armazenaremos o código VBA de nossas macros suplementares.
Você pode digitar o código do zero (se souber programar) ou copiá-lo de algum lugar pronto (o que é muito mais fácil). Vamos, para teste, inserir o código de uma macro simples, mas útil, no módulo vazio adicionado:
Após inserir o código, não esqueça de clicar no botão salvar (disquete) no canto superior esquerdo.
Nossa macro FórmulasParaValores, como você pode imaginar facilmente, converte fórmulas em valores em um intervalo pré-selecionado. Às vezes, essas macros também são chamadas procedimentos. Para executá-lo, você precisa selecionar células com fórmulas e abrir uma caixa de diálogo especial Macros da guia revelador (Desenvolvedor — Macros) ou atalho de teclado outro+F8. Normalmente, esta janela mostra as macros disponíveis de todas as pastas de trabalho abertas, mas as macros de suplemento não são visíveis aqui. Apesar disso, podemos inserir o nome do nosso procedimento no campo nome da macro (Nome da macro)e depois clique no botão Execute (corre) – e nossa macro funcionará:
| |
Aqui você também pode atribuir um atalho de teclado para iniciar rapidamente uma macro - o botão é responsável por isso parâmetros (Opções) na janela anterior Macro:
Ao atribuir teclas, lembre-se de que elas diferenciam maiúsculas de minúsculas e o layout do teclado. Então, se você atribuir uma combinação como Ctrl+Й, então, de fato, no futuro você terá que se certificar de que o layout está ativado e pressionar adicionalmente Shiftpara obter a letra maiúscula.
Por conveniência, também podemos adicionar um botão para nossa macro na barra de ferramentas de acesso rápido no canto superior esquerdo da janela. Para fazer isso, selecione Arquivo – Opções – Barra de Ferramentas de Acesso Rápido (Arquivo — Opções — Personalizar Barra de Ferramentas de Acesso Rápido)e, na lista suspensa na parte superior da janela, a opção Macros. Depois disso, nossa macro FórmulasParaValores pode ser colocado no painel com o botão Adicionar (Adicionar) e selecione um ícone para ele com o botão Mudar (Editar):
Etapa 4. Adicionar funções ao suplemento
BUT macro-procedimentos, há também macros de função ou como são chamados UDF (Função definida pelo usuário = função definida pelo usuário). Vamos criar um módulo separado em nosso complemento (comando de menu Inserir - Módulo) e cole o código da seguinte função lá:
É fácil ver que esta função é necessária para extrair o IVA do valor incluindo o IVA. Não é o binômio de Newton, é claro, mas servirá como exemplo para mostrar os princípios básicos.
Observe que a sintaxe de uma função é diferente de um procedimento:
- construção é usada Função…. Função Final em vez disso Sub… Fim Sub
- após o nome da função, seus argumentos são indicados entre colchetes
- no corpo da função, são realizados os cálculos necessários e, em seguida, o resultado é atribuído a uma variável com o nome da função
Observe também que esta função não é necessária e é impossível executar como o procedimento de macro anterior através da caixa de diálogo Macros e o botão Execute. Tal macrofunção deve ser utilizada como uma função de planilha padrão (SOMA, SE, PROCV…), ou seja, basta inserir em qualquer célula, especificando o valor do valor com o IVA como argumento:
… ou entre na caixa de diálogo padrão para inserir uma função (botão fx na barra de fórmulas), selecionando uma categoria Usuário definido (Usuário definido):
O único momento desagradável aqui é a ausência da descrição usual da função na parte inferior da janela. Para adicioná-lo, você terá que fazer o seguinte:
- Abra o Editor do Visual Basic com um atalho de teclado outro+F11
- Selecione o suplemento no painel Projeto e pressione a tecla F2para abrir a janela do Navegador de Objetos
- Selecione seu projeto de suplemento na lista suspensa na parte superior da janela
- Clique com o botão direito do mouse na função que aparece e selecione o comando Propriedades.
- Insira uma descrição da função na janela Descrição
- Salve o arquivo de suplemento e reinicie o excel.
Após reiniciar, a função deve exibir a descrição que inserimos:
Etapa 5. Crie uma guia complementar na interface
O toque final, embora não obrigatório, mas agradável, será a criação de uma guia separada com um botão para executar nossa macro, que aparecerá na interface do Excel após conectar nosso suplemento.
As informações sobre as guias exibidas por padrão estão contidas no livro e devem ser formatadas em um código XML especial. A maneira mais fácil de escrever e editar esse código é com a ajuda de programas especiais – editores de XML. Um dos mais convenientes (e gratuitos) é o programa de Maxim Novikov Editor XML da faixa de opções.
O algoritmo para trabalhar com ele é o seguinte:
- Feche todas as janelas do Excel para que não haja conflito de arquivos quando editarmos o código XML do suplemento.
- Inicie o programa Ribbon XML Editor e abra nosso arquivo MyExcelAddin.xlam nele
- Com botão guias no canto superior esquerdo, adicione o snippet de código da nova guia:
- Você precisa colocar aspas vazias id nossa guia e grupo (qualquer identificador exclusivo), e em rótulo – os nomes da nossa guia e um grupo de botões nela:
- Com botão botão no painel esquerdo, adicione um código em branco para o botão e adicione tags a ele:
- etiqueta é o texto no botão
— imagemMso — este é o nome condicional da imagem no botão. Eu usei um ícone de botão vermelho chamado AnimationCustomAddExitDialog. Os nomes de todos os botões disponíveis (e existem várias centenas deles!) podem ser encontrados em um grande número de sites na Internet se você pesquisar as palavras-chave “imageMso”. Para começar, você pode ir aqui.
- em ação – este é o nome do procedimento de retorno de chamada – uma macro curta especial que executará nossa macro principal FórmulasParaValores. Você pode chamar esse procedimento como quiser. Vamos adicioná-lo um pouco mais tarde.
- Você pode verificar a exatidão de tudo que foi feito usando o botão com uma marca de seleção verde na parte superior da barra de ferramentas. No mesmo local, clique no botão com um disquete para salvar todas as alterações.
- Feche o Editor XML da Faixa de Opções
- Abra o Excel, vá para o editor Visual Basic e adicione um procedimento de retorno de chamada à nossa macro KillFórmulaspara que execute nossa macro principal para substituir fórmulas por valores.
- Salvamos as alterações e, voltando ao Excel, verificamos o resultado:
Isso é tudo – o suplemento está pronto para uso. Preencha-o com seus próprios procedimentos e funções, adicione belos botões – e ficará muito mais fácil usar macros em seu trabalho.
- O que são macros, como usá-las em seu trabalho, onde obter código de macro no Visual Basic.
- Como fazer uma tela inicial ao abrir uma pasta de trabalho no Excel
- O que é um livro pessoal de macros e como usá-lo