Blog

Postado em em 8 de agosto de 2020

Compilação de Planilhas no VBA

Nas próximas publicações de VBA vou te ensinar todas as ferramentas necessárias para criar uma compilação de planilhas utilizando o VBA!

Aulas Disponíveis

Aula 1 – Compilação de Planilhas no VBA

Caso prefira esse conteúdo no formato de vídeo-aula, assista ao vídeo abaixo!

Clique aqui para baixar a planilha utilizada nessa publicação!

O que é uma Compilação de Planilhas?

Faremos uma série de vídeos e publicações para explicar como fazer a compilação de diversas planilhas em uma só. Ou seja, trazer as informações de várias planilhas que vão ser inseridas em uma planilha central, que concentrará todos os dados.

Para que serve essa ferramenta?

Serve para juntar planilhas no excel em uma só. Ou seja, concentrar dados que vêm de diversas planilhas separadas, que essencialmente tem dados com características ou tipos parecidos ou iguais, e devem ser agregados em um único lugar. Futuramente esses dados podem ser tratados, resumidos, apresentados.

Como fazer essa compilação de planilhas?

Nesta série iremos explicar as etapas necessárias para juntar planilhas em uma só. Ou seja, trazer as informações de diversas planilhas para uma planilha, que irá conter os dados de todas as outras.

No nosso exemplo, temos diversas lojas representadas por planilhas diferentes, que tem nomes de cores, que reportam as vendas delas durante o ano.  Reparem, temos vendas de 25 (vinte e cinco) planilhas, com milhares de linhas (146.187 para ser mais exato), para serem resumidas em um único local (nossa planilha Resumo).

Essas 25 planilhas possuem as mesmas informações. Delas virão os dados respectivos aos campos (colunas) de Data, Código Produto, Código Cliente, Valor, e na nossa planilha de Resumo (principal) será preenchida a coluna de Loja, para diferenciar de que loja veio aquela informação (de venda). Veja um exemplo abaixo:

Juntar Planilhas Excel Em Uma Só
Juntar Planilhas Excel Em Uma Só

Imagine que a empresa matriz quer saber como andam as vendas de cada empresa filial e quantas realizam durante o dia, mês, ano, e precisa juntar essas informações para analisar.

O intuito é trazer as informações das operações de vendas daquelas lojas para serem utilizadas nessa planilha de resumo, para transformar, resumir, criar gráficos, apresentar dados, etc.

Mas, novamente, repare: existem muitas linhas, muitas vendas, muitas planilhas, muito a ser considerado. Observe como ficará a consolidação dos dados das lojas trazidos para a planilha Resumo, após rodar o código de compilação (com no total de 146.187 linhas de operações de vendas):

Série Compilação de Planilhas no VBA
Série Compilação de Planilhas no VBA

O processo de consolidação de dados no Excel das vendas para a planilha principal deve ser feito todos os dias durante o ano, para garantir que as informações de vendas estão atualizadas.

Seria uma tarefa longa e cansativa, caso precisasse ser feita todos os dias copiando e colando, ou até manualmente lançando as novas vendas nessa planilha principal.

Esse processo, além de cansativo e demorado, pode estar sujeito a erros pela quantidade de informações a serem passadas de diferentes planilhas, pela quantidade linhas dessas planilhas, e de passos a serem realizados a cada arquivo.

Uma tarefa que você levaria muito tempo para fazer, o VBA pode te ajudar e muito, porque em alguns segundos ele consegue fazer esse tipo de operação de forma confiável, com os passos que você programa para ele fazer sempre da mesma forma.

E o detalhe principal: o VBA provavelmente fará esses processos para todas as planilhas com o mesmo tempo ou mais rápido do que você faria calmamente para uma delas.

Nessa série, que será dividida em diversos capítulos (de vídeo), ensinaremos como fazer esse código para fazer a consolidação de dados em planilhas, em diversas etapas:

  • varrer as planilhas que estão em uma pasta para serem abertas
  • encontrar as informações de uma planilha necessárias que serão trazidas para planilha principal (Resumo)
  • aprender a deixar o código mais rápido
  • mostrar o tempo que o código demora pra rodar (estimar tempo que ele leva para fazer as operações e comparar com o original, sem modificações)
  • formatar e ajeitar a planilha principal corretamente, etc.

Voltar ao índice

Aula 2 – Variáveis VBA – O que são e como usar?

As variáveis no VBA representam uma ajuda incrível para quem deseja organizar sua codificação e evitar problemas. É uma maneira de tornar o desenvolvimento mais ágil e seguro, de modo a evitar mais erros. Também constitui uma forma de organizar o uso de memória no sistema.

Saiba mais sobre o assunto com este conteúdo completo!

O que são variáveis no VBA?

Variáveis no VBA são quaisquer nomes que sejam diferentes dos reservados pela biblioteca de nomes do VBA e representam espaços de memórias reservados no computador para armazenar conteúdos (valores). 

Existem diversos tipos de variáveis para armazenar tipos de dados específicos (número, texto, data, objetos etc).

Quando não se tem uma variável, o programador precisa monitorar o valor e fazer cálculos o tempo todo, algo que é completamente inviável.

Por isso, usar variáveis é algo tão simples e básico. No VBA e em outras linguagens, elas nem mesmo precisam ser declaradas, o que facilita muito o uso.

Há linguagens, porém, que forçam a declaração e definem erros quando isso não ocorre.

Para que servem?

O conteúdo armazenado nas variáveis no VBA em um momento pode variar em um mesmo código de VBA em outro momento. 

Ou seja, ele pode assumir valores diferentes, conforme o código está em execução, assumindo o mesmo nome. Por isso é chamado de variável.

Como trabalhar com variáveis no VBA?

Ao longo da nossa série iremos trabalhar com diversas variáveis e seus tipos. Hoje nós nos limitaremos à declaração de alguns tipos delas e a explicação de como armazenam valores. 

Esse post irá ajudar a entender os próximos passos dessa série, que no final visa fazer a consolidação de dados no Excel.

No nosso arquivo de exemplo usado no vídeo, temos duas abas, com tabelas preenchidas com dados de tipos iguais, mas que são diferentes. Veja abaixo:

oc7qgt2DHaWnFzORi9XuEQPgvu5oWU28yNbp8WAc7WTug6OUM4YmVEpTB

Para começar tudo, iremos abrir o VBA (Alt+(Fn)+F11). Em seguida iremos na guia Inserir, e clicar em Módulo, para começar um módulo novo (Módulo 1, por padrão), em branco, onde iremos escrever nosso código.

A partir desse módulo em branco, iremos começar uma nova Sub (subrotina), que será o nome do nosso código. Ao inserir “Sub (nome do nosso código)” e dar Enter, automaticamente é preenchido um End Sub no final do código. O código será sempre o que estiver escrito entre Sub e End Sub.

Vamos começar declarando as variáveis. Precisamos declarar o tipo de variável para o VBA entender a quantidade de memória que o computador irá utilizar para armazenar aquele valor específico.

Lembrando que não é obrigatório declarar as variáveis no VBA antes de utilizá-las (mas precisamos defini-las, ou seja, dizer o que elas assumirão ou armazenarão, se formos utilizá-las após essa definição no código).

Como no nosso exemplo temos dados de tipos de texto, e números, tanto inteiros pequenos e grandes, quanto decimais, vamos declarar variáveis dos tipos String, Integer, LongLong e Double, respectivamente. Veja abaixo a parte do código que irá representar a declaração:

Tzrmf MRdyYAREtjv7fpkYFQV8nQP1FgP9EZ RW1iWEljsHYIs43rI0nGwebgtuOyBWVFvTogNQx9ZSh9knn1ZxtoEBdCFbJ4MPfu5zb4tJklsI1OiB88I3yoSSEXiM 7H FCmAX9lj792st6B5HtiU

Leitura do Bloco de declaração:

Dim nome As String é a instrução que fará a declaração da variável “nome” como um texto

Dim numero As Integer é a instrução que fará a declaração da variável “numero” como um número inteiro

Dim numero_decimal As Double é a instrução que fará a declaração da variável “numero_decimal” como um número decimal

Dim numero_grande As LongLong é a instrução que fará a declaração da variável “numero_grande” como um número inteiro muito grande

OBS: De primeira faz sentido usar a variável “numero_grande” como Long (por não sabermos os limites de valores que ela comporta). Mas, se rodarmos o código, o VBA nos retornará um erro de “Estouro”. Mas por quê?

Porque a variável numero_grande irá assumir o valor de 313.536.843.185 e a variável do tipo Long, por definição, só assume valores de -2.147.483.648 a 2.147.483.647. Por isso devemos usar a LongLong, um tipo de variável que assume valores de -9.223.372.036.854.775.808 a 9.223.372.036.854.775.807 (válido somente em plataformas de 64 bits), segundo a Microsoft.

Agora vamos para a parte que iremos passar os dados para serem armazenados nas variáveis declaradas. Abaixo temos o código para armazenar os valores da primeira aba (planilha) do nosso exemplo:

Leitura do Bloco de armazenamento para 1ª aba:

nome = Cells(1, 2).Value é a instrução do código que dirá que queremos armazenar o valor da célula da 1ª linha (linha = 1) da 2ª coluna (coluna = 2) da nossa primeira aba na variável “nome”

