Tutorial do Excel VBA - Como escrever código em uma planilha usando o Visual Basic

Introdução

Este é um tutorial sobre como escrever código em planilhas do Excel usando o Visual Basic for Applications (VBA).

O Excel é um dos produtos mais populares da Microsoft. Em 2016, o CEO da Microsoft disse "Pense em um mundo sem Excel. Isso é simplesmente impossível para mim". Bem, talvez o mundo não possa pensar sem o Excel.

  • Em 1996, havia mais de 30 milhões de usuários do Microsoft Excel (fonte).
  • Hoje, existem cerca de 750 milhões de usuários do Microsoft Excel. Isso é um pouco mais do que a população da Europa e 25 vezes mais usuários do que havia em 1996.

Somos uma grande família feliz!

Neste tutorial, você aprenderá sobre VBA e como escrever código em uma planilha do Excel usando o Visual Basic.

Pré-requisitos

Você não precisa de nenhuma experiência anterior em programação para entender este tutorial. No entanto, você precisará de:

  • Familiaridade básica a intermediária com Microsoft Excel
  • Se quiser acompanhar os exemplos VBA neste artigo, você precisará acessar o Microsoft Excel, de preferência a versão mais recente (2019), mas o Excel 2016 e o ​​Excel 2013 funcionarão perfeitamente.
  • Uma vontade de experimentar coisas novas

objetivos de aprendizado

Ao longo deste artigo, você aprenderá:

  1. O que é VBA
  2. Por que você usaria VBA
  3. Como se preparar no Excel para escrever VBA
  4. Como resolver alguns problemas do mundo real com VBA

Conceitos importantes

Aqui estão alguns conceitos importantes com os quais você deve estar familiarizado para compreender totalmente este tutorial.

Objetos : o Excel é orientado a objetos, o que significa que tudo é um objeto - a janela do Excel, a pasta de trabalho, uma planilha, um gráfico, uma célula. O VBA permite que os usuários manipulem e executem ações com objetos no Excel.

Se você não tem nenhuma experiência com programação orientada a objetos e este é um conceito totalmente novo, pare um segundo para entender!

Procedimentos : um procedimento é um pedaço de código VBA, escrito no Editor do Visual Basic, que realiza uma tarefa. Às vezes, isso também é conhecido como macro (mais sobre macros a seguir). Existem dois tipos de procedimentos:

  • Sub-rotinas: um grupo de instruções VBA que executa uma ou mais ações
  • Funções: um grupo de instruções VBA que executa uma ou mais ações e retorna um ou mais valores

Nota: você pode ter funções operando dentro de sub-rotinas. Você verá mais tarde.

Macros : se você passou algum tempo aprendendo funcionalidades mais avançadas do Excel, provavelmente encontrou o conceito de “macro”. Os usuários do Excel podem gravar macros, que consistem em comandos / pressionamentos de tecla / cliques do usuário, e reproduzi-los na velocidade da luz para realizar tarefas repetitivas. As macros gravadas geram código VBA, que você pode examinar. Na verdade, é muito divertido gravar uma macro simples e, em seguida, observar o código VBA.

Lembre-se de que às vezes pode ser mais fácil e rápido gravar uma macro em vez de codificar manualmente um procedimento VBA.

Por exemplo, talvez você trabalhe em gerenciamento de projetos. Uma vez por semana, você deve transformar um relatório bruto exportado de seu sistema de gerenciamento de projetos em um relatório limpo e bem formatado para a liderança. Você precisa formatar os nomes dos projetos acima do orçamento em texto vermelho em negrito. Você pode gravar as alterações de formatação como uma macro e executá-la sempre que precisar fazer a alteração.

O que é VBA?

Visual Basic for Applications é uma linguagem de programação desenvolvida pela Microsoft. Cada programa de software do pacote Microsoft Office é fornecido com a linguagem VBA sem nenhum custo extra. O VBA permite que os usuários do Microsoft Office criem pequenos programas que operam dentro dos programas de software do Microsoft Office.

Pense no VBA como um forno de pizza dentro de um restaurante. O Excel é o restaurante. A cozinha vem com aparelhos comerciais padrão, como grandes geladeiras, fogões e fornos ole regulares - todos esses são recursos padrão do Excel.

