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.