Estas são minhas anotações sobre o programa de minha participação no episódio 33 do Call for Help na TechTV Canadá.
VBA Macro Recorder
Cada cópia do Excel vendida desde 1995 inclui o incrivelmente poderoso Visual Basic for Applications (VBA) escondido atrás das células. Se você tiver o Excel, terá o VBA.
O VBA permite automatizar tudo o que você pode fazer no Excel, além de fazer mais coisas que geralmente não são possíveis no Excel.
Felizmente para nós, a Microsoft incluiu um gravador de macro com o Excel. Isso permite que você escreva código VBA sem ser um programador. Infelizmente, o gravador de macro tem falhas. Não produzirá código que funcione sempre. O gravador de macro vai chegar perto, mas em muitos casos você precisa corrigir o código um pouco para que ele funcione de forma confiável. Em outros casos, você precisa saber quando usar a Gravação relativa para fazer a macro funcionar.
Tracy Syrstad e eu escrevemos VBA e macros do Microsoft Excel 2016 para ajudá-lo a entender as limitações do código gravado e para ensiná-lo a consertar o código gravado em algo que funcione sempre.
No programa, demonstrei o processo de gravação de uma macro que funcionará perfeitamente se você souber usar o botão Referência relativa. Alguém me deu uma planilha do Excel com centenas de nomes e endereços. Eles queriam etiquetas de mala direta. Se você já usou o recurso de mala direta do Microsoft Word, sabe que precisa de cada campo em uma nova coluna da planilha. Em vez disso, esta planilha em particular tinha os dados descendo a coluna A.
O processo de corrigir um único rótulo é muito tedioso.
- Comece com o ponteiro da célula no nome na coluna A.
- Pressione a seta para baixo para mover para o endereço
- Ctrl + x para cortar
- Seta para cima, seta para a direita para mover para a coluna B ao lado do nome
- Ctrl + v para colar
- Seta para baixo duas vezes, seta para a esquerda uma vez para mover para a cidade
- Ctrl + x para cortar
- Seta para cima duas vezes, seta para a direita três vezes
- Ctrl + v para colar
- Seta para a esquerda duas vezes, seta para baixo uma vez para mover para a linha agora em branco.
- Segure a tecla shift enquanto pressiona a seta para baixo duas vezes
- Alt + edr para apagar as linhas em branco
- Seta para cima e seta para baixo para selecionar apenas o nome novamente.
Aqui está a animação mostrando estas etapas:
Configurando o Excel para permitir macros
Embora todas as cópias do Excel contenham VBA, por padrão, a Microsoft desativa a capacidade de executar macros. Você precisa fazer um ajuste único para permitir que as macros sejam executadas. Abra o Excel. No menu, selecione Ferramentas> Macro> Segurança. Se o nível de segurança de macro estiver definido como alto, altere-o para Médio.
Preparando para gravar a macro
Pense nas etapas necessárias para realizar a tarefa. Você deseja ter certeza de começar com o ponteiro da célula em um nome e terminar com o próximo nome. Isso permitirá que você execute a macro várias vezes repetidamente. Quando você tiver as etapas prontas para prosseguir, ligue o gravador de macro. No menu, selecione Ferramentas> Macro> Gravar nova macro.
Se você for executar essa macro por muitos dias, deverá dar a ela um nome útil. Se for uma macro única para automatizar uma tarefa única, provavelmente não haverá problema em deixar o nome da macro como Macro1. O campo importante aqui é o campo da tecla de atalho. Se você estiver criando uma macro para uso único, atribua a macro a uma tecla de atalho, como Ctrl + a - é muito fácil pressionar Ctrl + a repetidamente, pois as teclas estão próximas umas das outras. Se você vai criar uma macro que usará todos os dias, você deseja atribuir a macro a uma tecla que ainda não é uma combinação de teclas de atalho importante. Ctrl + j ou Ctrl + k são boas escolhas.
As macros de uso único devem ser armazenadas em ThisWorkbook. Se você precisar usar a macro repetidamente em muitas pastas de trabalho diferentes, poderá armazenar a macro na Pasta de trabalho pessoal de macros.
Agora você tem a menor barra de ferramentas em todo o Excel; a barra de ferramentas Parar gravação. Ele tem apenas dois ícones e se parece com este:
Se esta barra de ferramentas atrapalhar, não pressione X para fechá-la. Em vez disso, pegue a barra azul e arraste a barra de ferramentas para um novo local. O ato de mover a barra de ferramentas não é registrado na macro. Se você fechar acidentalmente a barra de ferramentas, recupere-a em Exibir> Barras de ferramentas> Parar gravação. Esta ação, entretanto, será registrada.
O primeiro botão da barra de ferramentas é o botão "Parar gravação". Isso é autoexplicativo. Quando terminar de gravar a macro, pressione a barra de ferramentas parar gravação.
O botão mais importante (e raramente compreendido) é o botão "Referência relativa".
Em nosso exemplo atual, você deve pressionar o botão Referência relativa antes de começar. Se você gravar uma macro com Referências Relativas ativadas, o Excel gravará etapas como esta:
- Mova uma célula para baixo
- Corte a célula atual
- Mova uma célula para cima
- Mover uma célula para a direita
- Colar
- etc.
Se, em vez disso, você gravar a macro sem referências relativas, a macro gravará estas etapas:
- Mover para a célula A4
- Cortar célula A4
- Mover para a célula A3
- Mover para a célula B3
- Colar na célula B3
- etc.
Isso seria terrivelmente errado - precisamos que a macro funcione em células que têm 1 e 2 células a partir do ponto inicial da macro. Conforme você executa a macro repetidamente, o ponto de partida será a linha 4, linha 5, linha 6, etc. Gravar a macro sem referências relativas ativadas faria com que a macro sempre rodasse na linha 3 como ponto de partida.
Siga esses passos:
- Escolha o botão de referência relativa na barra de ferramentas para parar a gravação
- O ponteiro da célula já deve estar em um nome na coluna A.
- Pressione a seta para baixo para mover para o endereço
- Ctrl + x para cortar
- Seta para cima, seta para a direita para mover para a coluna B ao lado do nome
- Ctrl + v para colar
- Seta para baixo duas vezes, seta para a esquerda uma vez para mover para a cidade
- Ctrl + x para cortar
- Seta para cima duas vezes, seta para a direita três vezes
- Ctrl + v para colar
- Seta para a esquerda duas vezes, seta para baixo uma vez para mover para a linha agora em branco.
- Segure a tecla shift enquanto pressiona a seta para baixo duas vezes
- Alt + edr para apagar as linhas em branco
- Seta para cima e seta para baixo para selecionar o próximo nome na lista.
- Clique na barra de ferramentas Parar Gravação
- Salve a pasta de trabalho
- Teste a macro pressionando a tecla de atalho atribuída acima. Deve corrigir perfeitamente o próximo nome da lista
Depois de ver que a macro funciona conforme desejado, você pode manter pressionado Ctrl + a para corrigir rapidamente alguns nomes por segundo. A lista completa de 500 nomes pode ser corrigida em um ou dois minutos.
Dica bônus - não está no programa
Você pode facilmente envolver a macro em um loop simples para que ela conserte todos os 500 nomes sem precisar pressionar Ctrl + várias centenas de vezes.
Pressione a tecla Alt + F11 para abrir o editor de macro.
Se você não vir o painel Projeto - VBAProject, pressione Ctrl + r.
Clique com o botão direito do mouse em Module1 e escolha View Code. Você verá um código semelhante a este:
Agora, você não precisa entender o que esse código faz, mas sabe que queremos executar tudo nessa macro uma vez para cada nome que temos. Se você souber que existem 462 nomes, poderá adicionar 2 linhas para executar o código 462 vezes. Na parte superior da macro, insira uma nova linha com as palavras " For i = 1 to 462". Na parte inferior da macro, insira uma linha que diga " Next i". Isso diz ao VBA para executar o código 462 vezes.
Conclusão
Depois dessa macro simples, mostrei um exemplo no programa de uma macro muito complexa. Essa macro criou tabelas dinâmicas e gráficos para 46 departamentos de uma empresa. Este relatório costumava levar 40 horas por mês. A macro VBA agora executa e cria todos os 46 relatórios em menos de 2 minutos.
O livro VBA e macros Microsoft Excel 2016 irá guiá-lo na curva de aprendizado para que você possa ir desde a gravação de macros simples como esta até a execução de relatórios muito complexos que podem economizar centenas de horas por ano. Claro, se você não quiser aprender VBA, contrate um Consultor Excel para projetar um relatório para você.
