Como Gravar Macro no Excel | Primeiros passos com exemplo

Aprenda como gravar macro no Excel. Imagine que você receba todos os dias um arquivo de texto contendo as informações de vendas e precise transformar esses dados em um relatório organizado no Excel. Se esse processo for realizado manualmente, você terá que abrir o arquivo, separar os dados em colunas, formatar o cabeçalho, calcular os totais e repetir todas essas etapas diariamente.

Esse é exatamente o tipo de tarefa que pode ser automatizado com uma macro.

Com o recurso de gravação de macros do Excel, podemos registrar as ações realizadas durante a criação do relatório e depois transformar essas ações em um procedimento VBA. A partir daí, o Excel poderá repetir automaticamente o processo sempre que a macro for executada.

Neste artigo, vamos criar uma macro para gerar um relatório a partir de um arquivo texto. O exemplo é relativamente simples, mas apresenta um conceito muito importante para quem está começando com VBA: gravar uma macro, analisar o código gerado e depois melhorar esse código manualmente.

Essa última etapa é especialmente importante. O gravador de macros é excelente para criar a estrutura inicial de um procedimento, mas nem sempre o código gerado é suficientemente flexível para trabalhar com dados que mudam de quantidade. É justamente aí que entra o conhecimento de VBA.

Ao final do exemplo, teremos uma macro capaz de abrir e organizar os dados, formatar o relatório e calcular os totais automaticamente. Além disso, faremos uma alteração no código para que o relatório continue funcionando mesmo quando a quantidade de registros do arquivo texto for diferente.

O que vamos automatizar?

O processo utilizado neste exemplo envolve algumas tarefas que normalmente seriam feitas manualmente:

• Abrir o arquivo texto no Excel.

• Separar as informações em colunas.

• Formatar o cabeçalho.

• Identificar o final da base de dados.

• Inserir uma linha de totalização.

• Contar os registros.

• Somar os valores.

• Aplicar a formatação necessária ao resultado.

Em uma situação real, esse tipo de relatório pode ser executado dezenas ou centenas de vezes. Automatizar essas etapas reduz o trabalho manual e, principalmente, diminui a possibilidade de erros durante a preparação do relatório.

Gravando a macro no Excel

O primeiro passo é utilizar o gravador de macros. Ele registra várias das ações realizadas pelo usuário e transforma essas ações em comandos VBA.

O objetivo não é apenas criar uma macro sem escrever código. O mais interessante neste exercício é utilizar o gravador como ponto de partida e depois abrir o código para entender como o Excel registrou as ações.

Primeiro, baixe o arquivo texto que utilizaremos no exemplo:

Abra o arquivo texto no Excel, clicando sobre ele e pressionando CTRL+SHIFT+ENTER, o arquivo será aberto linha por linha no Excel, sem abrir o assistente de importação.

Arquivo Texto - Clique para ampliar

Agora temos os dados disponíveis no Excel e podemos começar a registrar as ações necessárias para transformá-los no relatório.

Iniciando a gravação da macro

Agora clique na aba Desenvolvedor e clique no botão Gravar Macro. Nessa tela, informe o nome da macro, que servirá para identificá-la posteriormente. Na tecla de atalho, digite uma letra de sua preferência.

O nome da macro deve ser objetivo e representar a tarefa que ela executa. Isso facilita sua identificação quando o arquivo possuir várias macros.

No campo Armazenar Macro em, selecione Pasta de trabalho pessoal de macros para que a macro fique disponível em qualquer planilha. Caso você escolha outra opção, essa macro não será global e ficará limitada à pasta de trabalho selecionada.

Gravar Macro - Clique para ampliar

Essa escolha é importante porque determina onde o Excel armazenará o procedimento VBA que será criado.

Se a macro for armazenada na pasta de trabalho atual, ela ficará vinculada àquele arquivo. Já a Pasta de Trabalho Pessoal de Macros permite que o procedimento fique disponível para outros arquivos do Excel no mesmo computador.