numero = Cells(2, 2).Value é a instrução do código que dirá que queremos armazenar o valor da célula da 2ª linha (linha = 2) da 2ª coluna (coluna = 2) da nossa primeira aba na variável “numero”

numero_decimal = Cells(3, 2).Value é a instrução do código que dirá que queremos armazenar o valor da célula da 3ª linha (linha = 3) da 2ª coluna (coluna = 2) da nossa primeira aba na variável “numero_decimal”

numero_grande = Cells(4, 2).Value é a instrução do código que dirá que queremos armazenar o valor da célula da 4ª linha (linha = 4) da 2ª coluna (coluna = 2) da nossa primeira aba na variável “numero_grande”

OBS: Leitura para “Cells” no VBA é feita da seguinte forma: Cells(LINHA,COLUNA).

E se quiséssemos pegar os valores da Segunda Aba?

Para isso, devemos declarar uma variável que irá representar a segunda aba. Essa variável é a variável Object, porque a aba (planilha) é um objeto do programa do Excel e é assim que deve ser declarado.

Veja abaixo como ficará o código para fazer o mesmo procedimento de armazenar os valores da primeira aba com a segunda aba:

NqFi3LWmFBukeyF 1H7q5OAZfOmMZvqDI1Pj2St5nZrOkmP5scmiC d3U10vuYy5N1GL66E3OBPr3fkl9SSb U1C3loIR1RMor4D

Leitura do Bloco de armazenamento para a 2ª aba:

Para definir um objeto no VBA devemos usar o comando Set antes do nome da variável (declarada ou não) escolhida para que o VBA entenda que ela vai assumir um (“valor” de) objeto. 

Precisamos “setar” ele para que o código entenda que ele irá substituir um objeto (que pode, mas geralmente não muda) no código inteiro, e não será uma variável que substituirá um valor (texto, número, data) que poderá mudar várias vezes durante o código.

Dim aba_secundaria As Object é a instrução que fará a declaração da variável “aba_secundaria” como um objeto.

Set aba_secundaria = Sheets(“Segunda Aba”) é a instrução que passará para o VBA que queremos usar a variável “aba_secundaria” exatamente como a segunda aba do Excel, como uma substituição. Onde ela aparecer no código, entenderemos que essa variável vai substituir a segunda aba, em todas as partes em que ela aparecer no código.

nome = aba_secundaria.Cells(1, 2).Value é a instrução do código que dirá que queremos armazenar o valor da célula da 1ª linha (linha = 1) da 2ª coluna (coluna = 2) da nossa segunda aba na variável “nome”

numero = aba_secundaria.Cells(2, 2).Value é a instrução do código que dirá que queremos armazenar o valor da célula da 2ª linha (linha = 2) da 2ª coluna (coluna = 2) da nossa segunda aba na variável “numero”

numero_decimal = aba_secundaria.Cells(3, 2).Value é a instrução do código que dirá que queremos armazenar o valor da célula da 3ª linha (linha = 3) da 2ª coluna (coluna = 2) da nossa segunda aba na variável “numero_decimal”

numero_grande = aba_secundaria.Cells(4, 2).Value é a instrução do código que dirá que queremos armazenar o valor da célula da 4ª linha (linha = 4) da 2ª coluna (coluna = 2) da nossa segunda aba na variável “numero_grande”

OBS: Dim e Set

Dim: significa dimensionar (ou declarar) uma variável

Set: significa “setar”, ou seja, fazer a variável assumir algo ou atribuir algo a ela. É utilizada para atribuir uma referência de um objeto a uma variável.

Esteja ciente que as variáveis no VBA armazenam uma quantidade diferente de memória temporária do computador (fonte: Microsoft). 

Se você não declarar o tipo, ele irá armazenar a maior quantidade de memória possível para conseguir interpretar todos os tipos de dados, porque ele “não sabe” qual tipo de dado será armazenado.

Isso é considerado algo ineficiente para o código. Por exemplo, para um código pequeno não faz muita diferença, mas para um código extenso sim, faz bastante diferença no tempo de execução.

Quais os tipos de variáveis no VBA?

Já vimos o que é uma variável no VBA e sua importância. Agora, vamos entender melhor os tipos e especificar como se diferenciam entre si.

Temos o boolean, que se divide em True ou False. O Byte, que traz valores de 0 a 255.

Há o Date, que especifica datas e números que demarcam tempo. Já o Double serve para especificar números com casas decimais e elementos hexadecimais.

O Integer define números inteiros, que vão até 32 mil. O tipo Long também define números inteiros, só que os maiores, com a possibilidade de representar bilhões.

E o String é um texto, que pode ser pequeno ou ter até 2 bilhões de caracteres.

Já o tipo Variant é um tipo genérico, que permite abrigar qualquer tipo de dado em um espaço criado sem definição pelo VBA.

Como definir uma variável no VBA?

Uma variável é um espaço de memória, como já falamos. É uma definição que ajuda a guardar dados para depois usar em uma situação específica. 

Com uma variável, você não precisa repetir operações ou memorizar determinadas informações; pode simplesmente usar a variável sempre que necessário.

Para definir uma variável, você precisa igualar o nome dela a um valor, o que pode ser uma célula, por exemplo. 

Como lembrar de declarar variáveis no VBA?

Para lembrar de declarar, você pode estabelecer uma fase obrigatória antes da escrita do código que sempre define as principais variáveis, como um bloco anterior que você escreve primeiro. 

Então, antes mesmo de começar o código, o programador já se pergunta sobre as variáveis que vai precisar. Isso força a pensar a lógica de antemão e a ter mais organização para gerenciar melhor esses tipos de dados. 

Vale lembrar que a declaração é diferente da atribuição a uma variável. Isso pode ser feito em duas linhas diferentes. 

A importância da declaração em si reside no fato de que, ao declarar, você mostra ao computador exatamente a quantidade necessária de memória que precisa alocar. 

Contudo, também é comum que você sempre pense em uma variável posteriormente. Nesse caso, depois de mudanças de decisão sobre os tipos de dados no meio da codificação, é possível simplesmente ir no bloco anterior e adicionar uma variável nova. 

Com a familiaridade e a constância de sempre declarar esse bloco, você vai esquecer menos e tornar isso um hábito saudável. 

Voltar ao índice

Aula 3 – Copiar e Colar no VBA (Método Certo)

Você já precisou alguma vez copiar e colar no VBA? Existem algumas formas de se fazer isso e nessa publicação vamos comentar sobre elas!

Caso prefira esse conteúdo no formato de vídeo-aula, assista ao vídeo abaixo!

Clique aqui para baixar a planilha utilizada nessa publicação!

O que é o Método Certo de Copiar e Colar no VBA?

É o método de atribuição de valores, em que atribuímos o valor que está inserido em uma célula do Excel a outra célula, ou seja, a segunda recebe o conteúdo da primeira.


Por que usar esse método?

Porque ele é mais rápido que o método tradicional de copiar e colar, porque só faz a transferência dos valores, e nada mais. O método tradicional de copiar e colar carrega valores, formatação, fórmulas, tudo (somado) que estiver naquelas células.

 

Como Copiar e Colar no VBA?

Primeiramente temos que entender como fazer o método tradicional, para conhecê-lo e compará-lo com o método mais eficiente.

Vamos inicialmente abrir o VBA, apertando Alt+F11 (ou Alt+Fn+F11), e começar um novo módulo em Inserir > Módulo para que possamos escrever nosso código.

Após abrir o ambiente do VBA, criaremos um novo módulo em branco, em que vamos escrever o nosso código (nossa sub copia_cola). Podemos chamá-la de qualquer nome.

No nosso exemplo do vídeo temos 4 quadros com informações (Info 1 até Info 4 em cada quadro) a serem copiadas para outros quadros, na mesma aba (primeira) e para outras (segunda e terceira).

Copiar e Colar VBA
Copiar e Colar VBA

A seguir temos o código criado para copiar as informações da primeira aba para ela mesma, com o método tradicional. Observe:

Range("B1:B4").Copy

Range("B6").PasteSpecial

Explicação do código acima:

Simplesmente significa copiar o conteúdo inteiro (valor, formatação, fórmulas) do intervalo de células de B1 até B4, e colar essas informações em B6. Lembre-se que colar em B6 levará a colagem até B9, porque copiamos 4 células inteiras.

A seguir temos o código criado para copiar as informações da primeira aba para a segunda, com o método tradicional. Observe:

Sheets("Primeira Aba").Range("B1:B4").Copy

Sheets("Segunda Aba").Range("B1").PasteSpecial

Explicação do código acima:

Simplesmente significa copiar o conteúdo inteiro (valor, formatação, fórmulas) do intervalo de células de B1 até B4 da primeira aba, especificamente, e colar essas informações na célula B1 da segunda aba, especificamente. Lembre-se que colar em B1 levará a colagem até B4, porque copiamos 4 células inteiras.

Outro exemplo de colagem para outra aba é a colagem das informações para a terceira aba. Caso queira fazer essa operação, você apenas deve especificar a Terceira Aba no lugar da segunda, no código acima, porque colaríamos as informações na célula B1 da terceira aba também.

E o método certo de Copiar e Colar no VBA?

