Aprenda a usar macros do Excel para automatizar tarefas incômodas

Uma das funções mais poderosas, mas raramente usadas, do Excel é a capacidade de criar facilmente tarefas automatizadas e lógica personalizada dentro de macros. As macros fornecem uma maneira ideal de economizar tempo em tarefas previsíveis e repetitivas, bem como padronizar formatos de documentos - muitas vezes sem precisar escrever uma única linha de código.

Se você está curioso sobre quais macros são ou como realmente criá-las, não há problema - nós o orientaremos durante todo o processo.

Nota: o mesmo processo deve funcionar na maioria das versões do Microsoft Office. As capturas de tela podem parecer um pouco diferentes.

O que é uma macro??

Uma macro do Microsoft Office (como essa funcionalidade se aplica a vários aplicativos do MS Office) é simplesmente o código Visual Basic for Applications (VBA) salvo dentro de um documento. Para uma analogia comparável, pense em um documento como HTML e uma macro como Javascript. Da mesma maneira que o JavaScript pode manipular HTML em uma página da Web, uma macro pode manipular um documento.

As macros são incrivelmente poderosas e podem fazer praticamente qualquer coisa que sua imaginação possa conjurar. Como uma lista (muito) curta de funções que você pode fazer com uma macro:

• Aplicar estilo e formatação.
• Manipular dados e texto.
• Comunique-se com fontes de dados (banco de dados, arquivos de texto, etc.).
• Crie documentos inteiramente novos.
• Qualquer combinação, em qualquer ordem, de qualquer dos itens acima.

Criando uma macro: uma explicação por exemplo

Começamos com o arquivo CSV de variedade de jardins. Nada de especial aqui, apenas um conjunto de números de 10 x 20 entre 0 e 100 com um cabeçalho de linha e coluna. Nosso objetivo é produzir uma folha de dados apresentável e bem formatada que inclua os totais de resumo de cada linha.

Como dissemos acima, uma macro é código VBA, mas uma das coisas boas sobre o Excel é que você pode criar / gravá-las com codificação zero necessária - como faremos aqui.

Para criar uma macro, vá para Exibir> Macros> Gravar Macro.

Atribuir a macro um nome (sem espaços) e clique em OK.

Quando isso for feito, todos de suas ações são registradas - cada mudança de célula, ação de rolagem, redimensionamento de janela, o nome dela.

Há alguns lugares que indicam que o Excel é o modo de gravação. Uma delas é visualizando o menu Macro e observando que a opção Parar gravação substituiu a opção para Gravar macro.

O outro está no canto inferior direito. O ícone 'stop' indica que está no modo macro e pressionar aqui interrompe a gravação (da mesma forma, quando não estiver no modo de gravação, este ícone será o botão Gravar Macro, que você pode usar em vez de ir para o menu Macros).

Agora que estamos gravando nossa macro, vamos aplicar nossos cálculos de resumo. Primeiro adicione os cabeçalhos.

Em seguida, aplique as fórmulas apropriadas (respectivamente):

• = SUM (B2: K2)
• = MÉDIA (B2: K2)
• = MIN (B2: K2)
• = MÁXIMO (B2: K2)
• = MEDIANO (B2: K2)

Agora, destaque todas as células de cálculo e arraste o comprimento de todas as linhas de dados para aplicar os cálculos a cada linha.

Feito isso, cada linha deve exibir seus respectivos resumos.

Agora, queremos obter os dados de resumo de toda a planilha, portanto, aplicamos mais alguns cálculos:

Respectivamente:

• = SUM (L2: L21)
• = MÉDIA (B2: K21) *Isso deve ser calculado em todos os dados porque a média das médias das linhas não é necessariamente igual à média de todos os valores.
• = MIN (N2: N21)
• = MAX (O2: O21)
• = MEDIANO (B2: K21) * Calculado em todos os dados pelo mesmo motivo acima.

Agora que os cálculos estão concluídos, aplicaremos o estilo e a formatação. Primeiro, aplique formatação geral de números em todas as células, fazendo um Select All (Ctrl + A ou clique na célula entre os cabeçalhos de linha e coluna) e selecione o ícone "Comma Style" no menu Home.

Em seguida, aplique um pouco de formatação visual nos cabeçalhos de linha e coluna:

• Negrito.
• Centrado.
• Cor de preenchimento de fundo.

E finalmente, aplique algum estilo aos totais.

Quando tudo estiver terminado, é assim que nossa folha de dados se parece:

Como estamos satisfeitos com os resultados, pare a gravação da macro.

Parabéns - você acabou de criar uma macro do Excel.

