Select Menu



por Alessandro Trovato

» » » » » » » » » VBA - Artigo 023 - Fazendo funções retornarem mais de um valor
«
Proxima
Postagem mais recente
»
Anterior
Postagem mais antiga

Progredindo em VBA no Microsoft Excel

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. Em casos com quantidades indeterminadas (como esse exemplo de lista de cidades) pode ser melhor usar collection, pois este objeto não exige uma definição de quantidade e pode armazenar qualquer tipo de dado.

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

Autor Pedro Martins

Esta é uma breve descrição no bloco de autor sobre o autor. Você edita-lo, no html
«
Proxima
Postagem mais recente
»
Anterior
Postagem mais antiga

Nenhum comentário

Comentarios