Vamos para o código que irá copiar as informações, mas somente os valores, para as células de interesse. Lembrando que esse procedimento é mais rápido que a cópia e colagem tradicionais no VBA.

A seguir temos o código criado para levar as informações da primeira aba para a terceira aba (apenas comentado acima).

Copiar VBA Excel
Copiar VBA Excel
Sheets("Terceira Aba").Range("B1:B4").Value = Sheets("Primeira Aba").Range("B1:B4").Value

Explicação do código acima:

Estamos fazendo uma atribuição de valores, onde o intervalo de células B1 até B4 da terceira aba do nosso arquivo receberá os valores das células de B1 até B4 da primeira aba.

Se atente para isso: tratando de um código pequeno e simples, não fará muita diferença, mas em termos de códigos grandes e complexos, fará diferença no tempo de execução do código se você optar por usar o método de atribuição no lugar do tradicional.

Ele é muito mais eficiente do que o método de colagem normal, porque, lembrando, leva apenas as informações de valores para as células de interesse.

Voltar ao índice

Aula 4 – Primeira Linha Vazia com VBA

Você já precisou preencher tabelas ou cadastrar informações usando VBA? Provavelmente precisou encontrar a primeira linha vazia.

Caso prefira esse conteúdo no formato de vídeo-aula, assista ao vídeo abaixo!

Clique aqui para baixar a planilha utilizada nessa publicação!

Fala impressionadores! Na aula de hoje vamos aprender como encontrar a primeira linha vazia de uma tabela, independente do número de linhas que essa tabela tenha.

Fazer isso pelo Excel é bem simples, se estiver na última linha da tabela clique em control + seta para cima e vai encontrar a última linha preenchida. Se estiver na primeiro linha da tabela use o control + seta para baixo e vai obter o mesmo resultado.

Para saber qual é a primeira linha vazia, é só descer uma célula após já estar na última célula preenchida.

Como fazer isso pelo VBA?

Para isso, vamos ter que descobrir como simular esse atalho CTRL Seta para baixo/cima no VBA.

Nessa aula vou te mostrar duas formas de fazer isso utilizando a propriedade .End(xlUp) e .End(xlDown).

E aí? Quer aprender a descobrir a última linha em branco e preenchida pelo VBA?

É só baixar o material da aula e entrar no ambiente VBA -> para isso clique no atalho alt + F11 ou Fn + alt + F11.

Dentro do ambiente VBA clique no ícone criar módulo -> em seguida na opção módulo

Módulo
Módulo

Código:

Sub ultima_linha() -> Todo código vai começar com o Sub, seguido do nome do código, observe que o nome do código não pode ter caracteres especiais nem espaço.

Código para última linha preenchida
Código para última linha preenchida

End Sub -> Fim do código

Observe que o código para encontrar a última linha é muito simples, primeiro criamos e damos um nome intuitivo a variável (linha =).

Após, vamos indicar em qual aba da nossa planilha queremos encontrar a última linha preenchida ou em branco. Feito isso, vamos começar a pesquisa a partir da última linha da tabela -> (linha nº A1048576) em seguida usamos o .End(xlUp) que é o mesmo que clicar em seta para cima.

Se você estiver na última linha do Excel e usar o comando control + seta para cima vai notar que você não é levado a linha nº1 e sim a última linha onde temos um valor registrado, o que estamos fazendo aqui no VBA é justamente reproduzindo este código em um formato que o VBA entende.

Agora para encontrar a próxima linha em branco é fácil, afinal a última linha em branco é o mesmo que a última linha preenchida + 1 linha, ou seja:

linha = Sheets("Primeira Aba") .Range ("A1048576") .End(xlUp) . Row + 1

Apenas vamos acrescentar o +1 no final do código, salve o código e, clicando na tecla F8 e executando a linha do código, vamos descobrir qual é o número da última linha em branco da nossa tabela.

Voltar ao índice

Aula 5 – Aprenda a formatar e classificar planilhas Excel com VBA

A formatação no VBA é uma excelente estratégia para fazer mudanças gerais em uma planilha e obter um visual mais interessante. Permite manipular e formatar as células para otimizar a apresentação, com diversas opções.

Para quem gosta de trabalhar com o Excel, é essencial entender como fazer essa formatação e quais são os comandos envolvidos. Assim, você obtém maior produtividade sempre que precisar ordenar ou melhorar a visualização dos dados.

Saiba mais sobre o uso do VBA para formatar em planilhas Excel a seguir e confira um passo a passo de como transformar um conjunto desordenado de dados em uma tabela organizada

O que é a formatação no VBA e para que ela serve?

Formatar é dar forma ou deixar no formato que você deseja uma célula ou uma coluna de dados. Ordenar é o fato de dispor os dados (de uma coluna, por exemplo) através de uma forma organizada, em ordem.

A formatação e a disposição dos dados em ordem servem para possibilitar uma melhor visualização para uma pessoa que interpreta os dados. 

Assim, pode ajudar a esclarecer situações, tirar melhores conclusões, facilitar interpretações, porque os dados estão melhor dispostos.

No exemplo deste post, os dados originais são difíceis de ler, com o tamanho desconfigurado para cada célula e em formatos estranhos. 

Com o VBA, você conseguirá ver uma tabela, com os dados devidamente organizados como precisam estar.

Por outro lado, o format do VBA ajuda na manipulação de dados. Com uma visão mais organizada e espaçada, fica mais fácil aplicar mudanças em cada campo. 

Dessa forma, é possível editar melhor o que for necessário dentro do Excel, sem grandes esforços para visualizar. Caso a formatação não tivesse sido aplicada, era preciso navegar  em meio aos caos e tentar extrair sentido, com a possibilidade de cometer erros na edição. 

Propriedades para formatação de células com VBA

Vamos entender agora as propriedades do função format no VBA.

Formatação de Número

Para manipulação de número, temos a NumberFormat. Depois da igualdade, podem ser aplicadas as modificações necessárias, de acordo com os códigos respectivos. 

Então, você pode usar símbolos, como o “#”, o “?”, “,” e outros.

A formatação de números serve para vários fins. Você pode colocar um padrão de tempo (com hh:mm:ss, por exemplo), padrão de moeda, porcentagem, data, etc. 

Para horas, por exemplo:

Range("A1").NumberFormat = "h:mm:ss AM/PM;@"

Range define a célula ou o intervalo de células que se deseja manipular. 

Na aba personalizado do Excel, você encontra códigos para usar quando quiser obter algum formato específico. 

Formatação de Alinhamento

Quanto ao alinhamento podemos ter o alinhamento horizontal e o vertical. 

O código principal pode ser:

Range("A1").HorizontalAlignment = comando

Sendo que Range define a célula. 

Para o horizontal, há opções de esquerda (xlLeft), direita (xlRight), centralizado (xlCenter), justificado (xlJustify) e justificado de forma centralizada (xlDistributed).

Já para o vertical, temos para cima (xlTop), para baixo (xlBottom), centralizado (xlCenter), justificado (xlJustify) e distribuído (xlDistributed).

Formatação da Fonte

A configuração de fontes lembra um pouco a linguagem CSS, usada em programação web. Em suma, ajuda a definir características dos textos escritos nas células.

Você pode definir qual fonte será usada (nomeando o Font.Name), definir o tamanho (com o Size), o estilo (Font.Style) e questões especiais, como o negrito (Font.Bold).

Há também uma propriedade que define o tipo de sublinhado. Sem opção de sublinhado ( xlUnderlineStyleNone), sublinhado simples (xlUnderlineStyleSingle), sublinhado duplo (xlUnderlineStyleDouble) e outros.

Formatação da Borda

Pensando na borda, temos algumas opções. Com o elemento BorderAround, você estabelece uma linha e cerca os dados com a borda. Então, você personaliza com os atributos LineStyle e Weight.

O lineStyle pode ter como opções:

  • pontilhado (xlDot),
  • traço-ponto-ponto (xlDashDotDot),
  • tracejado (xlDash),
  • linha contínua ( xlContinuous),
  • linha dupla (xlDouble). 

Por sua vez, o Weight define a grossura da borda. Vai de “fio de cabelo” (xlHairline), passando por fino (xlThin) até chegar em grosso (xlThick).

Formatação de Preenchimento

A parte de preenchimento define a cor interna para preencher uma célula. Com a propriedade PatternColor, você estabelece a cor que deseja e faz as mudanças necessárias.

Um exemplo:

Range("A1").Interior.PatternColor = vbYellow

O ColorIndex também pode ser usado para definir a mesma coisa.

Exemplo de formatação utilizando o VBA

Clique aqui para baixar a planilha utilizada nessa publicação!

Vamos explicar etapas de como usar o format do VBA nas colunas e células de uma tabela de dados para organizá-los e ordená-los, em busca de uma melhor visualização.

Para mostrar como faremos o tratamento desses dados utilizaremos a tabela como no vídeo, que tem a seguinte visualização de dados:

Classificar no VBA

Iremos repetir os passos usados no vídeo, e explicaremos cada um deles e as linhas de código respectivas para cada passo. Observe abaixo o código pronto no vídeo, e as devidas explicações:

Formatação no VBA

1) Ajustar colunas para tamanho mínimo

O autoajuste é a operação de ajustar automaticamente as colunas com o tamanho mínimo possível para caber os maiores dados (em extensão de caracteres) presentes nas células daquela coluna.

