Você já fez automações no Excel? Nesta aula de Excel VBA para Automações vamos rodar macros para facilitar tarefas repetidas!
Caso prefira esse conteúdo no formato de vídeo-aula, assista ao vídeo abaixo ou acesse o nosso Canal do YouTube para mais vídeos!
Para receber por e-mail o(s) arquivo(s) utilizados na aula, preencha:
Fala Impressionadores! Na aula de hoje eu quero te mostrar como usar o VBA para facilitar o preenchimento de dados e como usar eventos para disparar macros automaticamente.
A ideia é que você consiga disparar suas macros de forma automática sem que você precise clicar em um botão ou ativar sua macro de forma manual.
Por esse motivo é que nós vamos utilizar os Eventos no VBA, que nada mais são do que “gatilhos” para ativar a nossa macro.
Nessa aula vamos ver o evento “Change”, ou seja, sempre que mudarmos a informação de uma célula a nossa macro será executada de forma automática sem que nenhum botão seja necessário.
E aí, quer aprender como rodar macros automaticamente utilizando eventos VBA?
Então vem comigo que eu vou te ensinar a criar macros automáticas com eventos!
O que vamos ver nesta aula? – Excel VBA para Automações
- Como usar eventos para disparar macros automáticamente
- Como usar o VBA para facilitar o preenchimento de dados
- Extra -> Como enviar e-mail pelo VBA
Vamos trabalhar com essa planilha de entrada de pagamentos, você pode baixar essa planilha no material didático desta aula, assim você obtêm junto todo o código da aula.
Objetivo
Nosso objetivo é fazer alguns preenchimentos automáticos usando o VBA.
Por exemplo, nesta planilha temos alguns IDs, então é interessante que assim que colocarmos um ID para preencher a lista as outras informações como o nome e unidade já sejam inseridas automaticamente a partir do ID digitado.
Outra informação automática que podemos inserir é a data do último pagamento e quantos dias fazem desde o último pagamento executado pelo cliente na loja.
Primeiros passos – Excel VBA para Automações
Clique no atalho alt+F11 para acessar o ambiente VBA, na planilha de exemplo, você já vai encontrar todo o código que executa todas essas ações, aqui iremos apenas explicar cada parte dele.
Como queremos criar uma macro que é um evento (disparado com o uso de uma célula), não vamos criar criá-la em Módulo, vamos iniciar o código da seguinte forma:
Private Sub Worksheet_Change(ByVal Target As Range)Observe que começamos com o Private, disparado por uma Change, dessa forma uma mudança na célula (target) dispara o evento.
Sempre que criarmos o evento temos que colocar uma condição, porque sem a condição o evento será disparado a cada célula clicada sem necessidade, e não apenas isso, ainda vai tornar a planilha mais pesada.
Como estamos preenchendo o ID para buscar os valores na coluna B ou coluna 2, então podemos colocar esta condição para o evento, ser somente disparado se a célula alterada for da coluna 2 da tabela.
Outro ponto é que quando colocamos um ID na coluna dois, vamos precisar verificar acima se o ID já existe, se já existir vamos completar as células do lado com as informações iguais as que existem na linha acima com o mesmo ID, esta é a lógica que queremos aplicar.
Porém, se as células ao lado já estiverem preenchidas não vamos preencher por cima dos resultados que já existem e foram colocados de forma manual, esse preenchimento de completar as informações só irá acontecer se as células a serem completadas estiverem vazias e esta é outra condição para o código.
A primeira verificação a ser feita é se existe o ID digitado, então vamos procurar por uma combinação nas células acima, se houver, teremos informação para preencher, se não, então é a primeira vez que estamos usando esse ID e todas as informações sobre ele serão preenchidas manualmente.
Mas, existe outro ponto, estamos fazendo o código verificar as células acima para encontrar uma combinação para o ID digitado antes mesmo de verificar se as células ao lado do ID estão vazias ou não, isso não faz muito sentido, certo?
Afinal, se o espaço para colocar a informação não estiver vazio nosso código não está sendo programado para sobrepor informações, então, se o espaço não existe, o código nem deveria começar a procurar acima uma combinação para o ID digitado, isso seria uma perda de tempo.
Então, vamos verificar antes se existe o espaço vazio e, após confirmar que o espaço existe aí, sim, verificar as combinações e preencher o espaço se encontrar a combinação exata.
Primeira verificação:
Todos os valores ao lado do ID estão vazios?
Como indicar ao código quais células verificar?
Observe que vamos verificar sempre olhando a partir do target (célula onde digitamos o último ID), então estamos vendo a primeira célula ao lado do target, a segunda, a terceira e assim por diante.
Private Sub Worksheet_Change(ByVal Target As Range)
If Target.Column = 2 Then
If Target.Offset(0, 1).Value = "" Or Target.Offset(0, 2).Value = "" _
Or Target.Offset(0, 3).Value = "" Or Target.Offset(0, 4).Value = "" _
Or Target.Offset(0, 5).Value = "" Then
End If
End IfVamos assumir que neste caso existe pelo menos uma célula com espaço para ser preenchido, então faz sentido fazer a próxima verificação -> ver se o ID já foi usado anteriormente.
Então vamos verificar todas as linhas acima com exceção da linha do target.
Private Sub Worksheet_Change(ByVal Target As Range)
If Target.Column = 2 Then
If Target.Offset(0, 1).Value = "" Or Target.Offset(0, 2).Value = "" _
Or Target.Offset(0, 3).Value = "" Or Target.Offset(0, 4).Value = "" _
Or Target.Offset(0, 5).Value = "" Then
For i = Target.Row – 1 To 2 Step -1Caso você tenha dúvidas sobre o comando FOR pode clicar no link que temos uma aula explicando seu funcionamento.
Após o comando FOR podemos utilizar o If para inserir uma condição.
If Target.Offset(0, 1).Value = "" Or Target.Offset(0, 2).Value = "" _
Or Target.Offset(0, 3).Value = "" Or Target.Offset(0, 4).Value = "" _
Or Target.Offset(0, 5).Value = "" Then
For i = Target.Row - 1 To 2 Step -1
If Cells(i, 2).Value = Target.Value Then
End If
Next i
End IfFeito isso, vamos analisar se a célula está vazia, então ela vai receber o valor que está na mesma coluna da linha i, porque acabamos de analisar que i corresponde ao ID correspondente ao target:
If Target.Column = 2 Then
If Target.Offset(0, 1).Value = "" Or Target.Offset(0, 2).Value = "" _
Or Target.Offset(0, 3).Value = "" Or Target.Offset(0, 4).Value = "" _
Or Target.Offset(0, 5).Value = "" Then
For i = Target.Row - 1 To 2 Step -1
If Cells(i, 2).Value = Target.Value Then
If Target.Offset(0, 1).Value = "" Then Target.Offset(0, 1).Value = Cells(i, 3).Value
If Target.Offset(0, 2).Value = "" Then Target.Offset(0, 2).Value = Cells(i, 4).Value
If Target.Offset(0, 3).Value = "" Then Target.Offset(0, 3).Value = Cells(i, 5).Value
If Target.Offset(0, 4).Value = "" Then Target.Offset(0, 4).Value = Cells(i, 1).Value
diferencaDias = Target.Offset(0, -1).Value - Cells(i, 1).Value
If Target.Offset(0, 5).Value = "" Then Target.Offset(0, 5).Value = diferencaDias
End If
Next i
End IfObserve que, na linha 4 o padrão de encontrar o valor muda um pouco, neste caso vamos encontrar o valor na 1º célula da coluna A, veja a imagem abaixo:
Para evitar erros também vamos acrescentar uma linha que identifica se estamos mexendo em mais de uma célula de uma só vez.
Se estivermos é provável que estamos apagando ou editando uma informação de forma manual, então é melhor sair da macro para evitar um aviso de erro.
If Target.Cells.Count > 1 Then Exit Sub
Código
Private Sub Worksheet_Change(ByVal Target As Range)
If Target.Cells.Count > 1 Then Exit Sub
If Target.Column = 2 Then
If Target.Offset(0, 1).Value = "" Or Target.Offset(0, 2).Value = "" _
Or Target.Offset(0, 3).Value = "" Or Target.Offset(0, 4).Value = "" _
Or Target.Offset(0, 5).Value = "" Then
For i = Target.Row - 1 To 2 Step -1
If Cells(i, 2).Value = Target.Value Then
If Target.Offset(0, 1).Value = "" Then Target.Offset(0, 1).Value = Cells(i, 3).Value
If Target.Offset(0, 2).Value = "" Then Target.Offset(0, 2).Value = Cells(i, 4).Value
If Target.Offset(0, 3).Value = "" Then Target.Offset(0, 3).Value = Cells(i, 5).Value
If Target.Offset(0, 4).Value = "" Then Target.Offset(0, 4).Value = Cells(i, 1).Value
If Target.Offset(0, 5).Value = "" Then Target.Offset(0, 5).Value = Target.Offset(0, -1).Value - Cells(i, 1).Value
End If
Next i
End IfAgora temos um código onde podemos colocar manualmente as informações na tabela na primeira vez e a partir da segunda vez, se houver espaço e uma combinação anterior com o ID, então o preenchimento é feito de forma automática.
Para deixar ainda mais eficiente podemos fazer com que assim que o código encontrar o ID e preencher os espaços, ele pare de verificar as outras linhas, afinal já preenchemos a informação que precisávamos.
Para fazer isso vamos colocar após o For o código -> Exit For
Dessa forma o código fica mais eficiente, imagine que em uma planilha com 1 milhão de linhas, assim que o código encontrar a correspondência ele vai parar e poupar tempo de processamento do código.
Outro ponto de melhora é que estamos calculando a quantidade de dias em que o último pagamento foi realizado, podemos tomar uma ação caso esse tempo seja muito grande.
Então, se o último pagamento foi a mais de 30 dias podemos disparar um e-mail para entrarem em contato com o cliente ou um e-mail direto para o cliente.
O primeiro passo é verificar se a diferença é maior que 30 dias:
Private Sub Worksheet_Change(ByVal Target As Range)
If Target.Cells.Count > 1 Then Exit Sub
If Target.Column = 2 Then
If Target.Offset(0, 1).Value = "" Or Target.Offset(0, 2).Value = "" _
Or Target.Offset(0, 3).Value = "" Or Target.Offset(0, 4).Value = "" _
Or Target.Offset(0, 5).Value = "" Then
For i = Target.Row - 1 To 2 Step -1
If Cells(i, 2).Value = Target.Value Then
If Target.Offset(0, 1).Value = "" Then Target.Offset(0, 1).Value = Cells(i, 3).Value
If Target.Offset(0, 2).Value = "" Then Target.Offset(0, 2).Value = Cells(i, 4).Value
If Target.Offset(0, 3).Value = "" Then Target.Offset(0, 3).Value = Cells(i, 5).Value
If Target.Offset(0, 4).Value = "" Then Target.Offset(0, 4).Value = Cells(i, 1).Value
diferencaDias = Target.Offset(0, -1).Value - Cells(i, 1).Value
If Target.Offset(0, 5).Value = "" Then Target.Offset(0, 5).Value = diferencaDias
Exit For
End If
Next i
End IfSe o último pagamento for a mais que 30 dias vamos encaminhar para este cliente um e-mail, podemos colocar essa informação como comentário ao lado do código por uma questão de organização.
If diferencaDias > 30 Then ' O último pagamento foi há mais de 30 dias e, por isso, vamos notificar a secretariaLogo após, vamos criar a estrutura para o e-mail ser enviado, essa estrutura está explicada detalhadamente em duas aulas sobre Como Enviar E-mail com VBA aqui do blog, se quiser aprender mais clique no link!
Código do E-mail:
Dim outlook As Object, novo_email As Object
Set outlook = CreateObject("Outlook.Application")
Set novo_email = outlook.CreateItem(0)
'Exibir a caixa de e-mail
'novo_email.display
'Definir o destinatário e o assunto
novo_email.To = "[email protected]"
novo_email.Subject = "Aviso sobre o ID " & Target.Value
'Escrevendo o corpo to e-mail
novo_email.HTMLbody = "Olá,<br><br>Esse e-mail automático foi enviado" _
& " para te informar que o(a) aluno(a) " & Target.Offset(0, 1).Value _
& " ficou " & Target.Offset(0, 5).Value & _
" dias sem efetuar um pagamento.<br><br>Att,<br>Diego Amorim"
'Enviando o e-mail
novo_email.Send
MsgBox ("E-mail enviado!")
Set outlook = Nothing
Set novo_email = Nothing
End If
End If
End SubEntão, caso algum dos IDs apresente o tempo de pagamento maior do que 30 dias, o código vai ler esse tempo e enviar o e-mail conforme o código acima, observe que o e-mail é gerado automaticamente com o código.
Após criar o e-mail o código já completa o destinatário e o assunto concatenando com o ID correspondente.
No corpo do e-mail também é enviado de forma automática uma mensagem escrita em HTML e após isso o código envia o e-mail e dispõe uma mensagem de confirmação -> e-mail enviado.
Código Completo VBA – Excel VBA para Automações
Private Sub Worksheet_Change(ByVal Target As Range)
If Target.Cells.Count > 1 Then Exit Sub
If Target.Column = 2 Then
If Target.Offset(0, 1).Value = "" Or Target.Offset(0, 2).Value = "" _
Or Target.Offset(0, 3).Value = "" Or Target.Offset(0, 4).Value = "" _
Or Target.Offset(0, 5).Value = "" Then
For i = Target.Row - 1 To 2 Step -1
If Cells(i, 2).Value = Target.Value Then
If Target.Offset(0, 1).Value = "" Then Target.Offset(0, 1).Value = Cells(i, 3).Value
If Target.Offset(0, 2).Value = "" Then Target.Offset(0, 2).Value = Cells(i, 4).Value
If Target.Offset(0, 3).Value = "" Then Target.Offset(0, 3).Value = Cells(i, 5).Value
If Target.Offset(0, 4).Value = "" Then Target.Offset(0, 4).Value = Cells(i, 1).Value
diferencaDias = Target.Offset(0, -1).Value - Cells(i, 1).Value
If Target.Offset(0, 5).Value = "" Then Target.Offset(0, 5).Value = diferencaDias
Exit For
End If
Next i
End If
If diferencaDias > 30 Then ' O último pagamento foi há mais de 30 dias e, por isso, vamos notificar a secretaria
Dim outlook As Object, novo_email As Object
Set outlook = CreateObject("Outlook.Application")
Set novo_email = outlook.CreateItem(0)
'Exibir a caixa de e-mail
'novo_email.display
'Definir o destinatário e o assunto
novo_email.To = "[email protected]"
novo_email.Subject = "Aviso sobre o ID " & Target.Value
'Escrevendo o corpo to e-mail
novo_email.HTMLbody = "Olá,<br><br>Esse e-mail automático foi enviado" _
& " para te informar que o(a) aluno(a) " & Target.Offset(0, 1).Value _
& " ficou " & Target.Offset(0, 5).Value & _
" dias sem efetuar um pagamento.<br><br>Att,<br>Diego Amorim"
'Enviando o e-mail
novo_email.Send
MsgBox ("E-mail enviado!")
Set outlook = Nothing
Set novo_email = Nothing
End If
End If
End SubConclusão – Excel VBA para Automações
Nesta aula trouxe um exemplo prático de como automatizar tarefas no Excel com VBA.
Essas automações fazem com que seu trabalho seja muito mais simples e fácil, o interessante é você entender como é feita a lógica por trás da automação e com isso ter ferramentas para inovar nos seus trabalhos!
Observe os exemplos da aula e as aulas extras que estão nos links propostos nessa aula!
Espero que tenha gostado! Eu fico por aqui! Um Abraço,
Hashtag Treinamentos
Para acessar outras publicações de VBA, clique aqui!
Expert em conteúdos da Hashtag Treinamentos. Auxilia na criação de conteúdos de variados temas voltados para aqueles que acompanham nossos canais.