Mas e se você quiser fazer pizza no forno a lenha? Não é possível fazer isso em um forno comercial padrão. VBA é o forno de pizza.

Yum.

Por que usar o VBA no Excel?

Porque a pizza no forno a lenha é a melhor!

Mas seriamente.

Muitas pessoas passam muito tempo no Excel como parte de seus trabalhos. O tempo no Excel também se move de maneira diferente. Dependendo das circunstâncias, 10 minutos no Excel podem parecer uma eternidade se você não for capaz de fazer o que precisa, ou 10 horas podem passar muito rapidamente se tudo estiver indo bem. É quando você deve se perguntar por que diabos estou gastando 10 horas no Excel?

Às vezes, esses dias são inevitáveis. Mas se você está gastando 8-10 horas todos os dias no Excel fazendo tarefas repetitivas, repetindo muitos dos mesmos processos, tentando limpar o arquivo após outros usuários, ou mesmo atualizando outros arquivos depois que as alterações são feitas no arquivo do Excel, um procedimento VBA pode ser a solução para você.

Você deve considerar o uso do VBA se precisar:

  • Automatize tarefas repetitivas
  • Crie maneiras fáceis para os usuários interagirem com suas planilhas
  • Manipule grandes quantidades de dados

Preparando-se para escrever VBA no Excel

Guia do desenvolvedor

Para escrever VBA, você precisará adicionar a guia Desenvolvedor à faixa de opções, para ver a faixa de opções assim.

Para adicionar a guia Desenvolvedor à faixa de opções:

  1. Na guia Arquivo, vá para Opções> Personalizar Faixa de Opções.
  2. Em Personalizar a faixa de opções e em Guias principais, marque a caixa de seleção Desenvolvedor.

Depois de mostrar a guia, a guia Desenvolvedor permanece visível, a menos que você desmarque a caixa de seleção ou precise reinstalar o Excel. Para obter mais informações, consulte a documentação de ajuda da Microsoft.

Editor VBA

Navegue até a guia Desenvolvedor e clique no botão Visual Basic. Uma nova janela aparecerá - este é o Editor do Visual Basic. Para os fins deste tutorial, você só precisa estar familiarizado com o painel Explorador de Projetos e o painel Propriedades da Propriedade.

Exemplos de Excel VBA

Primeiro, vamos criar um arquivo para brincarmos.

  1. Abra um novo arquivo Excel
  2. Salve-o como uma pasta de trabalho habilitada para macro (. Xlsm)
  3. Selecione a guia Desenvolvedor
  4. Abra o Editor VBA

Vamos agitar com alguns exemplos fáceis para fazer você escrever código em uma planilha usando o Visual Basic.

Exemplo nº 1: Exibir uma mensagem quando os usuários abrirem a pasta de trabalho do Excel

No Editor VBA, selecione Inserir -> Novo Módulo

Escreva este código na janela do módulo (não cole!):

Sub Auto_Open ()

MsgBox ("Bem-vindo à pasta de trabalho XYZ.")

End Sub

Salve, feche a pasta de trabalho e reabra a pasta de trabalho. Esta caixa de diálogo deve ser exibida.

Ta da!

Como está fazendo isso?