No VBA, o autoajuste é traduzido como Autofit, e é essa operação que devemos inserir no nosso código. Observe abaixo a linha de código que vai ajustar da coluna A até a coluna D, que será a mesma coisa que fazer o ajuste no Excel, com o mouse.

Columns("A:D").AutoFit é a instrução que irá realizar o autoajuste das colunas A até a D da nossa tabela.

2) Formatar coluna como data

Para formatar um número no Excel, devemos mudar o seu tipo de acordo com o que queremos. Para isso, devemos abrir a caixa de formato de número, através do atalho Ctrl+1. Em seguida optamos por algum formato específico, e apertamos em OK.

Mas para mudar esse formato no VBA precisaremos passar o que está escrito na opção de “Personalizado” nesse quadro de formatação de número.

Para saber exatamente o formato, deve-se optar pelo formato escolhido, e em seguida clicar em “Personalizado”.

O que estiver escrito em “Tipo” será o que deveremos escrever em nosso código, devidamente traduzido para o inglês (EUA), por que o VBA lê o código em inglês, e sem os “;” e “@”. Observe abaixo:

Formatando Células com VBA

Columns("A:A").NumberFormat = "dd/mm/yy" é a instrução que passa para o VBA que queremos botar na coluna A o formato de número de dia/mês/ano, cada um com 2 caracteres.

Lembre-se que na imagem acima o ano é representado pela letra “a”, e no VBA devemos passar a letra “y” como equivalente (year = ano, em inglês).

3) Alinhar as colunas ao centro

Columns("A:D").HorizontalAlignment = xlCenter é a instrução do código que fará que as colunas de A até D fiquem com seus dados alinhadas na horizontal, e no centro das células.

4) Botar o cabeçalho em negrito

Range("A1:D1").Font.Bold = True é a instrução que dirá para o código fazer a operação de colocar a fonte das células A1 até D1 em negrito.

5) Formatar a coluna de valores como moeda

Columns(D:D").NumberFormat = "$ #,##0.00" é a instrução que colocará o formato de moeda na coluna D inteira.

Lembre-se que para entender esse passo deve-se repetir o que foi feito no passo 2, optar pela coluna de moeda, e dessa vez, passar para o VBA as instruções de moeda como no inglês americano.

Ou seja, a moeda em “$” ao invés de “R$”, e as casas decimais devem ser representadas por “,” em vez de “.”, e o que representa os centavos, “.” em vez de “,”.

6) Colocar borda nas células da tabela

Range("A1:D17").Borders.LineStyle = xlContinuous é a instrução que passará ao código a informação para botar o estilo da borda das células de A1 até D17 (nossa tabela de dados) da forma contínua, ou seja, ao redor delas inteiras, individualmente.

7) Pintar o cabeçalho de azul

Range("A1:D1").Interior.ColorIndex = 23 é a instrução que passará ao código que queremos colocar a cor de índice 23 (azul escuro) no interior das células do cabeçalho da nossa tabela, ou seja, das células A1 até D1.

OBS: Como citado no vídeo, na segunda aba do arquivo teremos uma paleta de cores e seus índices, para que sirva de exemplo de aplicação e orientação.

8) Ordenar a tabela de acordo com a data mais antiga para mais nova

Como a gente não sabe exatamente como passar esse comando e não precisa diretamente gravar alguns comandos de cabeça, podemos usar a gravação de macro sempre que precisamos (geralmente quando não sabemos programar algo em VBA).

Para esse caso, de ordenar a tabela pela coluna de data, iremos gravar uma macro para auxiliar o nosso código, indo na guia Exibir > Macros > Gravar Macro… > OK. A partir daí, todos os passos que fizermos no Excel serão traduzidos pela macro.

Ao terminar os passos, vamos no mesmo local, e em Parar Gravação. Essa macro ficará gravada em um novo módulo (provavelmente Módulo 2 no VBA), como no nosso vídeo.

Observe como ficará o código para ordenar a tabela de acordo com a data mais antiga para mais nova.

ActiveWorkbook.Worksheets("Primeira Aba").Sort.SortFields.Clear
ActiveWorkbook.Worksheets("Primeira Aba").Sort.SortFields.Add2 Key:=Range( _
    "A1"), SortOn:=xlSortOnValues, Order:=xlAscending, DataOption:= _
    xlSortNormal
With ActiveWorkbook.Worksheets("Primeira Aba").Sort
    .SetRange Range("A2:D17")
    .Header = xlNo
    .MatchCase = False
    .Orientation = xlTopToBottom
    .SortMethod = xlPinYin
    .Apply
End With

Após realizar esses passos, a nossa tabela ficará com a aparência fina como na imagem abaixo:

Classificar Dados

Voltar ao índice

Aula 6 – Fórmulas de Texto no VBA

Nessa publicação vamos falar um pouco mais sobre as fórmulas de texto no VBA e como você pode utilizá-las em suas planilhas e códigos!

Caso prefira esse conteúdo no formato de vídeo-aula, assista ao vídeo abaixo!

Para receber a planilha que usamos na aula no seu e-mail, preencha:

 

O que são as fórmulas de texto no VBA?

São as fórmulas no ambiente do VBA que nos permitem modificar, extrair, transformar, converter dados do tipo texto no Excel.

Como usá-las para tratar dados?

Existem diversas fórmulas de texto no VBA que podem modificar os dados usados a partir do Excel. Nesse post iremos cobrir a explicação das que foram mencionadas no vídeo de exemplo.

Observe abaixo a tabela presente no vídeo, contendo as funções que iremos abordar:

Consolidação de Dados no Excel - Fórmulas de Texto no VBA
Fórmulas de Texto no VBA

Para criar o código para utilizar nossas fórmulas de texto precisamos Inserir um novo Módulo depois de abrir o VBA (Alt(+Fn)+F11), como já vimos em outros posts. Criaremos uma sub de nome qualquer para inserir o código desejado.

&

Esse operador irá juntar textos ou caracteres de texto no VBA, assim como faz no Excel.

No nosso exemplo, será responsável por colocar na célula D2 o texto “Nome: Pedro”, da seguinte forma:

Range("D2").Value = "Nome" & ":" & " Pedro"

Replace – Fórmulas de Texto no VBA

A função replace (em português: substituir) irá simplesmente substituir caracteres de texto por outros, numa cadeia de texto. Irá realizar o mesmo que a função SUBSTITUIR faz no Excel.

No nosso exemplo, será responsável por substituir “r” (minúsculo) por R (maiúsculo) no texto “carro” e colocar o resultado na célula D3, como mostrado na linha de código abaixo:

Range("D3").Value = Replace("carro", "r", "R") => nos resulta em “caRRo"

InStr

Essa função é responsável por retornar a posição de um caractere específico (ou uma cadeia de caracteres) no texto em questão, se existente naquele texto, ou retornar 0 se aquele caractere específico (ou cadeia de caracteres) não está no texto em questão.

OBS: Lembrando que ele nos dá a primeira ocorrência desses caracteres num texto maior. Caso haja mais de uma ocorrência no texto maior, ela retornará sempre a posição da primeira.

No nosso exemplo ela será responsável por dizer se o caractere em questão está presente no texto “carro”, retornando um número maior que 0 correspondente à posição, ou nos retornando 0, se o caractere não existe naquele texto. Observe abaixo os exemplos de códigos usados no vídeo:

Range("D4").Value = InStr("carro", "a") => resulta em 2

Porque o caractere “a” está na 2ª posição do texto.

Range("D4").Value = InStr("carro", "r") => resulta em 3 

Porque o caractere “r” está na 3ª posição do texto. Repare que ele também está na 4ª posição, mas a função nos retorna a primeira ocorrência dele no texto.

Range("D4").Value = InStr("carro", "p") => resulta em 0

Porque o caractere “p” não existe no texto.

Left

É a função equivalente à função ESQUERDA do Excel. Ela é responsável por extrair uma quantidade de caracteres específica a partir de um texto, a partir do primeiro caractere mais à esquerda. Exemplo de código:

Range("D5").Value = Left("carro", 2) 

No nosso exemplo ela irá extrair 2 caracteres do texto “carro”, nos retornando “ca”

Right

É a função equivalente à função DIREITA do Excel. Ela é responsável por extrair uma quantidade de caracteres específica a partir de um texto, a partir do primeiro caractere mais à direita.

No nosso exemplo ela irá extrair 2 caracteres do texto “carro”, nos retornando “ro”. Exemplo de código:

Range("D6").Value = Right("carro", 2)

Mid – Fórmulas de Texto no VBA

É a função equivalente à função EXT.TEXTO do Excel. Ela é responsável por extrair uma quantidade de caracteres específica a partir de um texto, a partir de um caractere específico especificado por você.

No nosso exemplo ela irá extrair 2 caracteres do texto “carro”, a partir do 2º caractere, nos retornando “ar” (porque o 2º caractere é o “a”; então ele pega dois contando com ele). Exemplo de código:

Range("D7").Value = Mid("carro", 2, 2)

Len

É a função equivalente à função NÚM.CARACT do Excel. Ela é responsável por extrair a quantidade de caracteres específica a partir de um texto, e nos retornar um número.

No nosso exemplo ela irá extrair a quantidade de caracteres presentes no texto “carro”, e nos retornando 5. Exemplo de código:

