Capítulo 12: Criação de Funções Personalizadas de Planilha (UDFs)

🎯 Objetivo da Aula

Nesta aula, você aprenderá a estender a biblioteca nativa do Microsoft Excel construindo Funções Definidas pelo Usuário (User Defined Functions - UDFs). Você compreenderá as diferenças conceituais e operacionais entre uma rotina Sub e uma Function, entenderá as restrições de segurança aplicadas às fórmulas de planilha e construirá fórmulas exclusivas para a equipe comercial da FastLog, prontas para serem digitadas diretamente nas células do Excel com o sinal de igual (=).

Ao final desta aula, você terá implementado as fórmulas corporativas oficiais =CALCULARFRETE() e =VALIDARCNPJ().

🏢 Cenário Prático (FastLog S.A.)

Empresa: FastLog Transportes & Logística Integrada S.A.
Problema: Os consultores de vendas da FastLog enviam dezenas de cotações para clientes corporativos diariamente. Para calcular o frete comercial, eles precisavam colar na célula do Excel uma fórmula quilométrica com 6 funções SE, E, PROCV e multiplicações tarifárias. Frequentemente, os vendedores apagavam um parêntese por engano, gerando o erro #VALOR! na frente do cliente ou, pior, cobrando fretes com 50% de prejuízo. Além disso, clientes digitavam CNPJs falsos ou com erros de dígito verificador na planilha de cadastro.
Sua Missão: Como desenvolvedor de soluções do Excel, você programará funções personalizadas no VBA para que qualquer colaborador da empresa possa simplesmente digitar na célula:
=CALCULARFRETE(C2; D2; E2) e =VALIDARCNPJ(B2), simplificando a operação e blindando os cálculos matemáticos da companhia.

🧠 Fundamentos: A Teoria Traduzida

1. Sub vs. Function: A Diferença Crucial

flowchart TD
    subgraph SUB["Procedimento Sub"]
        S1["Executa uma Ação / Rotina"]
        S2["Pode formatar células, criar abas, pintar telas"]
        S3["NÃO retorna valor para fórmulas de células"]
    end

    subgraph FUNC["Função Function (UDF)"]
        F1["Calcula e RETORNA um valor digitado"]
        F2["Pode ser usada dentro de células: =MINHAFUNCAO()"]
        F3["RESTRIÇÃO: Não pode alterar outras células ou formatar cores!"]
    end

    style SUB fill:#1a365d,stroke:#fff,stroke-width:2px,color:#fff
    style FUNC fill:#217346,stroke:#fff,stroke-width:2px,color:#fff
    style S1 fill:#2c3e50,stroke:#fff,stroke-width:2px,color:#fff
    style F1 fill:#27ae60,stroke:#fff,stroke-width:2px,color:#fff

2. A Mecânica de Retorno de uma Function

Para que uma função devolva o resultado calculado para a célula da planilha, você deve atribuir o valor final ao próprio nome da função:

1
2
3
4

Public Function Dobro(ByVal numero As Double) As Double
    ' Atribuição ao próprio nome da função define o retorno
    Dobro = numero * 2
End Function

3. Restrições Estritas de uma UDF de Planilha

