Gravar Modificar Executar Macro do Excel - Dicas do Excel

Esta é a 19ª dica semanal do Excel em.com. Muitas das dicas do Excel envolvem algum tipo de truque de macro. Esta semana, para os usuários do Excel que nunca escreveram uma macro, ofereço uma introdução sobre como gravar e, em seguida, personalizar uma macro útil do Excel.

Dados de endereço de amostra

Digamos que você tenha 400 linhas de dados de endereço como aquele mostrado na figura superior à esquerda. O campo do nome está na coluna A, o endereço da rua na coluna B e a cidade na coluna C.

Seu objetivo é converter os dados em uma única coluna, como mostrado na segunda figura.

Este problema simples será usado para ilustrar como gravar, modificar e então executar uma macro simples.

Para usuários do Excel 95: Após gravar a macro, o Excel colocará sua macro em uma planilha chamada Módulo1 em sua pasta de trabalho. Você pode apenas clicar na folha para acessar a macro.

Embora existam 400 registros nesta planilha, desejo registrar um pequeno pedaço da macro que cuida apenas do primeiro endereço. A macro assumirá que o ponteiro de célula está no primeiro nome. Ele irá inserir três linhas em branco. Ele copiará a célula à direita da célula original para a célula abaixo da célula original. Ele copiará a célula da cidade para a célula 2 linhas abaixo da célula original. Ele deve então mover o ponteiro da célula para baixo de forma que fique no próximo nome.

O segredo é refletir sobre esse processo antes de gravá-lo. Você não quer cometer muitos erros ao gravar a macro.

Portanto, coloque o ponteiro de sua célula na célula A1. Vá para o menu e selecione Ferramentas> Macro> Gravar nova macro. A caixa de diálogo Gravar macro sugere um nome de Macro1. Isso é bom, então clique em OK.

O gravador de macro do Excel tem uma configuração padrão muito estúpida que você absolutamente deve alterar para que esta macro funcione. No Excel 95, vá para Ferramentas> Macro> Usar Referências Relativas. No Excel 97-2003, clique no segundo ícone na barra de ferramentas Parar Gravação. O ícone se parece com uma pequena planilha. Uma hemácia em C3 aponta para outra hemácia em A3. O ícone é denominado Referência relativa. Quando este ícone está 'ligado', há alguma cor em torno do ícone. O ícone lembra a última configuração da sessão atual do Excel, então você pode ter que clicar nele algumas vezes para descobrir qual método está ativado ou não. No Excel 2007, use Exibir - Macros - Usar referências relativas.

OK, estamos prontos para ir. Siga esses passos:

Pressione a seta para baixo uma vez para mover para a célula B1.
Mantenha pressionada a tecla shift e aperte a seta para baixo duas vezes para selecionar as linhas 2, 3 e 4
No menu, selecione Inserir e, em seguida, selecione Linhas para inserir três linhas em branco.
Aperte a seta para cima e a seta para a direita para mover para a célula B2.
Pressione Ctrl X para cortar a célula B2.
Pressione a seta para baixo, a seta para a esquerda e pressione Ctrl V para colar na célula A2.
Pressione a seta para cima, seta para a direita, seta para a direita, Ctrl X, seta para a esquerda, seta para a esquerda, seta para baixo, seta para baixo, Ctrl V para mover C1 para A3.
Pressione a seta para baixo duas vezes para que o ponteiro da célula esteja agora no próximo nome na Linha A5.
Clique no ícone “Parar gravação” na barra de ferramentas para interromper a gravação da macro.

Bem, você gravou sua primeira macro. Vamos dar uma olhada. Vá para Ferramentas> Macro> Macros. Na lista, destaque Macro1 e pressione o botão Editar. Você deve ver algo parecido com isso.

Sub Macro1() ' ' Macro1 Macro ' Macro recorded 4/18/99 by Reader ' ' ActiveCell.Offset(1, 0).Range("A1:A3").Select Selection.EntireRow.Insert ActiveCell.Offset(-1, 1).Range("A1").Select Selection.Cut ActiveCell.Offset(1, -1).Range("A1").Select ActiveSheet.Paste ActiveCell.Offset(-1, 2).Range("A1").Select Selection.Cut ActiveCell.Offset(2, -2).Range("A1").Select ActiveSheet.Paste ActiveCell.Offset(2, 0).Range("A1").Select End Sub

Ei, se você não é um programador, isso provavelmente parece bastante intimidante. Não deixe assim. Se houver algo que você não entende, há uma ajuda excelente. Clique com o cursor em algum lugar na palavra-chave Offset e pressione F1. Desde que tenha instalado o arquivo de ajuda do VBA, você verá o tópico de ajuda para a palavra-chave Offset. A ajuda informa a sintaxe da instrução. Diz que é Offset (RowOffset, ColumnOffset). Ainda não está muito claro? Procure a palavra "exemplo" sublinhada em verde próximo ao topo da ajuda. Os exemplos de VBA do Excel permitirão que você aprenda o que está acontecendo. No exemplo de Offset, diz para ativar a célula duas linhas abaixo e três linhas à direita da célula atual, você usaria:

