Dicas de VBA – WorksheetFunction

Por Jorge Paulino para o Pplware!

Em artigos anteriores iniciei algumas dicas sobre programação em Visual Basic for Applications (VBA), aplicado ao Microsoft Excel, de forma a clarificar e a mostrar algumas das melhores práticas.

• Excel: Dicas de VBA - Eventos – O que são e como utilizar eventos no Microsoft Excel • Excel: Dicas de VBA - Performance – Como melhorar a performance de execução do código com métodos simples e fáceis de utilizar. • Excel: Dicas de VBA - Ciclos – Como efectuar ciclos da forma correcta

Irei em próximos artigos explorar mais algumas funcionalidades e mostrar algumas dicas que julgo importantes, mas para já o objecto WorksheetFunction.

O objecto WorksheetFunction permite utilizar fórmulas que já utilizamos directamente nas células, através de métodos, que estão disponíveis no Microsoft Excel. Tem ainda algumas propriedades (Application, Creator e Parent), embora menos utilizadas.

Esta possibilidade de utilizar fórmulas no código simplifica bastante inúmeras operações e é sem dúvida um objecto muito importante. Podem ser utilizadas formulas simples como o Sum(), Min(), Max(), Count(), etc, até formulas mais complexas, como o SumProduct(), Index(), VLookup(), DCount(), etc. E se em alguns casos simplifica apenas a leitura do código, em outros, a sua utilização resolve-nos alguns problemas mais complexos.

Quando colocamos o ponto (.) a seguir ao objecto WorksheetFunction, é mostrada a lista dos métodos e propriedades disponíveis e podemos aqui encontrar uma lista muito extensa.

NOTA: De referir que as formulas que podemos usar (e apresentadas na lista) são apenas as que estão disponíveis na versão utilizada, ou seja, a versão do Excel 2003 tem menos funções disponíveis do que o Excel 2007 ou 2010. Isto é importante também por questões de compatibilidade.

Por exemplo, quando queremos gerar um número aleatório entre dois valores, podemos fazer da forma tradicional: Dim resultado As Integer Randomize resultado = Int((10 * rnd) + 1) MsgBox resultado

Neste caso é chamada a função Randomize e depois é gerado um número entre 1 e 10. Mas vejamos como fazer utilizando a função RandBetween(), disponível na versão 2007 ou superior, através do objecto WorksheetFunction:

Dim resultado As Double resultado = WorksheetFunction.RandBetween(1, 10) MsgBox resultado

Além de ser utilizada menos uma linha, que é pouco importante obviamente, o código é mais simples de analisar.

Mas existem casos mais complexos em que se não utilizarmos o objectoWorksheetFunction, o código é mais extenso. Por exemplo, para somar os valores do seguinte range (como mostra a figura seguinte) da célula A1 à célula A10, teríamos de fazer um ciclo e somar o valor de cada célula para uma variável.

Utilizando o objecto WorksheetFunction podemos fazer da seguinte forma:

Dim rng As Range Set rng = Range("a1:a10") MsgBox WorksheetFunction.Sum(rng)

Neste simples código, onde o resultado será 55, define-se um range que depois será indicado como argumento da função. O range poderia ser indicado directamente, mas desta forma é mais simples ler o código. Pode-se utilizar mais argumentos (agora indicado directamente os ranges):

MsgBox WorksheetFunction.Sum(Range("a1:a10"), Range("b1:b10"))

Outro exemplo, utilizando a mesma tabela, poderá ser o de contar quantas linhas têm o valor maior ou igual a 8. Neste caso, sem utilizar este objecto, teríamos de fazer um ciclo, verificar o valor e contando apenas os que estivessem de acordo com o critério definido. Com o objecto WorksheetFunction podemos fazer muito facilmente utilizando a formula CountIf():

Dim rng As Range Set rng = Range("a1:a10") MsgBox WorksheetFunction.CountIf(rng, ">=8")

Estes são alguns exemplos simples de como o objecto WorksheetFunction simplifica e muito o código e certamente existem muito mais aplicações práticas onde o podemos utilizar.

Uma das “desvantagens” deste objecto é de utilizar sempre as funções em Inglês, o que não será problema para quem já conhece e utiliza as formulas não traduzidas.digo em VBA!

Escrito por: Jorge Paulino
Homepage: Página Autor: Jorge Paulino