Range("D8").Value = Len("carro")

Ucase (Upper case)

É a função equivalente à função MAIÚSCULA do Excel. É responsável por colocar os caracteres de um texto em caixa alta, nos retornando o mesmo texto com todas as letras em maiúscula.

No nosso exemplo ela irá atuar transformando o texto “carro” em “CARRO”. Exemplo de código:

Range("D9").Value = Ucase("carro")

Lcase (Lower case) – Fórmulas de Texto no VBA

É a função equivalente à função MINÚSCULA do Excel. É responsável por colocar os caracteres de um texto em caixa baixa, nos retornando o mesmo texto com todas as letras em minúscula.

No nosso exemplo ela irá atuar transformando o texto “CARRO” em “carro”. Exemplo de código:

Range("D10").Value = Lcase("CARRO")

É fundamental conhecer as fórmulas de texto no VBA, para ter um leque de opções para extrair, transformar, usar os dados de outras formas, caso você precise. Essas fórmulas são importantes para ajudar na nossa série em que vamos juntar planilhas em uma só.

Fique ligado no nosso Blog para mais conteúdo de VBA e no nosso canal do YouTube para mais conteúdo de VBA, Excel e Power BI. Até a próxima!

Voltar ao índice

Aula 7 – If VBA – Como usar a Fórmula SE no VBA?

Na publicação de hoje vou te mostrar como fazer a Fórmula SE através de códigos. Para utilizá-la, vamos precisar conhecer melhor o If VBA! Conhecer essa estrutura vai melhorar e muito seus códigos e fazer você avançar na programação com VBA!

Caso prefira esse conteúdo no formato de vídeo-aula, assista ao vídeo abaixo!

O que é o If VBA?

A fórmula SE no Excel e no VBA irá realizar um teste lógico, para verificar se uma condição é verdadeira, e nos retornar um resultado específico se essa condição for verdadeira, e outro se ela for falsa.

O If VBA faz um teste com uma regra para determinar qual caminho irá seguir. O número de caminhos em uma estrutura simples é apenas dois: o If (se) e o Else (senão). Sendo que ele executa só um por vez, a depender do resultado da condição. 

A estrutura condicional permite tornar o código mais robusto e inteligente. Afinal, decisões serão tomadas com base no comportamento dos dados e em alterações prévias. Assim, o programador já estabelece alguns gatilhos para estabelecer mudanças no fluxo e no funcionamento da planilha.

Pode ser, por exemplo, mudar uma célula com base em outra. Ou qualquer outra ação. Tudo depende do que você precisa fazer, e a estrutura condicional facilita sua vida, automatizando certas funções.

Aliás, assim, o programador consegue incorporar o VBA ao seu dia a dia e gerar valor com o uso da linguagem.

Como utilizá-la no VBA?

Existem diversas fórmulas no VBA que podem modificar os dados usados a partir do Excel. 

Nesse post, iremos cobrir a explicação da rmula SE no VBA.

Observe abaixo a tabela presente no vídeo, contendo a função que iremos abordar:

Queremos falar sobre quando uma situação de uma determinada venda é considerada Boa ou Ruim, de acordo com a classificação mostrada pela legenda em E1:F2.

Para criar o código para utilizar nossas fórmulas de texto precisamos Inserir um novo Módulo depois de abrir o VBA (Alt(+Fn)+F11), como já vimos em outros posts. Criaremos uma sub de nome qualquer (nesse caso daremos o nome de if_vba) para inserir o código desejado.

Sub if_vba()

If Range("B2").Value >= 100000 Then

Range("C2").Value = "Boa"

Else

Range("C2").Value = "Ruim"

End If

End Sub

Explicação do código – If VBA

Se o valor da célula B2 for maior ou igual a 100000 (cem mil), escreva “Boa” na célula C2.

Caso contrário (seja menor que 100000), escreva “Ruim” em C2.

A fórmula If irá testar uma condição específica que iremos passar, e, caso ela seja verdadeira, irá fazer tudo que estiver após o “Then”.

Caso ela não seja verdadeira, era lerá o que está “dentro” do Else, que é o “caso contrário” ao que está sendo testado.

Seguindo essa estrutura, o End If irá “fechar” o teste lógico.

Iremos repetir o mesmo código para as linhas 3, 4, 5 e 6, apenas mudando o número da linha respectiva das células e apertando em F5 novamente. Assim, todas as situações de venda ficarão preenchidas e classificadas de acordo com o nosso teste lógico.

Como fazer um Else If no Excel?

Se você já sabe usar o if, é preciso também entender como funciona o Else If. Trata-se de um complemento lógico importante.

Você já viu que o Else não precisa especificar uma condição, pois ele representa o contrário do if. 

Já no Else If, você especifica necessariamente uma condição que seja diferente da inicial, do if, e consegue aumentar a complexidade do seu bloco condicional.

Ela precisa ser completamente diferente da regra do If, porque senão o programa vai executar a ação do If e pular para fora da estrutura. 

Digamos que você queira fazer uma ação com uma célula se ela tem um número maior do que 50. Então, usaria um If:

If Range("B2").Value > 50

Para complementar, você pode definir um ElseIf que busca outra condição, especificando ainda mais o tratamento das possibilidades do código.

ElseIf Range("B2").Value = 50

Então, somente se o valor dessa célula for diferente de 50 e menor do que 50 o fluxo vai cair no Else final. 

Ou seja, o Else If serve para definir mais uma regra. Ele não tem limites; você pode adicionar quantas regras precisar para deixar o seu código mais completo e abrangente. 

O que representa o bloco If Then no VBA?

Em algumas linguagens, você não precisa explicitar o Then, mas no VBA, ele é obrigatório. Basicamente, é uma cláusula para indicar a estrutura do If e determinar o que vem depois, como um comando. 

O que surge depois do Then é a ação que será executada caso a condição seja cumprida. Há linguagens que utilizam chaves para isso ({).

Além de representar, como já falamos, um teste lógico, o If then representa sobretudo um controle de fluxo da execução do programa. O fluxo é interrompido e é retomado apenas depois que a condição é analisada. 

Até porque a execução de qual bloco é algo que será decidido apenas em tempo de execução. O programa não sabe inicialmente qual é o bloco, mas precisará fazer uma análise das regras.

Isso fica bem evidente ao ter uma estrutura condicional maior. 

Voltar ao índice

Aula 8 – For Each VBA (Estrutura de Repetição)

Aqui vou te mostrar como utilizar as estruturas de repetição do VBA! Mais especificamente, vamos ver como utilizar o For Each VBA!

Caso prefira esse conteúdo no formato de vídeo-aula, assista ao vídeo abaixo!

Para receber a planilha que usamos na aula no seu e-mail, preencha:

 

O que é uma estrutura de repetição no VBA?

São estruturas usadas dentro do VBA responsáveis por realizar ações ou executar comandos a partir de determinadas condições desejadas pelo programador para realizar ações e repetir essas ações ou decisões inúmeras vezes.

Por que usar o For Each VBA?

Porque, com ele, realizamos uma mesma ação ou executamos um mesmo comando desejado uma enorme quantidade de vezes e com grande rapidez, dependendo do número de vezes que essa ação se repete.

Como fazemos para usar o For Each VBA?

Essa é a oitava aula da série e nela ensinamos conteúdos que serão fundamentais nesse post e nele serão abordados mais superficialmente.

Existem diversas estruturas de repetição no VBA e cada uma delas é mais recomendada para uma situação específica. A estrutura que vamos dedicar o post é a For Each. Essa é uma estrutura cujo foco é percorrer objetos como um todo. Por exemplo: todas as células de um intervalo, todas as abas de uma planilha, todas as planilhas de uma pasta e por aí vai.

No nosso arquivo base de exemplo, temos uma tabela de valores de venda de uma empresa e precisamos preencher a situação da venda, classificada como Boa ou Ruim, se maior ou igual a 100.000 ou menor, respectivamente.

1) Percorrendo células em um intervalo (de células)

Trabalhando na primeira aba do nosso arquivo, de nome “For Each”, queremos preencher as células da coluna C com a situação de venda de acordo com a classificação desejada pré-estabelecida. Observe a tabela da primeira aba:

For Each VBA
For Each VBA

Então, programaremos o código para percorrer o intervalo de células-alvo da coluna C.

Observe abaixo o código que fará esse varrimento, executando ações. Iremos explicá-lo em seguida.

Sub estrutura_repeticao()
For Each celula In Range("c2:c6")
      If celula.Offset(0, -1).Value >= 100000 Then
              celula.Value = "Boa"
      Else
              celula.Value = "Ruim"
      End If
Next
End Sub

Explicação da sub estrutura_repeticao():

A sub basicamente nos diz para percorrer todas as células no intervalo de C2 a C6, uma a uma, e testar a seguinte condição: o valor da célula da coluna B (valor de venda – 1 coluna imediatamente à esquerda da célula-alvo) vai ser comparado com 100.000. Se esse valor for maior do que o 100.000, na célula-alvo ficará escrito “Boa”.

Caso contrário (Else), ou seja, o valor da célula da coluna B (de venda) for menor do que 100.000, na célula-alvo queremos escrever “Ruim”.

