Macros do Excel - Tutorial prático para iniciantes com exemplos

excel macros hands tutorial

Este tutorial prático de macros do Excel explica o que é uma macro, como criar e usar macros VBA com muitos exemplos:

A maioria de nós na indústria certamente terá certas tarefas que devem ser executadas repetidamente quase todos os dias. Agora imagine se essas tarefas são feitas com apenas um clique. Parece excitante? Excel Macros é a resposta para isso.

Neste tutorial, aprenderemos o que é Macro? Como gravar uma macro usando referência absoluta e relativa junto com alguns exemplos práticos.

=> Dê uma olhada no guia para iniciantes em VBA aqui

O que você aprenderá:

• O que são macros do Excel
  • Como ativar macros no Excel
  • Habilitando a guia Desenvolvedor
  • Opções da guia do desenvolvedor
  • Como gravar uma macro
  • Salvando uma pasta de trabalho do Excel com macro
  • Executando uma Macro
  • Referência de Célula
  • Gravar macro do Excel usando referência relativa
  • perguntas frequentes
• Conclusão
  • Leitura recomendada

O que são macros do Excel

Uma macro é um conjunto de ações que você pode executar para realizar a tarefa desejada.

Suponha que a cada mês você crie um relatório que precisa marcar as contas de usuário com o valor vencido em negrito e vermelho. Em seguida, você pode criar e executar uma macro que aplique essas alterações de formatação sempre que desejar.

Como ativar macros no Excel

A guia Desenvolvedor nos dá a capacidade de trabalhar com recursos como Macros, Suplementos, e também nos permite escrever nosso próprio código VBA que nos ajudará a automatizar qualquer coisa que desejarmos. Esta guia está oculta por padrão.

Siga as etapas abaixo para exibir a guia Desenvolvedor. Isso funciona em todas as versões do Excel para Windows (Excel 2007,2010, 2013, 2016, 2019).

Observação: Este é um processo único. Depois de ativar a guia Desenvolvedor, ela sempre será exibida em uma faixa de opções personalizada para cada instância do Excel que você abrir, a menos que vá em frente e desative-a explicitamente.

Habilitando a guia Desenvolvedor

# 1) Clique no Arquivo aba

#dois) Clique Opções

# 3) Clique em Personalizar fita.

# 4) Em Personalizar Faixa de Opções, habilite Desenvolvedor.

Depois de ativar a guia Desenvolvedor, ela será exibida na lista da faixa de opções.

Opções da guia do desenvolvedor

Listadas abaixo estão as opções que estão presentes na guia Desenvolvedor.

• Visual básico: Fornece um Editor para escrever ou editar o código VBA. Também pode ser aberto usando Alt + F11.
• Macros: Dá a lista de todas as macros já gravadas e também é usado para gravar uma nova. Alt + F8 abrirá diretamente a lista de macros.
• Suplementos: Permite inserir um add-in e também pode gerenciá-los.
• Controles: Ajuda-nos a usar controles de formulário e controles ActiveX. Visualizando e editando as propriedades do controle. O modo de design ON / OFF é controlado aqui.
• XML: Ajuda-nos a importar / exportar um arquivo de dados XML, para gerenciar pacotes de expansão XML e também para abrir o painel de tarefas XML Source.

Como gravar uma macro

Considere um exemplo , que sua empresa possui uma determinada ferramenta que gera planilhas de horas para diversos departamentos em Excel. Você, como gerente, tem a responsabilidade de revisar e enviar a planilha para a equipe de finanças todas as semanas.

Mas antes de enviar, você é solicitado a fazer alguma formatação como:

1. Insira o título para cada folha que inclui o nome da equipe e o número da semana, marque-o em negrito e o fundo amarelo.
2. Desenhe uma borda
3. Em negrito os títulos das colunas.
4. Renomeie o nome da planilha como o nome da equipe.

Em vez de fazer isso manualmente todas as semanas, você pode apenas criar uma macro e executar todas essas ações com apenas um clique.

Gravar macro é bastante fácil. Navegue até a guia Desenvolvedor e clique em Macro de registro.

Isso abrirá uma janela onde você precisa entrar.

# 1) Nome da macro: O nome não deve ter espaços entre as palavras. Isso tem que começar com um alfabeto ou sublinhado.

