Capítulo 20: Projeto Integrador Final - Arquitetura de Sistemas com Excel e VBA

🎯 Objetivo da Aula

Parabéns! Você chegou ao ápice da sua formação técnica em Lógica de Programação e Automação. Você percorreu uma jornada completa: desde a compreensão de células e variáveis até a escrita de laços matriciais em VBA.

O objetivo desta aula de encerramento não é introduzir comandos isolados, mas sim integrar todos os conceitos no formato de um Sistema de Gestão Logística Completo (FastLog TMS 1.0), aplicando princípios de arquitetura de software:

1. O padrão de Arquitetura em Três Camadas (3-Tier Architecture) em planilhas.
2. Variáveis de Objeto e a palavra-chave Set, a base para manipular várias abas ao mesmo tempo.
3. Manipulação de Múltiplas Abas via VBA (Transferência de dados do Formulário para a Base).
4. Geração automática de Chaves Primárias (IDs Auto-incremento).
5. Tratamento de Exceções e Erros com On Error GoTo.
6. Segurança e Proteção Programática de código e planilhas.

🏢 O Cenário Prático (Seu Desafio Final)

Situação: A diretoria da FastLog necessita de uma ferramenta centralizada para o Centro de Controle Operacional (CCO). Eles não querem mais que os operadores manipulem a tabela de dados brutos diretamente (risco de corromper fórmulas ou apagar registros).

Missão: Construir o sistema integrado FastLog TMS, composto por:

1. Aba Painel (Dashboard & Consulta): Busca inteligente por placa (PROCX), semáforo visual de entrega (Formatação Condicional) e resumo por filial (Tabela Dinâmica).
2. Aba Formulario (Interface de Entrada): Validação de dados (Listas suspensas) e um botão VBA que transfere o novo frete para a base de dados, limpa os campos e gera o próximo ID automaticamente.
3. Aba Banco_Dados (Persistência): Armazenamento seguro e protegido contra alterações manuais indevidas.

🧠 Fundamentos: Arquitetura de Sistemas em Excel/VBA

1. O Padrão Arquitetural em 3 Camadas (3-Tier Pattern)

No desenvolvimento de software corporativo (como sistemas ERP, bancários e e-commerces), a regra número um é a Separação de Responsabilidades (Separation of Concerns). Nunca misturamos a tela visual com a base de dados pura.

graph TD
    subgraph Camada_Apresentacao ["1. Camada de Apresentação (Front-end / UI)"]
        UI_Form["Aba 'Formulario'<br/>(Coleta com Validação)"]
        UI_Dash["Aba 'Painel'<br/>(KPIs, Gráficos e PROCX)"]
    end

    subgraph Camada_Logica ["2. Camada de Lógica de Negócio (VBA Engine)"]
        VBA_Val["Validação de Campos Obrigatórios"]
        VBA_ID["Geração de Chave Primária (ID)"]
        VBA_Err["Tratamento de Erros (On Error)"]
    end

    subgraph Camada_Dados ["3. Camada de Persistência (Back-end / Database)"]
        DB_Table["Aba 'Banco_Dados'<br/>(Matriz Tabular Pura e Blindada)"]
    end

    UI_Form -->|1. Clica em 'Salvar'| VBA_Val
    VBA_Val -->|2. Valida Dados| VBA_ID
    VBA_ID -->|3. Append na Próxima Linha| DB_Table
    DB_Table -->|4. Atualiza Consultas| UI_Dash
    
    style Camada_Apresentacao fill:#2980b9,stroke:#fff,stroke-width:2px,color:#fff
    style Camada_Logica fill:#8e44ad,stroke:#fff,stroke-width:2px,color:#fff
    style Camada_Dados fill:#217346,stroke:#fff,stroke-width:2px,color:#fff

2. Variáveis de Objeto e a Palavra-Chave Set

Até agora (Cap. 18), só declaramos variáveis de tipos primitivos: Dim valorCarga As Double, Dim categoria As String. Esses tipos guardam um valor simples (um número, um texto).