Quando uma função do VBA é executada a partir de uma fórmula em uma célula (ex: =CALCULARFRETE(...)), o motor de cálculo do Excel impõe regras rígidas de segurança (Sandbox):

  • PODE: Ler valores de argumentos, realizar cálculos matemáticos, processar strings e retornar um resultado tipado (String, Double, Currency, Boolean).
  • NÃO PODE: Alterar o valor de outra célula, mudar a cor de fundo de uma célula, inserir novas abas ou apagar arquivos do disco. (Se tentar, o Excel retorna o erro #VALOR!).

📖 Exemplo Guiado: Passo a Passo no Laboratório

No Excel, abra o VBE com Alt + F11.

Passo 1: Inserção do Módulo de Funções Personalizadas

  1. No menu superior, clique em Inserir ➔ Módulo.
  2. Na janela de propriedades (F4), renomeie o módulo para: modFuncoesUDF.
  3. Confirme a presença de Option Explicit.

Passo 2: Teste de Uma Fórmula Simples

Digite no módulo a função de teste:

1
2
3

Public Function SomarTaxaExpedicao(ByVal curValor As Currency) As Currency
    SomarTaxaExpedicao = curValor + 35
End Function

Volte ao Excel. Em qualquer célula vazia (ex: A1), digite:

1

=SomarTaxaExpedicao(100)

Pressione Enter. A célula exibirá imediatamente: 135! Você acabou de criar sua primeira fórmula no Excel!

🛠️ Prática Obrigatória 1: Criação da Fórmula Oficial =CALCULARFRETE()

Construiremos a UDF corporativa que recebe a distância em km, o peso da carga em kg e a categoria do transporte, retornando o valor final do frete comercial.

Passo a Passo no Computador:

  1. No módulo modFuncoesUDF, digite a função completa:

     1
 2
 3
 4
 5
 6
 7
 8
 9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43

    Option Explicit

Public Function CALCULARFRETE(ByVal dblDistanciaKm As Double, _
                              ByVal dblPesoKg As Double, _
                              ByVal strTipoCarga As String) As Variant
    Dim curTarifaBase As Currency
    Dim curAdicionalPeso As Currency
    Dim curFatorRisco As Currency
    Dim curTotal As Currency

    ' Validação de argumentos para evitar valores negativos
    If dblDistanciaKm <= 0 Or dblPesoKg <= 0 Then
        CALCULARFRETE = CVErr(xlErrNum) ' Retorna o erro #NÚM! do Excel
        Exit Function
    End If

    ' 1. Custo por Quilômetro Rodado (R$ 3,80/km)
    curTarifaBase = dblDistanciaKm * 3.8

    ' 2. Custo por Tonelada (R$ 0,15 por kg)
    curAdicionalPeso = dblPesoKg * 0.15

    ' 3. Fator de Categoria e Risco Operacional
    Select Case UCase(Trim(strTipoCarga))
        Case "PERIGOSA", "QUIMICA"
            curFatorRisco = 1.35   ' Acréscimo de 35%
        Case "REFRIGERADA"
            curFatorRisco = 1.2    ' Acréscimo de 20%
        Case "VALIOSA"
            curFatorRisco = 1.5    ' Acréscimo de 50%
        Case "PADRAO", "SECA"
            curFatorRisco = 1#     ' Sem sobretaxa
        Case Else
            CALCULARFRETE = "CATEGORIA INVÁLIDA"
            Exit Function
    End Select

    ' Cálculo Final
    curTotal = (curTarifaBase + curAdicionalPeso) * curFatorRisco

    ' Retorna o valor calculado com precisão monetária
    CALCULARFRETE = Round(curTotal, 2)
End Function

  2. Utilização Prática Diretamente na Planilha:

    • Alterne para o Excel.
    • Na linha 1, monte a simulação de cotações:
      • A1: Origem_Destino | B1: Distancia_Km | C1: Peso_Kg | D1: Tipo_Carga | E1: Frete_Calculado_UDF
    • Na linha 2, insira os valores:
      • A2: SP -> Curitiba | B2: 410 | C2: 8500 | D2: REFRIGERADA
    • Na célula E2, digite a fórmula criada:
      1

      =CALCULARFRETE(B2; C2; D2)
    • Pressione Enter.
    • Veja o resultado calculado instantaneamente pela sua própria fórmula do VBA: R$ 3.400,00! (Cálculo: (410 * 3,80 + 8500 * 0,15) * 1,20 = (1558 + 1275) * 1,20 = 2833 * 1,20 = 3399,60 ➔ R$ 3.400,00).

✅ Resultado Esperado (Prática 1):

A fórmula =CALCULARFRETE() funciona de forma idêntica às funções nativas do Excel, com suporte a arrastar a alça de preenchimento para as linhas de baixo.

🛠️ Prática Obrigatória 2: Criação da UDF de Validação de Documentos =VALIDARCNPJ()

Agora criaremos uma UDF de conformidade fiscal e sanitização que valida o tamanho e os caracteres básicos de um CNPJ empresarial, retornando um valor lógico (VERDADEIRO ou FALSO).

Passo a Passo no Computador:

  1. No mesmo módulo modFuncoesUDF, adicione a seguinte função:
     1
 2
 3
 4
 5
 6
 7
 8
 9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37

    Public Function VALIDARCNPJ(ByVal varCNPJ As Variant) As Boolean
    Dim strLimpo As String
    Dim i As Long
    Dim strChar As String

    ' Trata casos de células em branco
    If IsMissing(varCNPJ) Or IsEmpty(varCNPJ) Then
        VALIDARCNPJ = False
        Exit Function
    End If

    strLimpo = ""

    ' Remove pontos, barras e traços mantendo apenas números
    For i = 1 To Len(CStr(varCNPJ))
        strChar = Mid(CStr(varCNPJ), i, 1)
        If IsNumeric(strChar) Then
            strLimpo = strLimpo & strChar
        End If
    Next i

    ' Um CNPJ brasileiro deve conter obrigatoriamente 14 dígitos numéricos
    If Len(strLimpo) <> 14 Then
        VALIDARCNPJ = False
        Exit Function
    End If

    ' Rejeita sequências conhecidas de dígitos repetidos falsos
    Select Case strLimpo
        Case "00000000000000", "11111111111111", "99999999999999"
            VALIDARCNPJ = False
            Exit Function
    End Select

    ' Retorna True se o documento passar na triagem estrutural
    VALIDARCNPJ = True
End Function
  2. Teste na Planilha:
    • Na célula G2, digite um CNPJ com máscara: "45.892.104/0001-33".
    • Na célula H2, digite a fórmula:
      1

      =VALIDARCNPJ(G2)
    • Pressione Enter. O Excel exibirá: VERDADEIRO.
    • Teste digitar um CNPJ incompleto (ex: "12.345/0001"): a fórmula mudará dinamicamente para FALSO!

✅ Resultado Esperado (Prática 2):

A função valida os cadastros em tempo real na planilha, podendo ser combinada com formatações condicionais nativas do Excel.

📤 Instruções de Entrega (Microsoft Teams)

  1. Salve o arquivo como: Atividade_12_VBA_SeuNome_SeuSobrenome.xlsm.
  2. Acesse a plataforma Microsoft Teams.
  3. Abra a tarefa: “VBA - Cap 12: Funções Personalizadas (UDFs)”.
  4. Anexe a planilha e envie.

💡 Checkpoint de Gestão & TI

As UDFs representam um dos maiores trunfos de produtividade para analistas de negócios. Elas encapsulam políticas de precificação e regras tributárias complexas dentro do código-fonte, impedindo que usuários finais vejam ou alterem fórmulas confidenciais. Além disso, quando uma regra tributária muda (por exemplo, a alíquota de risco passa de 35% para 38%), a alteração de uma única linha na função UDF atualiza instantaneamente centenas de planilhas de vendedores em toda a empresa.

🔥 Desafio de Fixação: Previsão de Entrega Rodoviária

Crie a UDF corporativa =PREVISAOENTREGA(distanciaKm; dataInicio):

  1. Assinatura: Public Function PREVISAOENTREGA(ByVal dblKm As Double, ByVal dtmInicio As Date) As Date.
  2. Regra da Lei do Motorista: Uma carreta pode percorrer no máximo 650 km por dia útil.
  3. Calcule a quantidade de dias necessários com a divisão inteira: lngDias = Application.WorksheetFunction.RoundUp(dblKm / 650, 0).
  4. Retorne a data somando os dias calculados (dtmInicio + lngDias).
  5. Teste na planilha prevendo a data de entrega de uma viagem de 1.800 km com saída hoje.
🔑 Revelar Gabarito Oficial de Laboratório (Clique para expandir)
💡 Incentivo Pedagógico: Tente resolver as práticas e o desafio de fixação por conta própria antes de abrir o gabarito. Seus erros e tentativas durante o laboratório são a chave para a consolidação real do aprendizado!

Código Consolidado (modFuncoesUDF):

 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79

Option Explicit

Public Function CALCULARFRETE(ByVal dblDistanciaKm As Double, _
                             ByVal dblPesoKg As Double, _
                             ByVal strTipoCarga As String) As Variant
    Dim curTarifaBase As Currency
    Dim curAdicionalPeso As Currency
    Dim curFatorRisco As Currency
    Dim curTotal As Currency
    
    If dblDistanciaKm <= 0 Or dblPesoKg <= 0 Then
        CALCULARFRETE = CVErr(xlErrNum)
        Exit Function
    End If
    
    curTarifaBase = dblDistanciaKm * 3.8
    curAdicionalPeso = dblPesoKg * 0.15
    
    Select Case UCase(Trim(strTipoCarga))
        Case "PERIGOSA", "QUIMICA"
            curFatorRisco = 1.35
        Case "REFRIGERADA"
            curFatorRisco = 1.2
        Case "VALIOSA"
            curFatorRisco = 1.5
        Case "PADRAO", "SECA"
            curFatorRisco = 1#
        Case Else
            CALCULARFRETE = "CATEGORIA INVÁLIDA"
            Exit Function
    End Select
    
    curTotal = (curTarifaBase + curAdicionalPeso) * curFatorRisco
    CALCULARFRETE = Round(curTotal, 2)
End Function

Public Function VALIDARCNPJ(ByVal varCNPJ As Variant) As Boolean
    Dim strLimpo As String
    Dim i As Long
    Dim strChar As String
    
    If IsMissing(varCNPJ) Or IsEmpty(varCNPJ) Then
        VALIDARCNPJ = False
        Exit Function
    End If
    
    strLimpo = ""
    For i = 1 To Len(CStr(varCNPJ))
        strChar = Mid(CStr(varCNPJ), i, 1)
        If IsNumeric(strChar) Then
            strLimpo = strLimpo & strChar
        End If
    Next i
    
    If Len(strLimpo) <> 14 Then
        VALIDARCNPJ = False
        Exit Function
    End If
    
    If strLimpo = "00000000000000" Or strLimpo = "11111111111111" Then
        VALIDARCNPJ = False
        Exit Function
    End If
    
    VALIDARCNPJ = True
End Function

' Desafio de Fixação: Previsão Logística
Public Function PREVISAOENTREGA(ByVal dblKm As Double, ByVal dtmInicio As Date) As Date
    Dim lngDias As Long
    If dblKm <= 0 Then
        PREVISAOENTREGA = dtmInicio
        Exit Function
    End If
    
    ' 650 km por dia limite
    lngDias = -Int(-dblKm / 650) ' Arredondamento para cima
    PREVISAOENTREGA = dtmInicio + lngDias
End Function

📝 Atividade Extra: Questionário de Fixação (Caderno)

Instruções: Responda no caderno, de próprio punho, as 10 perguntas abaixo com base no que você estudou neste capítulo. Ao concluir, apresente o caderno ao professor para visto.

  1. O que representa a sigla UDF e como uma função personalizada é utilizada na interface do Excel?
  2. Qual é a diferença fundamental entre um procedimento Sub e um procedimento Function no VBA?
  3. Como o valor de resposta é devolvido ao Excel dentro do corpo de uma Function?
  4. Quais são as restrições impostas pelo Excel a uma UDF quando chamada a partir de uma fórmula em célula?
  5. Uma UDF pode alterar a cor de fundo de uma célula vizinha ou deletar uma linha? Explique.
  6. O que a instrução CVErr(xlErrNum) provoca na célula que executou a fórmula?
  7. Como a função VALIDARCNPJ processa números que contenham máscaras com pontos, traços e barras?
  8. Qual é a principal vantagem de segurança corporativa ao substituir fórmulas longas por UDFs em planilhas de vendas?
  9. Como o modificador Public na frente da palavra Function influencia a disponibilidade da fórmula na planilha?
  10. No Desafio de Fixação, qual fórmula matemática foi aplicada para calcular a quantidade de dias de viagem rodoviária?