Lembre-se que essa comparação será feita em cada célula da coluna B, e o “resultado” da comparação ficará escrito na coluna C. A estrutura de repetição irá mudar de linha automaticamente a cada “resultado” da estrutura If-Else-End If.

2) Percorrendo abas (no conjunto de abas)

Nesse tópico queremos fazer operações percorrendo as abas do arquivo que tem tabelas iguais as mostradas abaixo (Aba 1, Aba 2, Aba 3 e Aba 4).

For Each Next
For Each Next

Então iremos passar para a sub uma estrutura que percorra as abas, e faça a classificação das vendas nessas tabelas, em cada aba, de forma parecida com o primeiro exemplo. Observe a seguir o exemplo de código dado no vídeo:

Sub abas()
For Each aba In ThisWorkbook.Sheets
    If aba.Name <> "For Each" Then
        
        If aba.Range("b2").Value >= 100000 Then
              aba.Range("c2").Value = "Boa"
        Else
              aba.Range("c2").Value = "Ruim"
        End If
    End If
Next
End Sub

Explicação da sub abas():

A sub irá testar a primeira condição para saber em que aba fará os procedimentos desejados. Como temos as abas “For Each”, “Aba 1”, “Aba 2”, “Aba 3”, “Aba 4”, queremos apenas modificar as 4 últimas abas.

Por isso estamos colocando a condição de a aba ser diferente da aba “For Each”, para não mexermos nela.

O VBA irá percorrer todas as abas, excluindo a “For Each”, e realizando as ações seguintes.

Dentro de cada aba-alvo, a nossa sub irá testar o valor da célula B2 (de cada aba), e comparar com o 100.000.

Caso esse valor seja maior ou igual ao próprio 100.000, na célula C2 (de cada aba) ele irá escrever como resultado o texto “Boa”. Caso contrário, ou seja, esse valor seja menor do que 100.000, a célula C2 vai receber como resultado o texto “Ruim”.

Com isso, o usuário tem muito tempo poupado e transforma pensamentos em ações automatizadas e funções (decisões) que ele mesmo faria manualmente uma a uma, e em alguns segundos (ou minutos) tem todas essas ações realizadas repetidamente pelo código.

Voltar ao índice

Aula 9 – Abrir Arquivos no VBA

Nessa publicação vou te mostrar como você pode abrir arquivos no VBA, assim como edita-los, buscar informações neles e fecha-los!

Caso prefira esse conteúdo no formato de vídeo-aula, assista ao vídeo abaixo!

Para receber a planilha que usamos na aula no seu e-mail, preencha:

Por que trabalhar com arquivos diferentes?

Porque conseguimos fazer com que o VBA consiga atuar em arquivos diferentes para realizar ações em ambos, consultar algum outro arquivo, ou usar um arquivo como auxílio para o nosso arquivo final, que concentra dados.

Como trabalhar com arquivos diferentes no VBA?

Iremos basear nosso post de hoje conforme os arquivos utilizados no vídeo, sendo os arquivos da Aula 9 e Aula 4.

Primeiramente, para criar o código precisamos sempre Inserir um novo Módulo após abrir o VBA (Alt(+Fn)+F11), como já vimos em outros posts.

Para abrir arquivo de Excel com VBA e fazer alguma operação dele, devemos compreender que existem comandos específicos para abrir, e fechar o arquivo, por exemplo.

Queremos, assim como mostrado no vídeo, abrir o arquivo “Aula 4 – Descobrindo a primeira linha vazia”, pegar o valor da célula C9 desse arquivo, e fechá-lo.

Para isso, vamos usar esses comandos específicos, e outros já mostrados em posts anteriores aqui no Blog. Observe abaixo como ficará o código para realizar esse procedimento:


Sub trabalhar_arquivos()
Dim planilha As Object
Set planilha = Workbooks.Open("C:UsersdamorOneDriveHashtag (1)OnlineConteúdosPlanilhas2020VBA8-01 - Série Compilação com PlanilhasAula 4 - Descobrindo a primeira linha vazia.xlsx")
conteudo = planilha.Sheets("Primeira Aba").Range("c9").Value
planilha.Close
ThisWorkbook.Sheets("Fórmulas").Range("b1").Value = conteudo
End Sub

Explicação da sub trabalhar_arquivos():

Iremos inicialmente dimensionar a planilha a ser aberta como um objeto, para que o VBA entenda o arquivo é um objeto e usar as propriedades específicas para ele.

Estamos definindo a variável “planilha” para que ela substitua o nosso arquivo aberto em todo o código, que será a planilha que se encontra no caminho de pasta passado para o VBA, que está dentro do comando Workbooks Open. Com essa linha de comando, iremos abrir o arquivo da Aula 4.

OBS: Para obter o caminho do arquivo inteiro, podemos, na caixa do Windows Explorer, da pasta em que estão as planilhas, clicar no arquivo desejado (no nosso exemplo, o da Aula 4).

Com o arquivo selecionado (em 1), ir em Início (em 2) > Copiar caminho (em 3). Pronto, agora você tem copiado o caminho inteiro do arquivo, e pode passar para o VBA apenas colando (Ctrl+V).

Abrir Arquivo no VBA
Abrir Arquivos no VBA

OBS2: Lembre-se que o caminho será diferente do que está apresentado no arquivo de gabarito da Aula 9. Cada computador tem um caminho de pasta diferente.

Iremos armazenar o valor da célula C9 da “Primeira Aba” da planilha que pedimos ao VBA para abrir (da Aula 4) na variável “conteudo”.

Após realizar esse armazenamento, iremos comandar o VBA para fechar a planilha que abrimos no começo, com o comando “planilha.Close”.

Então, iremos passar o comando para o VBA armazenar o valor atribuído à variável “conteudo” na célula B1, da aba “Fórmulas” do arquivo que estamos escrevendo a macro (Aula 9).

E então, se encerra a nossa sub.

Aprender a abrir arquivo de excel com VBA vai ser muito útil para conclusão da nossa série, em que vamos abrir diversas planilhas, extrair informações e exportá-las para a nossa planilha de resumo.

Nesse post aprendemos então a abrir planilha com VBA. Esse procedimento irá se complementar com outros conteúdos de posts anteriores e será importante nas próximas aulas.

Voltar ao índice

Aula 10 – Percorrendo Arquivos de uma Pasta

Nessa publicação vou te mostrar como usar o VBA para arquivos quando estivermos percorrendo arquivos de uma pasta no computador.

Caso prefira esse conteúdo no formato de vídeo-aula, assista ao vídeo abaixo!

Para receber a planilha que usamos na aula no seu e-mail, preencha:

Por que percorrer arquivos de uma pasta?

Porque, percorrendo os arquivos de uma pasta podemos realizar determinadas ações com todos esses arquivos de uma vez, em um único código. Será fundamental para a ferramenta final da nossa série de consolidação de dados no Excel.

Como fazemos para percorrer os arquivos de uma pasta?

Essa aula é dedicada à décima aula da nossa série e nela ensinamos conteúdos que serão fundamentais nesse post e, portanto, nele será abordado mais superficialmente.

Existem diversas estruturas de repetição no VBA e cada uma delas é mais recomendada para uma situação específica. A estrutura que vamos dedicar o post é a For Each. Essa é uma estrutura cujo foco é percorrer objetos como um todo.

Por exemplo: hoje queremos percorrer arquivos em uma pasta, com objetivo de abrir todas as planilhas dentro dela. Lembre-se que recentemente fizemos um post dedicado as estruturas de repetição, onde explicamos detalhadamente.

Nosso objetivo nesse post é mostrar como andar por esses arquivos, que representam as vendas das unidades da nossa empresa, e colocar os nomes dessas planilhas no nosso arquivo central, na coluna A.

Para isso, precisamos abrir o VBA, através do atalho Alt(+Fn)+F11, e ir na guia Inserir > Módulo. A partir dali, criaremos a nossa sub percorrer_pasta, como no vídeo. Confira abaixo o código da nossa sub pronta e em seguida sua explicação:

Sub percorrer_pasta() 

Dim pasta As Object

Dim caminho_pasta As String

caminho_pasta = "C:UsersdamorOneDriveHashtag (1)OnlineConteúdosPlanilhas2020VBA8-01 - Série Compilação com PlanilhasPlanilhas" & ""

Set pasta = CreateObject("Scripting.FileSystemObject").getfolder(caminho_pasta)
 
For Each arquivo In pasta.Files

    primeira_vazia = Range("A1048576").End(xlUp).Row + 1
   
    Cells(primeira_vazia, 1).Value = arquivo.Name

Next

End Sub
Explicação da sub percorrer_pasta():

Logo no começo precisamos definir algumas variáveis para o VBA, porque iremos usá-los durante o nosso código. A variável “pasta” será um objeto e a variável “caminho_pasta” será um texto (string).

Depois disso, diremos para o VBA armazenar o caminho da pasta onde estão todos os nossos arquivos a serem percorridos na variável “caminho_pasta”.

Após, vamos passar para o VBA que a variável “pasta” (objeto que criamos no início) assumirá ou representará o objeto que será um arquivo do sistema criado pelo VBA que está no caminho passado pela variável “caminho_pasta”.

Então, começamos a nossa estrutura de repetição, dizendo para o VBA que queremos percorrer todos os arquivos dentro dos arquivos (files) do objeto pasta (objeto que é representado pela nossa variável “pasta”).