• Clique em OK.

• A partir deste momento, tudo o que estiver sendo feito será gravado pelo Excel. Por isso, você deve tomar cuidado para executar somente as ações que realmente deseja transformar em parte da macro.

• Para saber que a gravação está ativa, observe o comando que mudou de Gravar Macro para Parar Gravação na barra.

Esse é um dos principais cuidados ao utilizar o gravador de macros. Se você clicar em células, abrir menus, aplicar formatos ou executar ações desnecessárias durante a gravação, essas ações poderão aparecer no código VBA gerado.

Por isso, antes de clicar em Gravar Macro, é interessante planejar o processo. Quanto mais objetiva for a sequência de ações executadas durante a gravação, mais fácil será analisar e adaptar o código posteriormente.

Formatando o relatório

Com a gravação ativa, vamos executar as ações necessárias para transformar os dados brutos em um relatório.

Primeiro, precisamos separar as informações que estão na coluna A em diferentes colunas.

• Clique na coluna A e depois em Dados->Texto para colunas.

• Na tela que segue, clique em Delimitado, Avançar, marque o delimitador Vírgula, depois no botão Avançar e em seguida Concluir.

Arquivo separado em colunas

O recurso Texto para Colunas é utilizado porque o arquivo possui os campos separados por vírgulas. Ao indicar a vírgula como delimitador, o Excel consegue distribuir cada informação em sua respectiva coluna.

Esse é um bom exemplo de uma tarefa manual que o gravador de macros consegue registrar. Quando a macro for executada novamente, o Excel poderá repetir essa sequência de comandos.

Depois de separar os dados, vamos formatar o relatório e criar os cálculos.

• Formate o cabeçalho como negrito.

• Na última linha digite Total na célula A12, na célula F12 digite =CONT.VALORES(F2:F11) e na célula G12 digite SOMA(G2:G11).

Relatório Pronto

Observe que, neste momento, estamos utilizando referências fixas. A linha de total é a linha 12 e as fórmulas também consideram um intervalo específico de linhas.

Isso funciona perfeitamente para o arquivo utilizado no exemplo, mas existe um problema.

E se amanhã o arquivo tiver 20 registros? E se tiver 50? E se tiver apenas 5?

Nesse caso, a macro gravada inicialmente não será suficientemente flexível, porque ela foi construída com base na quantidade de registros existente no momento da gravação.

É justamente para resolver esse problema que vamos modificar o código VBA manualmente.

• Na guia Desenvolvedor, clique em Parar Gravação.

Analisando o código VBA gravado

Agora começa uma das partes mais importantes deste treinamento: analisar o código gerado automaticamente pelo Excel.

O gravador de macros funciona como uma ferramenta de tradução. Você executa ações na interface do Excel e o programa registra essas ações utilizando a linguagem VBA.

Isso torna o gravador uma excelente ferramenta para quem está começando a aprender programação no Excel.

Em vez de tentar memorizar todos os comandos de VBA, você pode realizar uma determinada ação manualmente, gravar a macro e depois analisar o código produzido.

Com o tempo, você começa a identificar padrões, objetos, propriedades e métodos utilizados pelo Excel.

Como melhorar a macro gravada

• Caso seja a primeira vez que você tenha gravado macros na Pasta de Trabalho Pessoal de Macros, será necessário que você feche o Excel e, quando ele solicitar salvar alterações, clique em Sim.

• Abra o Excel novamente, clique na aba Desenvolvedor e no botão Visual Basic.

• Na guia Projeto, selecione o projeto VBAProject (PERSONAL.XLSB), clique na pasta Módulos e selecione o Módulo1.

• A parte de código abrirá o conteúdo do Módulo1. Localize a Sub Relatorio_Vendas().

• Localize o código onde se inicia a gravação dos totais e inclua o código abaixo:

    '<alteração>
    'Cria a variável iTotalLinhas, seleciona a última célula, passa a quantidade de linhas total para + 1
    'para o TotalLinhas
    Dim iTotalLinhas As Integer
    Selection.End(xlDown).Select
    iTotalLinhas = Cells(Rows.Count, 1).End(xlUp).Row + 1
    '</alteração>