# 2) Tecla de atalho: Isso é útil quando você está executando uma macro. Se você pressionar a tecla de atalho, ele será executado. Certifique-se de fornecer uma chave que ainda não foi usada, caso contrário, a macro a substituirá.

Por exemplo, se você mencionar Ctrl + S como o atalho, toda vez que você pressionar Ctrl + S, sua macro será executada, ignorando assim a opção de salvar arquivo. Portanto, é recomendado adicionar Shift, como Ctrl + Shift + D

# 3) Armazenar macro em: Isso tem 3 opções conforme fornecido abaixo.

• Este manual: Todas as macros criadas estarão disponíveis apenas para a pasta de trabalho atual. Se você abrir um novo Excel, a macro criada anteriormente não estará disponível e, portanto, não poderá ser usada.
• Livro de trabalho macro pessoal: Se você selecionar esta opção, a macro criada será armazenada e será exibida quando você abrir uma nova planilha do Excel.
• Novo livro de exercícios: Esta opção abrirá uma nova pasta de trabalho e as ações realizadas nessa pasta de trabalho serão registradas.

# 4) Descrição: Isso descreverá o propósito da macro. Recomenda-se fornecer uma descrição detalhada para que todos os que o utilizem saibam exatamente para que serve.

Depois de preencher os detalhes dos campos mencionados acima, você pode prosseguir e executar as ações necessárias na pasta de trabalho do Excel e tudo será registrado. Uma vez feito isso, volte para a guia Desenvolvedor e clique em Pare de gravar.

Salvando uma pasta de trabalho do Excel com macro

Selecionando Armazenar macro como “Esta pasta de trabalho”: Considere que você selecionou a macro Armazenar em como “Esta pasta de trabalho” durante a gravação. Depois de terminar, salve o arquivo. Ao salvar, você precisa selecionar a pasta de trabalho habilitada para macros do Excel. Você não precisa salvar explicitamente a macro. Ele é salvo automaticamente.

Selecionando Armazenar macro como “Pasta de trabalho Macro Pessoal”: Agora, considere selecionar a macro Armazenar como “pasta de trabalho Macro pessoal” durante a gravação. Você precisa salvar a macro explicitamente. Basta salvar o arquivo do Excel e tentar fechá-lo. Em seguida, você receberá uma caixa de diálogo pop-up conforme mostrado abaixo.

Observação: Se você não salvar, a macro será excluída.

Executando uma Macro

Agora que terminamos de gravar e salvar o arquivo, vamos tentar executá-lo e alcançar os resultados desejados. Seguimos em frente e gravamos uma macro com todas as etapas necessárias para atingir o exemplo da planilha de horas de atendimento e salvamos como uma pasta de trabalho com a tecla de atalho Ctrl + Shift + B.

Portanto, toda semana, ao receber um novo Excel da ferramenta de software, basta abrir esse arquivo Excel e pressionar a tecla de atalho (Ctrl + Shift + B) e todas as alterações serão incorporadas conforme o esperado. O Excel resultante é fornecido abaixo.

Anexado o Excel-Macro-Workbook

Observação:

1. Se você esqueceu a tecla de atalho, você pode ir para Desenvolvedor -> Macros, selecionar a macro e clicar em opções.
2. Se a macro armazenada como armazenamento pessoal não estiver visível na guia Macros. Vá em Exibir -> Mostrar e isso mostrará a lista de todas as macros.

Referência de Célula

Existem 2 maneiras de gravar uma macro, conforme mostrado abaixo.

1. Referência Célula Absoluta
2. Referência Célula Relativa

Referência de célula absoluta: As referências absolutas sempre apontarão para a célula específica onde foram registradas. Por exemplo: se você gravar um texto na célula A10, da próxima vez que usar essa macro em outra pasta de trabalho, esse texto será colocado na A10.

Considere nosso exemplo de quadro de horários de Presença. Queremos sempre que o título esteja na primeira linha de cada folha. Não queremos que a referência da célula mude quando for copiada para outras planilhas ou pastas de trabalho. Nesse caso, o Absolute Cell Referencing é útil.

Referência de célula relativa: Suponha que você precise repetir as etapas em vários lugares da planilha. As referências relativas são convenientes sempre que você precisa repetir o mesmo cálculo ou etapas em várias linhas ou colunas.

