Aula de VBA – O que é Melhor: Cópia de Fórmulas com Loop ou sem Loop?

Meu nome é César Tanaka, utilizo linguagens de programação há mais de 30 anos e coordeno o curso de VBA na Empresa MPR Informática desde 2005. Me formei em Ciência da Computação na USP, mas acredito que programação pode beneficiar todas as pessoas. Na MPR, nós ajudamos as pessoas a se tornarem profissionais mais completos para o mercado de trabalho, sempre respeitando, acima de tudo, o ritmo de cada pessoa.

Nessa Live, gravada durante o período de restrição devido ao Coronavírus, mostro um exemplo de como usar o VBA para criar uma coluna de fórmulas em uma tabela de dados no Excel.

Muitas vezes precisamos elaborar relatórios de tabelas dinâmicas a partir de dados importada. Logo após a importação dos dados é comum a necessidade de se criar colunas adicionais com PROCV (VLOOKUP) ou mesmo o cálculo de indicadores (KPIs). Podemos automatizar a criação dessas colunas adicionais com VBA.

Mostraremos nesse vídeo duas maneiras, em VBA, de se criar essas colunas calculadas, sem Loop, do modo como fazemos manualmente no Excel e com Loop. Assista a esse vídeo até o fim e veja qual é a melhor maneira. Esse vídeo foi extraído de uma LIVE do curso de VBA da MPR Informática.

Curso de VBA: https://www.mprinformatica.com.br/vbaexcel2007.html

Nossos contatos:
✓ WhatsApp: → (11) 97564-8538
✓ E-mail: → contato@mprinformatica.com.br
✓ Site: → https://www.mprinformatica.com.br

Nossas redes sociais:
✓ Instagram → https://www.instagram.com/mprinformatica
✓ Facebook → https://www.facebook.com/mprinformatica
✓ YouTube → https://www.youtube.com/mprinformatica

VBA: Como descobrir a última linha de dados

10 em cada 10 profissionais de Excel que usam VBA precisam, periodicamente, descobrir a última linha de dados de suas planilhas.

No processo de automação de tratamento de dados (ETL – Extract, Transform and Load) ou inserção de dados em uma planilha de Excel, usando a linguagem de programação VBA, é recorrente a necessidade de descobrir a última linha de dados preenchida, ou para colocar novos dados após essa linha, ou para tratar os dados que vão até esta linha.
Felizmente o código VBA para fazer essa operação é bastante simples se realizado do modo que mostraremos nesse vídeo. Acompanhe conosco e deixe o seu comentário.

Se você se interessou pelo nosso conteúdo, mande uma mensagem por Whatsapp (11) 97564-8538 com a palavra Excel, que manteremos você informado sobre novidades.

Quer conhecer a nossa escola presencialmente na região da Av. Paulista, e conhecer como é o nosso método de ensino, que você faz as aulas no seu ritmo e nos horários que você escolher com atendimento personalizado? Agende uma aula teste gratuita, preenchendo o formulário: https://www.mprinformatica.com.br/nov…

Curso de VBA: https://www.mprinformatica.com.br/vbaexcel2007.html

Nossos contatos:
✓ WhatsApp: → (11) 97564-8538
✓ E-mail: → contato@mprinformatica.com.br
✓ Site: → https://www.mprinformatica.com.br

Nossas redes sociais:
✓ Instagram → https://www.instagram.com/mprinformatica
✓ Facebook → https://www.facebook.com/mprinformatica
✓ YouTube → https://www.youtube.com/mprinformatica

Uso da Estrutura Select Case

Além da estrutura condicional If … EndIf, o VBA possui uma estrutura muito versátil e simples de ser usada em situações semelhantes a um “menu”, ou seja, um conjunto de opções, que quando uma delas for escolhida realiza-se uma sequência de comandos.
Mostrarei essa estrutura através de um exemplo que permite ao usuário digitar um valor na célula A2 da planilha Plan1 e de acordo com esse valor, uma macro (procedimento) será executada.

Text frame options

O código VBA deve ser digitado no container da Plan1. Depois de entrar no ambiente VBA (Alt+F11), dê um clique duplo na Plan1, que fica no Project Explorer (normalmente no lado esquerdo da tela).

Text frame options

O código para esse exemplo é apresentado a seguir:

Text frame options

Procura Vertical (PROCV) com imagens

