VBA: automatizando a comparação entre planilhas e dados

Comparando dados entre planilhas - Apresentação

Olá! Eu sou o Roberto Sabino, instrutor na Alura, e este é o quarto passo da formação VBA: da macro ao código. Estamos muito empolgados para este curso e esperamos que você também esteja!

Audiodescrição: Roberto se descreve como um homem de pele clara, cabelo e barba escuros, talvez um pouco grisalhos, e olhos castanhos. Ele veste uma camiseta preta totalmente lisa, e a parede ao fundo também é lisa, iluminada em tons de azul.

Neste curso, falaremos sobre dia a dia: trabalharemos com ferramentas que podemos usar no cotidiano, utilizando Excel, automatizando algumas tarefas, e construindo uma caixa de ferramentas para nos auxiliar em tarefas diárias.

Para quem é este curso?

Este curso é para você que deseja se aprofundar em VBA. Adiantamos que o conteúdo está bastante completo e, ao longo das aulas, criamos códigos muito interessantes.

O que vamos aprender?

No decorrer do curso, entenderemos como usar o VBA no dia a dia, e ao final, saberemos como construir uma planilha com capacidade de comparar intervalos de outras planilhas gravadas no computador.

Nesse processo, podemos escolher a pasta de trabalho e qual planilha desejamos comparar como origem. Depois, conseguimos indicar qual intervalo específico queremos comparar.

Em seguida, conseguimos definir uma pasta de destino, que pode ser a mesma pasta, mas também há a possibilidade de escolher outra. Nesse caso, faremos isso para comparar com outra planilha.

Observe o exemplo de comparação abaixo:

• Caminho Pasta: C:\Projetos Alura\

• Pasta Origem: Planilha Modelo.xlsx

• Planilha Origem: Produtos

• Intervalo Origem: B4:f31

• Pasta Destino: Planilha Modelo.xlsx

• Planilha Destino: Produtos 2

• Intervalo Destino: B4:f31

Perceba que pedimos para comparar a Planilha Modelo.xlsx com a mesma Planilha Modelo.xlsx, mas no final, comparamos a planilha Produtos com a planilha Produtos 2.

Tudo isso foi feito lendo a pasta "Projetos Alura" do nosso computador. O programa analisou a máquina, identificou o conteúdo dela, e trouxe as opções. A partir disso, fizemos a comparação.

Após clicar em "Comparar", o programa fará a comparação, marcará as diferenças existentes, e ainda fará uma lista delas, indicando em uma nova planilha Diferenças quais células possuem diferenças.

Aprendizagem ativa

O mais interessante é que você irá aprender a construir e a melhorar todo esse código, não apenas usar a planilha. Lembre-se: este curso foi desenvolvido para que você tenha uma aprendizagem ativa, então além de ter contato com os conteúdos, você terá outros meios de aprendizado.

Por exemplo: temos o fórum, onde você pode tirar suas dúvidas e interagir com outras pessoas estudantes. Além disso, teremos desafios para aprendermos com a prática, trabalhando em conjunto com o intuito de obter o melhor resultado possível de aprendizagem.

Conclusão

Percebeu como este curso será sensacional? Estamos empolgados para começar e aguardamos você na primeira aula. Lembre-se que iremos trabalhar juntos, então se prepare para colocar a mão na massa e aprender muito sobre VBA Excel neste passo 4 da formação!

Comparando dados entre planilhas - Entendendo a jornada

Chegou a hora de começarmos a trabalhar no quarto passo da formação VBA: da macro ao código. Acreditamos que este é o momento de acelerar um pouco e construir algumas coisas mais complexas para utilizarmos no dia a dia e melhorarmos o trabalho com o VBA nas nossas planilhas Excel.

Entendendo a jornada

Se você fez a formação Business Intelligence com Excel ou a formação Excel, deve se lembrar da planilha do Café Alura, utilizada em alguns cursos. A partir dessa planilha, queremos te mostrar algo.

Executando a macro de comparação de planilhas

Imagine que, nessa planilha, temos duas listas de produtos (Produtos e Produtos (2)) com algumas pequenas diferenças.