Dependendo de sua familiaridade com programação, você pode ter alguns palpites. Não é particularmente complexo, mas há muita coisa acontecendo:

  • Sub (abreviação de "Subrotina): lembre-se desde o início," um grupo de instruções VBA que executa uma ou mais ações. ”
  • Auto_Open: esta é a sub-rotina específica. Ele executa seu código automaticamente quando o arquivo Excel é aberto - este é o evento que aciona o procedimento. Auto_Open só será executado quando a pasta de trabalho for aberta manualmente; ele não será executado se a pasta de trabalho for aberta por meio de código de outra pasta de trabalho (Workbook_Open fará isso, saiba mais sobre a diferença entre os dois).
  • Por padrão, o acesso de uma sub-rotina é público. Isso significa que qualquer outro módulo pode usar esta sub-rotina. Todos os exemplos neste tutorial serão sub-rotinas públicas. Se necessário, você pode declarar sub-rotinas como privadas. Isso pode ser necessário em algumas situações. Saiba mais sobre modificadores de acesso de sub-rotina.
  • msgBox: esta é uma função - um grupo de instruções VBA que executa uma ou mais ações e retorna um valor. O valor retornado é a mensagem “Bem-vindo à pasta de trabalho XYZ”.

Resumindo, esta é uma sub-rotina simples que contém uma função.

Quando posso usar isso?

Talvez você tenha um arquivo muito importante que é acessado com pouca frequência (digamos, uma vez por trimestre), mas é atualizado automaticamente diariamente por outro procedimento VBA. Quando ele é acessado, é por muitas pessoas em vários departamentos, em toda a empresa.

  • Problema: na maioria das vezes, quando os usuários acessam o arquivo, eles ficam confusos sobre a finalidade desse arquivo (por que ele existe), como ele é atualizado com tanta frequência, quem o mantém e como devem interagir com ele. Os novos contratados sempre têm toneladas de perguntas, e você precisa respondê-las continuamente.
  • Solução: crie uma mensagem para o usuário que contenha uma resposta concisa para cada uma dessas perguntas frequentes.

Exemplos do mundo real

  • Use a função MsgBox para exibir uma mensagem quando houver algum evento: o usuário fecha uma pasta de trabalho do Excel, o usuário imprime, uma nova planilha é adicionada à pasta de trabalho, etc.
  • Use a função MsgBox para exibir uma mensagem quando um usuário precisar preencher uma condição antes de fechar uma pasta de trabalho do Excel
  • Use a função InputBox para obter informações do usuário

Exemplo 2: Permitir que o usuário execute outro procedimento

No Editor VBA, selecione Inserir -> Novo Módulo

Escreva este código na janela do módulo (não cole!):

Sub UserReportQuery ()

Dim UserInput enquanto

Dim Answer As Integer

UserInput = vbYesNo

Answer = MsgBox ("Processar o relatório XYZ?", UserInput)

Se Resposta = vb Sim, então ProcessReport

End Sub

Sub ProcessReport ()

MsgBox ("Obrigado por processar o relatório XYZ.")

End Sub

Salve e navegue de volta para a guia Desenvolvedor do Excel e selecione a opção “Botão”. Clique em uma célula e atribua a macro UserReportQuery ao botão.

Agora clique no botão. Esta mensagem deve ser exibida:

Clique em “sim” ou pressione Enter.

Mais uma vez, tada!

Observe que a sub-rotina secundária, ProcessReport, pode ser qualquer coisa . Vou demonstrar mais possibilidades no exemplo # 3. Mas primeiro...

Como está fazendo isso?

Este exemplo se baseia no exemplo anterior e possui alguns elementos novos. Vamos revisar as coisas novas:

  • Dim UserInput As Long: Dim é a abreviação de “dimensão” e permite declarar nomes de variáveis. Nesse caso, UserInput é o nome da variável e Long é o tipo de dados. Em inglês simples, esta linha significa “Aqui está uma variável chamada“ UserInput ”e é um tipo de variável longa.”
  • Dim Answer As Integer: declara outra variável chamada “Answer,” com um tipo de dados Integer. Saiba mais sobre os tipos de dados aqui.
  • UserInput = vbYesNo: atribui um valor à variável. Nesse caso, vbYesNo, que exibe os botões Sim e Não. Existem muitos tipos de botão, saiba mais aqui.
  • Answer = MsgBox (“Processar o relatório XYZ?”, UserInput): atribui o valor da variável Answer como uma função MsgBox e a variável UserInput. Sim, uma variável dentro de uma variável.
  • If Answer = vbYes Then ProcessReport: esta é uma “declaração If”, uma declaração condicional, que nos permite dizer se x é verdadeiro, então faça y. Neste caso, se o usuário selecionou “Sim”, execute a sub-rotina ProcessReport.

Quando posso usar isso?

Isso pode ser usado de muitas maneiras. O valor e a versatilidade dessa funcionalidade são mais definidos pelo que a sub-rotina secundária faz.

Por exemplo, talvez você tenha um arquivo que é usado para gerar 3 relatórios semanais diferentes. Esses relatórios são formatados de maneiras dramaticamente diferentes.

  • Problema: Cada vez que um desses relatórios precisa ser gerado, um usuário abre o arquivo e altera a formatação e os gráficos; e assim por diante. Este arquivo está sendo editado extensivamente pelo menos 3 vezes por semana e leva pelo menos 30 minutos cada vez que é editado.
  • Solução: crie 1 botão por tipo de relatório, que reformata automaticamente os componentes necessários dos relatórios e gera os gráficos necessários.

Exemplos do mundo real

  • Crie uma caixa de diálogo para o usuário preencher automaticamente certas informações em várias planilhas
  • Use a função InputBox para obter informações do usuário, que são preenchidas em várias planilhas

Exemplo # 3: adicionar números a um intervalo com um loop For-Next

Os loops For são muito úteis se você precisar realizar tarefas repetitivas em um intervalo específico de valores - arrays ou intervalos de células. Em inglês simples, um loop diz “para cada x, faça y”.

No Editor VBA, selecione Inserir -> Novo Módulo

Escreva este código na janela do módulo (não cole!):

Sub LoopExample ()

Dim X como inteiro

Para X = 1 a 100

Intervalo ("A" e X) .Valor = X

Próximo X

End Sub

Salve e navegue de volta para a guia Desenvolvedor do Excel e selecione o botão Macros. Execute a macro LoopExample.

Isso deve acontecer:

Etc, até a 100ª carreira.

Como está fazendo isso?

  • Dim X As Integer: declara a variável X como um tipo de dados de Integer.
  • Para X = 1 a 100: este é o início do loop For. Simplificando, ele diz ao loop para continuar repetindo até X = 100. X é o contador . O loop continuará em execução até X = 100, execute uma última vez e depois pare.
  • Range ("A" & X) .Value = X: declara o intervalo do loop e o que colocar nesse intervalo. Como X = 1 inicialmente, a primeira célula será A1, ponto em que o loop colocará X nessa célula.
  • Próximo X: isso diz ao loop para ser executado novamente

Quando posso usar isso?

O loop For-Next é uma das funcionalidades mais poderosas do VBA; existem vários casos de uso potenciais. Este é um exemplo mais complexo que exigiria várias camadas de lógica, mas comunica o mundo de possibilidades em loops For-Next.

Talvez você tenha uma lista de todos os produtos vendidos em sua padaria na coluna A, o tipo de produto na coluna B (bolos, donuts ou muffins), o custo dos ingredientes na coluna C e o custo médio de mercado de cada tipo de produto em outra folha.

Você precisa descobrir qual deve ser o preço de varejo de cada produto. Você está pensando que deveria ser o custo dos ingredientes mais 20%, mas também 1,2% abaixo da média do mercado, se possível. Um loop For-Next permitiria que você fizesse esse tipo de cálculo.

Exemplos do mundo real

  • Use um loop com uma instrução if aninhada para adicionar valores específicos a uma matriz separada apenas se eles atenderem a certas condições
  • Execute cálculos matemáticos em cada valor em um intervalo, por exemplo, calcule cobranças adicionais e adicione-as ao valor
  • Faça um loop em cada caractere em uma string e extraia todos os números
  • Selecione aleatoriamente vários valores de uma matriz

Conclusão

Agora que falamos sobre pizza e bolinhos e, ah, sim, como escrever código VBA em planilhas do Excel, vamos fazer uma verificação de aprendizado. Veja se você pode responder a essas perguntas.

  • O que é VBA?
  • Como posso começar a usar o VBA no Excel?
  • Por que e quando você usaria o VBA?
  • Quais são alguns problemas que eu poderia resolver com o VBA?

Se você tem uma ideia razoável de como poderia responder a essas perguntas, então foi um sucesso.

Quer você seja um usuário ocasional ou um usuário avançado, espero que este tutorial forneça informações úteis sobre o que pode ser realizado com apenas um pouco de código em suas planilhas do Excel.

Boa codificação!

Recursos de aprendizagem

  • Excel VBA Programming for Dummies, John Walkenbach
  • Introdução ao VBA, documentação da Microsoft
  • Aprendendo VBA no Excel, Lynda

Um pouco sobre mim

Sou Chloe Tucker, artista e desenvolvedora em Portland, Oregon. Como ex-educador, estou continuamente buscando a interseção de aprendizagem e ensino, ou tecnologia e arte. Fale comigo no Twitter @_chloetucker e verifique meu site em chloe.dev.