O objetivo dessa alteração é fazer com que a macro deixe de depender de uma linha fixa.

Anteriormente, o relatório considerava que a linha de totalização seria sempre a linha 12. Isso ocorre porque o arquivo utilizado durante a gravação possuía uma quantidade específica de registros.

Agora, a variável iTotalLinhas será utilizada para identificar dinamicamente onde os dados terminam e, consequentemente, onde a linha de totalização deverá ser criada.

Por que utilizar a última linha preenchida?

Encontrar a última linha preenchida é uma das tarefas mais comuns em VBA. Isso acontece porque uma macro eficiente não deve depender, sempre que possível, de uma quantidade fixa de registros.

Imagine que o relatório de hoje tenha 10 registros. Amanhã podem existir 25 registros. Na próxima semana, talvez sejam 200.

Se o código estiver baseado em referências como A12, F12 e G12, ele não acompanhará automaticamente essa mudança.

Já utilizando:

Cells(Rows.Count, 1).End(xlUp).Row

podemos localizar a última célula preenchida da coluna A. Ao adicionar + 1, encontramos a próxima linha disponível para inserir o total.

Esse conceito é fundamental para quem deseja desenvolver macros mais robustas.

Agora é necessário localizar no código, abaixo das linhas incluídas, as células que se referem à linha 12, pois esta foi a última linha incluída durante a gravação.

Altere o código conforme abaixo:

    '
    'Altera para pegar sempre a última linha
    Range("A" & iTotalLinhas).Select
    ActiveCell.FormulaR1C1 = "Total"
    Range("G" & iTotalLinhas).Select
    ActiveCell.FormulaR1C1 = "=SUM(R[-10]C:R[-1]C)"
    Range("F" & iTotalLinhas).Select
    ActiveCell.FormulaR1C1 = "=COUNTA(R[-10]C:R[-1]C)"
    Range("A" & iTotalLinhas & ":G" & iTotalLinhas).Select
    Range("G" & iTotalLinhas).Activate
    Selection.Font.Bold = True
    Range("G2:G" & iTotalLinhas).Select
    Selection.Style = "Comma"
    Range("A1").Select
    '

• Este código acima irá variar conforme a forma com que a macro foi gravada.

Essa observação é importante. O código produzido pelo gravador de macros depende das ações executadas e da estrutura da planilha durante a gravação. Por isso, não devemos assumir que o código será exatamente igual em todos os arquivos.

Entendendo a alteração realizada

Depois da alteração, a macro passa a utilizar a variável iTotalLinhas como referência para a linha de totalização.

O comando que preenche a célula da coluna A passa a utilizar:

Range("A" & iTotalLinhas)

Isso significa que o endereço da célula é construído dinamicamente. Se iTotalLinhas tiver o valor 12, o endereço será A12. Se tiver o valor 25, será A25. Se tiver o valor 101, será A101.

A mesma lógica é aplicada às colunas F e G.

Outro detalhe importante está nas fórmulas utilizando FormulaR1C1. Esse formato permite trabalhar com referências relativas em relação à célula atual.

No caso da soma, a fórmula:

=SUM(R[-10]C:R[-1]C)

indica que o Excel deve somar as células que estão acima da célula atual, considerando o intervalo definido pelo código.

Essa abordagem é particularmente interessante em macros gravadas porque permite transformar referências que inicialmente seriam fixas em referências relativas.

O que mudou na prática?

Antes da alteração, a macro estava preparada para um relatório com uma quantidade específica de linhas. Se o arquivo texto tivesse uma quantidade diferente de registros, a linha de totalização poderia ficar no lugar errado ou as fórmulas poderiam não considerar todos os dados.

Depois da alteração, a macro identifica a última linha utilizada e utiliza essa posição para criar os totais.

Isso torna o procedimento muito mais útil para uma rotina diária.