A lista Produtos não é tão grande, então poderíamos usar a função PROCV para buscar as diferenças. Porém, também podemos criar um VBA muito útil para mostrar essas diferenças.

Com a lista aberta, usaremos o atalho "Ctrl + Shift + Espaço" para selecionar todas as informações da tabela. Feito isso, vamos acessar a aba "Desenvolvedor" no menu superior e executar a macro que compara planilhas, clicando em "Macros > 'VBA 4.xlsm'!sbComparaPlanilhas > Executar".

Em seguida, surgirá a janela "Planilha Comparar" com a mensagem "Escolha a planilha que deve ser a base para comparação!". Nesse caso, queremos comparar com a Produtos (2) da mesma pasta de trabalho.

Após selecionar, o Excel faz a comparação e mostra que existe uma diferença na célula D11, ou seja, há apenas uma diferença entre as duas planilhas. Se compararmos manualmente, reparamos que realmente somente esta célula está diferente. Assim, temos um VBA bastante útil para o nosso dia a dia.

Como conseguimos executar a macro?

Talvez você não tenha reparado em algumas coisas tão importantes quanto o que acabamos de fazer. Por exemplo: a planilha com a qual trabalhamos (Planilha Modelo.xlsx) tem o formato .xlsx.

Neste passo da formação, você pode pensar o seguinte: não deveríamos gravar a planilha como .xlsm para conseguir rodar uma macro? Sim, ainda é necessário fazer isso. Rodamos a macro no exemplo anterior, mas ela não está nesta planilha, e sim em outra planilha chamada VBA 4.xlsm.

É possível ter uma planilha com várias macros que podemos executar em outras planilhas. Isso significa que qualquer VBA que fizermos poderá ser executado dessa forma? Na verdade, não.

Precisamos escolher a linguagem ou os comandos corretos para conseguirmos fazer esse tipo de utilização. Se usarmos nomes de planilhas, provavelmente não funcionará. Da mesma forma, provavelmente haverá erro se usarmos nomes de tabelas. Portanto, devemos fazer as coisas de uma maneira mais genérica, e essa é uma das coisas que vamos aprender durante este curso.

Outro ponto importante é que misturamos algumas coisas abordadas nos cursos anteriores. Por exemplo: quando executamos a macro, foi aberto um formulário. A janela que mencionamos anteriormente, com a mensagem "Escolha a planilha que deve ser a base para comparação!", é um formulário.

Portanto, usamos o repertório que já temos, aumentando alguns detalhes para fazermos coisas que serão úteis para o nosso dia a dia.

Falando sobre a linguagem VB6

Para você entender melhor o que queremos fazer neste curso, vamos conhecer uma história rápida. No começo dos anos 2000, o instrutor Roberto era desenvolvedor VB6.

O VB6 é uma linguagem de programação, praticamente não utilizada hoje em dia, mas no início dos anos 2000, no Brasil, ainda era usado como plataforma Microsoft o VB6, o ASP, e depois eles foram substituídos por outras linguagens de programação.

Esse é um dos motivos pelos quais o instrutor gosta tanto do VBA: porque ele é quase igual à linguagem VB6, mas possui os objetos dos nossos aplicativos. Portanto, o VB6 é o "pai" do VBA.

Naquela época, o instrutor desenvolvia produtos profissionais, ou seja, trabalhava com produtos complexos. Às vezes, ele tinha dúvidas, e no passado, não se usava tanto o Google. Não havia tanta informação disponível e era necessário pesquisar um pouco mais.

Nesse cenário, era interessante ter em mãos algumas coisas disponíveis. Havia um colega desenvolvedor que quando perguntávamos algo, sempre respondia: "Eu não sei fazer isso, mas tenho um exemplo de código que pode te ajudar". A partir desse exemplo de código, fazíamos um "Ctrl + C" e "Ctrl + V", seguidos de alguns ajustes, e tudo funcionava bem.

Neste curso, também queremos trazer alguns exemplos. Construiremos alguns códigos bastante úteis, que são estruturas que você pode usar no seu dia a dia para fazer outras coisas.