Para usar nossa macro recém-gravada, temos que salvar nossa pasta de trabalho do Excel em um formato de arquivo habilitado para macro. No entanto, antes de fazermos isso, primeiro precisamos limpar todos os dados existentes para que eles não sejam incorporados em nosso modelo (a ideia é que toda vez que usarmos esse modelo, importaremos os dados mais atualizados).

Para fazer isso, selecione todas as células e exclua-as.

Com os dados agora apagados (mas as macros ainda incluídas no arquivo do Excel), queremos salvar o arquivo como um arquivo de modelo habilitado para macro (XLTM). É importante observar que, se você salvar isso como um arquivo de modelo padrão (XLTX), as macros não ser capaz de ser executado a partir dele. Como alternativa, você pode salvar o arquivo como um arquivo de modelo legado (XLT), que permitirá que as macros sejam executadas.

Depois de salvar o arquivo como modelo, vá em frente e feche o Excel.

Usando uma macro do Excel

Antes de abordar como podemos aplicar essa macro recém-gravada, é importante abordar alguns pontos sobre macros em geral:

• Macros podem ser maliciosas.
• Veja o ponto acima.

O código VBA é realmente muito poderoso e pode manipular arquivos fora do escopo do documento atual. Por exemplo, uma macro pode alterar ou excluir arquivos aleatórios na sua pasta Meus documentos. Como tal, é importante certificar-se de que só executar macros de fontes confiáveis.

Para colocar nossa macro de formato de dados em uso, abra o arquivo Modelo do Excel que foi criado acima. Quando você fizer isso, supondo que você tenha configurações de segurança padrão ativadas, você verá um aviso na parte superior da pasta de trabalho que diz que as macros estão desabilitadas. Como confiamos em uma macro criada por nós mesmos, clique no botão "Ativar conteúdo".

Em seguida, vamos importar o conjunto de dados mais recente de um CSV (essa é a fonte da planilha usada para criar nossa macro).

Para concluir a importação do arquivo CSV, talvez seja necessário definir algumas opções para que o Excel interprete corretamente (por exemplo, delimitador, cabeçalhos presentes, etc.).

Uma vez que nossos dados são importados, basta ir ao menu Macros (sob a aba Visualizar) e selecionar Exibir macros.

Na caixa de diálogo resultante, vemos a macro “FormatData” que registramos acima. Selecione-o e clique em Executar.

Uma vez em execução, você poderá ver o cursor pular por alguns momentos, mas ao fazer isso, você verá os dados sendo manipulados exatamente como nós gravamos. Quando tudo estiver dito e feito, ele deve se parecer com o nosso original - exceto com dados diferentes.

Olhando sob o capô: o que faz uma macro funcionar

Como mencionamos algumas vezes, uma macro é orientada pelo código Visual Basic for Applications (VBA). Quando você “grava” uma macro, o Excel está traduzindo tudo o que você faz em suas respectivas instruções do VBA. Para simplificar - você não precisa escrever nenhum código porque o Excel está escrevendo o código para você.

Para ver o código que faz com que a nossa macro seja executada, na caixa de diálogo Macros, clique no botão Editar.

A janela que é aberta exibe o código-fonte que foi gravado de nossas ações ao criar a macro. Claro, você pode editar este código ou até mesmo criar novas macros inteiramente dentro da janela de código. Embora a ação de gravação usada neste artigo provavelmente atenda à maioria das necessidades, ações mais altamente personalizadas ou ações condicionais exigiriam que você editasse o código-fonte.

Levando o nosso exemplo um passo mais longe…

Hipoteticamente, assuma que nosso arquivo de dados de origem, data.csv, é produzido por um processo automatizado que sempre salva o arquivo no mesmo local (por exemplo, C: \ Data \ data.csv é sempre os dados mais recentes). O processo de abrir este arquivo e importá-lo também pode ser facilmente transformado em uma macro:

1. Abra o arquivo de modelo do Excel que contém a macro "FormatData".
2. Grave uma nova macro chamada "LoadData".
3. Com a gravação de macro, importe o arquivo de dados como faria normalmente.
4. Depois que os dados são importados, pare de gravar a macro.
5. Exclua todos os dados da célula (selecione todos e exclua).
6. Salve o modelo atualizado (lembre-se de usar um formato de modelo habilitado para macro).

Feito isso, sempre que o modelo for aberto, haverá duas macros - uma que carrega nossos dados e outra que formata.

Se você realmente quisesse sujar as mãos com um pouco de edição de código, poderia combinar facilmente essas ações em uma única macro copiando o código produzido de “LoadData” e inserindo-o no início do código em “FormatData”..

Baixe este modelo

Para sua conveniência, incluímos o modelo do Excel produzido neste artigo, bem como um arquivo de dados de amostra para você brincar com.

Faça o download do Excel Macro Template do How-To Geek