segunda-feira, 15 de junho de 2009

Procura em Lista Fixa

Se você precisa adicionar uma fórmula que retorno um determinado item a partir de uma lista, porém não quer escrever esta lista na planilha, você pode utilizar a fórmula abaixo:

=EXT.TEXTO("Item1;Item2;Item3";Indice*(CaracteresItem+1)-CaracteresItem;CaracteresItem)

Onde:

"Item1;Item2;Item3": É a lista de itens onde a busca deve ser feita;
Indice: é a posição do item desejado;
CaracteresItem: é a quantidade de caracteres de cada item.

Esta fórmula funciona bem para lista pequenas e todos os itens devem ter a mesma quantidade de caracteres. Por exemplo:

=EXT.TEXTO("JAN;FEV;MAR;ABR;MAI;JUN;JUL;AGO;SET;OUT;NOV;DEZ";5*(3+1)-3;3)

Neste caso, a fórmula retorna "MAI" correspondente ao item 5 informado. E o 5 poderia ser substituído por MÊS(A1) considerando que na célula A1 existe uma data válida.
No entanto, se você possui uma lista cujos itens possuem tamanhos diferente, pense na possibilidade de igualá-los com espaços e suprimir estes espaços após o resultado, como abaixo:

=ARRUMAR(EXT.TEXTO("Verde ;Vermelho;Azul ";A1*9-8;8))

A função ARRUMAR retirar os espaços à esquerda e à direita de um texto. É a tradução do Excel para a função TRIM.

segunda-feira, 1 de junho de 2009

Método Evaluate

O método Evaluate dos objetos Workbook, Worksheet e Chart é uma funcionalidade que pode reduzir drasticamente seu código e o tempo de resposta da sua aplicação.

A principal característica desse método é que ele retorna o resultado de uma fórmula assim como a digitamos diretamente na planilha. Por exemplo, imagine que você queira saber quantas vezes aparece a palavra "São Paulo" em uma determinada no intervalo A1:A200. Você provavelmente digitaria em uma célula uma fórmula como:

=CONT.SE(A1:A200;"São Paulo")

No entanto, se você precisa desse resultado no código, sem o Evaluate você pode escrever:

. Dim I As Integer
. Dim intQuant As Integer

. For I = 1 To 200
. . If Plan1.Cells(I, 1) = "São Paulo" Then
. . . intQuant = intQuant
. . End If
. Next I

Com o Evaluate, você apenas digitaria:

Dim intQuant As Integer

intQuant = Plan1.Evaluate("COUNTIF(A1:A200, ""São Paulo"")")

A fórmula é exatamente a mesma, mas tem que usar a sintaxe em inglês. Se não sabe como é o nome da função em inglês, digite a fórmula na planilha, selecione a célula com a fórmula e digite na janela imediata:

?ActiveCell.Formula

Propriedade ScreenUpdating

Se você tem uma macro que navega pelas planilhas, faz scrow, copia e cola, entre outras coisas, ao executá-la essas ações acontecem como se um usuário estivesse de fato executando cada etapa, mas, mais rápido, claro. Na maioria das vezes não se deseja visualizar toda essas ações, apenas o resultado final, pois, toda essa atualização de tela tem um efeito desagradável.

Uma outra questão que deve ser observada é que a atualização de tela, como todo processamento gráfico, tem um custo significativo de processamento. Então, se vizualizar essas atualizações durante o processo não interessa, pode-se utilizar a propriedade ScreenUpdating do objeto Application no começo da macro, conforme abaixo:

Application.ScreenUpdating = False

Dependendo do tamanho da macro, o tempo de resposta diminui significativamente.

quarta-feira, 27 de maio de 2009

Tamanho de uma Planilha

Faça o seguinte teste:
1. Crie uma pasta de trabalho nova
2. Na célula A1 digite "A"
3. Na célula B1 digite "B"
4. Salve a pasta de trabalho
5. Veja o tamanho do arquivo criado

Agora faça o seguinte:
1. Crie uma pasta de trabalho nova
2. Na célula A1 digite "A"
3. Na célula IV65536 (ou XDF1048576 no caso da última versão do Excel) digite "B"
4. Salve a pasta de trabalho
5. Veja o tamanho do arquivo criado e compare com o primeiro