Imagine que você receba um arquivo com 100 registros hoje e 350 amanhã. Você não precisa alterar o código novamente. A macro identifica a quantidade existente no momento da execução e adapta a linha de totalização.

Essa é uma das grandes diferenças entre simplesmente gravar uma macro e desenvolver uma automação com VBA.

O gravador registra o que você fez. O programador analisa o código e identifica o que precisa ser transformado em uma regra dinâmica.

Gravador de macros: quando utilizar?

O gravador de macros é uma excelente ferramenta para quem está começando, mas também pode ser útil para usuários experientes.

Ele ajuda a descobrir a sintaxe necessária para executar determinadas operações no Excel. Se você não sabe como o VBA representa uma ação específica, pode executá-la manualmente enquanto grava uma macro e depois analisar o código gerado.

Porém, o código gravado geralmente pode ser simplificado e melhorado.

É comum encontrar comandos como Select, Activate e Selection em códigos produzidos pelo gravador. Eles funcionam, mas nem sempre são a melhor alternativa para desenvolver uma automação mais robusta e eficiente.

Conforme você evolui no VBA, o ideal é aprender a trabalhar diretamente com os objetos do Excel, reduzindo a dependência de seleções e ativações de células.

Mesmo assim, o gravador continua sendo uma excelente ferramenta de aprendizagem e uma forma prática de começar a entender como o Excel traduz ações da interface em código.

Automatizando uma rotina real

O exemplo apresentado neste artigo representa uma situação bastante comum em empresas: receber dados externos e transformá-los em um relatório padronizado.

Esse mesmo conceito pode ser utilizado em diversas outras rotinas.

Você pode receber arquivos de vendas, movimentações financeiras, informações de estoque, dados de clientes, arquivos exportados de sistemas corporativos ou qualquer outra informação que siga uma estrutura semelhante.

Quando o processo é repetitivo e possui regras bem definidas, existe uma boa oportunidade para automação.

O ganho não está apenas no tempo economizado. Uma macro também ajuda a padronizar o processo. Se todos os relatórios forem gerados utilizando exatamente as mesmas regras, diminui a possibilidade de alguém esquecer uma etapa durante a preparação manual.

O resultado final

O resultado final dessas alterações é uma macro que realiza a abertura dos dados, formatação e totalização automaticamente sempre que as teclas de atalho forem pressionadas ou a macro for acionada manualmente.

O mais importante é que agora temos uma solução mais flexível do que a macro simplesmente gravada. A quantidade de registros pode variar e a rotina consegue localizar a posição necessária para inserir a linha de totalização.

Faça o download do código fonte e do arquivo texto para análise e testes.

Conclusão

Neste exemplo, começamos com uma tarefa totalmente manual: receber um arquivo texto e transformá-lo em um relatório no Excel.

Utilizamos o gravador de macros para registrar as ações, armazenamos o procedimento na Pasta de Trabalho Pessoal de Macros e depois acessamos o código VBA para fazer uma alteração importante.

Essa alteração foi necessária porque uma macro gravada com referências fixas pode funcionar corretamente apenas enquanto a estrutura e a quantidade de dados permanecerem iguais às utilizadas durante a gravação.

Ao substituir a referência fixa pela identificação dinâmica da última linha preenchida, tornamos o relatório mais adaptável a diferentes quantidades de registros.

Esse é um dos conceitos que você deve levar para outros projetos: não basta fazer a macro funcionar com os dados de hoje; é preciso pensar em como ela se comportará com os dados de amanhã.

Se você está começando a estudar VBA, recomendo utilizar o gravador de macros como uma ferramenta de aprendizado. Grave tarefas simples, abra o código gerado e tente entender o que cada comando está fazendo. Depois, faça pequenas alterações e observe o resultado.

Com esse processo, você começa a desenvolver a capacidade de transformar tarefas repetitivas em procedimentos automáticos e, principalmente, passa a entender como o Excel pode ser programado para trabalhar de acordo com as suas regras.