Exemplo: Suponha que você tenha uma planilha do Excel com os nomes completos, números de telefone e DOBs de 1.000 funcionários. (O formato é mostrado abaixo)

Seu gerente espera que você:

qual downloader de vídeo mp4 é compatível com o youtube

1. Separe o nome e o sobrenome.
2. Adicione Exemplo de código de país (+91) ao número de telefone.
3. Mostrar DOB na forma de dd-mon-yy, Exemplo: 10 de janeiro de 87.

Uma vez que existem 1000 registros, fazer isso manualmente levaria tempo. Então você decide criar uma Macro. Mas usar a referência absoluta não resolverá seu problema da maneira como você deseja que funcione em várias linhas e colunas. Nesse caso, a referência relativa é útil.

Gravar macro do Excel usando referência relativa

Para gravar usando uma referência relativa, primeiro selecione a célula na qual deseja iniciar a gravação.

Vá para Desenvolvedor -> clique em Usar Referência Relativa -> Gravar Macro . Grave o que quiser e clique em parar de gravar.

Para o exemplo acima, siga estas etapas.

1. Primeiro, precisamos inserir uma coluna ao lado de Emp FullName e alterar o título da coluna como FirstName e LastName.
2. Selecione Célula B2-> Vá para Desenvolvedor -> Usar referência relativa -> Gravar Macro .
3. Usando o delimitador de texto, separe o nome e o sobrenome. Depois de terminar, pare de gravar.
4. Da mesma forma, crie mais 2 macros para Número de telefone e DOB.
5. Salve o arquivo.
6. Para executar, selecione todo o Emp FullName, ou seja, B3 até o último emp que é B1001 e execute o 1^stMacro.
7. Siga etapas semelhantes para número de telefone e data de nascimento. O Excel resultante é mostrado abaixo.

Arquivo anexado para referência

perguntas frequentes

P # 1) O que é um exemplo de macros no Excel?

Responda: Uma macro é um conjunto de ações que você pode executar para realizar a tarefa desejada.

Suponha que você crie um relatório a cada mês que precise marcar as contas de usuário com o valor vencido em negrito e vermelho. Você pode criar e executar uma macro que aplica essas alterações de formatação sempre que desejar com apenas um único clique.

P # 2) Onde estão as macros no Excel?

Responda: Todas as macros gravadas estarão disponíveis em Guia Desenvolvedor -> Macros

Se você não conseguir encontrar uma macro pessoal, vá para Exibir -> Mostrar .

Q # 3) Quais são os tipos de referências de células no Excel?

Responda:

• Absoluto: As referências absolutas sempre apontarão para a célula específica onde foram registradas. Por exemplo, se você gravar um texto na célula D10, toda vez que a macro for usada, ela sempre apontará para D10.
• Relativo: Eles são convenientes sempre que você precisa repetir o mesmo cálculo ou etapas em várias linhas ou colunas.

P # 4) Como faço para salvar uma macro no Excel para todas as pastas de trabalho?

Responda: Ao gravar uma macro, selecione a pasta de trabalho Macro pessoal em armazenar macro em, isso fará com que sua macro esteja disponível para todas as pastas de trabalho. Se você ainda não vir a opção, vá para Exibir -> Mostrar .

Conclusão

Neste tutorial, aprendemos Macros do Excel que nos ajudam a automatizar as tarefas de rotina no Excel.

Nós vimos o que é uma macro? Como ativar a exibição da macro no Excel. Também exploramos como gravar uma macro usando referência de célula absoluta e relativa com exemplos.

=> Leia a série de treinamento Easy VBA

Leitura recomendada

• Tutorial do Excel VBA - Introdução ao VBA no Excel
• Tipos de dados VBA - Tipos de dados numéricos e não numéricos em VBA
• 35+ Perguntas e respostas mais comuns para entrevistas no Microsoft Excel
• Como automatizar tarefas repetitivas de controle de qualidade usando macros do Excel (exemplos)
• Como abrir arquivo XML no Excel, Chrome e MS Word
• Como ler ou gravar dados da planilha do Excel no Selenium Web Driver
• As 5 principais coisas que um testador deve ter para se destacar (e as perspectivas de mudança do testador de software)
• Trabalhando com objetos VBScript Excel