Como criar funções próprias no Excel com VBA

Como criar funções próprias no Excel com VBA

Neste treinamento você verá passo-a-passo como criar funções próprias no Excel com VBA, ou seja, funções como as que existem no Excel, como o SOMA ou o PROCV, mas com novas funcionalidades.

A função que utilizaremos como exemplo é uma função de texto que retornará a posição de determinado caractere a partir da ocorrência X em um texto, ou seja, se estiver procurando a segunda ocorrência de um espaço em um texto ele retorna esta posição.

Esta função pode ser muito útil para situações em que seja necessário realizar tratamentos de texto de arquivos como extratos bancários e relatórios de empresas com o objetivo de extrair determinadas informações.

No nosso exemplo usaremos esta função para pegar o primeiro e o último nome dos colaboradores de uma empresa.

Isto também pode ser feito, á partir do Excel 2013, com o preenchimento inteligente, mas neste caso teria que sempre arrastar os dados, e na função ele já estaria pronto, além de poder ser utilizado em muitas outras situações.

Funcionamento do preenchimento relâmpago

Preenchimento relâmpago Excel

Criando estrutura para as suas funções próprias VBA Excel

Para colocar nossas funções próprias nós iremos criar um suplemento do Excel.

  1. Abra o Excel com uma planilha em branco.
  2. Caso não esteja habilitada a sua guia desenvolvedor habilite-a, veja neste artigo como: http://guiadoexcel.com.br/habilitar-guia-desenvolvedor-no-excel-e-copiar-procedimentos-vba.
  3. Clique na guia Desenvolvedor e no botão Visual Basic.
  4. Na estrutura da esquerda selecione aberta.
  5. Clique no menu em Inserir->Módulo.
  6. Abra este novo arquivo criado clicando duas vezes e cole o código seguinte. Este é o código da nossa função própria que identifica a posição de um carácter em um texto de uma célula á partir de determinada ocorrência.
'Retorna a posição de uma caractere conforme a ocorrência
Public Function POSICAO(ByVal texto_procurado As String, ByVal no_texto As String, Optional ByVal Ocorrencia As Long = 1, Optional ByVal ultima_ocorrencia As Boolean) As Long
    Dim lContador As Long
    Dim lQtdeOcor As Long
    
    lQtdeOcor = 0
    lContador = 1
    
    While lContador <= Len(no_texto)
        If Mid(no_texto, lContador, 1) = texto_procurado Then
            lQtdeOcor = lQtdeOcor + 1
            
            If lQtdeOcor = Ocorrencia And ultima_ocorrencia = False Then
                POSICAO = lContador
                Exit Function
            Else
                POSICAO = lContador
            End If
        End If
        
        lContador = lContador + 1
    Wend
End Function

7. Agora vamos inserir a linha que define a categoria da função, de modo que ela fique corretamente organizada nas funções.

Sub lsDefinirCategoria()
    Application.MacroOptions Macro:="POSICAO", Category:=7
End Sub

No lugar do campo 7 escolha a categoria para a sua função conforme a lista abaixo:

0-Sem categoria
1-Financeira
2-Data e Hora
3-Matemática e Trigonometria
4-Estatística
5-Procura e Referência
6-Banco de Dados
7-Texto
8-Lógico
9-Informação

Após isso clique sobre esta função e pressione a tecla F5 ou o botão Executar para registrar a função, depois pode apagar estas linhas inseridas por último.

Criar funções próprias Excel VBA

8. Definiremos agora uma descrição para a função criada. Para isso clique em qualquer lugar do código da função POSICAO e pressione a tecla F2.

9. Digite a descrição para a sua função e clique em OK.

Criar funções próprias Excel VBA 2

10. Feche o VBE, ambiente do VBA, e volte para o Excel.

11. Salve a sua planilha com o nome de Funções Próprias e no campo Salvar como tipo, altere para Suplemento do Excel(*.xlam).

12. Importante, caso queira que a função fique em uma única planilha você só precisa salvar ela com a extensão xlsm, habilitada para macros, ficando assim disponível somente para esta planilha.

A sua função já está pronta para uso.

Utilizando a função

Se você escolheu salvar a sua função como suplemento, salvando-a como xlam, é importante salientar que a função só estará disponível no seu Excel, caso queira distribuí-la para outra pessoa, será necessário incluir nesta planilha, para tanto siga o passo 12 anterior do subtítulo anterior.

Para habilitar o suplemento

  1. Clique no ícone do Excel e depois no botão Opções.
  2. Clique no botão Suplementos.
  3. Selecione no botão Gerenciar o tipo Suplementos do Excel e clique no botão Ir.
    Criar funções próprias Excel VBA 3 - Suplemento no Excel
  4. Selecione Minhas funções na janela que abre e clique no botão OK.
    Criar funções próprias Excel VBA 4 - Suplemento no Excel
  5. Pronto a sua função está pronta para uso, se quiser pode incluir quantas você quiser dentro de um mesmo suplemento, bastado abrir o mesmo e inserir da mesma forma como explicado neste artigo.

