VBA: manipulação e limpeza de dados

Trabalhando com data no VBA - Apresentação

Olá! Eu sou o Roberto Sabino, um dos instrutores da Alura, e estou aqui para iniciar o segundo curso da nossa formação em VBA. Neste curso, vamos explorar como automatizar tarefas, uma habilidade que faz toda a diferença na produtividade.

Para fins de acessibilidade, farei minha audiodescrição.

Audiodescrição: Sabino se identifica como um homem de pele clara. Com cabelos castanhos curtos e escuros e barba grisalha. Veste uma camiseta escura lisa. Ao fundo, há parede lisa com um gradiente em azul.

Para quem é este curso?

Este curso é para quem quer aprender a automatizar tarefas, aprender o VBA, e aprimorar o vocabulário em VBA.

Como o VBA é uma linguagem de programação, é essencial possuir um amplo vocabulário para utilizá-la efetivamente.

Estamos realizando tarefas manualmente neste momento. Apesar de termos acesso à inteligência artificial, acredito firmemente que devemos integrá-la em nossas práticas diárias. Aqui, estamos focando em aprender programação de forma genuína.

O que aprenderemos?

Neste curso, abordaremos um tópico bastante intrigante. Imagine o seguinte cenário: teremos uma planilha na qual um colega de trabalho solicitou assistência.

A demanda é selecionar determinadas linhas e transferi-las para uma nova planilha denominada "versão final". Além disso, se esta "versão final" ainda não existir, como é o caso atual (observe na parte inferior que não há uma aba com esse nome), precisamos criá-la. Após executar essa etapa de criação, transferimos as linhas selecionadas.

Para exibir a aba "Versão Final", o instrutor clica em “Macros” na parte inferior esquerda e depois no botão “Executar”.

Aba "Versão Final" com a tabela com as linhas selecionadas:

Adicionalmente, na aba "Movimentação" estamos realizando a formatação de datas na aba "Datas" (D), convertendo-as do formato americano na aba "Movimentação" para o brasileiro na aba "Versão Final". Este aspecto é particularmente interessante, pois a manipulação de datas em VBA pode ser desafiadora. Exploraremos profundamente este tema ao longo do curso.

Se você está interessado em aprender a automatizar tarefas, manipular dados e fazê-lo de maneira automática usando VBA, este curso é ideal para você. É importante lembrar que o curso foi desenvolvido para promover uma aprendizagem prática e ativa.

Você terá a oportunidade de aplicar esses conhecimentos no seu dia a dia, experimentar diferentes técnicas e interagir com outros estudantes por meio do fórum, o que é uma parte essencial do processo de aprendizado. Afinal, quem não deseja automatizar tarefas, aumentar a produtividade e melhorar suas perspectivas de emprego? Então, sem mais delongas, vamos para a nossa primeira aula!

Trabalhando com data no VBA - Criando uma função personalizada

Estamos empregados na Bytebank e recentemente concluímos o primeiro curso da formação em VBA da Alura. Iniciamos a aplicação prática do que aprendemos, alterando nossas abordagens anteriores e adotando técnicas para manipular e limpar dados de forma mais eficiente.

Nossos colegas de trabalho notaram nossas mudanças e começaram a nos procurar para auxiliá-los em determinadas situações. Esse tipo de reação é típico entre aqueles que estão aprendendo VBA. Como resultado, estamos conseguindo resolver mais problemas, automatizar tarefas e despertar o interesse das pessoas por essa habilidade de automatização de tarefas.

Contextualizando

Um dia, um colega de trabalho nos trouxe um desafio: ele tinha um arquivo contendo dados para ajustar os relatórios diários de movimentação de clientes, e as datas estavam no formato americano (ano, mês, dia).

Ele tentou formatar as datas usando as opções padrão do Excel, mas não obteve sucesso. Mesmo ao tentar diferentes formatos de data e hora, ele percebeu que o Excel continuava tratando as datas como strings (cadeias de caracteres), impedindo qualquer alteração efetiva.

Rapidamente, examinamos a situação e ponderamos que talvez fosse viável tratar a data como uma string e explorar algumas possibilidades. Contudo, com o conhecimento adquirido no curso inicial, ainda não seria trivial resolver esse problema.

Expandindo o vocabulário em Excel

Portanto, vamos iniciar por aqui e expandir nosso vocabulário no Excel, especialmente em VBA. A linguagem VBA do Excel é compartilhada com o Word e o PowerPoint? Sim, a linguagem é a mesma, os conceitos de loops e variáveis são idênticas, porém os objetos são distintos.