Como pode ver, apesar de os 2 arquivos terem os mesmos dados, a posição destes dados na planilha influencia seu tamanho e, consequentemente a segunda planilha fica mais pesada. Isso ocorre porque o Excel não tem mapeadas todas as células previstas em suas planilhas. O mapeamento ocorre até o limite utilizado. Ou seja, no primeiro exemplo, o limite é B1, 2 colunas e 1 linha que é igual a 2 células. No segundo exemplo, o limite é IV65536, 256 colunas e 65.536 linhas que é igual a 16.777.216 células mapeadas. E, claro, para fazer esse mapeamento, precisa-se de espaço em disco e em memória.

É muito comum colocar alguns dados auxiliares "escondidos" em áreas fora do campo de visão do usuário. Pense como alternativa colocar essas informações em uma outra planilha e, se for o caso, ocultá-la. Além de ser uma solução mais elegante, a pasta de trabalho terá seu tamanho reduzido.



terça-feira, 26 de maio de 2009

Repetindo Valores

Se você quer entrar com um mesmo valor em várias células, selecione todas as células que você quer que tenha o mesmo valor, digite o valor desejado - apenas a célula ativa dentro da seleção será alterada - e, ainda com a célula em modo de edição, tecle Ctrl + Enter. Pronto!

Isso é muito útil quando se quer repetir valores em intervalos não contíguos, em planilhas filtradas e quando se quer alterar os valores sem mudar a formatação. Nestes casos, se você clicar no canto inferior direto da célula e arrastar, terá problemas.

segunda-feira, 25 de maio de 2009

Suplementos

Se você utiliza algum suplemento em sua planilha e tem que distribuí-la depois, você vai ter que informar ao usuário a necessidade habilitar esse suplemento para que a planilha funcione corretamente.

Você também pode automatizar a habilitação do suplemento adicionado o código abaixo ao evento Open do Workbook:

If Not AddIns(“Ferramentas de análise”).Installed Then
. AddIns(“Ferramentas de análise”).Installed = True
End If


No entanto, se algum usuário utiliza o Excel em outro idioma, o suplemento não será encontrado, a menos que você faça a verificação no outro idioma também. Uma maneira de resolver isso é fazer a habilitação do suplemento a partir do nome da biblioteca - o arquivo DLL - mas nesse caso você vai ter que percorrer toda a lista de suplementos disponíveis em vez de fazer uma referência direta, como abaixo:

Dim I As Integer

For I = 1 To AddIns.Count

. . If AddIns(I).Name = "ANALYS32.XLL" Then
. . . If Not AddIns(I).Installed Then
. . . . AddIns(I).Installed = True
. . . End If
. . . Exit For
. End If
Next I

Para descobrir nome da biblioteca, na janela imediata digite:

?AddIns(“Ferramentas de análise”).Name

Importante! Os códigos apresentados não instalam os suplementos, apenas os habilitam! Para instalação, consulte o manual do fornecedor do suplemento.

domingo, 24 de maio de 2009

Sistemas em Excel

Dependendo da empresa onde você trabalha, é bem possível que existam várias planilhas sem as quais a empresa não funciona. É um controle disso, um controle daquilo, listas, check lists, cadastros e por aí vai. Surgem como apenas mais um documento e um chefe gosta e no outro mês pede novamente a planilha. Com algumas alterações e mais alguns dados. Sugestões surgem, alguém que conhece um pouco mais adiciona uma tabela dinâmica, gráficos e até arrisca criar algumas macros que aprendeu em um livrinho que comprou na banca de revistas.

O circo está armado! A planilha criada sem grandes pretensões faz parte agora do processo da empresa. De fato, virou um sistema, muitas vezes com regras de negócio bastante intrincadas e uma salada de soluções técnicas desenvolvidas por alguém cheio de boas intenções mas sem nenhuma experiência com programação, estrutura de dados e etc..

Há quem diga que Deus fez o mundo usando o Excel. Por isso o fez rápido. Mas, como tudo que é feito em Excel, dar manutenção não é fácil.

Recentemente fui a uma grande instituição financeira para dar uma olhada em uma dessas planilhas. A dita já existia há 5 anos, tendo sido alterada durante todo esse tempo por diversas pessoas. Tudo que se pode imaginar de recursos do Excel foi utilizado. A planilha gera diversas outras planilhas que são enviadas para mais de 100 empresas em diversos países. E, da olhada surgiu um cronograma de manutenção com vários meses de trabalho. Como ela surgiu? Como apenas mais um documento que o chefe gostou e... Bem, você já sabe.

Então, se você conhece bem esta história ou, principalmente, se você se viu na ingrata tarefa de dar manutenção em uma destas planilhas, este blog vai ajudá-lo com dicas, melhores práticas e é também um espaço para a troca de ideias e experiências.

Seja bem vindo!