A função de procura vertical (PROCV ou VLOOKUP em inglês) está entre as mais solicitadas pelos nossos alunos do Curso de Excel (http://www.mprinformatica.com.br/excelintermediario.html), isso se deve ao fato de frequentemente organizarmos informações em tabelas e precisarmos localizar e extrair algum dado específico.
A função PROCV extrai qualquer tipo de dado que esteja em uma célula, mas e se precisarmos extrair uma imagem? Ou seja, se quisermos procurar por um item e depois visualizar a imagem correspondente? Como exemplo, imagine que possuímos uma tabela que possua as informações de nossos clientes e como a tabela é extensa, ficaria muito prático se ao selecionarmos um cliente pudéssemos ver a foto desse cliente.
As células do Excel não armazenam nem exibem imagens, porém como as imagens são arquivos de computador, podemos armazenar o caminho das imagens nas células, ou seja a sua localização em disco, conforme mostrada na figura abaixo.

Colocamos esses dados numa planilha chamada Tabela. Nessa tabela simplificada, temos uma coluna Número, que pode ser o número de cadastro do cliente, o campo Nome – nome completo do cliente, e a coluna Caminho, que é a localização do arquivo de imagem com a foto do cliente. Neste exemplo, haverá uma pasta Imagem no drive C:, com todas as fotos dos clientes, o que torna bastante simples a organização e como os arquivos de imagem não foram incorporados na planilha do Excel, ela ficará muito leve.

Além da planilha com os dados onde serão feitas as pesquisas, criamos uma planilha chamada Principal, onde será efetuada a pesquisa, ou seja, onde usaremos a função PROCV. Ela terá a seguinte aparência.

onde:
> As células A1:A4 são apenas descrições
> A célula B1 é o local onde digitaremos o número do cliente, para que sejam exibidos o nome e a imagem do mesmo.
> A célula B2 possui uma simples busca pelo nome do clinte com a seguinte fórmula: =PROCV(Principal!B1;Tabela!$A$4:$C$9;2;FALSO)
> A célula B3 possui a busca pelo caminho da imagem. A fórmula é: =PROCV(Principal!B1;Tabela!$A$4:$C$9;3;FALSO)

Relembrando, as células do Excel não exibem imagens, então precisaremos de um recurso que faça isso. Escolhemos usar um controle ActiveX, para inseri-lo faça o seguinte:
– Ative a guia do Desenvolvedor (Botão do Office; Opções do Excel; ativar a caixa Mostrar guia Desenvolvedor na Faixa de Opções; OK.
– Na guia desenvolvedor, clique no botão Inserir (ícone de maleta com ferramentas) e clique sobre o controle Imagem (ActiveX);
– Agora desenhe um retângulo, arrastando o cursor do mouse de um vértice até o vértice oposto. O tamanho da imagem pode ser mudado depois através dos círculos em torno desse retângulo.

O local onde será exibida a imagem está pronto, veja o nosso exemplo na figura anterior. Esse controle precisa ser configurado, para isso clique no botão propriedades que está na guia Desenvolvedor. Altere as seguintes propriedades:
(Name) para imgfoto
PictureSizeMode para 1-fmPictureSizaModeStretch

Feche a janela de propriedades e desative o botão Modo de Design na guia Desenvolvedor.

A etapa final é elaborar um pequeno código em VBA para que a imagem seja atualizada sempre que um novo código for digitado. Para isso vá até o editor do VBA através da combinação Alt+F11 e dê um clique duplo na planilha Principal, conforme indicado na figura a seguir:

Do lado direito haverá uma grande área em branco para inserção de código, copie e cole o seguinte trecho:

Private Sub Worksheet_Change(ByVal Target As Range)
‘Lembrando que target é a referência para a célula modificada

‘verifica se a célula que contém o número do cliente foi modificada
‘Nesse caso é a célula B1
If Target.Row = 1 And Target.Column = 2 Then
‘Carrega imagem de acordo com o caminho indicado na procura vertical
‘A função LoadPicture carrega um caminho de imagem na propriedade Picture
‘O seu uso é necessário
imgfoto.Picture = LoadPicture(Range(“B3”).Value)
End If
End Sub

Feche o editor de VBA e salve a planilha habilitando-a para usar macros: Salvar Como e depois escolher a segunda opção “Pasta de Trabalho habilitada para Macro do Excel”.

Antes de experimentar o sistema, crie uma pasta com o nome Imagens, no drive C: (se for outro local, mude também o caminho na tabela) e copie os arquivos de imagem listados na tabela, como exemplo, eu disponibilizei para download um arquivo compactado com as imagens usadas no exemplo (imagens.zip)

Agora teste o sistema, digitando valores na célula B1 da planilha principal.
(Este exemplo pode ser baixado neste link procura vertical com imagens.xlsm)

Macro Auto Executável

Para criar uma macro auto executável abra o Excel e aperte as teclas de atalho Alt+F11.

Acione o botão Inserir Módulo, indicado na imagem abaixo, para inserir o módulo onde será criado o procedimento.

Com o módulo Inserido construa o procedimento desejado, como no exemplo abaixo.

* Para que a macro se torne auto executável deve-se dar o nome a ela de auto_open do contrario não funcionará o recurso.

Repare que o procedimento mostrado acima insere o texto MPR Informática na célula B4 e formata para a fonte Times new Roman.

Execute o procedimento e em caso de erro faça as correções necessárias.

Depois de executar o procedimento, salve o arquivo com o nome desejado.

Selecione a planilha inteira em que a macro foi executada e limpe-a (Limpar/Limpar tudo).

Salve o arquivo novamente e feche-o.

Agora basta abrir o arquivo novamente para que a Macro seja executada automaticamente.

André Lanzillotta
Clique aqui – Curso de VBA – MPR Informática

Como ajustar a escala do eixo de um gráfico em VBA

Gráficos são excelentes ferramentas para análise de dados, eles fornecem uma excepcional visão qualitativa de um fenômeno a ser estudado. Algumas vezes, porém, temos que ajustar a escala do gráfico para que possamos observar o trecho que nos interessa, nada que não se possa fazer configurando os valores mínimo e máximo dos eixos, mas e se houvesse uma maneira mais rápida de fazer isso?

Essa maneira mais rápida existe e pode ser feita com auxílio do VBA. Para exemplificar vamos apresentar um exemplo semelhante ao que apresentamos em nosso curso de VBA para Excel (http://www.mprinformatica.com.br/vbaexcel2007.html). Considere um gráfico, chamado “Gráfico 1” que está dentro da planilha “Plan1”. Ele representa uma função de segundo grau, a famosa parábola (y=ax2 + bx + c) , cujos coeficientes a, b e c estão nas células A5, B5 e C5 respectivamente. Os pontos da função estão no intervalo B7:V8. Veja a figura abaixo:

Observe como está a fórmula na célula B8, que depois foi copiada para as outras células na linha 8. Note que também já foram inseridos três botões. Foram inseridos pela guia Desenvolvedor (se ela não estiver aparecendo Clique no botão do Office, Opções do Excel, Mais Usados e ative o item Mostrar guia Desenvolvedor na faixa de Opções), Inserir / Botão de Comando (ActiveX).

Botão da esquerda: (Name) = cmdMenos ; Caption = < Botão do meio: (Name) = cmdAutomático ; Caption = Automático Botão da esquerda: (Name) = cmdMais ; Caption = >

Enquanto estiver criando os códigos, mantenha o Modo de Design ativado na guia Desenvolvedor.

Mostrarei o código para o botão cmdMenos, realiza um Zoom na escala do eixo X. Para os demais botões o código é semelhante e pode ser obtido fazendo download do arquivo (clique aqui para download). Estando no Modo de Design, dê um clique duplo no botão da esquerda para editar o código VBA.

‘Reduz a distância entre o máximo e o mínimo pela metade
Private Sub cmdMenos_Click()
Dim Grafico As ChartObject
Dim EixoX As Axis
Dim Delta As Double

‘ Variável que aponta para o Gráfico 1. Selecione o gráfico e verifique o nome do seu objeto gráfico na barra de nomes do Excel.
Set Grafico = Plan1.ChartObjects(“Gráfico 1”)
‘ Objeto que aponta para o Eixo horizontal do gráfico
Set EixoX = Grafico.Chart.Axes(xlCategory)
With EixoX
‘A escala do eixo horizontal irá dobrar de tamanho, metade em direção
‘ao mínimo, metade em direção ao máximo
Delta = (.MaximumScale – .MinimumScale) / 2
‘Altera o mínimo
.MinimumScale = .MinimumScale – Delta
‘Altera o máximo
.MaximumScale = .MaximumScale + Delta
End With
‘Libera apontadores para os objetos
Set Grafico = Nothing
Set EixoX = Nothing
End Sub

Ressalto que quando um gráfico está dentro de uma planilha ele é do tipo ChartObject, porém se ele está separado da planilha é do tipo Chart, ou seja a hierarquia de objetos muda.
Façam download do arquivo, experimentem o exemplo e analisem o código VBA.

Carlos César Tanaka
MPR Informática
Cursos de Excel e VBA