No Excel, encontramos o Workbook e o Active Sheet, enquanto no Word, por exemplo, teríamos o Active Document, não o Active Sheet, devido à diferença nos objetos. Consequentemente, a linguagem acaba sendo um tanto distinta. Estamos lidando, por exemplo, com o objeto Range, que representa uma célula. No Word, não teríamos o Range, sendo necessário utilizar outras abordagens.

Mas isso é apenas uma curiosidade; normalmente, as pessoas aprendem VBA no Excel mesmo, então não se preocupe com isso.

Formatando a data para BR

Como podemos fazer para formatar esta data no formato BR (brasileiro), conforme solicitado pelo nosso colega de trabalho? Temos duas opções: podemos abordar isso como uma Sub ou como uma Function, como aprendemos no início do Curso 1.

Optaremos por começar com uma Function. Por quê? Há um dado importante aqui: nosso colega de trabalho não nos explicou o motivo por trás disso, nem o que ele faria em seguida com os dados. Na verdade, nós também não perguntamos, então só temos uma parte da informação.

De fato, isso é interessante, porque quando conhecemos apenas uma parte do problema, é mais simples de lidar. No futuro, vamos perceber que a melhor abordagem para resolver um grande problema é dividi-lo em problemas menores.

Essa foi uma das primeiras lições que aprendi no início da graduação em tecnologia. Isso aconteceu há muitos e muitos anos atrás, poderíamos até dizer décadas atrás, e poderíamos usar o plural "décadas".

Então, vamos observar que subdividir um problema em partes menores é vantajoso. Portanto, não vamos nos preocupar com o que exatamente o colega de trabalho deseja. Em vez disso, vamos focar neste problema específico e criar uma Function.

Criando uma function

Como vamos fazer essa Function? A primeira coisa é lembrar o nosso passo a passo. Primeiro, a aba "Desenvolvedor" na parte superior, se não estiver disponível a aba "Desenvolvedor", clique com o botão direito, e opte por "personalize a faixa de opções".

Na janela seguinte, clique no checkbox escrito "Desenvolvedor" do lado direito. Logo após, em "Ok". Só para recordar, isso nós já fizemos e já temos a aba na parte superior.

Posteriormente, acessamos o "Visual Basic" clicando nele no canto superior esquerdo do Excel. Aqui, o nosso explorador de projeto do lado esquerdo terá as pastas de trabalho abertas, e é importante manter o foco na pasta que estamos utilizando.

• Solver (SOLVER.XLAM)
• VBAProject (Movimentacoes)
  • Microsoft Excel Objetos
    • EstaPastaDeTrabalho
    • Planilha 1 (Movimentação)
• VBAProject (PERSONAL.XLSB)

Para começarmos corretamente, clicamos sobre "VBAProject(Movimentacoes)", depois clicamos com o botão direito do mouse e selecionamos "Inserir > Módulo". Assim, estaremos prontos para iniciar a criação dos nossos códigos VBA.

No primeiro curso, dedicamos bastante tempo às Subs, e agora estamos adentrando o uso das Functions. Vamos revisar? Uma Function é declarada utilizando a palavra-chave function, seguida pelo nome da função. Neste caso, é recomendável começar com "Fn". Por exemplo, vamos chamá-la de "AjustaData".

Function fnAjustaData()

Esta function requer parâmetros, e vamos explorar isso com mais detalhes agora que estamos aprofundando o tema das functions. Ela retornará uma data neste caso específico, pois não se trata apenas de uma data, mas sim de um tipo de dado date, em inglês. Portanto, aqui está nossa function. No final digitamos End Function.

Function fnAjustaData() as Date


End Function

Na verdade, é relativamente fácil fazer isso aqui, mas precisamos entender o passo a passo. Primeira coisa, normalmente não fazemos loop na function, então uma coisa que precisamos entender muito bem, e que vamos usar isso sempre.

Quando criamos uma function, não estamos tentando resolver todas as datas de uma vez. Em vez disso, nosso objetivo é criar uma function que resolva apenas uma data de cada vez. Depois, podemos aplicar essa mesma function a todas as outras datas necessárias.

Portanto, não precisamos nos preocupar com múltiplas datas de uma vez. Essa é uma das vantagens de usar function. Mudar nossa perspectiva para focar em problemas menores nos permite resolvê-los com mais facilidade.

Agora, vamos considerar um problema potencial que um colega de trabalho pode ter. Eles podem precisar lidar com uma data específica, mas ainda não sabemos exatamente o que eles estão tentando fazer com ela. Mais tarde, podemos perguntar por curiosidade ou para aprender mais.