Quando o projeto tem várias abas (Formulario, Banco_Dados, Painel), digitar Worksheets("Banco_Dados") inteiro em toda linha de código fica repetitivo e propenso a erro de digitação. A solução é guardar a própria referência à aba em uma variável — e para isso, declaramos um tipo de dado de objeto, como Worksheet:

1
2
3
4
5

Dim wsDB As Worksheet
Set wsDB = Worksheets("Banco_Dados")

' Agora "wsDB" pode ser usado no lugar de "Worksheets(\"Banco_Dados\")" em qualquer linha:
wsDB.Cells(1, 1).Value = "ID"

3. Manipulação de Múltiplas Abas via VBA

Quando o código precisa transferir um valor digitado na aba Formulario para a aba Banco_Dados, referenciamos explicitamente o objeto pai — usando Set, como acabamos de aprender, para guardar cada aba em sua própria variável:

 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11

Dim wsForm As Worksheet
Dim wsDB As Worksheet
Set wsForm = Worksheets("Formulario")
Set wsDB = Worksheets("Banco_Dados")

' Lê da aba de cadastro (.Value devolve um texto simples, não um objeto — não precisa de Set aqui)
Dim destino As String
destino = wsForm.Range("B4").Value

' Escreve na aba de banco de dados
wsDB.Cells(proxLinha, 2).Value = destino

4. O Algoritmo de Inserção na Próxima Linha Vazia (Append Record)

Para adicionar um novo registro sem sobrescrever os existentes, calculamos a primeira linha disponível:

1
2

Dim proxLinha As Long
proxLinha = Worksheets("Banco_Dados").Cells(Rows.Count, 1).End(xlUp).Row + 1

E para gerar a Chave Primária (ID) sequencial:

1
2
3

Dim novoID As Long
novoID = proxLinha - 1 ' Supondo que a linha 1 é o cabeçalho
Worksheets("Banco_Dados").Cells(proxLinha, 1).Value = novoID

5. Tratamento Profissional de Exceções (On Error GoTo)

Em linguagens como Java, Python e C#, usamos blocos try / catch / finally. No VBA, a robustez é implementada com a instrução On Error GoTo:

 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11
12
13
14
15
16
17
18
19
20

Sub RotinaBlindada()
    ' Redireciona para o rótulo de erro caso ocorra qualquer falha inesperada
    On Error GoTo TrataErro
    
    ' Desliga atualização de tela
    Application.ScreenUpdating = False
    
    ' [CÓDIGO CRÍTICO AQUI]
    
    ' Se tudo correu bem, restaura o Excel e sai sem passar pelo tratamento de erro
    Application.ScreenUpdating = True
    Exit Sub

TrataErro:
    ' Bloco de Escape / Recuperação de Falhas
    Application.ScreenUpdating = True
    MsgBox "Ocorreu uma falha durante o processamento:" & vbCrLf & _
           "Erro #" & Err.Number & " - " & Err.Description, _
           vbCritical, "Falha de Execução"
End Sub

📖 Exemplo Guiado: A Estrutura das Três Abas

Passo a Passo

1. Crie uma nova Pasta de Trabalho e salve como: FastLog_TMS_Final.xlsm.
2. Crie 3 abas e renomeie-as exatamente como:
   • Painel (Cor da guia: Azul)
   • Formulario (Cor da guia: Verde)
   • Banco_Dados (Cor da guia: Cinza)
3. Na aba Banco_Dados, monte o cabeçalho oficial na linha 1 (A1:E1):
   • ID | Destino | Placa | Valor_Frete | Status
4. Preencha 2 linhas iniciais para termos massa de dados:
   • Linha 2: 1 | Rio de Janeiro - RJ | ABC-1234 | 1850 | Entregue
   • Linha 3: 2 | Curitiba - PR | XYZ-9876 | 3200 | Em Trânsito

🛠️ Prática Obrigatória 1: Construindo o Front-end de Consulta (Aba Painel)

Passo 1: O Painel de Rastreamento Rápido

Na aba Painel, configure a área de consulta do gestor (A2 até B5):