Por exemplo: no caso anterior, há um código que nos ajuda a comparar duas planilhas, independentemente de quais planilhas são essas. Não importa quantas colunas elas possuem, quantas linhas, quais os tipos de dados, se são numéricos ou não; apesar disso, ele irá comparar as planilhas. Isso é bastante útil em várias situações e você pode modificar conforme as suas necessidades.

Conclusão

Portanto, passaremos por uma série de exemplos de códigos que você poderá aplicar no seu cotidiano. Começaremos com o código da comparação: vamos entender como fazer este código, e como sempre, disponibilizaremos as planilhas para você utilizar.

Porém, também queremos explicar uma filosofia de utilização destes exemplos. Como tornamos o nosso código mais genérico? Como fazemos para o nosso código ser mais útil para várias situações, e não para uma situação específica? Quais comandos vamos utilizar para fazer essas coisas?

Primeiramente, entenderemos o código pronto e faremos eles um pouco mais fixos. À medida que avançarmos nas aulas, construiremos códigos mais elaborados. Nessa jornada, passaremos por algumas soluções e apresentaremos alguns exemplos que você conseguirá aplicar no dia a dia para automatizar suas tarefas e melhorar ainda mais a sua produtividade!

Comparando dados entre planilhas - Código de comparação

Neste curso, diferente dos anteriores, não vamos escrever o código durante as aulas; traremos o código pronto. Assim, conseguimos avançar mais rápido e você pode aproveitar melhor o conhecimento que já tem.

Código de comparação

O que precisa ser feito para comparar duas planilhas? A primeira coisa que precisamos considerar é que comparar duas planilhas significa comparar dois ranges, isto é, dois intervalos. Para isso, precisamos especificar qual planilha queremos comparar com qual, o que fazemos no formulário.

Esse formulário possui alguns pontos interessantes, mas falaremos mais sobre ele no próximo vídeo, para facilitar nosso processo e manter os assuntos separados.

Basicamente, precisamos percorrer o intervalo e, de alguma forma, relacioná-lo com a outra planilha. No nosso caso, comparamos as mesmas células. Por exemplo: se selecionamos de B5 até E13 na planilha Produtos (2), na planilha Produtos, vamos comparar com o mesmo intervalo (B5:E13).

Porém, isso pode mudar, e nesse cenário, conseguimos fazer um código mais flexível. No nosso caso, fizemos um código bastante simples, mas iremos aprimorá-lo posteriormente.

Se você quiser tentar fazer esse código com o que já aprendeu nos cursos anteriores, é possível. Basta usar a instrução For Each. Caso queira testar, pause o vídeo agora e tente construir manualmente, da forma mais simples possível, pois na sequência, mostraremos o código pronto.

Conhecendo a estrutura do projeto

Primeiramente, lembre-se que o código não está na planilha Planilha Modelo.xlsx, e sim em uma planilha vazia chamada VBA 4.xlsm. Devemos ter muita atenção para não usar nomes de tabelas, de planilhas, ou de intervalos específicos. Precisamos construir da forma mais genérica possível.

Começaremos com duas planilhas abertas no Microsoft Visual Basic for Applications. No momento, podemos ignorar o Solver (SOLVER.XLAM) e o VBAProject (PERSONAL.XLSB) da tela do instrutor, pois pode ser que o seu Excel os exiba ou não, e não é algo com que precisamos nos preocupar.

Posteriormente, vamos explicar o que é PERSONAL.XLSB. Já abordamos rapidamente nos outros cursos, mas entenderemos novamente o que ele é em breve.

Neste momento, vamos fixar nossa atenção nas planilhas. Primeiro, temos a planilha Planilha Modelo.xlsx, que não tem nenhum item de VBA; e depois temos a planilha VBA 4.xlsm, que tem um formulário e um módulo chamado Aula1, para facilitar no momento de visualizar os códigos.

• VBAProject (Planilha Modelo.xlsx)
  • Microsoft Excel Objetos
    • EstaPastaDeTrabalho
    • Planilha1 (Produtos)
    • Planilha2 (Produtos (2))
    • Planilha3 (Planilha2)