Além disso, é importante visualizar essa data como uma string. O que é uma string? Basicamente, é um tipo de dado que representa uma cadeia de caracteres.

Uma string pode ser considerada como texto. Mas se pensarmos nela como uma cadeia de caracteres, podemos entender melhor algumas propriedades dela. Por exemplo, se perguntarmos sobre a posição 1 dessa string, ela pode não ser o que esperamos. No caso, na coluna "Data" a data 2024-01-01 é D2.

Em alguns ambientes de desenvolvimento, como programação, algumas linguagens começam a contar do zero, enquanto outras começam do um. No caso do VBA (Visual Basic for Applications), por padrão, ele usa a base 1, ou seja, o primeiro item de uma coleção é o item 1. No entanto, em certas situações, pode-se deparar com o uso da base 0 também.

Vejamos um exemplo: suponha que temos uma string representando uma data 2024-01-01 (no caso, a primeira posição da string é o caractere "2"). Se é uma data ou nome, não importa, é uma cadeia de caracteres.

2024-01-01

Considerando a ordem sequencial dos caracteres, começando pelo número "2" como o primeiro, seguido pelo "0" como segundo, o "2" como terceiro, o "4" como quarto e o traço "-" como quinto, cada elemento subsequente é contado consecutivamente. Portanto, o "0" é o sexto, o "1" o sétimo. Novamente, o traço é contado como oitavo, seguido pelo "0" e "1", sendo o nono e o décimo, respectivamente.

Essa contagem se deve ao fato de estarmos lidando com uma sequência de caracteres, onde cada um é contabilizado sequencialmente: 2, 3, 4, 5, 6, 7, 8, 9, 10. Assim, essa cadeia possui um total de 10 caracteres.

No entanto, sabemos que esses caracteres têm significados específicos. Por exemplo, os quatro primeiros representam o ano (2024). Poderíamos usar funções de data e pedir o ano dessa determinada string. Há casos em que isso funciona e há casos que não.

Ou podemos optar por utilizar funções de string para extrair os elementos dessa sequência de caracteres, permitindo-nos manipular os itens presentes nessa cadeia. Esta abordagem é preferível porque lidar com datas pode ser bastante complexo, variando em sua funcionalidade dependendo das configurações regionais (regional settings).

O que isso significa? Quando acessamos nosso computador e especificamos como queremos que a data seja formatada, seja por dia, mês, ano, ou por ano, mês, dia, ou talvez apenas por dia e mês, estamos lidando com as configurações regionais, conhecidas como Regional Settings.

No Windows, temos uma seção dedicada para definir isso. Aqui, estamos fazendo algo semelhante. Podemos manipular a data diretamente, ou podemos optar por trabalhar com uma cadeia de caracteres.

Por quê? Porque ao lidarmos com uma cadeia de caracteres, não estamos vinculados às configurações regionais. Elas só entram em jogo quando convertemos essa cadeia de caracteres de volta para uma data.

Então, o que estamos propondo é o seguinte: quando enviamos o valor final para o final desta função, vamos fazer com que ela ajuste a data. Passamos que fnAjustaData = "Alguma coisa", mas não é assim que irá ficar.

Function fnAjustaData() As Date

        fnAjustaData = "Alguma coisa"
End Function

Precisamos inserir algum conteúdo aqui. Por enquanto, é apenas ilustrativo. Esse "Alguma coisa", quando jogarmos para ajustaData, se estivermos trabalhando com strings, elas serão convertidas em data, já que o resultado final da função é uma string.

No entanto, ainda pode haver problemas com as configurações regionais, e você precisará verificar como o seu Windows está configurado. Para evitar completamente problemas com as configurações regionais, teríamos que retornar a data como string, mas mesmo assim não resolveria completamente, pois o Excel pode interpretá-la como uma data e convertê-la para algum formato.

function fnAjustaData() As string

        fnAjustaData = "Alguma coisa"
End Function

Portanto, trabalhar com datas é bastante complexo e requer muita atenção.

Vamos continuar trabalhando aqui. Esta abordagem funcionará bem com as configurações regionais que estamos utilizando. Se as configurações regionais forem diferentes, pode ser necessário fazer alguns ajustes. De qualquer forma, já temos uma boa ideia do que faremos nesta função. E o que faremos na função?

Vamos pegar o formato atual (2024-01-01) e convertê-lo para o formato brasileiro de dia, mês e ano. Faremos isso no próximo vídeo!

Trabalhando com data no VBA - Formatando as datas