• A2: === RASTREAMENTO OPERACIONAL ===
• A4: Selecione a Placa:
• B4: (Aplique Validação de Dados > Lista > Fonte: =Banco_Dados!$C$2:$C$10)
• A5: Status da Carga:
• B5: =PROCX(B4; Banco_Dados!C:C; Banco_Dados!E:E; "Placa Não Localizada")

Passo 2: O Sinalizador Visual (Semáforo)

1. Selecione a célula B5.
2. Vá em Formatação Condicional > Regras de Realce das Células > Texto que Contém….
3. Se contiver Entregue, formate com preenchimento Verde Claro.
4. Se contiver Em Trânsito, formate com preenchimento Amarelo Claro.

🛠️ Prática Obrigatória 2: O Robô de Cadastro e Persistência em VBA

Passo 1: A Interface de Entrada (Aba Formulario)

Na aba Formulario, monte os campos de entrada nas células A3 até B6:

• A3: Destino do Frete: | B3: (Digite Campinas - SP)
• A4: Placa do Veículo (7 dígitos): | B4: (Digite BRA-2E19)
• A5: Valor do Frete (R$): | B5: (Digite 2150)
• A6: Status Inicial: | B6: (Selecione Em Trânsito via Lista Suspensa)

Passo 2: O Código do Motor de Persistência

Abra o VBE (Alt + F11), insira um Módulo e digite o script completo de transferência de dados:

 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

Option Explicit

Sub SalvarNovoFrete()
    On Error GoTo TrataErro
    
    Dim wsForm As Worksheet
    Dim wsDB As Worksheet
    Dim proxLinha As Long
    Dim novoID As Long
    
    Set wsForm = Worksheets("Formulario")
    Set wsDB = Worksheets("Banco_Dados")
    
    ' 1. Validação de Integridade (Campos Obrigatórios)
    If Trim(wsForm.Range("B3").Value) = "" Or _
       Trim(wsForm.Range("B4").Value) = "" Or _
       Trim(wsForm.Range("B5").Value) = "" Then
       
        MsgBox "Atenção: Todos os campos do formulário são de preenchimento obrigatório!", _
               vbExclamation, "Dados Incompletos"
        Exit Sub
    End If
    
    ' 2. Otimização de Performance
    Application.ScreenUpdating = False
    
    ' 3. Localiza a próxima linha livre na Base
    proxLinha = wsDB.Cells(wsDB.Rows.Count, 1).End(xlUp).Row + 1
    novoID = proxLinha - 1
    
    ' 4. Transfere os dados (Gravando no Banco de Dados)
    wsDB.Cells(proxLinha, 1).Value = novoID
    wsDB.Cells(proxLinha, 2).Value = wsForm.Range("B3").Value
    wsDB.Cells(proxLinha, 3).Value = UCase(Trim(wsForm.Range("B4").Value))
    wsDB.Cells(proxLinha, 4).Value = CDbl(wsForm.Range("B5").Value)
    wsDB.Cells(proxLinha, 5).Value = wsForm.Range("B6").Value
    
    ' Formata a coluna de valor como Moeda
    wsDB.Cells(proxLinha, 4).NumberFormat = "R$ #,##0.00"
    
    ' 5. Limpa os campos do formulário para o próximo uso
    wsForm.Range("B3:B5").ClearContents
    wsForm.Range("B6").Value = "Em Trânsito"
    wsForm.Range("B3").Select
    
    Application.ScreenUpdating = True
    
    ' 6. Feedback de Sucesso ao Operador
    MsgBox "Frete registrado com sucesso!" & vbCrLf & _
           "• Protocolo/ID: #" & novoID, _
           vbInformation, "FastLog TMS"
    Exit Sub

TrataErro:
    Application.ScreenUpdating = True
    MsgBox "Erro ao salvar frete: " & Err.Description, vbCritical, "Erro de Sistema"
End Sub

Passo 3: Vinculando o Botão de Ação

Na aba Formulario, insira um botão verde estilizado 💾 GRAVAR NOVO FRETE na célula B8 e atribua a macro SalvarNovoFrete.

✅ Teste de Ponta a Ponta