Ao entrar em cada arquivo, queremos:

1) Descobrir a primeira linha vazia da coluna A, para fazermos o registro do nome dessa planilha e armazenar essa informação na variável “primeira_vazia”;

2) Registraremos o nome desse arquivo (arquivo.Name) na célula correspondente à primeira linha vazia da coluna A (1ª linha).

Ir para o próximo arquivo, repetir, até os arquivos serem todos registrados e fecharemos nossa sub. Veja abaixo o resultado dela, quando executada de uma vez:

Listar Arquivos de Uma Pasta no Excel
Listar Arquivos de Uma Pasta no Excel

OBS1. Lembrando que não somos obrigados a declarar as variáveis. Nesse caso deixamos de declarar a variável “primeira_linha” e a variável “arquivo”.

O VBA vai entender que a primeira_linha é uma variável do tipo Variant (engloba todos os tipos), e vai entender que a “arquivo” é um objeto, porque estamos dizendo que arquivo é uma unidade do conjunto de arquivos dentro do objeto “pasta” (estará implícito).

OBS2. Lembre-se que o caminho da nossa sub será diferente do que está apresentado no arquivo de gabarito. Cada computador tem um caminho de pasta diferente.

Voltar ao índice

Aula 11 – Como Acelerar seu código em VBA

Vou te mostrar como acelerar seu código em VBA utilizando algumas dicas e truques bem simples e fáceis de serem implementados!

Caso prefira esse conteúdo no formato de vídeo-aula, assista ao vídeo abaixo!

Para receber a planilha que usamos na aula no seu e-mail, preencha:

 

Por que deixar seu código mais rápido?

Sempre é bom fazer uma tarefa mais rápido, para usar esse tempo em outra. Acelerando seu código em VBA, você terá mais tempo para fazer outras operações com sua planilha, ou apenas fará tudo mais rápido, simplesmente, e usará seu tempo com outra coisa.

Como acelerar seu código em VBA?

Estamos aqui na nossa décima primeira aula da série. Nesse post abordaremos alguns conteúdos mais superficialmente se comparado à forma que foram abordados nos anteriores, que foram mais explicados.

Vamos mostrar através do código exemplo do último post. Vamos rodá-lo, e inicialmente marcar seu tempo. Após, fazer algumas modificações no código, que gerarão uma segunda e uma terceira rodadas, que serão marcadas também, para podermos comparar o quão mais rápido ele ficou.

Repare abaixo o código presente no nosso vídeo, que explicaremos em seguida:

Sub percorrer_pasta()

Application.ScreenUpdating = False
Application.Calculation = xlCalculationManual
Application.DisplayAlerts = False

Dim pasta As Object
Dim caminho_pasta As String
Dim planilha As Object
Dim tempo As Double
Dim tempo_final As Double
Dim tempo_total As Double

tempo = Timer

caminho_pasta = "C:UsersdamorOneDriveHashtag (1)OnlineConteúdosPlanilhas2020VBA8-01 - Série Compilação com PlanilhasPlanilhas" & ""

Set pasta = CreateObject("Scripting.FileSystemObject").getfolder(caminho_pasta)

For Each arquivo In pasta.Files

    If InStr(arquivo.Name, "Aula") = 0 Then

        primeira_vazia = Range("A1048576").End(xlUp).Row + 1     

        Set planilha = Workbooks.Open(caminho_pasta & arquivo.Name)

        planilha.Close      

        Cells(primeira_vazia, 1).Value = arquivo.Name

    End If

Next

tempo_final = Timer

tempo_total = Round(tempo_final - tempo, 1)

Application.DisplayAlerts = True

MsgBox ("Concluído em " & tempo_total & " segundos")

' 1ª Tentativa: 58,63
' 2ª Tentativa: 43
' 3ª Tentativa: 39,41

Application.ScreenUpdating = True
Application.Calculation = xlCalculationAutomatic

End Sub
Explicação da sub percorrer_pasta():

Basicamente essa sub irá fazer o que fizemos no último post, com o adendo “setar” a variável planilha a cada arquivo da pasta como objeto da estrutura For Each, de abrir e fechar cada planilha da pasta.

Além disso, temos uma parte inicial de definição de variáveis que irão compor nosso cronômetro (Timer) que irá marcar o tempo que a macro irá rodar, algumas linhas de código novas (três primeiras), e uma função de texto para evitar de abrir arquivos que não queremos.

Ao entrar na estrutura de repetição, queremos, a partir da função InStr buscar a palavra “Aula” no nome do arquivo (arquivo.Name).

Se a função não achar (retornar 0), então queremos localizar a primeira linha vazia da nossa planilha, abrir a planilha (arquivo), fechá-la e registrar seu nome na primeira linha vazia da coluna A da nossa planilha. Caso contrário, ou seja, se tiver “Aula” no nome desse arquivo, não faremos nada.

Inicialmente dimensionamos tempo, tempo_final e tempo_total como Double, porque elas representarão números com casas decimais (inteiros), que armazenarão o tempo de funcionamento da macro dado através da função Timer nativa do VBA.

A variável tempo irá armazenar a hora que a macro começou a rodar, e a tempo_total irá armazenar a hora que a macro parou de rodar. Por consequência de como está definida, a tempo_total irá representar a duração da macro.

Ela será mostrada na MsgBox (caixa de mensagem) nas três rodadas que demonstramos no vídeo com tempos respectivos de 58,63 s, 43 s e 39,41 s (como estão escritos os comentários).

Lembrando que usamos a função Round para arredondar os segundos mostrados pela msgbox para apenas 1 casa decimal.

As três primeiras linhas do código dizem respeito a ações na aplicação do Excel, que devem ser “desativadas” no começo e ser “ativadas” no final.

O primeiro comando diz respeito à atualização de tela do Excel. Ou seja, desativamos a “piscada” de tela ao realizar ações com trocas de abas ou arquivos, e faz com que o código fique mais fluido.

O segundo diz respeito ao cálculo de fórmulas do Excel. A todo momento, o Excel realiza cálculos em todo o arquivo, que por padrão fica com os cálculos automáticos.

O nosso código força o Excel a ter o cálculo da planilha como manual durante o tempo que a macro roda para fazer com que o Excel não calcule nada, e evite perder tempo com esses cálculos automáticos.

O terceiro diz respeito a desativar o Excel de mostrar caixas de alerta (ex: msgbox, caixa de salvamento pré-fechamento sem salvar). Se não mostrarmos, ou ignorarmos essas caixas, ficaremos com a macro mais fluida também.

Lembrando que iremos desfazer esses comandos ao final da nossa macro. Ou seja, voltaremos à situação padrão para que o Excel volte a operar normalmente.

OBS: O comando de caixas de alertas irá ser ativado novamente antes do comando de Msgbox porque queremos ver a Msgbox que dirá quanto tempo nosso código demorou para rodar.

OBS2: Lembre-se que o caminho da nossa sub será diferente do que está apresentado no arquivo de gabarito. Cada computador tem um caminho de pasta diferente.

Com isso, aprendemos como acelerar seu código em VBA, deixando a macro mais rápida e eficiente, apenas com alguns ajustes.

Ao acelerar uma macro, você terá mais tempo para fazer outras operações com sua planilha, ou desenvolver outras atividades. Afinal, poupar tempo é sempre um objetivo de qualquer pessoa no dia-a-dia.

Voltar ao índice

Aula 12 – Compilar Planilhas no VBA

Nessa publicação vamos passar pelo código que criamos ao longo da Série de Compilação com o objetivo de Compilar Planilhas no VBA.

Caso prefira esse conteúdo no formato de vídeo-aula, assista ao vídeo abaixo!

Para receber a planilha que usamos na aula no seu e-mail, preencha:

O que é?

O último vídeo de nossa série de consolidação de dados em planilhas. Ou seja, vamos explicar como trazer as informações de várias planilhas que vão ser inseridas em uma planilha central, que concentrará todos os dados, de acordo com o exemplo do vídeo.

Como Compilar Planilhas no VBA?

Iremos nesse post finalizar a série, vamos realizar a consolidação de dados no Excel de diversas planilhas, representadas por lojas que realizam vendas, e queremos concentrar essas informações em uma planilha central.

Abaixo temos o código necessário para fazer todas essas etapas para juntar planilhas em uma só. Iremos observar e após teremos o texto explicativo dos passos. Alguns já foram aprofundados em outros posts e serão abordados mais superficialmente.