A principal consideração, e por isso introduzimos esta aula no início, é perceber a necessidade de usar uma estrutura de repetição ou aplicar um raciocínio único. No contexto de uma função, é importante reiterar que ela deve ser executada apenas uma vez.

Formatando as datas

Ao receber um parâmetro aqui, esse parâmetro deve ser a própria data. Realizaremos alguma operação com esse parâmetro. Uma observação importante sobre o parâmetro é que ele, denominado pData (parâmetro data), será fornecido como uma cadeia de caracteres (string).

Function fnAjustaData(pData as String) As Date

        fnAjustaData = "Alguma coisa"
End Function

Identificando a tipificação

É relevante destacar que estamos tipificando nossas variáveis e parâmetros para demonstrar como isso é comum na prática. Embora seja possível no VBA prosseguir sem essa prática e escrever um código mais genérico, como a seguinte:

Function fnAjustaData(pData) As Date

        fnAjustaData = "Alguma coisa"
End Function

No entanto, prefiro enfatizar a tipificação como uma abordagem mais profissional. Por quê? Porque ao tipificar, estamos especificando que ele receberá uma string como parâmetro.

Utilizando as funções de string

Agora, utilizaremos as funções de string. Principalmente, faremos uso da função mid(), que extrairá um segmento intermediário (middle) da nossa string.

Podemos aplicá-la para obter partes do início ou do final da string, e é isso que faremos aqui. Existem outras funções que também poderíamos utilizar, e demonstraremos como implementá-las.

Qual é o objetivo dentro desse "alguma coisa"? Pretendemos inverter a ordem dos caracteres em nossa string. Sendo a coluna "Data" da aba "Movimentação".

2024-01-01

Portanto, inicialmente, vamos selecionar os dois últimos caracteres (01, da direita para a esquerda), adicionar uma barra, selecionar mais dois caracteres (01 entre os dois traços da data) e adicionar novamente a barra e selecionaremos os próximos quatro caracteres para concatenação.

01/01/2024

Assim, o primeiro passo é determinar quais são os caracteres desejados. Poderíamos utilizar as funções left(), que extrai a parte inicial, e right(), que extrai a parte final, o que também funcionaria. No entanto, se preferirmos, podemos realizar todas as operações utilizando mid() desde o início.

Como a função mid() opera? Primeiro, especificamos a string alvo (nesse caso, a data completa "2024-01-01"), então indicamos o ponto de partida, ou seja, o caractere inicial do qual desejamos extrair, e por fim, especificamos o tamanho.

Por exemplo, na coluna "Data" desejamos os dois caracteres "01" da direita para a esquerda. Precisamos saber que da esquerda para a direita na data "2024-01-01" o número "2" é o 1, o "0" é o "2", o "2" é o "3", e seguinte: 4, 5, 6, 7, 8, 9. Desejamos dois caracteres.

Vamos ao Microsoft Visual Basic.

Ao trabalharmos com nosso código VBA, especificamos que desejamos extrair uma parte de pdata utilizando a função mid(). Quem é pData? É o parâmetro que foi recebido anteriormente. Portanto, queremos extrair uma porção específica da nossa string pData. Onde queremos que essa extração comece? Queremos iniciar a partir do nono (9) caractere e capturar tanto o nono (9) quanto o décimo (10). Portanto, buscamos extrair duas (2) posições: Mid(pData,9,2).

Function fnAjustaData(pData As String) As Date

        fnAjustaData = mid(pdata,9,2)
                
End Function

Utilizando a concatenação

Em seguida, vamos concatenar esse resultado com uma barra, para representar o dia, mês e ano. Agora, vamos prosseguir concatenando, utilizando o operador &, e então aplicaremos outra função Mid().

Function fnAjustaData(pData As String) As Date

        fnAjustaData = Mid(pData,9,2) & "/" & mid()
                
                )
End Function

Qual parte da string queremos extrair agora? Vamos analisar onde está localizado o mês na coluna "Data".

2024-01-1

Contabilizando da esquerda para a direita, o mês está nas posições 1, 2, 3, 4, 5 e 6 (01 entre os traços), e também ocupa duas posições. Portanto, vamos extrair duas posições a partir do sexto caractere de pData: Mid(pData,6,2).

Function fnAjustaData(pData As String) As Date

        fnAjustaData = Mid(pData,9,2) & "/" & Mid(pData,6,2)
                
                )
End Function

Após isso, vamos concatenar outra barra e, em seguida, vamos concatenar o ano. O ano está localizado no início da string (1), então vamos usar a função Mid() novamente, especificando que queremos os quatro primeiros caracteres de pData: Mid(pData,1,4).

