Fazendo funções retornarem mais de um valor
Quem já criou suas próprias funções em VBA certamente
reparou que uma função retorna uma única variável. Isto
não significa que a função retornará apenas um único
valor. Uma função criada para retornar um valor
Variant permite retornar
arrays
ou objetos, que podem conter mais de um valor. Veja o
código abaixo de exemplo:
Function LancarDados(Quantidade as Integer) As
Variant
Dim Dado() As Integer ' Cria
um array dinâmico, sem quantidade definida
Dim Iteracao As Integer
ReDim Dado(1 To Quantidade)
' Redefine o array de 1 à Quantidade recebida
For Iteracao = 1 To Quantidade
Dado(Iteracao) = Int(6 * Rnd() + 1) ' Gera um
número aleatório entre 1 e 6
Next
LancarDados = Dado() ' Atribui o
array ao resultado da função
End Function
Sub JogarDados()
Dim Numeros As Variant
Dim Iteracao As Integer
Numeros = LancarDados(5) ' Atribui
o resultado da função LancarDados
For Iteracao = LBound(Numeros) To
UBound(Numeros)
Debug.Print "Dado " & Iteracao & ": " &
Numeros(Iteracao)
Next
End Sub
A função LancarDados gera uma
Quantidade de números aleatórios entre 1 e 6 e os
armazena em um array, que em seguida é
encaminhado como resultado da função. A sub-rotina
JogarDados chama essa função, armazenando o
resultado em Numeros. Perceba que esta variável
foi criada como Variant, caso contrário daria
erro. Em seguida, uma estrutura de repetição se
encarrega de percorrer o array retornado e exibir os
valores dos dados:
Dado 1: 4
Dado 2: 3
Dado 3: 6
Dado 4: 3
Dado 5: 2
É possível fazer uma rotina para gerar um valor
aleatório de um dado e na rotina principal criar uma
rotina que chame quantas vezes necessário. Porém esta
forma é mais eficiente, pois chama a função e retorna os
valores necessários de uma vez.
Lembre-se que arrays podem conter os mais diversos
tipos, não apenas valores numéricos. Você pode criar uma
função que pesquise nomes de cidades para um determinado
estado enviado como parâmetro, por exemplo. Apenas
atente para a quantidade de itens retornados, pois o
código da função precisa prever a quantidade
possível.
Funções também podem retornar objetos
type. Suponha que você queira criar uma função que retorne
um resumo estatístico para um intervalo dado. É possível
usar um array, mas neste caso precisaríamos saber
qual valor corresponde a qual posição. Neste caso um
objeto
type
funciona melhor. Analise o código abaixo:
Type tpResumoEstatistico
DesvioPadrao As
Double
Maximo
As Double
Media
As Double
Mediana
As Double
Minimo
As Double
Moda
As Double
End Type
Function ResumoEstatistico(Intervalo As Range) As
tpResumoEstatistico
With ResumoEstatistico
.DesvioPadrao =
WorksheetFunction.StDev_S(Intervalo)
.Media =
WorksheetFunction.Average(Intervalo)
.Mediana =
WorksheetFunction.Median(Intervalo)
.Moda =
WorksheetFunction.Mode(Intervalo)
.Minimo =
WorksheetFunction.Min(Intervalo)
.Maximo =
WorksheetFunction.Max(Intervalo)
End With
End Function
Sub ExibirResumo()
Dim Intervalo As Range
Dim RE As tpResumoEstatistico
Set Intervalo =
Range("A1:D15")
RE =
ResumoEstatistico(Intervalo)
With RE
Debug.Print "Resumo
estatístico para o intervalo A1:D15" &
Chr$(13)
Debug.Print "Mínimo: " & .Minimo
Debug.Print "Máximo: " & .Maximo
Debug.Print "Média: " & .Media
Debug.Print "Moda: " & .Moda
Debug.Print "Mediana: " & .Mediana
Debug.Print "Desvio Padrão: " &
.DesvioPadrao
End With
End Sub
Perceba que a ordem usada na definição do objeto
type não precisa ser repetida no preenchimento de
valores e muito menos na exibição deles, pois cada
elemento é independente dos outros. Se fosse utilizado
um array seria necessário saber qual valor
deveria ser colocado em qual posição, o que deixaria
tudo muito mais complicado. Com o type é possível
ainda adicionar novos valores que não afetará os já
existentes.
Preenchi o intervalo A1 a D15 com notas aleatórias para
simular uma turma de alunos e executei a sub-rotina
ExibirResumo, que gerou o seguinte texto na área
de Verificação imediata:
Resumo estatístico para o intervalo A1:D15
Mínimo: 3,4
Máximo: 9,9
Média: 6,62166666666667
Moda: 6,5
Mediana: 6,3
Desvio Padrão: 1,91063851840755
Se for testar o código não esqueça de preencher células
com as funções equivalentes do Excel para poder comparar
com o resultado da execução.
Uma função também pode retornar um objeto, inclusive os
próprios do Excel, como Worksheet e Range.
Você pode criar uma função que retorne uma célula ou um
intervalo que virá não só com os valores, mas também com
a formatação e todas as outras propriedades pertinentes.
Porém, é preciso tomar atenção porque o retorno é um
ponteiro para os objetos, ou seja, eventuais alterações
irão afetar o objeto retornado. Também deve-se lembrar
que o retorno será um objeto e por isso o Set é
necessário ao associar o retorno da função ao resultado.
Analise o código abaixo:
Function EncontrarValor(Valor As Double, Intervalo As
Range As Range
Dim
Pesquisa
As Range
Dim
PesquisaAnterior As Range
Set Pesquisa =
Intervalo.Find(Valor, LookIn:=xlFormulas,
LookAt:=xlWhole, _
SearchOrder:=xlByColumns)
If Pesquisa Is Nothing Then
Set
EncontrarValor = Nothing
Exit
Function
Else
Set
EncontrarValor = Pesquisa
End If
Do
Set
EncontrarValor = Union(EncontrarValor, Pesquisa)
Set
PesquisaAnterior = Pesquisa
Set
Pesquisa =
Intervalo.FindNext(After:=PesquisaAnterior)
Loop Until Pesquisa.Row <=
PesquisaAnterior.Row And _
Pesquisa.Column <= PesquisaAnterior.Column
End Function
Sub RealcarValores()
Dim Intervalo As
Range
Dim
Valor As
Double
Dim
Retorno As Range
Set Intervalo =
Range("A1:D15")
Valor = Range("F1").Value
Intervalo.Font.Bold = False
Intervalo.Font.Color = vbBlack
Set Retorno =
EncontrarValor(Valor, Intervalo)
If Retorno Is Nothing Then
Exit
Sub
Else
Retorno.Font.Bold = True
Retorno.Font.Color = vbRed
End If
End Sub
A função EncontrarValor localiza um
Valor em um Intervalo, sendo ambos
passados por parâmetro. A pesquisa é feita utilizando o
método Find, cuja explicação se encontra no
artigo 22. Há uma verificação para saber se não houve retorno da
pesquisa, o que fará a função retornar Nothing,
caso contrário associa o valor ao resultado da pesquisa
e continua. Isso é feito porque o método
Union que está na estrutura de repetição não
aceita conteúdos vazios e gera um erro. Se um valor for
encontrado, então prossegue para a estrutura de
repetição, que irá pesquisar por mais valores iguais,
fazendo a união para acrescentar os novos valores.
Essa estrutura de repetição processa até que a linha e
a coluna do resultado atual sejam menores ou iguais ao
último resultado. Como a pesquisa é circular (ou seja,
volta a pesquisar no começo do intervalo se não
encontrar nada), é preciso validar se o resultado obtido
(Pesquisa) não está em uma posição anterior ao
último valor encontrado (PesquisaAnterior), o que
é um indício de que a pesquisa voltou ao início e,
portanto, o valor obtido já foi armazenado anteriormente
em EncontrarValor.
A sub-rotina RealcarValores efetua a pesquisa
por um valor digitado na célula F1 que esteja no
intervalo de A1 a D15. Se encontrar algum valor irá
marcar as células em negrito e em vermelho. As células
do intervalo são redefinidas para cor preta e sem
negrito antes de executar a função, para que o realce
seja somente para os valores novos.
Perceba que o Retorno da função
EncontrarValor foram as próprias células, não
apenas valores. Por isso é possível editar as
propriedades de formatação do Retorno, o que não
seria possível se fosse recebido apenas os valores ou os
endereços das células em questão.
A outra forma de fazer o mesmo trabalho seria criar uma
sub-rotina para verificar se cada célula do intervalo
corresponde ao valor, formatá-la caso positivo e então
continuar a verificação até o final do intervalo. A
função EncontrarValor retornando as células não
só efetua o trabalho mais árduo como ainda permite ser
reutilizada em outras necessidades, evitando ter de
reescrever mais código adaptado para cada uma delas.
Vimos aqui como exemplos de funções retornando
array, type e range. Você pode
criar funções que retornem outros tipos de objetos
conforme sua necessidade. Não se esqueça que essas
funções que retornem objetos devem estar em um módulo a
parte com a linha Option Private Module, para que
não fiquem disponíveis como fórmulas para as
planilhas.
Escreva algumas funções para praticar, de preferência
com alguma necessidade que já passou ou que possa
precisar. Teste bastante com uma sub-rotina feita para
essa finalidade. Exercite o conteúdo deste e dos outros
artigos e releia-os sempre que houver necessidade. Até o
próximo artigo!
Pedro Martins
Pós-graduando em Business Intelligence e Big Data pela Faculdade
Impacta de Tecnologia. Formado em Tecnologia em Eletrônica Digital
com Ênfase em Microprocessadores