1. Preencha o formulário e clique em Gravar.
2. Vá até a aba Banco_Dados e veja a linha 4 preenchida automaticamente com o ID 3.
3. Vá até a aba Painel, selecione a placa BRA-2E19 e veja o status ser recuperado instantaneamente!

📤 Instruções de Entrega (Microsoft Teams)

1. Salve o arquivo no formato .XLSM (Habilitado para Macro).
2. Nome: PROJETO_FINAL_SeuNome_SeuSobrenome.xlsm
3. No Microsoft Teams, envie na tarefa “Capítulo 20 - Projeto Integrador Final”.
4. Este projeto consolida todas as 20 aulas e compõe a avaliação prática do módulo de tecnologia.

💡 Checkpoint de Lógica: A Jornada do Aprendizado

Você construiu um Software Operacional Completo. Você não é mais alguém que apenas “sabe mexer em planilhas”; você compreende:

• Modelagem de Dados: Como estruturar tabelas relacionais.
• Algoritmos e Decisões: Como encadear testes com SE, PROCX e If/Else.
• Programação Orientada a Objetos: Como navegar e alterar estados de instâncias (Worksheets, Range, Cells).
• Engenharia de Software: Como separar telas visuais, regras de negócio e armazenamento em camadas isoladas.

graph LR
    A["Semana 1:<br/>O que é Célula?"] --> B["Semana 5:<br/>Funções SE e Lógica"]
    B --> C["Semana 11:<br/>Buscas PROCX e Dados"]
    C --> D["Semana 18:<br/>Código VBA e Memória"]
    D --> E["Semana 20:<br/>Arquiteto de Soluções TMS!"]
    
    style E fill:#f1c40f,stroke:#333,stroke-width:2px,color:#333

🔥 Desafio de Fixação: Blindagem e Proteção com Senha

Adicione duas macros de segurança no seu projeto para travar a aba Banco_Dados, impedindo que curiosos alterem os registros diretamente.

O argumento nomeado UserInterfaceOnly:=True faz a proteção bloquear apenas a edição manual do usuário na tela — o seu código VBA (como o SalvarNovoFrete da Prática 2) continua conseguindo escrever na aba protegida normalmente. Sem esse argumento, até a sua própria macro seria bloqueada pela proteção.

 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11
12
13
14
15
16
17

Sub ProtegerBase()
    Worksheets("Banco_Dados").Protect Password:="fastlog2026", _
        UserInterfaceOnly:=True
    MsgBox "Banco de Dados protegido!", vbInformation
End Sub

Sub DesprotegerBase()
    Dim senhaDigitada As String
    senhaDigitada = InputBox("Digite a senha de administrador:", "Autenticação")
    
    If senhaDigitada = "fastlog2026" Then
        Worksheets("Banco_Dados").Unprotect Password:="fastlog2026"
        MsgBox "Banco de Dados liberado para manutenção.", vbInformation
    Else
        MsgBox "Senha incorreta! Acesso negado.", vbCritical
    End If
End Sub

📝 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 foi estudado neste capítulo. Ao concluir, leve o caderno até o professor para correção e visto.

1. O que é a Arquitetura em 3 Camadas (3-Tier Architecture) e quais são suas três camadas?
2. Por que é uma boa prática separar a tela de entrada de dados (Formulário) do Banco de Dados bruto?
3. Como o VBA calcula a próxima linha livre para inserir um novo registro (Append Record)?
4. O que é uma Chave Primária (ID) e como ela pode ser gerada automaticamente em VBA?
5. Qual a instrução VBA usada para tratamento de erros, equivalente ao Try/Catch de outras linguagens?
6. Como referenciar uma célula de outra aba dentro do código VBA (ex: Worksheets(“Formulario”))?
7. Por que a aba de Banco de Dados deve ser protegida contra edição manual direta pelos usuários?
8. Descreva o fluxo completo de dados desde o preenchimento do Formulário até a atualização do Painel.
9. Qual a função do PROCX na aba Painel do projeto FastLog TMS?
10. Refletindo sobre todo o curso, qual foi o conceito de lógica de programação que você considerou mais importante para o seu trabalho em Administração/Logística? Justifique.