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
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.
Clique na guia Desenvolvedor e no botão Visual Basic.
Na estrutura da esquerda selecione aberta.
Clique no menu em Inserir->Módulo.
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.
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.
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
Clique no ícone do Excel e depois no botão Opções.
Clique no botão Suplementos.
Selecione no botão Gerenciar o tipo Suplementos do Excel e clique no botão Ir.
Selecione Minhas funções na janela que abre e clique no botão OK.
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.
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.
Veja a lista que temos, queremos pegar somente o primeiro e o último nome de cada indivíduo e criar um nome resumido.
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.Perceba que a função já está constando na lista, inclusive com a descrição que criamos.
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.
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.
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.
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.
Veja que os dados são automaticamente preenchidos com o uso da função.
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.
Lempre de fazer a inserção de outras funções próprias VBA Excel sempre que necessário no arquivo de suplemento criado.