Utilizando a função criada

Esta função foi criada para resolver um problema de um arquivo que é rodado mensalmente pelo departamento de RH da empresa que utiliza a versão 2010 do Excel e precisava extrair o primeiro e o último nome de todos da lista enviada mensalmente com mais de 1000 nomes para enviar ao plano de saúde.

A função original criada foi a seguinte:

=ESQUERDA(B2;LOCALIZAR(” “;B2;1))&SEERRO(EXT.TEXTO(B2;LOCALIZAR(” “;B2;LOCALIZAR(” “;B2;LOCALIZAR(” “;B2;LOCALIZAR(” “;B2;LOCALIZAR(” “;B2;LOCALIZAR(” “;B2;1)+1)+1)+1)+1)+1)+1;100);SEERRO(EXT.TEXTO(B2;LOCALIZAR(” “;B2;LOCALIZAR(” “;B2;LOCALIZAR(” “;B2;LOCALIZAR(” “;B2;LOCALIZAR(” “;B2;1)+1)+1)+1)+1)+1;100);SEERRO(EXT.TEXTO(B2;LOCALIZAR(” “;B2;LOCALIZAR(” “;B2;LOCALIZAR(” “;B2;LOCALIZAR(” “;B2;1)+1)+1)+1)+1;100);SEERRO(EXT.TEXTO(B2;LOCALIZAR(” “;B2;LOCALIZAR(” “;B2;LOCALIZAR(” “;B2;1)+1)+1)+1;100);SEERRO(EXT.TEXTO(B2;LOCALIZAR(” “;B2;LOCALIZAR(” “;B2;1)+1)+1;100);SEERRO(EXT.TEXTO(B2;LOCALIZAR(” “;B2;1)+1;100);””))))))

Complicada e bastante limitada, nela basicamente é localizada a posição do último espaço, campo que separa os nomes, e depois retornado o sobrenome.

Então criei a seguinte função que explicamos neste artigo como utilizar, para resolver o problema.

  1. Veja a lista que temos, queremos pegar somente o primeiro e o último nome de cada indivíduo e criar um nome resumido.
    Criar funções próprias Excel VBA 5 - função procurar excel
  2. Vá na guia Fórmulas, botão Texto, e selecione a nossa função criada POSICAO, que irá identificar a ocorrência de determinado carácter no Excel utilizando nossa função própria VBA.
    Função posicao excel vba função própria
    Perceba que a função já está constando na lista, inclusive com a descrição que criamos.
  3. Clicando sobre ela é aberta a janela para que sejam digitados os argumentos da nossa função de busca da posição de determinada letra no Excel com VBA.
    Função posicao excel vba função própria 1
  4. No texto procurado pressione a tecla espaço, No_texto selecione a célula A2, no campo ocorrência digitaríamos o número da ocorrência que queremos identificar a posição, mas no nosso caso colocaremos 1 no parâmetro Ultima_ocorrencia para que seja identificada a posição da última ocorrência do espaço no texto.
    Função posicao excel vba função própria 2
  5. Clique em OK, veja que é retornado um número, esta é a posição do seu caracter na string de texto. Arrastando você percebe que ele sempre mostrará a posição do último carácter de espaço.
  6. Agora utilize a função =ESQUERDA(A2;POSICAO(” “;A2)-1)&” “&EXT.TEXTO(A2;POSICAO(” “;A2;;1)+1;100) no lugar da sua função digitada na B2 e arraste para as demais células.
  7. Veja que os dados são automaticamente preenchidos com o uso da função.
  8. Veja que se não for digitado nada no campo de ocorrência, será retornada a posição da primeira ocorrência do texto espaço, e se colocar o número 1 para retornar posição da última ocorrência do espaço no texto.
  9. Lempre de fazer a inserção de outras funções próprias VBA Excel sempre que necessário no arquivo de suplemento criado.
Baixe a planilha

Abraço

Marcos Rieper

Curso Excel Completo – Do Básico ao VBA

Quer aprender Excel do Básico, passando pela Avançado e chegando no VBA? Clique na imagem abaixo:


Marcos Rieper

Pai, marido, professor e consultor em Excel.

Obrigado por ler este artigo, este blog foi criado para difundir o conhecimento em Excel à todos.

Divulgamos novos artigos nas redes sociais, basta clicar nos ícones abaixo.

Excel não precisa ser complicado

Assine nossa newsletter e receba dicas práticas para dominar o excel