Dicas de VBA – ParamArray!

Por Jorge Paulino para o Pplware!

Quando criamos funções ou procedimentos, necessitamos muitas das vezes de indicar um ou mais argumentos. Isto é uma situação muito normal, especialmente em funções, mas o problema coloca-se quando não sabemos o total de argumentos que necessitamos de indicar.

Por exemplo, se queremos contar um determinado número de valores mas não sabemos quantos valores iremos indicar o que podemos fazer? Durante este pequeno artigo iremos ver algumas soluções para diferentes casos.

Se for um número fixo de argumentos, podemos indicar vários na função, como por exemplo:

Function mySum(va1 As Double, val2 As Double) As Double
     ‘ …
End Function

Isto é, como já foi referido, uma situação normal, mas se necessitarmos de um número indefinido de valores?

Quando a função é para ser usada no código, a solução é simples e pode-se usar um array e indicar n valores. Um exemplo simples para ilustrar esta utilização será a criação de uma função para somar os diversos valores:

Function mySum(values() As Double) As Double
    Dim v As Variant
    Dim contador As Double

    ‘ Ciclo nos valores do array
    For Each v In values  

        ‘ Soma os valores
        contador = contador + CDbl(v) 

    Next
    mySum = contador
End Function

Depois, criando o array que será usado como argumento e chamando a função:

Sub DoWork()

    ‘ Criação de um Array
    Dim doubleArray(2) As Double
    doubleArray(0) = 12.5
    doubleArray(1) = 5
    doubleArray(2) = 30.2

    ‘ Execução da função indicando o array criado com argumento

    Dim result As Double
    result = mySum(doubleArray) 

    MsgBox “O resultado é ” & CStr(result)

End Sub

Até aqui tudo simples e certamente do conhecimento da maioria, mas se queremos usar a função numa célula, ou seja, se quisermos criar uma User-Defined Functions (UDF) onde possamos indicar n argumentos?

Aqui é um bom exemplo para a utilização de um ParamArray!

Um ParamArray é uma das opções que estão disponíveis quando indicamos argumentos (assim como o Optional, ByVal e ByRef) e pode/deve ser utilizado quando não sabemos o número de dados que iremos indicar. De um modo geral:

• É um array de valores do tipo Variant (uma vez que podem ser indicados diversos tipos);
• Podemos utilizar apenas um por função/procedimento;
• Não podemos usar argumentos opcionais (keyword Optional) na função ou procedimento quando usamos um ParamArray;
• Tem de ser sempre definido no final dos argumentos, ou seja, tem de ser o último argumento a ser indicado.

Este é um exemplo da sua utilização:

Function funcao(nome As String, ParamArray valores() As Variant) As String
    ‘ …
End Function

Indicamos primeiro os argumentos que queremos (caso necessário) e no final, iniciando a keyword ParamArray, o array do tipo Variant.

Por exemplo na função SUM(), como em muitas outras, quando estamos a digitar na célula aparece-nos a indicação de que podemos indicar vários argumentos (sem especificar quantos). Veja a seguinte imagem:

É esta a aplicação do ParamArray e desta forma podemos, por exemplo, criar uma função que receba diversos ranges e somar esses valores.

Function mySum(ParamArray values() As Variant) As Double
    Dim total As Double
    Dim rng As Variant

    ‘ Ciclo nos diferentes parâmetros indicados
    For Each rng In values

         ‘ Verifica se foi indicado um Range
        If TypeOf rng Is Range Then
            Dim r As Range
            Set r = rng

            ‘ Novo ciclo nas células do Range
            Dim cell As Range
            For Each cell In r

                ‘ Verifica se é um número para incrementar a variável
                If IsNumeric(cell) Then
                    total = total + cell.Value
                End If
            Next
        End If

   Next

    ‘ Atribui o resultado à função
    mySum = total

End Function

Depois, na célula, podemos usar das seguintes formas:

    Somando o valor de uma célula apenas:
=mySum(A1)

Somando um intervalo da célula A1 à A10:
         =mySum(A1:A10)

Somando múltiplos intervalos:
=mySum(A1:A10;B1:B10;C1:C10)

NOTA: Para que a função possa ser usada na célula (UDF) é necessário que seja criada num módulo.

Neste caso estamos a criar uma função muito semelhante ao SUM() mas que serve para ilustrar a utilização do ParamArray e de que forma pode ajudar na criação de UDF’s.

Certamente terão ideias de onde e como aplicar!

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