• VBAProject (VBA 4.xlsm)
  • Microsoft Excel Objetos
    • EstaPastaDeTrabalho
    • Planilha3 (VBA4)
  • Formulários
    • frmEscolhaPlanilha
  • Módulos
    • Aula1

Observe que a planilha VBA 4.xlsm não tem praticamente nada, apenas uma planilha chamada Planilha3 (VBA4). É justamente essa a ideia de usar os VBAs em outras planilhas.

Analisando o código de comparação

Vamos percorrer o código e entender o que ele faz? Começaremos pelo trecho abaixo:

Módulos > Aula1:

'Diretiva para "obrigar" a declaração de variáveis
Option Explicit

Public sNomePlanComparar As String

' CÓDIGO OMITIDO

Já sabemos o que é Option Explicit, pois já trabalhamos com isso. Ele nos obriga a fazer a declaração de variáveis. Recomendamos sempre utilizar isso, conforme dito anteriormente.

Explicaremos o que é Public quando falarmos sobre o formulário. Por enquanto, precisamos saber apenas que o sNomePlanComparar é uma string. No próximo vídeo, abordaremos com mais detalhes.

Na sequência, começamos a Sub sbComparaPlanilhas(), que por sua vez, começa com uma declaração de variáveis:

' CÓDIGO OMITIDO

Sub sbComparaPlanilhas()

    'Declaração de variáveis
    Dim rCelula                 As Range
    Dim iDiferencas             As Integer

' CÓDIGO OMITIDO

Declaramos uma variável do tipo Range chamada rCelula, e uma variável do tipo Integer chamada iDiferencas, para verificar os intervalos que vamos percorrer e as diferenças, respectivamente.

Em seguida, fizemos algumas verificações:

' CÓDIGO OMITIDO

'Verifica se existem pelo menos 2 planilhas na pasta
If ActiveWorkbook.Sheets.Count <= 1 Then
    MsgBox "Não há planilhas suficientes para comparação"
    Exit Sub
End If

' CÓDIGO OMITIDO

A verificação acima nos ajuda a ter uma premissa de qual é o ambiente. Por exemplo: nesse caso, verificamos se existem pelo menos duas planilhas na pasta.

Isso significa que, se houver somente uma planilha na pasta, não podemos compará-la com ela mesma, pois sempre será igual. Portanto, precisamos ter, pelo menos, duas planilhas para fazer sentido compará-las.

Se por acaso, não houver pelo menos duas planilhas na pasta, receberemos a mensagem (MsgBox) "Não há planilhas suficientes para comparação" e sairemos da Sub.

Após isso, inicializamos a variável para informar quantas diferenças existem nas duas planilhas, e depois chamamos o formulário. No próximo vídeo, entenderemos como trabalhar com o formulário.

' CÓDIGO OMITIDO

'Inicializa a variável de diferenças
iDiferencas = 0

frmEscolhaPlanilha.Show

' CÓDIGO OMITIDO

No formulário, escolhemos quais planilhas queremos comparar. No total, temos três planilhas, e comparamos a que está selecionada com as demais. Para isso, temos o seguinte código:

' CÓDIGO OMITIDO

'Verifica se as demais planilha destino é igual a planilha origem
For Each rCelula In Selection
    If rCelula.Value <> Sheets(sNomePlanComparar).Range(rCelula.Address) Then
        'Muda a cor da fonte e do interior da célula se ela for diferente da origem
        rCelula.Interior.Color = vbRed
        rCelula.Font.Color = vbYellow
        iDiferencas = iDiferencas + 1
    Else
        'Garante que a célula esteja sem preenchimento e com a fonte "automatic"
        rCelula.Interior.Pattern = xlNone
        rCelula.Font.ColorIndex = xlAutomatic
    End If

Next

' CÓDIGO OMITIDO

Observe que usamos o For Each, responsável por percorrer uma coleção. Nesse caso, ele percorre uma coleção de rCelula, que é o Range declarado anteriormente, isto é, o intervalo.

