Função Definida pelo Usuário (UDF) no Excel 2010

Em nossa última postagem, onde falarmos sobre Análise Combinatória (Análise Combinatória no Excel 2010: Combinação, Arranjo ou Permutação) mencionamos sobre criação de Função Definida pelo Usuário. Conforme prometemos, mostraremos como criar uma função personalizada para calcular a combinação com repetição no final desta postagem.

Existem muitas funções prontas no EXCEL, mas nem todas as fórmulas do mundo estão implementadas na forma de função. Entretanto, é possível criar nossas próprias funções, uma função definida pelo usuário (UDF – User Defined Functions ou Custom Excel Functions). Este tópico é descrito pela Microsoft como

Escrevendo um procedimento Function

Se houver um cálculo complexo a ser reutilizado com freqüência no Microsoft Excel, você pode criar sua própria função de planilha para executar o cálculo.

Para criar suas próprias funções personalizadas, utilize o Microsoft Visual Basic® for Applications (VBA). O VBA é uma linguagem de programação interna do Excel que está acessível pelo atalho ALT+F11.

Vamos começar com uma fórmula simples de ser implementada como função…

Função Definida pelo Usuário

Problema: um termômetro americano marca 68 graus Fahrenheit. Será que está quente ou frio?

A fim de manter o exemplo simples, escolhemos implementar uma função calcula o número de graus Celsius a partir dos graus Fahrenheit. Quando a função for chamada a partir de uma célula do EXCEL, o resultado do cálculo é retornado e mostrado na célula.

Antes de começar, crie a seguinte planilha colocando os valores numéricos nas células A2, A3 e A4: tabela fahrenheit-celsius excel

O procedimento a seguir ensina, em apenas 5 passos, como criar e usar uma função definida pelo usuário:

I) O atalho ALT+F11 abre a janela do VBA (Microsoft Visual Basic For Applications)

II) Procure pela guia Inserir e selecione a opção Módulovba - inserir módulo no excel 2010

III) Na janela que abriu, digite o código de sua função. Por exemplo, uma função para calcular graus Celsius a partir dos graus Fahrenheit:

Function Celsius(grausF)   

        Celsius = (grausF - 32) * 5 / 9

End Function

Digite a função acima na janela Módulo 1 aberta:

digitando uma UDF no VBA Excel

IV) No menu Arquivo do VBA, clique em Fechar e voltar para Microsoft Excel (ou use o atalho Alt+Q) e Volte para a planilha Excel .

V) Teste sua função. Digite na planilha a função que você acabou de implementar (não esqueça de começar com o sinal de igualdade) colocando entre parênteses o argumento desejado (neste caso um número ou uma célula que contém um número). Neste exemplo você deve digitar =CELSIUS(A2) na célula B2 (ou outra célula qualquer) do Excel.

Observe que o Excel reconhece a existência da função CELSIUS à medida que está sendo digitada:

digitando a UDF no Excel

Na planilha, se os valores numéricos estiverem nas células A2, A3 e A4, use sua função para fórmulas do mesmo modo que usaria qualquer função de planilha. Por exemplo, a função Celsius pode ser usada na planilha assim:

UDF digitada no Excel 2010

 Importante: Uma função criada dessa maneira somente se encontra disponível para uso na pasta de trabalho onde tiver sido criada e, se quiser ser usada posteriormente, precisará ser salva como Pasta de Trabalho Habilitada Para Macro Do Excel.

Dica: se quiser usar essa função em outro local, você pode copiar o código da função para módulos do VBA situados em outras pastas de trabalho.

Detalhes da função definida pelo usuário Celsius

Eis, novamente, a listagem de nossa função, vamos examiná-la em detalhes:

Function Celsius(grausF) 


     Celsius = (grausF - 32) * 5 / 9

End Function

O nome da função que será usada pelo usuário, Celsius no exemplo acima, deve receber dentro do corpo da função o resultado do cálculo realizado com o argumento grausF, que, neste caso foi subtraído de 32 e depois multiplicado por cinco nonos (5/9). As quatro operações básicas (soma, subtração, multiplicação e divisão)  mais a potência utilizam os mesmos operadores que usamos dentro de uma planilha Excel: +*/ e ^.

