Excel & VBA para Excel Carlos César Tanaka em 10 Jun 2009
Procura Vertical (PROCV) com imagens
A função de procura vertical (PROCV ou VLOOKUP em inglês) está entre as mais usadas no Excel, 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)
em 26 de Junho de 2009 @ 00:29 1.Raphael disse:
Achei excelente a dica. Já testei e consegui fazer com que a idéia funcionasse.
Estou porém com um problema… Eu quero que a celula alterada (no caso do exemplo acima a B1) esteja em uma planilha diferente da figura.
Neste caso tanto o comando
If Target.Row = 1 And Target.Column = 2 Then quanto
LoadPicture(Range(”B3″).Value)
devem buscar dados em outra planilha.
Poderia me auxiliar?
em 5 de Agosto de 2009 @ 14:18 2.O Planilhão disse:
Muito bom, parabéns.
em 28 de Agosto de 2009 @ 13:18 3.Ivam Medeiros disse:
Excelente! Passei alguns meses procurando por esta solução. Obrigado.
em 2 de Setembro de 2009 @ 21:36 4.Fernando disse:
Caramba… não consegui fazer essa planilha.
Eu copiei e colei o comando no visual basic como vc sugeriu:
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
Dae eu habilito a planilha para macro no excel, mas as fotos não aparecem e quando eu altero o numero do codigo do cliente, o excel vai para a tela do visual basic e aparece uma mensagem “Erro de compilação” “Erro de sintaxe” e a frase “Private Sub Worksheet_Change(ByVal Target As Range)
‘Lembrando que target é a referência para a célula modificada” aparece grifada.
O que está errado? Como fazer a planilha funcionar?
Muito obrigado pela atenção.
Abs
em 29 de Setembro de 2009 @ 16:05 5.Carlos Cardoso disse:
Ola,
Parabens pelo blog, achei muito util sua explicação para esta materia, já que há tempos tento inserir uma imagem no excel.
Porem, sou novato no assunto, e estou com problema na hora de criar o codigo em VBA.
Eu copiei e colei o codigo na area branca, mas ele dá erro de compilação.
Onde será que estou errando??
A unica diferença para a minha planilha para a sua se refere as celulas, onde o seu B1=V2 e o seu B3=W1 no meu.
Preciso alterar mais alguma coisa??
Se vc puder me ajudar agradeço.
Abraço
em 26 de Outubro de 2009 @ 19:16 6.Carlos César Tanaka disse:
Prezado Fernando,
Percebi que se você copiar e colar o código, os caracteres apóstrofe (’) e aspas (”) são alterados, devido ao tipo de fonte usado no blog.
Para corrigir isso, depois que você colar o código, redigite todos os apóstrofes e aspas.
Outra opção é fazer o download dos arquivos originais, indicados na dica.
Abç.
em 26 de Outubro de 2009 @ 19:21 7.Carlos César Tanaka disse:
Olá Carlos Cardoso,
Veja o comentário anterior que fiz para o Fernando.
Altere as fórmulas que usam a função PROCV das células B2 e W1 para usarem a célula V2 em vez de B1.
Se isso não resolver, favor enviar mais detalhes sobre a mensagem de erro.
Abç.
em 26 de Outubro de 2009 @ 19:33 8.Carlos César Tanaka disse:
Olá Raphael,
Sua resposta foi enviada por email, mas vou deixar registrado no blog também.
O código VBA deverá estar na planilha que contém a célula a ser alterada (e não na Principal).
As fórmulas que contém as Procv, também devem ser ajustadas com o endereço da nova planilha.
Por último o código VBA deve sofrer uma adaptação, em vez de usar simplesmente:
imgfoto.Picture = LoadPicture(Range(”B3″).Value)
Se a sua nova planilha de chamar Teste, use
imgfoto.Picture = LoadPicture(Sheets(”Teste”).Range(”B3″).Value)
Abç.
em 19 de Novembro de 2009 @ 08:47 9.Carlos Cardoso disse:
Olá,
Realmente não levo jeito para VBA, já tentei tudo o que foi possivel para fazer funcionar meu arquivo mas não tem jeito.
Há a possibilidade de eu te enviar o arquivo para vc fazer o codigo para mim?
Desde já agradeço a paciencia e a boa vontade.
abraço
Carlos
em 10 de Dezembro de 2009 @ 10:01 10.Átila César disse:
Carlos,
Meus parabéns pela dica!
Mas vai uma pergunta boba: só funciona no Excel 2007?
Como uso o Excel 2003, dá pra fazer o mesmo usando outros recursos?
Desde já grato pela atenção.
em 18 de Dezembro de 2009 @ 18:20 11.Carlos César Tanaka disse:
Prezado Átila,
Esse exemplo também funciona no Excel 2003.
Sobre a outra questão, em quais recursos você está pensando?
em 20 de Janeiro de 2010 @ 19:00 12.Fernando disse:
Caramba Carlos… Excelente dica!!! Funcionou perfeitamente para o que eu queria. Muito obrigado.
Só tenho um pequeno problema. Quando eu não tenho a foto do produto e digito o código, aparece uma mensagem dizendo:
“Erro em tempo de execução ‘53′:
O arquivo não foi localizado”
E daí o excel me dá as seguintes opções “Fim”, “Depurar”, “Ajuda”.
Como eu faço para que o Excel simplesmente deixe uma foto dizendo “Foto indisponivel”?
Outra duvida é: Eu consigo usar fotos em jpeg ou invés de bmp?
se puder me responder para o meu e-mail para que eu não tenha que entrar no site todos os dias, lhe agradeço, no entanto se não puder não tem problema!
Muito obrigado pela atenção e mais uma vez PARABÉNS!
Abs
em 1 de Fevereiro de 2010 @ 12:30 13.Carlos César Tanaka disse:
Prezado Fernando,
O controle de imagem presente no Excel não aceita imagens do tipo JPEG.
Em relação ao erro se não existir o arquivo, você pode modificar o procedimento em VBA para avisar o usuário caso a imagem não exista, vide exemplo abaixo.
Note que se você copiar e colar o código abaixo, preste atenção, pois às vezes o caracter de comentário é trocado pelo acento grave e as aspas também podem ser trocadas.
Boa sorte!
Private Sub Worksheet_Change(ByVal Target As Range)
On Error GoTo Mensagem
‘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
Exit Sub
Mensagem:
MsgBox (”Imagem de ” & Range(”B2″).Value & ” não existente.”)
End Sub
em 1 de Fevereiro de 2010 @ 17:36 14.Fernando disse:
Carlos,
Obrigado por responder a minha duvida. Entendi o que você fez e funcionou muito bem. No entanto isso não atende muito bem o que eu preciso. Pois essa planilha se transforará em uma apresentação
Eu preciso que caso o sistema não encontre a foto no banco de dados, o sistema puxe uma foto no banco de dados chamada “Foto Indisponível”. Essa foto conterá nada mais do que uma frase escrito “Foto Indisponível”.
Como eu faço isso e mais uma vez obrigado por responder esse monte de perguntas!
Abs
em 2 de Fevereiro de 2010 @ 15:52 15.Carlos César Tanaka disse:
Fernando,
Crie um arquivo de imagem com o conteúdo que você deseja e deixe-o no mesmo local dos outros arquivos de imagem. Digamos que esse arquivo se chama indisponivel.bmp.
Depois troque a linha
MsgBox (”Imagem de ” & Range(”B2″).Value & ” não existente.”)
por
Imgfoto.Picture = LoadPicture(”indisponivel.bmp”)
Isso deve resolver o seu problema.
em 2 de Fevereiro de 2010 @ 19:14 16.Fernando disse:
Putz Carlos… o pior é que resolveu, mas parcialmente apenas. A sua dica está certa, no entanto eu coloquei nessa planilha pelo menos 6 fotos, que são controladas por códigos, da seguinte forma:
******* Inicio dos Comandos **********
Private Sub Worksheet_Change(ByVal Target As Range)
If Target.Row = 4 And Target.Column = 12 Then
imgfoto.Picture = LoadPicture(Range(”M4″).Value)
End If
If Target.Row = 5 And Target.Column = 12 Then
imgfoto1.Picture = LoadPicture(Range(”M5″).Value)
End If
If Target.Row = 6 And Target.Column = 12 Then
imgfoto2.Picture = LoadPicture(Range(”M6″).Value)
End If
If Target.Row = 7 And Target.Column = 12 Then
imgfoto3.Picture = LoadPicture(Range(”M7″).Value)
End If
If Target.Row = 8 And Target.Column = 12 Then
imgfoto4.Picture = LoadPicture(Range(”M8″).Value)
End If
If Target.Row = 9 And Target.Column = 12 Then
imgfoto5.Picture = LoadPicture(Range(”M9″).Value)
End If
End Sub
******* Final dos Comandos **********
Quando eu dou a instrução que você me deu, colocando no inicio:
Private Sub Worksheet_Change(ByVal Target As Range)
On Error GoTo Mensagem
E no final de todos os comandos eu coloco:
Mensagem:
Imgfoto.Picture = LoadPicture(”indisponivel.bmp”)
End Sub
Ele obviamente só altera a imgfoto.Picture, porém esse comando não funciona com as demais imagens, Imgfoto1.Picture, Imgfoto2.Picture,3, 4, 5, etc.
Como eu faço para fazer essa função funcionar para todos os casos? Ou seja, como eu faço para que em todos os casos, se eu digitar um código que não tenha a foto na pasta o excel puxe automaticamente a foto “indisponivel”?
Abs
em 13 de Fevereiro de 2010 @ 18:30 17.Ruan disse:
cara consegui fazer funcionar só que este macro é executado so se eu for la na célula b1 e digitar , mas
o que altera essa célula, no meu caso é um controle de formulário e infelizmente assim o macro não execultou, valeu pela atenção
em 17 de Fevereiro de 2010 @ 19:48 18.Carlos César Tanaka disse:
Fernando,
Penso que um Select Case deve atender a sua necessidade. Algo assim:
Mensagem:
Select Case Target.Row
Case 4: Imgfoto.Picture = LoadPicture(”indisponivel.bmp”)
Case 5: Imgfoto1.Picture = LoadPicture(”indisponivel.bmp”)
Case 6: Imgfoto2.Picture = LoadPicture(”indisponivel.bmp”)
Case 7: Imgfoto3.Picture = LoadPicture(”indisponivel.bmp”)
Case 8: Imgfoto4.Picture = LoadPicture(”indisponivel.bmp”)
Case 9: Imgfoto5.Picture = LoadPicture(”indisponivel.bmp”)
End Select
Boa sorte.
em 17 de Fevereiro de 2010 @ 19:57 19.Carlos César Tanaka disse:
Ruan,
Você pode tentar duas abordagens.
1. Dependendo do controle que você usa, há propriedades que vinculam o conteúdo do controle a uma célula, por exemplo ‘ControlSource’ e ‘LinkedCell’. Indique a célula com o código nesses campos que o sistema funcionará.
2. Em VBA, você pode usar as funções procv e proch (vlookup e hlookup) pelo objeto: worksheetfunction, exemplo, application.WorksheetFunction.VLookup
Boa sorte.
em 2 de Março de 2010 @ 13:31 20.Smiliguiido disse:
Genteeh, eu gostariia de saber, se as células é em toda a parte do coorp? obgd se respondereem. -**-
em 2 de Março de 2010 @ 17:13 21.Carlos César Tanaka disse:
Smiliguiido,
Não entendi a sua pergunta. Pode reformulá-la?
em 9 de Março de 2010 @ 11:35 22.Lincoln Diones disse:
Carlos bom dia! Parabéns pela dica, excelente. Porém gostaria de uma ajudinha sua. Gostaria que a fórmula buscasse a figura dentro da própria planilha.
Grato.
em 9 de Março de 2010 @ 12:17 23.Lincoln Diones disse:
Carlos, outra coisa.
Como faço para colocar a figura no tamanho que eu quiser?
Grato.
em 9 de Março de 2010 @ 15:48 24.Carlos César Tanaka disse:
Olá Lincoln,
Sobre a primeira questão, se a sua tabela estiver na mesma planilha onde está a figura, então mude as fórmulas das células B2 e B3. Por exemplo, se a tabela estiver no intervalo H1:J5 as fórmulas ficarão:
em B2 =PROCV(B1; $H$1:$J$5;2;FALSO)
em B3 =PROCV(B1; $H$1:$J$5;3;FALSO)
Sobre o redimensionamento da figura. Primeiro ative a guia Desenvolvedor (botão do Office, Opções do Excel, ativar “Mostrar guia Desenvolvedor na Faixa de Opções”)
Depois ative o “Modo de Design” pela guia Desenvolvedor. Clique na figura e dimensione conforme a sua necessidade.
Por último desative o modo de Design.
Boa sorte.
em 9 de Março de 2010 @ 16:58 25.Lincoln Diones disse:
Carlos boa tarde!
Acredito que me expressei mal, vou tentar ser um pouco mais claro para que você consiga entender o que necessito. Suponhamos que tenho uma arquivo de Excel na qual temos: Plan1, Plan2 e Plan3.Na Plan1 tenho uma tabela que necessita associar um código a uma figura, porém todas as figuras (aproximadamente 20) estão dispostas em Plan3. Como faço para que o código VBA busque estas figuras na Plan3 e às associe de acordo com o código que eu digitar em uma célula em Plan1?
Desde já agradeço.
Obs: com relação em alterar o tamanho da imagem, sem problemas conseguir fazer.
em 9 de Março de 2010 @ 21:21 26.Carlos César Tanaka disse:
Lincoln,
Isso que você quer vai depender de como estão as suas imagens.
Há como me enviar esse arquivo em anexo para análise? Mande para o email atendimento@mprinformatica.com.br ou verifique no nosso site www.mprinformatica.com.br)