Como criar uma função personalizada que retorne um vetor de dados

Este post foi originalmente publicado em http://officevbavsto.blogspot.com/2012/05/criando-uma-funcao-personalizada-que.html

Embora o Microsoft Excel possua muitas funções próprias tais como =SOMA(), =PROCV(), =ESQUERDA(), e etc. é possível que você precise usar o Excel para tarefas mais complexas, e por conseguinte precise de uma função que não se encontra disponível junto a instalação do produto Office. É ai que entra a necessidade de se criar funções personalizadas.

Função personalizada

Da mesma maneira que uma Macro do Excel permite você encapsular uma sequência de ações em um único comando (método, função), uma função personalizada permite você encapsular uma sequência de cálculos para que assim você possa executar estes cálculos com uma única Fórmula [1].

Por que e quando usamos uma função personalizada? Assim como é mais conveniente usarmos =SOMA(A1:A20) ao invés de =A1+A2+...+A20, uma função personalizada pode ser mais conveniente quando nós precisamos repetidamente realizarmos um certo tipo de cálculo pelo qual o Excel não tem Função própria [2].

Vamos a um exemplo:

1. Crie uma nova Pasta de Trabalho do Microsoft Excel;
2. Abra o Visual Basic Editor (Guia Desenvolvedor, opção Visual Basic ou tecle a combinação ALT + F11);
3. Crie um novo módulo de código: no Visual Basic Editor - menu Inserir – Módulo;
4. Digite o seguinte código:

Public Function RetornaA() As String
 
	RetornaA = "Hello World"
 
End Function

Agora volte à planilha do Excel e em qualquer célula digite a seguinte fórmula:

=RetornaA()

Funções personalizadas, assim como Macros, usam a linguagem de programação Visual Basic for Applications (VBA) [1]. Funções personalizadas diferem de Macro de duas maneiras significantes: primeiro, elas usam Functions (funções) ao invés de Sub (sub-rotinas), isto é, elas começam com uma declaração Function ao invés de uma declaração Sub e terminam com uma declaração End Function ao invés de uma declaração End Sub. Segundo, elas realizam cálculos ao invés de tomar ações.

Funções personalizadas não podem incorporar nenhum comando de “ação” do VBA [2]. Alguns tipos de declarações (tais como Selecionar e Formatar células) são excluídos de funções personalizadas [1]. Nenhum usuário experiente do Excel tentaria usar a função =RAIZ() em uma planilha com o objetivo de esta função calcular a raiz quadrada de um número e, ao mesmo tempo, a própria função executar o comando de abrir uma nova pasta de trabalho para inserir o resultado lá; funções personalizadas também não são diferentes [2].

Função personalizada que retorna um vetor de dados

Volte ao Visual Basic Editor e declare a seguinte função:

Public Function RetornaVetorDias() As String()
 
End Function

Observe que o tipo de dados String possui um sinal de parênteses. Isso indica que o retorno da função será um vetor.

Agora crie um vetor do tipo String conforme a seguir:

Public Function RetornaVetorDias() As String()
 
    Dim strVetor(6) As String
    strVetor(0) = "Dom"
    strVetor(1) = "Seg"
    strVetor(2) = "Ter"
    strVetor(3) = "Qua"
    strVetor(4) = "Qui"
    strVetor(5) = "Sex"
    strVetor(6) = "Sab"
 
    RetornaVetorDias = strVetor
End Function

Agora, vamos testar a função?

Bem, usar essa função é simples. É feito da mesma maneira como se faz quando usa Fórmulas Matriciais do Excel, tais como {=TRANSPOR()}.

Se você nunca usou, faça o seguinte procedimento:

1) Na planilha, selecione horizontalmente um intervalo de 7 células, conforme abaixo:

2) Digite a função =RetornaVetorDias() e pressione ao mesmo tempo CTRL + SHIFT + ENTER.

Fórmulas Matriciais

Se você já programou alguma vez, você provavelmente já deve ter ouvido falar do termo Array. Array, é apenas uma coleção de itens. No Excel, estes itens podem estar presentes em uma única linha (chamado de Array horizontal de uma-dimensão), uma coluna (um Array vertical de uma-dimensão) ou múltiplas linhas e colunas (Array bi-dimensional) [3].

Em programação de computadores, um array, também conhecido como vetor (para arrays uni-dimensionais) ou matriz (para arrays bi-dimensionais), é uma das mais simples estruturas de dados [4].

Uma Fórmula Matricial (ou Array Formula) é uma formula que pode realizar múltiplos cálculos em um ou mais itens de uma Matriz (ou Array bi-dimensional). Fórmulas Matriciais podem retornar tanto múltiplos resultados quanto um único resultado [3].

Você pode identificar uma Fórmula Matricial por que ela é fechada entre colchetes “{ }” quando exibida na Barra de Fórmulas do Excel. [5]

Figura 5: Colchetes entre Fórmula Matricial

Quando você digitar uma Fórmula Matricial, pressione CTRL + SHIFT + ENTER (não apenas ENTER) e não digite os colchetes “{}” (O Excel insere os colchetes para você). Se precisar editar uma Fórmula Matricial, não se esqueça de usar o CTRL + SHIFT + ENTER quando terminar a edição (caso contrário, a Fórmula Matricial será revertida para uma Fórmula Comum e retornará um resultado incorreto) [5].

Créditos

Referência Bibliográfica

1. "Creating custom functions - Excel - Office.com." Office - Office.com. http://office.microsoft.com/en-us/excel-help/creating-custom-functions-HA001111701.aspx (accessed April 26, 2013);
2. Liengme, Bernard. A Guide to Microsoft Excel 2007 for Scientists and Engineers. Burlington: Elsevier, 2008;
3. "Introducing array formulas in Excel - Excel - Office.com." Office - Office.com. http://office.microsoft.com/en-us/excel-help/introducing-array-formulas-in-excel-HA001087290.aspx (accessed April 26, 2013);
4. "Array – Wikipédia, a enciclopédia livre." Wikipédia, a enciclopédia livre. http://pt.wikipedia.org/wiki/Array (accessed April 26, 2013);
5. Walkenbach, John. Office 2007 bible. Indianapolis, IN: Wiley, 2007.