O avanço tecnológico trouxe diversas facilidades para os seres humanos, principalmente no que se refere a trabalhos repetitivos e recorrentes. Um caso típico desses trabalhos é a transformação de dados em objetos com significado.
Pense no seguinte cenário: você recebe uma planilha com 100 mil linhas e três colunas. A primeira coluna traz o primeiro nome de um usuário; a segunda informa seu sobrenome; e a terceira indica o serviço de email que o usuário usa, como por exemplo @gmail.com ou @hotmail.com. É sua tarefa montar uma lista com base nessa planilha no seguinte formato: [primeiro nome][.][segundo nome][serviço de email].
Como você está em nível avançado, com certeza deve ter pensando em uma forma de solucionar esse problema no Excel mais ou menos assim: unir todas as informações em uma única célula e aplicar essa formatação a todas as linhas da planilha.
Essa é uma solução válida para o problema, mas queremos ir um pouco além neste módulo. Nosso objetivo é te ensinar a usar o VBA, pelo menos algumas partes introdutórias, para criar scripts versáteis e personalizados para cada problema enfrentado por você.
Basicamente estudaremos em profundidade o funcionamento das macros do Excel, mas sem entrar na linguagem de programação em si. Preferimos deixar a linguagem de programação de fora deste módulo porque seu uso tem como principal requisito o pensamento computacional, que é a habilidade de pensar em algoritmos que podem ser lidos pelo computador, respeitando as limitações impostas pela máquina. Nos limitaremos aos scripts gerados automaticamente pelo Excel ao gravar uma nova macro. Embora possa parecer uma abordagem um pouco rasa, entender nesse nível permitirá a você criar aplicativos inteiros no Excel.
Vamos relembrar como podemos gravar macros no Excel para depois adentrarmos em conceitos mais avançados. Usaremos a planilha contendo as informações sobre os veículos encontrados no mercado alemão usada no último módulo. Você precisa de uma conta corporativa ou de estudante para conseguir usar macros no Excel.
Abra um novo documento Excel e importe os dados, sem tratamento, para uma planilha.
Vamos supor o seguinte cenário: você sempre vai receber o arquivo CSV igual ao da aula. Dessa forma, sempre precisará tratar os dados de forma parecida, e fazer tudo “na mão” leva muito tempo. Seu supervisor não quer todos os dados da planilha, mas sim um pequeno resumo contendo marca e preço, tudo formatado adequadamente.
O Excel importa os dados em uma planilha nova, deixando a primeira planilha aberta (quando criamos um novo documento) intacta. Vamos organizar as informações nessa planilha em branco. Para ficar organizado, use a célula A1 como título da planilha, inserindo o texto “Marcas e Preços do Mercado Alemão de Veículos”.
Copie a coluna brand da planilha com os dados e cole na coluna G. Clique no título da coluna e dê um CTRL+C para copiar a coluna inteira. Você pode usar o atalho de cola CTRL+V para colar formatação na planilha do resumo.
Faça o mesmo com a coluna price_in_euro. Recomendamos começar de baixo para cima por causa das células em branco dessa coluna. Cole essa coluna sem formatação ao lado da brand, na coluna H.
Faça uma caixa com as células entre A1 e E2 e mescle tudo. Esse será o título dessa planilha.
Faça com que pareça um título profissional. Você pode mudar a cor do texto, o tamanho das palavras e assim por diante. O nosso ficou assim.
Altere os títulos e os tamanhos das células das colunas retiradas do arquivo CSV. Colocamos os preços em formato Moeda e escolhemos o Euro. Alinhamos as marcas à direita para facilitar a associação entre marca e preço. Utilizamos a cor verde para o título das colunas. Ficou assim.
Vamos supor que mais uma planilha com dados chega para você. Você teria que repetir todo o processo outra vez para ter o mesmo resultado; porém, provavelmente você teria pequenas alterações por esquecer de algum detalhe, causando problemas na entrega. As macros servem justamente para garantir consistência em procedimentos repetitivos e recorrentes como este. Crie uma nova planilha, vá em Automate e em Gravar Ações.
Clique em Gravar Ações no menu lateral e repita o procedimento que fizemos anteriormente. Recomendamos criar uma planilha em branco como primeira ação da macro. Quando terminar, clique em Parar no menu lateral.
Clique em Executar no menu Editor de Códigos e veja o que acontece!
Compare as duas imagens acima. A única diferença está na formatação dos números na coluna price_in_euro. Ou seja, você acabou de criar uma macro otimizada para fazer um trabalho simples para você. Mas, como você deve imaginar, as aplicações desse recurso são ilimitadas.
Vamos entender o que as macros são, de fato, olhando por trás das cortinas!
Entendendo as macros no Excel
No menu lateral intitulado Editor de Códigos, clique em Editar no script que acabamos de criar.
Você pode alterar o tamanho da janela para facilitar a visualização.
Esse código é a macro que acabamos de criar, com a vantagem de não termos escrito código algum. Vamos entender um pouco mais sobre o VBA, que é o nome dessa linguagem de programação própria dos produtos da Microsoft.
A linha 1 determina o começo do código. Por padrão, todos os códigos derivados da linguagem C precisam de uma função main(), logo fica claro que o VBA deriva da linguagem C, assim como diversas outras linguagens modernas. As linhas 2, 4 e 7, por começarem com “//” são comentários, ou seja, não são lidas pelo computador.
A linha 3 realiza uma ação interessante: vemos que existe um workbook, nome dado à região de trabalho, no caso o Excel, e que essa linha aplica um método chamado addWorksheet() responsável por adicionar uma nova planilha ao documento.
Na linha 5 temos o método getRange usado na célula A1, seguido pelo método setValue com o título que demos à nossa planilha. Ou seja, o primeiro método serve para selecionar uma célula na planilha, enquanto o segundo altera seu conteúdo.
A linha 8 usa novamente o método getRange, mas agora passando o valor “G:G”, dizendo que toda a coluna G, da planilha 7, foi selecionada. O método copyFrom aponta para a coluna B da planilha gcar_data. Portanto, essa linha copia todo o conteúdo da coluna B da planilha gcar_data e cola na coluna G da planilha 7.
Na linha 12 vemos o método setNumberFormatLocal que altera o tipo de formatação da célula. É o passo onde selecionamos a linha G na planilha 7 e alteramos seu tipo para Moeda e Euro.
Em resumo, o VBA é uma linguagem de programação específica dos produtos Microsoft cuja principal função é usar os recursos dos softwares da empresa. As macros são uma transcrição das ações do usuário para essa linguagem de programação, permitindo a criação de scripts para reuso.
Agora vamos aprender a criar planilhas interativas no Excel usando as macros.
Desenvolvendo planilhas interativas no Excel
Nossa ideia é facilitar o trabalho de uma pessoa que sempre recebe uma planilha contendo informação de veículos e, com base nesses dados, precisa criar relatório para seu supervisor. Para isso precisamos unir nossos conhecimentos em macros com alguns conceitos de aulas passadas, como as tabelas dinâmicas.
Vamos supor que o supervisor queira dois relatórios em planilhas diferentes: o primeiro deve informar todas as marcas e o menor preço do veículo por marca, enquanto o segundo deve trazer a soma total dos preços por marca.
A estrutura do algoritmo do primeiro relatório é:
1. Inicie a gravação da macro
2. Crie uma nova planilha
3. Selecione todos os dados da planilha com as informações dos veículos
4. Crie uma tabela dinâmica com esses dados, deixando a opção Planilha existente selecionada, marcando uma célula da nova planilha
5. Use as colunas brand e price_in_euro como colunas para compor a tabela
6. Clique em uma célula vazia na planilha
7. Termine a macro
Vamos renomear o script para modelo_preco facilitando sua identificação no futuro. Feche a planilha que usou para gravar a macro antes de executar o script para teste.
Faça o mesmo procedimento para o segundo relatório. Lembre-se de tomar cuidado com os detalhes, pois eles fazem toda a diferença para a máquina.
Os nomes ficaram um pouco confusos. Vamos alterar o script modelo_preco para modelo_menor_preco. Assim fica mais legível para quem for usar as macros no futuro.
Deixe somente a planilha com os dados aberta. Vá até a coluna P e selecione a célula P1. Em seguida, clique no nome do script modelo_soma_preço e depois em Adicionar na pasta de trabalho.
Faça o mesmo com o script modelo_menor_preco, colocando-o na célula P3. Você deve ter uma planilha assim.
Em resumo, acabamos de adicionar dois botões para disparar as macros que fizemos. Basta clicar e o algoritmo fará o processo gravado anteriormente.
Queremos deixar um desafio para você. Perceba que as macros possuem uma limitação forte: você deve ativá-las enquanto está na planilha com as informações dos veículos. Isso acontece porque gravamos desse jeito e a macro sempre vai executar o caminho gravado. Pensando nisso, como poderíamos elaborar uma planilha com diversos botões de macros para controlar a ativação dos scripts rapidamente? Tente fazer essa planilha!
Isso encerra nosso tópico. Nos vemos nas aulas de Power BI.