Function fnAjustaData(pData As String) As Date

        fnAjustaData = Mid(pData,9,2) & "/" & Mid(pData,6,2) & "/" & Mid(pData,1,4)
                
End Function

O que estamos fazendo? Estamos devolvendo essa string. Ela permaneceu no estado "meio" de alguma coisa (Mid(pData,9,2)). Nós concatenamos com uma barra, seguida de outro "meio" de outra coisa (Mid(pData,6,2)), mais uma barra e outro "meio" (Mid(pData,1,4)).

Em outras palavras, estamos reorganizando a string e agora vamos interpretá-la como uma data, pois a função retorna uma data. Assim, automaticamente ocorrerá o que chamamos de conversão implícita. Não estamos explicitamente instruindo para converter, mas o programa realizará a conversão para uma data.

Vamos experimentar então? Vamos ao Excel na célula E2.

Se utilizarmos a função fnAjustaData() com o parâmetro "2024-01-01" da célula D2: fnAjustaData(E2). Obtemos:

01/01/2024

Agora vamos executar isso até o final arrastando para baixo a função e teremos as datas convertidas corretamente no formato desejado. E perceba também que agora elas estão em formato de data.

01/01/2024

02/.01/2024

03/01/2024

…

Como sabemos que é uma data? Porque a string, por padrão, a linha do lado esquerdo da célula (como observamos na coluna "Data") e a data alinhada lado direito da célula (como observamos em 01/01/2024). Portanto, ele devolveu uma data, mas além disso é óbvio, podemos verificar no formato na parte superior e saber que ele está no formato de data.

Se desejarmos modificar o formato desta data, podemos fazê-lo facilmente. Agora, observe como a data completa é automaticamente ajustada conforme necessário. Anteriormente, ao lidarmos com a data completa, ela não era modificada porque era uma string.

Em outras palavras, não apenas realizamos a conversão de uma string de um formato para outro, mas também transformamos o tipo de dado de string para data.

Agora nosso colega pode utilizar os dados de forma mais eficaz. No entanto, ele nos solicitou uma data. Já implementamos uma função que converte essa data, ajustando-a e formatando-a conforme o padrão brasileiro. Será que isso atende às necessidades dele?

Gostaríamos de chamar sua atenção para um ponto importante: ao trabalhar com VBA, programação e automação, muitas vezes o objetivo final desejado não é imediatamente aparente. O desafio reside em identificar esse objetivo, que pode não ser evidente à primeira vista.

No entanto, essa complexidade tem seus pontos negativos e positivos. Por um lado, podemos nos deparar com dificuldades para visualizar o objetivo inicialmente. Por outro lado, se conseguirmos decompor o problema em partes menores, torna-se mais fácil resolver cada uma individualmente.

Por exemplo, o colega expressou uma preocupação após receber uma solução: "É bom o que você fez, mas ainda não resolve completamente meu problema, pois ainda terei que criar uma função e aplicá-la em cada célula.".

De fato, essa necessidade era prevista desde o início, mas talvez não tenha sido discutida adequadamente. Ela expressou o desejo de uma solução mais simplificada, como um botão que preenchesse automaticamente os resultados desejados.

Então, como você abordaria a questão de fazer com que este código que montamos funcione para várias datas consecutivas? Vamos pensar juntos. Você já tenha o código da função ajustaData. Como você poderia adaptá-lo para lidar com múltiplas datas, inserindo uma após a outra?

Você pensaria em usar repetição, certo? Sim, a repetição seria uma abordagem apropriada. Mas seria necessário incorporar a repetição de forma externa à função, não dentro dela.

Conclusão

Tente resolver antes de assistir ao próximo vídeo. Nele, mostraremos uma maneira relativamente simples de fazer com que o código que você já tem seja aplicado a todas as datas, resolvendo assim o problema para todas elas. E faremos isso utilizando o conhecimento que já adquirimos ao longo do curso. Você será capaz de fazer isso.

Será possível alcançar isso? Se estivéssemos na sua posição, consideraríamos tentar. Antes de prosseguir para o próximo vídeo, onde explicamos o processo que seguimos aqui.

Sobre o curso VBA: manipulação e limpeza de dados

O curso VBA: manipulação e limpeza de dados possui 149 minutos de vídeos, em um total de 50 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:

• Trabalhando com data no VBA
• Estratégias de manipulação de dados
• Criando a planilha versão final
• Formatando os dados
• Lapidando o código final