Outros operadores bem como algumas funções estão disponíveis, mas usam uma forma diferente dos operadores e funções que usamos dentro de uma planilha. Outros operadores disponíveis dentro do VBA: \ (divisão inteira), Mod (para obter o resto da divisão inteira), & (para concatenar texto). Existem ainda várias funções , mas isso é fácil de achar na Internet e foge do escopo da nossa postagem.

O exemplo de FUNÇÃO DEFINIDA PELO USUÁRIO (UDF) usado nesta postagem foi bastante simplificado, apenas para demonstrar o funcionamento básico de uma função definida pelo usuário. Para criar funções complexas no VBA, é preciso conhecer um pouco o modelo de objetos do VBA para Excel, a estrutura da linguagem do Visual Basic (VB) e o ambiente VBA.

Exemplo de outra Função Definida pelo Usuário

1) Como calcular através de uma UDF o processo inverso, transformação de Celsius para Fahrenheit?

Neste caso, siga os mesmos passos anteriores, mas a função digitada em módulo 1 ficará assim (pode ser digitada abaixo da função anterior, se não quiser apagar a outra função já digitada):

Function Fahrenheit(grausC) ' de graus C para graus F

     Fahrenheit = grausC * 9 / 5 + 32

End Function

Para usar a função acima dentro de uma planilha, escreva =FAHRENHEIT(100) dentro de uma célula no Excel para saber, por exemplo, a temperatura em que a água ferve (100°C) em um termômetro com a escala Fahrenheit. Deverá surgir o número 212.

2) Eis aqui um outro exemplo de função definida pelo usuário para calcular centímetros a partir de valores em polegadas:

Function cm(pol)  ' transformar polegadas em centímetros

     
     cm = pol * 2.54


End Function

A função acima, quando digitada em Módulo 1 (conforme fizemos com a função anterior), permite calcular na planilha a conversão de polegadas para centímetros, bastando para isso digitar =CM(A2) na planilha Excel, caso o número em polegadas esteja na célula A2, por exemplo.

3) A função que faz o contrário, transforma centímetros em polegadas está aqui:

Function pol(cm)  ' transformar centímetros em polegadas 

     pol = cm / 2.54

End Function

Para usar, basta digitar, por exemplo, =POL(12) na planilha Excel e saberemos quanto mede, em polegadas, a medida 12 centímetros. Deve dar 4,724409 polegadas.

Como criar uma função para calcular a combinação com repetição

Seguindo os passos acima, mostrados aqui de forma resumida:

I) Tecle o atalho ALT+F11 para abrir a janela do VBA

II) Vá na guia Inserir e selecione a opção Módulo

III) Digite o seguinte código:

Function CombinRep(ByVal n As Long, ByVal p As Long) As Double

    If (n > 0 And p > 0) Then

       CombinRep = factorial(n + p - 1) / (factorial(p) * factorial(n - 1))

    Else

        CombinRep = Error()

    End If

End Function

Function factorial(ByVal n As Long) As Double

    factorial = 1

    Dim a As Double

    For a = 1 To n

        factorial = factorial * a

    Next a

End Function

IV) Tecle o atalho ALT+Q para sair do VBA e voltar à planilha

V) Teste a função entrando com algum valor, como por exemplo, digitando na planilha Excel, =CombiRep(4,2)  em alguma célula qualquer. Se tudo estiver funcionando como deveria, deve aparecer programagicamente o número 10, conforme previsto em nossa última postagem (http://xek.me/?p=1099).

Acabamos de criar, conforme prometido, uma função para cálculo de Combinação com Repetição.

Em nossa próxima postagem veremos como fazer funções que permitam usar intervalos como argumentos ou entrar com número variável de argumentos, exatamente como faz a função SOMA do Excel e assim poderemos implementar a PERMUTAÇÃO com ELEMENTOS REPETIDOS.