Arquivo
Dicas de autofiltro no vba
Pensei neste artigo:”Dicas de autofiltro no vba”, pois a maioria apenas conhece o uso, muito interessante, nas planilhas. Mas e nos códigos?
Neste primeiro momento não quero descrever o porque e quando usar, mas qual a importância ou o cuidado de desativar os autos filtros nos códigos VBA?
Problema ou Solução “autofiltro”
Dependendo do código, ainda mais se envolver cópia, o autofiltro vai influenciar muito. Em outros casos talvez não influencie, mas pensando no seu usuário final talvez seja melhor deixar a planilha sem filtros para evitar equívocos de interpretação de dados.
Benefícios autofiltro no vba
Além do uso clássico que por sinal ajuda muito, podemos também usufruir do autofiltro para “pesquisar” e selecionar itens em uma lista, ao invés de verificar item por item.
Desabilitar o auto-filtro faça:
Sub FILTRAR_E_DESABALITAR() Rows(4).AutoFilter ActiveSheet.AutoFilterMode = False End Sub
Dicas de autofiltro no vba -> Algumas formas de filtrar:
Por uma palavra chave:
Neste exemplo a tabela de cidades será filtrada pela palavra “PALMAS” na coluna 2
'FILTRAR CAMPO 2 PELO CRITERIO "PALMAS"
Range("a4").AutoFilter Field:=2, Criteria1:="Palmas"
Exemplo para limpar todos os filtros utilizados em uma tabela:
'LIMPAR TODOS OS FILTROS ActiveSheet.ShowAllData
Código vba demonstrando múltiplos autofiltros:
'MULTILPLOS FILTROS
Range("a4").AutoFilter Field:=2, _
Criteria1:=Array("São Paulo", "Manaus"), _
Operator:=xlFilterValues
Segue Vídeo:
Variáveis de objeto no vba
Variáveis de objeto é diferente de uma variável, independente do seu tipo.
As variáveis “normais” são como pequenas ferramentas e as usamos para armazenar dados temporariamente.
Creio que a maioria já teve contato com as variáveis que por sua vez contém apenas um valor momentaneamente.
Exemplo:
Salário = 0
A variável Salário contém apenas uma informação, no caso o número zero.
Entretanto dentro do vba podemos utilizar as variáveis de objeto auxiliando-nos como se fosse um capacho ou uma super caixa de ferramentas.
Isso porque ao invés de armazenar apenas uma informação podemos armazenar várias “características” do objeto que desejamos.
Mas que objeto é esse?
Para facilitar e visualizar esta definição podemos chamar de objeto todos os recursos inclusos dentro do Excel: a planilha, tabelas dinâmicas, células, formas, guias e etc… Ou seja, tudo de que utilizamos.
Senta que lá vem a história…
A maioria de nós que gostamos do VBA não gastamos muito tempo declarando as variáveis (“antecipadamente”), isso ocorre porque é necessário você saber de todas as variáveis antes de programar.
Entretanto geralmente gostamos de criar variáveis na adrenalina da lógica e acima da velocidade permitida do cérebro.
A maioria dos livros (e outros) imploram para declarar variáveis com os DIM. E no fundo há benefícios para isso:
- Baixo consumo e memória com variáveis específicas para seu uso
- Possibilidade se utilizar o autoCompletar aumento a velocidade de desenvolvimento.
Mas como funciona as variáveis de objeto?
Vamos imaginar que estamos programando e precisamos mudar de guia constantemente.
Normalmente você escreveria worksheets(“Plan2”) várias vezes, mas podemos melhorar isso escrevendo Guia no lugar de worksheets(“Plan2”) :
Ou seja, ao invés de escrever:
worksheets(“Plan2”).select
escreveremos:
guia.select
outro exemplo: no lugar de escrevermos:
worksheets(“Plan2”).Cells(2, “a”).Value
passamos a escrever
Guia.Cells(2, “a”).Value
[box type=”warning”] Para funcionar é necessário: [/box]
- Usar a Palavra SET
- Nome da variável
- Símbolo de igualdade, “=”
- Recurso do excel que deseja
Set Guia = Worksheets (“Plan2”)
Exemplos de variáveis de objeto
Aqui no site há diversos artigos com as mais variadas utilidades cuja funcionalidade está principalmente nas variáveis de objeto e o uso do for each
- Excluir Imagens do Excel
- Valores de slicer com VBA
- Ultimo Registro De Um Recordset VBA (consulta sql)
- VBA Copiar colar arquivos diferentes
- Inserir Gráfico Em Formulário VBA
- Vba Internet : listbox, radiobox, button…
- Excluindo modulo em outra planilha
- E etc
Em resumo podemos usar qualquer recurso do Excel em apenas uma palavra e assim ganhar tempo e melhorar o entendimento do código para futuras manutenções e melhorias.
Vídeo Demonstrativo
VBA Copiar colar arquivos diferentes
Vocês votaram na enquete aqui do site excel e vba e em segundo lugar ficou o assunto VBA Copiar colar arquivos diferentes.
A maneira que demonstro é uma mais avançada e também com menos código.
Porém se você não gostar ou achar complicado aqui mesmo no site há outras maneiras (passo1 e passo2)que envolvem copiar célula por célula com um loop.
Você que escolhe a melhor maneira para trabalhar excel com vba.
1 – Vamos ao codigo VBA Copiar Colar arquivos diferentes:
________________________________________________________
Sub COPIAR_COLAR_ARQUIVO_DIFERENTES()
'Declarações
Dim appExcel As Excel.Application
Dim wb As Excel.Workbook
'A Aplicação Excel é criada aqui:
Set appExcel = CreateObject("Excel.Application")
'A linha abaixo é importante: eu estou dizendo que todo o processo seja invisível.
'Talvez para testes seja importante deixar como True e assim acompanhar o processo
appExcel.Visible = False
'Note que, na linha abaixo, foi adicionado um Workbook (um plan)
'E esse workboook fica nomeado como "wb"
Set wb = appExcel.Workbooks.Add
'Agora para simplificar o processo, também vou gravar o nome da planilha origem
Set oldPlan = Workbooks("planilha_antiga.xlsm").Worksheets("Plan1")
'Então copio todos os dados da tabela
oldPlan.Range("A2").CurrentRegion.Copy
'Na linha abaixo eu colo os dados na nova planilha (lembre-se do "wb")
wb.Worksheets("Plan1").Range("A1").PasteSpecial xlPasteValues
'Nas linhas abaixo eu salvo a planilha no mesmo diretorio que a planilha antiga
diretorio = ThisWorkbook.Path & "\"
nome = "exemplo.xls"
appExcel.DisplayAlerts = False
wb.SaveAs Filename:=diretorio & nome, FileFormat:=xlNormal
'Agora eu elimino o processo aberto do excel
appExcel.Quit
'E finalizado as declarações
Set wb = Nothing
Set appExcel = Nothing
End Sub
2 – Vídeo Demonstrativo
Como deixar codigo vba mais rápido
Apesar de cada pessoa ter sua lógica de desenvolvimento, por muitas vezes existem maneiras mais rápidas de desenvolvimento e também de deixar o código vba mais rápido de executar
As vezes o tempo de ler linha por linha e em cada linha copiar e colar em outra planilha é muito mais lento que ler todas as linhas gravar em uma matriz e depois colar todas as informações.
Entretanto nem sempre é possível alterar o código e é muito mais rápido efetuar alguns “truques” para deixar o código vba mais rápido.
Truques VBA Mais Rápido
“Congelar” tela
Já até fiz um post a respeito (Paralisar Tela), mas sempre vale a pena comentar, usando ScreenUpdating você “congela a tela” e com isso você ganha muito desempenho no excel. Isso porque o excel não precisa mostrar as movimentações de células.
Existe um ganho secundário que é o conforto pra quem está executando seu código, de não precisar ver sua tela “tremendo”.
Junto ao ScreenUpdate é muito interessante usar colocar uma tela de apresentação para seu cliente e também usar o StatusBar, que permite usar a barra de rodapé do excel com informações de % de execução.
______________________________________________________________
“Desabilitar fórmulas”
Essa é outra ótima opção pois as fórmulas ficam gravadas nas células, mas não apresentam os resultados ou as atualizações dos resultados.
E se o excel não precisa processar as fórmulas você ganha com desempenho. Principalmente quando você trabalha com vba e fórmulas junto.
É claro que no final de seu código você deve voltar ao padrão.
Para paralisar as fórmulas você de colocar o seguinte comando:
Application.Calculation = xlCalculationManual
Para voltar ao normal você altera somente o final para:
Application.Calculation = xlCalculationAutomatic
______________________________________________________________
“Desabilitar eventos de excel”
Sinceramente não vejo um ganho de desempenho da mesma forma que nas duas primeiras dicas, mas é não deixa de ser interessante você desativar alguns eventos do excel.
Exemplo:
Este exemplo desativa eventos antes de um arquivo ser salvo para que o evento BeforeSave não ocorra.
Application.EnableEvents = False ‘(desativa)
ActiveWorkbook.Save
Application.EnableEvents = True ‘(ativa novamente)
______________________________________________________________
“Desabilitar Alertas”
Não considero essa dica como ganho de desempenho no VBA, mas só o fato de o Excel não párar sua execução para mostrar telas de alerta já um ganho muito grande.
Exemplo de uso: para mostrar que o arquivo não está sendo salvo ou que irá sobrescrever algum outro arquivo.
Como eu já escrevi a respeito SEQUE AQUI o post publicado.
VBA Texto – Principais funções para usar no vba
Tratamento para excesso de espaço – TRIM
Tratamento para letras maiúsculas e minúsculas – LCASE e UCASE
Tratamento para quebrar letras – LEFT, MID, RIGHT
Tratamento para saber quantidade de letras – LEN
Tratamento para localização de um caractere – INSTR
Bonus – Quebrar texto a partir de um caracter qualquer
Vídeo do Post
Já vimos funções data e se você trabalha e/ou utiliza vba com certeza já precisou manipular textos ou um dia irá precisar e, portanto ótimo post para excelevba.com.br.
Eu usei esta afirmativa, pois é um dos trabalhos mais comuns ter que localizar algum caractere em uma palavra, ou verificar se uma palavra está com excesso de espaço ou ver a quantidade de caracteres que existe ou ainda separar uma palavra de um número ou nome de um sobrenome.
Claro, pois o vba é perfeito para essas situações! Trabalhar arrumando texto, linha por linha de uma tabela “gigante” é muito monótono, chato e suscetível a erros.
Então coloco abaixo as funções mais utilizadas bem como exemplos de como usar essas funções no vba: Lcase, ucase, trim, left, mid, right, len, instr
Vamos ver cada função abaixo:
Tratamento para excesso de espaço – TRIM
Casos de uso:
Em formulários de cadastro, onde o cliente necessita digitar nome e muitas vezes colocam espaço no inicio das palavras.
Exemplo de uso:
Range("a2").value = " João"
variavelEspaço = Trim(range("A2").value)
ou seja
variavelEspaço agora possui a palavra “João” sem espaço
Tratamento para letras maiúsculas e minúsculas – LCASE e UCASE
Casos de uso:
O UCase você vai querer usar para transformar palavras ou frases todas em caracteres maiúsculos.
O LCase quando você precisar de todas as letras em minúsculo.
São muito usados para testes usando SE, para testar o que foi digitado e válido .Assim não é preciso usar o SE para frase toda maiúscula, toda minúscula e maiúscula só a primeira letra.
Exemplo de uso UCASE :
Range("b2").value = "empresa fictícia ltda"
varMaiuscula = UCase(range("b2").value
resultado -> varMaisucula agora possui a frase: “EMPRESA FICTÍCIA LTDA”
Exemplo de uso LCASE:
Range("b2").value = "LápiS"
varMinuscula = LCase(range("C2").value
resultado -> varMinuscula agora possui a palavra: “lápis”
Tratamento para quebrar letras – LEFT, MID, RIGHT
Casos de uso:
Quando é preciso separar alguns caracteres de uma palavra.
Talvez seja mais fácil ver o exemplo abaixo:
Exemplo de uso Left:
Range("a2").value = 112222-3333
varEsquerda = left(Range("a2").value,2)
ou seja
varEsquerda ->> 11
Exemplo de uso Right:
Range("a2").value = 112222-3333
varDireita = right(Range("a2").value,4)
ou seja
varEsquerda ->> 3333
Exemplo de uso Mid:
Range("a2").value = 112222-3333
varMeio = mid(Range("a2").value,3,4)
ou seja
varMeio ->> 2222
Tratamento para saber quantidade de letras – LEN
Casos de uso:
Muitas vezes precisamos saber a quantidade de caracteres existe em uma palavra.
Seja para validar um e-mail, senha.
Exemplo de uso:
Range("a2").value = mail@mail.com.br
varQtde = len(range("A2").value)
resultado:
varQtde ->> 16
Tratamento para localização de um caracter – INSTR
Casos de uso:
Caso você precise saber onde está o caracter “@” e assim separar o nome do resto do e-mail. Existe vários outros casos: separar uma palavra que contenha “–“ (hífen) e etc
Exemplo de uso:
Range("a2").value = mail@mail.com.br
varLocal = instr(1,range("A2").value,"@")
resultado:
varLocal ->> 5
Bônus – Quebrar um texto a partir de um caracter qualquer
O interessante é você usar as funções juntas, o INSTR com LEFT e etc.
Separar o domínio do e-mail ou separar o ano de uma data e etc.
varEmail = mail@mail.com.br arroba = instr(1, varEmail,"@") qtdeLetras = len(varEmail) nome = right(varEmail, qtdeLetras - arroba)
Segue vídeo do post
Atualizar Tabela Dinâmica Automático 2
Anteriormente havia feito o post “Atualizar Tabela Dinâmica (Sem Botão)” e conforme sugestão do Marlon que viu o post anterior eu estou colocando mais essa maneira para atualizar tabela dinâmica automático.
Neste post coloco um código que verifica em cada alteração de célula se pertence a tabela de dados (e que por sua vez também faz parte da tabela dinâmica).
Caso faça parte dos dados que nos interessa, o código aguarda 3 segundos (mas pode ser o tempo que você quiser) e chama a atualização da tabela dinâmica.
PARTE 1
Primeiro código deve estar na Plan que seus dados que fazem parte da tabela dinâmica estão, no meu caso coloquei tanto os dados como a tabela dinâmica na Plan1.
Outro detalhe que deve ser observado na imagem acima é que o código será executado em cada mudança nas células da Plan1 (Worksheet change).
______________________________________________________________________________________
PARTE 1-2
Observem que a tabela de dados vai até a coluna 4 e até linha 9, por isso em cada mudança nas células é verificado se a alteração faz parte destas colunas e linhas
If Target.Column <= 4 And Target.Row <= 9 Then ... End If
Caso faça parte será chamado outra “sub Atualiza_TD” que atualiza a tabela dinâmica após 3 segundos
Application.OnTime Now + TimeSerial(0, 0, 3), "Atualiza_TD"
Poderia ser horas ou minutos ou outros segundos qualquer (0,0,3), eu coloquei 3 segundos
_________________________________________________________________________________
PARTE 2
No módulo fica a Sub que chamamos lá atrás na Plan1, o que ele faz é somente atualizar a tabela dinâmica.
No caso acima “Plan1” é onde está minha Tabela Dinâmica, e “TD_GENERO” é o nome da tabela dinâmica.
Caso tenha alguma dúvida de como colocar nome da Tabela Dinâmica ou outra dúvidas veja o vídeo abaixo.
__________________________________________________________________
Segue vídeo demonstrativo
Campos Nomeados Com VBA
Esta semana estava pensando sobre o que escrever e por coincidência ou não houve uma dúvida no meu trabalho sobre campos nomeados e logo relacionei ao que escrever.
Confesso que eu uso com pouca frequência, mas colegas meus usam com mais constantemente e eu acho bem interessante!
Nós vimos no post Usando CurrentRegion como selecionar uma tabela apenas com um comando e com campos nomeados também podemos fazer isso.
A diferença é que usamos campos nomeados quando a tabela de dados são de valores fixos, ou seja não serão adicionados linhas ou colunas.
A vantagem? Agiliza e simplifica seu código.
Imagine que eu tenha uma tabela de carros e sempre uso essa tabela copiando e colando em outra pasta de trabalho (guia).
Ao invés de usar o range, ou saber a ultima linha e ultima coluna, eu posso usar uma referencia ao nome que foi dado a tabela de carros.
Também podemos pintar sempre uma tabela nomeada independente dos dados e ainda usar campos nomeados em fórmulas.
Exemplos:
________________________________________________________
Usar em fórmulas
Produto = dados1 * qtde '(imaginando que "dados1" é nome de G1 e "qtde" é o nome de K1)
________________________________________________________
Selecionar uma tabela
Sheets(1).[tb_carros].Select
________________________________________________________
Para inserir nome à uma tabela via código
ActiveWorkbook.Names.Add Name:="Qlqr_Nome", RefersTo:=Worksheets("Plan1").Range("A1:A10")
Para inserir nome via código no modo invisível
ThisWorkbook.Names.Add Name:="Qlqr_Nome", _
RefersTo:=Worksheets("Plan1").Range("C1:C10"), Visible:=False
________________________________________________________
Para excluir um nome de referencia via código:
ActiveWorkbook.Names("carros").Delete
________________________________________________________
Segue Demonstração Abaixo:
Obs.: Ao copiar os códigos do site e colar direto no excel substitua “ e ” por ” (aspas duplas)
Usando o CurrentRegion
Se esses dados sempre aumentarem… Então para automatizar você sempre irá precisar saber a última linha (veja mais em Dicas rápidas) e se aumentar o número de Colunas então também irá precisar saber a Ultima Coluna
Segue códigos sobre currentRegion
Sub SelecionarTabela()
Range("A2").CurrentRegion.Copy
Selection.Copy
End Sub
Sub SelecionarTabela2()
'No código abaixo em 1 linha estou selecionando toda tabela, copiando os dados e colando onde quiser
Range("A2").CurrentRegion.Copy Destination:=Range("B6:C10")
End Sub
Funções de Data e Hora
Pessoal, segue uma porção de funções de Data e Hora
Quase sempre estamos utilizando essas funções (quem nunca precisou usar um format vba ) e por isso creio que seja válido postar, pois muitas vezes há como facilitar o trabalho ou linhas de código se souber uma função que já traga uma data formatada.
Eu mesmo no início quando queria fazer um log com datas eu concatenava day(now), month(now) e year(now)
e conforme abaixo da para usar DateSerial(Year(Now), Month(Now), Day(Now))
Outra função que facilita muito é o format()…sempre precisamos quando o cliente pede a data em algum formato específico ou quando pegamos data em formato de outro idioma.
Bem façam bom proveito!!!
Funções de Data e Hora VBA
Função |
Descrição |
| Now | Data e hora atual. Exemplo: 22/01/2013 21:00:47 PM retornado por Now() |
| Date | Data atual. Exemplo: 22/01/2013 retornado por Date() |
| Time | Hora atual. Exemplo: 21:00:47 retornado por Time() |
| Timer | Número de segundos desde a meia-noite. Exemplo: 75783,98 retornado por Timer |
| TimeValue() | Retorna somente a hora. Exemplo: 21:00:47 é o retorno de TimeValue(Now) |
| DateValue() | Retorna somente a data de um argumento |
| DateSerial() | Traz a data divida por 3 argumentos: year, month, day |
| Examplo: logDeData = DateSerial(Year(Now), Month(Now), Day(Now)) | |
| logDeData = 22/01/2013 | |
| DatePart() | Retorna uma porção da data conforme informado. |
| Exemplo de ano: 2013 é o retorno de DatePart(“yyyy”, Date) | |
| Exemplo de mês: 01 é o retorno de DatePart(“m”, #22/01/2013#) | |
| Número da semana: 4 returned by DatePart(“ww”, Now) | |
| Dia da semana(traz em número 1 a 7, domingo=1), DatePart(“w”, Now) | |
| Trimestre: 1 é o retorno de DatePart(“q”, Now) | |
| Year() | Traz somente o ano de uma data |
| Month() | Traz somente o mês de uma data |
| Day() | Traz somente o dia de uma data |
| MonthName() | Traz o nome do mês, exemplo de uso: MonthName(Month(Date)) |
| WeekdayName() | Nome do dia da semana, ex: terça-feira = WeekdayName(Weekday(Date)) |
| DateDiff() | Retorna a diferença entre datas. |
| Diferença de dias: 344 para o próximo ano novo, DateDiff(“d”, Date, #1/1/2014#) | |
| Meses: 12 de diferença, DateDiff(“m”, #22/01/2013#, #1/1/2014#) | |
| DateAdd() | Adiciona e subtrai datas. |
| 22/01/2014 é o resultado da adição de ano, DateAdd(“yyyy”, 1, #1/22/2013#) | |
| Hoje mais 15 dias é o retorno de DateAdd(“d”, 15, Date) | |
| Para subtrair de uma data basta: DateAdd(“d”, -45, Date) | |
| Format() | Muito útil para formatar datas (vale também p/ formatar datas que estão em inglês) |
| ter, 22-janeiro-2013 é o retorno de Format(Date,”ddd, d-mmmm-yyyy”) | |
| 22-jan-13 é o resultado de Format(Date,”d-mmm-yy”) |
[box type=”warning”] Obs.: Ao copiar os códigos do site e colar direto no VBAProject substitua “ e ” por ” “(aspas duplas) [/box]
Outras Dúvidas sobre data no vba
Após tanta informação eu levanto duas perguntas aparentemente simples, a resposta também, mas não é o que parece…
- Como você faria para saber a idade de uma pessoa dentro do excel? [button link=”http://excelevba.com.br/formula-idade-excel/” color=”red”] Excel VBA[/button]
- A partir da data como você faria para fechar a planilha após algum tempo? [button link=”http://excelevba.com.br/expiracao-de-data-para-excel/” color=”red”] Excel VBA[/button]
Para ajudar coloco gratuitamente a planilha abaixo exemplificando cada função acima
Ultima Coluna
Neste artigo quero tratar de um assunto que conforme vamos usando o vba surge a necessidade de sabermos: a ultima coluna de uma planilha.
Para não ficar “quebrando a cabeça” segue um código rápido e simples para pegar a última coluna preenchida.
Código Ultima Coluna
colunaVerificada = 5000 linhaVerificada = 2 ultimaColuna = ActiveSheet.Cells(linhaVerificada, colunaVerificada ).End(xlToLeft).Column
Obs.: Esse código verifica apenas de uma linha, no caso acima na linha 2
Segue demonstração abaixo:
______________________________________________________________
*Dúvidas sobre o vídeo:
>>Como habilitar a Guia Desenvolvedor ?
>>Primeiros Passos em VBA?
______________________________________________________________
Obs.: Ao copiar os códigos do site e colar direto no excel substitua “ e ” por ” (aspas duplas)
Conhecendo VBA 