Sub compilacao()
' Desativar atualização de tela e cálculo automático
Application.Calculation = xlCalculationManual
Application.ScreenUpdating = False
' Declarar variáveis
Dim tempo_inicial As Double
Dim tempo_final As Double
Dim tempo_total As Double
Dim caminho_pasta As String
Dim pasta As Object
Dim aba_resumo As Object
Dim planilha As Object
Dim linha_vazia_resumo As Long
Dim qtd_info As Long
Dim ult_linha As Long
tempo_inicial = Timer ' Guardar momento em que a macro começou
caminho_pasta = ThisWorkbook.Path & "" ' Definir a pasta onde estão os arquivos a serem compilados
Set pasta = CreateObject("Scripting.FileSystemObject").getfolder(caminho_pasta) ' Criar um objeto para a pasta em questão
Set aba_resumo = ThisWorkbook.Sheets("Resumo") ' Dar um nome à aba principal da planilha Resumo
For Each arquivo In pasta.Files ' Percorrer todos os arquivos da pasta em questão
    linha_vazia_resumo = aba_resumo.Range("A1000000").End(xlUp).Row + 1 ' Verificar a primeira linha vazia da aba Resumo
    If InStr(arquivo.Name, "Resumo") = 0 Then ' Só analisar se o nome do arquivo não contiver "Resumo"
        Set planilha = Workbooks.Open(caminho_pasta & arquivo.Name) ' Abre a planilha     
        qtd_info = planilha.Sheets(1).Range("A1000000").End(xlUp).Row - 1 ' Armazena a quantidade de informações na planilha
        
        If qtd_info >= 1 Then ' Só executa se tiver pelo menos uma informação
                aba_resumo.Range("A" & linha_vazia_resumo & ":D" & linha_vazia_resumo + qtd_info - 1).Value = planilha.Sheets(1).Range("A2:D" & qtd_info + 1).Value ' Transfere as informações da planilha da loja para a planilha de Resumo
                aba_resumo.Range("E" & linha_vazia_resumo & ":E" & linha_vazia_resumo + qtd_info - 1).Value = Replace(arquivo.Name, ".xlsx", "") ' Adiciona na planilha de Resumo uma informação dizendo de qual loja vieram essas movimentações
        End If
    
    planilha.Close ' Fecha a planilha da loja
    End If
Next
aba_resumo.Columns("A:E").AutoFit ' Utiliza o autoajuste nas colunas
aba_resumo.Columns("A:E").HorizontalAlignment = xlCenter ' Centraliza os textos
With aba_resumo.Sort ' Organiza do mais antigo para o mais novo
    .SortFields.Clear
    .SortFields.Add2 Key:=Range("A1"), SortOn:=xlSortOnValues, Order:=xlAscending, DataOption:=xlSortNormal
    .SetRange Range("A:E")
    .Header = xlYes
    .MatchCase = False
    .Orientation = xlTopToBottom
    .SortMethod = xlPinYin
    .Apply
End With
aba_resumo.Columns("A:A").NumberFormat = "dd/mmm/yy" ' Formatar a coluna A como data
aba_resumo.Columns("D:D").NumberFormat = "$ #,##0.00" ' Formatar a coluna D como moeda
ult_linha = aba_resumo.Range("A1000000").End(xlUp).Row ' Armazena a última linha preenchida da planilha
aba_resumo.Range("A2:E" & ult_linha).Borders.LineStyle = xlContinuous ' Coloca borda em todas as células preenchidas
tempo_final = Timer ' Guarda o momento em que a macro finalizou
tempo_total = Round(tempo_final - tempo_inicial, 1) ' Guarda o tempo total gasto para rodar a macro
MsgBox ("Concluído em " & tempo_total & "s") ' Exibe uma mensagem com o tempo necessário para rodar a macro
' Reativar atualização de tela e cálculo automático
Application.Calculation = xlCalculationAutomatic
Application.ScreenUpdating = True
End Sub

Explicação da sub compilacao():

As duas primeiras linhas do código dizem respeito a ações na aplicação do Excel, que devem ser “desativadas” no começo e ser “ativadas” no final. 

Lembrando que iremos desfazer esses comandos ao final da nossa macro. Ou seja, voltaremos à situação padrão para que o Excel volte a operar normalmente.

O primeiro comando diz respeito à atualização de tela do Excel. Ou seja, desativamos a “piscada” de tela ao realizar ações com trocas de abas ou arquivos, e faz com que o código fique mais fluido.

O segundo diz respeito ao cálculo de fórmulas do Excel. A todo momento, o Excel realiza cálculos em todo o arquivo, que por padrão fica com os cálculos automáticos.

Após dizer os comandos da aplicação, iremos declarar as variáveis que iremos utilizar durante o código, cada uma com um tipo específico, como já explicado em posts anteriores.

Em seguida, guardaremos o tempo inicial que teremos no nosso computador na variável tempo_inicial através da função Timer para que possamos ter o tempo total de duração do nosso código (lá no final).

Logo após iremos pegar o caminho da pasta que queremos abrir e armazenar na variável caminho_pasta. Já explicamos como fazer isso em posts anteriores.

OBS: Lembre-se que o caminho da nossa sub será diferente do que está apresentado no arquivo de gabarito. Cada computador tem um caminho de pasta diferente.

Usaremos de uma estrutura de repetição para percorrer os arquivos da pasta em questão, mas somente os arquivos que não contenham a palavra “Resumo”, porque temos dois arquivos com “Resumo” nos nomes que não queremos compilar.

OBS2: Excluiremos os arquivos que não tem nenhuma informação dentro, ou seja, que tem qtd_info = 0.

Ao entrar no arquivo em questão, iremos pegar todas as informações desse arquivo, de forma a atribuir valores de um intervalo. Olhando especificamente para essa linha do código, como podemos ver abaixo:

   If qtd_info >= 1 Then ' Só executa se tiver pelo menos uma informação
         aba_resumo.Range("A" & linha_vazia_resumo & ":D" & linha_vazia_resumo + qtd_info - 1).Value = planilha.Sheets(1).Range("A2:D" & qtd_info + 1).Value
 ' Transfere as informações da planilha da loja para a planilha de Resumo
         aba_resumo.Range("E" & linha_vazia_resumo & ":E" & linha_vazia_resumo + qtd_info - 1).Value = Replace(arquivo.Name, ".xlsx", "") 
' Adiciona na planilha de Resumo uma informação dizendo de qual loja vieram essas movimentações
   End If

Vamos transferir as informações da planilha da loja para a planilha de resumo.

Queremos atribuir, do nosso arquivo de Resumo, da coluna A, da primeira linha vazia (linha_vazia_resumo, antes definida) até a coluna D, pegando até a última informação preenchida (OBS3), os valores do intervalo inteiro da primeira aba da planilha aberta em questão (de alguma loja), do intervalo de A2 até a coluna D, pegando até a linha representada pela qtd_info + 1

OBS3:linha_vazia_resumo + qtd_info – 1” vai nos dizer quantas informações vem de uma planilha das lojas, e vamos descontar 1 unidade da linha_vazia_resumo porque temos o cabeçalho contando como uma linha (se não temos nenhuma informação na aba resumo, ela começa na 2ª linha, porque o cabeçalho está na 1ª), e a qtd_info vai ser inserida a partir dessa linha.

OBS4: qtd_info + 1”: porque a qtd_info vai nos dar a quantidade de linhas preenchidas tirando o cabeçalho, por definição, e precisamos acrescentar 1 linha para que ele interprete a última linha corretamente)

Após essa atribuição de valores para o intervalo da aba da planilha de Resumo, iremos preencher na coluna E dela o nome de qual loja aquelas informações vieram, para que fique categorizado corretamente. Iremos registrar em cada célula da coluna E o nome da planilha (arquivo.Name), substituindo seu final (.xlsx) por nada (“”), para que fiquemos só com a primeira parte, ou seja, o nome da loja.

Após, como já explicado em outros posts, iremos fechar o arquivo da loja em questão, repetir a estrutura em loop até percorrer todos os arquivos da pasta, pegar todas as informações e trazer para a aba resumo.

Em seguida iremos ajeitar a formatação da aba de Resumo, fazendo o autoajuste e centralizando as informações das colunas A até E, e classificaremos esses dados de acordo com a data, da mais antiga para a mais nova. Então, formataremos a coluna A para data, e a coluna D como moeda.

Em seguida vamos preencher todas as linhas que tem informações com bordas, para que fique num formato de tabela, através da informação da última linha preenchida.

Para finalizar teremos a MsgBox nos mostrando quanto tempo o Excel levou para rodar aquele código, como já explicado também em posts da série. As duas últimas instruções irão ativar os comandos da aplicação desativados no início.

Com isso, demonstramos ao longo de toda a série o passo a passo necessário para construir essa ferramenta e conseguir compilar planilhas no VBA. Concluímos a série por aqui, mostrando e explicando o código utilizado linha por linha para criar essa ferramenta de compilação de planilhas.

Conclusão – Compilação de Planilhas no VBA

Nesta série vamos abordar do início ao fim como construir uma ferramenta de compilação de planilhas no VBA.

Durante as 12 aula vamos passar por diversos conceitos e códigos importantes do VBA e você vai conseguir aprender na prática como organizar e entender as informações enquanto constrói uma ferramenta com a funcionalidade de percorrer os arquivos de uma pasta, e para cada um deles vamos realizar determinadas ações.

Vale lembrar que tudo que foi utilizado na construção dessa ferramenta foi ensinado ao longo das 12 aulas da série onde aprendemos a juntar planilhas no Excel em uma só. Ficamos por aqui, e até a próxima! Fique ligado no blog e no nosso canal do YouTube para mais conteúdo de VBA!

Voltar ao índice

Hashtag Treinamentos

Para acessar outras publicações de VBA, clique aqui!


Quer aprender mais sobre VBA com um minicurso básico gratuito?

 


Quer ter acesso a um Minicurso de Finanças no Excel de forma 100% gratuita? Preencha seu e-mail abaixo!