ActiveCell.Offset(3, 2).Activate

OK, isso é uma pista. A função de deslocamento é uma forma de se mover pela planilha do Excel. Com essa informação, você pode ver o que a macro está fazendo. O primeiro deslocamento (1, 0) é onde movemos o ponteiro de célula para baixo para A2. O próximo deslocamento é onde subimos uma linha (-1 linhas) e mais 1 coluna. Você pode não entender mais nada na macro, mas ainda é útil.

Volte para a planilha do Excel. Coloque o ponteiro do seu celular na célula A5. Escolha Ferramentas> Macro> Macros> Macro1> Executar. A macro é executada e seu segundo endereço é formatado.

Você pode estar dizendo que selecionar toda essa longa sequência de comandos é mais difícil do que apenas formatar à mão. OK, vá em Ferramentas> Macro> Macros> Opções. Na caixa de atalho, digamos que Ctrl + w é a tecla de atalho para esta macro. Clique em OK e feche a caixa de diálogo Macro com Cancelar. Agora, quando você pressiona Ctrl w, a macro será executada. Você pode formatar um endereço em uma única tecla.

Você está pronto para o grande momento? Quantos endereços você ainda tem? Apertei Ctrl wa algumas vezes, então tenho 395 restantes. Volte para sua macro. Vamos colocar todo o código da macro em um loop. Insira uma nova linha que diga "Do until activecell.value =" "" antes da primeira linha do código da macro. Insira uma linha que diga "Loop" antes da linha End Sub. O loop Do irá executar tudo entre o Do e a linha do Loop até chegar a uma linha em branco. A macro agora se parece com isto:

Sub Macro1() ' ' Macro1 Macro ' Macro recorded 4/18/99 by Reader ' ' Do Until ActiveCell.Value = "" ActiveCell.Offset(1, 0).Range("A1:A3").Select Selection.EntireRow.Insert ActiveCell.Offset(-1, 1).Range("A1").Select Selection.Cut ActiveCell.Offset(1, -1).Range("A1").Select ActiveSheet.Paste ActiveCell.Offset(-1, 2).Range("A1").Select Selection.Cut ActiveCell.Offset(2, -2).Range("A1").Select ActiveSheet.Paste ActiveCell.Offset(2, 0).Range("A1").Select Loop End Sub

Volte para sua planilha do Excel. Coloque o ponteiro da célula no próximo nome. Pressione Ctrl w e a macro formatará todos os seus registros em alguns segundos.

Os autores dos livros do Excel dizem que você não pode fazer nada útil gravando uma macro. Não é verdade! Para quem teria que cortar e colar 800 vezes, essa macro é muito útil. Demorou alguns minutos para gravar e personalizar. Sim, os programadores profissionais dirão que o código é terrivelmente ineficiente. O Excel coloca um monte de coisas lá que não precisa ser colocado lá. Sim, se você souber o que está fazendo, poderá realizar a mesma tarefa com metade das linhas que serão executadas em 1,2 segundos em vez de 3 segundos. E DAÍ? 3 segundos é muito mais rápido do que os 30 minutos que a tarefa levaria.

Mais algumas dicas para iniciar gravadores de macro:

O apóstrofo é usado para indicar um comentário. Qualquer coisa após o apóstrofo é ignorado pelo VBA
Esta é a programação orientada a objetos. A sintaxe básica é object.action. Se um compilador orientado a objetos estivesse jogando futebol, ele diria "ball.kick" para chutar a bola. Portanto, "Selection.Cut" diz para fazer um "edit> cut" na seleção atual.
No exemplo acima, os modificadores de intervalo são relativos à célula ativa. Se a célula ativa estiver em B2 e você disser "ActiveCell.Range (" A1: C3 "). Selecione", então selecione a área de 3 linhas por 3 colunas começando na célula B2. Em outras palavras, você seleciona B2: D4. Dizer "ActiveCell.Range (" A1 ")" indica para selecionar o intervalo de células 1 x 1 começando com a célula ativa. Isso é incrivelmente redundante. É equivalente a dizer "ActiveCell.Select".
Salve sua pasta de trabalho antes de executar uma macro pela primeira vez. Desta forma, se houver um erro e ocorrer algo inesperado, você pode fechar sem salvar e voltar para a versão salva.

Esperamos que este exemplo simples dê a vocês gravadores de macro novatos a coragem de gravar uma macro simples na próxima vez que você tiver uma tarefa recorrente para executar no Excel.