O código diz para percorrer todas as células In Selection. Perceba como começamos a usar o VBA de forma genérica: não usamos nomes de planilhas específicas e determinados intervalos; usamos o Selection para considerar tudo que estiver selecionado.

Abaixo, indicamos que se o valor da célula (rCelula.Value) que comparamos for diferente da origem, uma vez que iremos percorrer cada célula do intervalo, mudaremos a cor da fonte e o interior da célula.

Nesse trecho, usamos o nome da planilha Sheets(sNomePlanComparar), pois veio do formulário. No próximo vídeo, explicaremos isso em mais detalhes.

Após isso, usamos Range(rCelula.Address). rCelula.Address usa exatamente o mesmo endereço da célula original. Se a célula original é B5, ele analisará a célula B5 da outra planilha. Ou seja, ele pega a planilha selecionada (Selection) e compara com a outra planilha que pedimos.

Se elas forem diferentes, a célula será marcada em vermelho, a fonte ficará amarela, e será indicado que existe uma diferença a mais. Sempre que houver uma célula B5 diferente da B5 em outra planilha, B6 diferente de B6, D5 diferente de D5, e assim por diante, ele marcará mais uma diferença.

Caso contrário, para garantir que as células não ficarão marcadas em vermelho e amarelo, voltamos a cor para xlNone, ou seja, nenhuma cor, e a fonte para xlAutomatic, ou seja, a cor automática.

Em Next, terminamos o For Each, e na sequência, temos o seguinte trecho:

' CÓDIGO OMITIDO

    'If que define qual mensagem vai ser mostrada
    If iDiferencas = 0 Then
        MsgBox "Nenhuma Célula Modificada"
    Else
        MsgBox iDiferencas & " Células Modificadas no Destino"
    End If

End Sub

Se não houver nenhuma diferença, recebemos a mensagem "Nenhuma Célula Modificada". Porém, se existirem diferenças, serão indicadas quantas são essas diferenças. Com isso, encerramos o código.

Testando o código

Se apagarmos as planilhas Produtos (2) e Planilha2 e tentarmos executar a macro, receberemos a mensagem "Não há planilhas suficientes para comparação" e sairemos do formulário.

Poderíamos tentar usar o atalho "Ctrl + Z" para desfazer a ação, mas sabemos que, após rodar a macro, esse recurso não funciona. Porém, podemos usar a planilha Produtos para criar Produtos (2). A partir disso, conseguimos fazer a comparação novamente.

Se compararmos agora, executando a macro em "Desenvolvedor > Macros > 'VBA 4.xlsm'!sbComparaPlanilhas > Executar", receberemos a mensagem "Nenhuma Célula Modificada" e voltaremos às cores originais.

Quando executamos a macro, o Excel não perguntou com qual planilha queremos comparar, pois só temos duas planilhas. Se existem apenas duas planilhas, obviamente queremos comparar uma com a outra.

Agora vamos criar uma nova planilha em branco. Com isso, teremos três planilhas: Produtos, Produtos 2, e Planilha5.

Se selecionarmos uma delas, o Excel não saberá com qual planilha queremos comparar, e é justamente por isso que ele irá exibir o formulário com a mensagem "Escolha a planilha que deve ser a base para comparação!". Ou seja, o formulário só roda se tivermos mais que duas planilhas.

Conclusão

Como fizemos isso? Como esse formulário nos ajuda? Como podemos utilizar o formulário no código? Como fazemos para o formulário ser genérico e poder ser usado em outra planilha? Descobriremos no próximo vídeo!

Sobre o curso VBA: automatizando a comparação entre planilhas e dados

O curso VBA: automatizando a comparação entre planilhas e dados possui 190 minutos de vídeos, em um total de 46 atividades. Gostou? Conheça nossos outros cursos de Excel em Data Science, ou leia nossos artigos de Data Science.

Matricule-se e comece a estudar com a gente hoje! Conheça outros tópicos abordados durante o curso:

• Comparando dados entre planilhas
• Automatizando a leitura de pastas e arquivos
• Comparando planilhas em pastas
• Criando uma solução completa com todos os passos
• Inteligência artificial pode ajudar