Capítulo 08: Coleções de Objetos (For Each) e Otimização de Performance

🎯 Objetivo da Aula

Nesta aula, você aprenderá a percorrer coleções inteiras de objetos no Excel utilizando o poderoso laço For Each...Next. Além disso, dominará as técnicas profissionais de otimização de desempenho corporativo, aprendendo a desativar o consumo inútil de processamento com o “quarteto de ouro” do VBA (ScreenUpdating, Calculation, EnableEvents, DisplayAlerts) e a cronometrar a velocidade de execução com a função Timer.

Ao final desta aula, você será capaz de aplicar padronizações visuais e bloqueios de segurança em dezenas de planilhas simultaneamente em menos de 1 segundo.

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

Empresa: FastLog Transportes & Logística Integrada S.A.
Problema: Toda virada de mês fiscal, a equipe de controladoria precisa inspecionar a pasta de trabalho de fechamento anual, contendo 12 abas (uma para cada mês do ano) mais as abas de consolidação. Para cada aba, o assistente precisava:

  1. Desbloquear a planilha.
  2. Inserir a data de homologação na célula H1.
  3. Ajustar a largura de todas as colunas (AutoFit).
  4. Reaplicar a proteção com a senha oficial da empresa.
    O estagiário anterior escreveu um script que usava comandos Select e Activate aba por aba sem desativar a atualização de tela. O Excel piscava descontroladamente por quase 1 minuto e, se houvesse fórmulas pesadas de PROCV, o computador travava por completo.
    Sua Missão: Desenvolver o procedimento PadronizarPastaTrabalho, utilizando For Each ws In Worksheets e o quarteto de alta performance, executando a tarefa completa em menos de 0,3 segundo.

🧠 Fundamentos: A Teoria Traduzida

1. O que são Coleções e o Laço For Each?

No VBA, uma Coleção (Collection) é um grupo ordenado de objetos do mesmo tipo mantidos pelo Excel. Por exemplo:

  • Worksheets: Todos os objetos de planilha da pasta.
  • Workbooks: Todos os arquivos abertos no Excel.
  • Range("A1:C10"): Coleção de células dentro de um intervalo.

Enquanto o laço For i = 1 To N exige controlar um contador numérico, o For Each extrai diretamente o próprio objeto a cada ciclo:

1
2
3
4

Dim ws As Worksheet
For Each ws In ThisWorkbook.Worksheets
    ws.Tab.Color = RGB(0, 51, 102)
Next ws
flowchart TD
    COLL["📁 Coleção ThisWorkbook.Worksheets<br/>[Jan, Fev, Mar, ..., Dez]"] --> FE{"For Each ws In Worksheets"}
    FE --> W1["Aba 'Janeiro': AutoFit + Proteger"]
    FE --> W2["Aba 'Fevereiro': AutoFit + Proteger"]
    FE --> W3["Aba 'Março': AutoFit + Proteger"]
    FE --> WN["... Repete automaticamente até a última aba!"]

    style COLL fill:#1a365d,stroke:#fff,stroke-width:2px,color:#fff
    style FE fill:#f39c12,stroke:#fff,stroke-width:2px,color:#fff
    style W1 fill:#27ae60,stroke:#fff,stroke-width:2px,color:#fff
    style W2 fill:#27ae60,stroke:#fff,stroke-width:2px,color:#fff
    style W3 fill:#27ae60,stroke:#fff,stroke-width:2px,color:#fff
    style WN fill:#2980b9,stroke:#fff,stroke-width:2px,color:#fff

2. O Quarteto de Alta Performance do VBA

Quando uma macro roda, o Excel tenta redesenhar cada pixel na tela, recalcular todas as fórmulas da pasta e disparar alertas do sistema. Desativar esses 4 itens antes do laço e reativá-los no final transforma rotinas lentas em execuções quase instantâneas:

Propriedade de ApplicationEstado NormalAo Iniciar a MacroO que ela faz?
.ScreenUpdatingTrueFalseCongela a atualização visual da tela, evitando redesenhos lentos.
.CalculationxlCalculationAutomaticxlCalculationManualImpede que o Excel recalcule todas as fórmulas a cada célula alterada.
.EnableEventsTrueFalseEvita disparar eventos acidentais em cadeia (Worksheet_Change).
.DisplayAlertsTrueFalseSuprime confirmações pop-up do Excel (ex: “Deseja excluir a planilha?”).

📖 Exemplo Guiado: Passo a Passo no Laboratório

No computador do laboratório, acesse o VBE com Alt + F11.

Passo 1: Inserção do Módulo de Performance

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

Passo 2: Criação de Múltiplas Abas para Teste

Para testar o poder do For Each, precisamos de várias abas na pasta de trabalho:

  • No Excel, clique no botão + no rodapé da planilha e crie 5 abas com nomes quaisquer (ex: Aba1, Aba2, Aba3, Aba4, Aba5).

🛠️ Prática Obrigatória 1: Padronização em Massa com For Each

Criaremos a rotina que percorre todas as abas da pasta de trabalho, inserindo o carimbo institucional da FastLog e travando as planilhas com senha.

Passo a Passo no Computador:

  1. No módulo modOtimizacao, digite o seguinte código:
     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

    Option Explicit

Sub PadronizarTodasAsAbas()
    Dim ws As Worksheet
    Dim lngContador As Long

    lngContador = 0

    ' Percorre diretamente cada objeto Worksheet da pasta
    For Each ws In ThisWorkbook.Worksheets
        ' Desprotege provisoriamente se estiver protegida
        On Error Resume Next
        ws.Unprotect Password:="FastLog2026"
        On Error GoTo 0

        ' Insere o carimbo de auditoria na célula A1
        With ws.Range("A1")
            .Value = "SISTEMA FASTLOG - ABA HOMOLOGADA: " & UCase(ws.Name)
            .Font.Bold = True
            .Font.Color = RGB(0, 51, 102)
        End With

        ' Insere timestamp de auditoria na célula A2
        ws.Range("A2").Value = "Última verificação de segurança: " & Now
        ws.Range("A2").Font.Italic = True
        ws.Range("A2").Font.Size = 9

        ' Autoajuste de colunas
        ws.Columns("A:H").AutoFit

        ' Aplica a proteção com senha oficial
        ws.Protect Password:="FastLog2026", DrawingObjects:=True, Contents:=True

        lngContador = lngContador + 1
    Next ws

    MsgBox "Sucesso! Total de " & lngContador & " abas padronizadas e protegidas com senha!", _
           vbInformation, "Auditoria de Pastas FastLog"
End Sub
  2. Posicione o cursor no procedimento e tecle F5.
  3. Clique em cada uma das abas no rodapé do Excel:
    • Observe que todas elas receberam o título azul escuro com seus respectivos nomes na célula A1 e foram travadas contra edição!

✅ Resultado Esperado (Prática 1):

Todas as abas da pasta de trabalho são formatadas e protegidas em lote de forma uniforme e sem repetição de código.

🛠️ Prática Obrigatória 2: O Quarteto de Otimização e Benchmark com Timer

Agora implementaremos o modelo de arquitetura de alta velocidade com cronometragem em milissegundos utilizando a função Timer.

Passo a Passo no Computador:

  1. No mesmo módulo, insira o procedimento corporativo otimizado:
     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

    Sub ExecutarRotinaAltaVelocidade()
    Dim dblTempoInicio As Double
    Dim dblTempoFinal As Double
    Dim ws As Worksheet
    Dim r As Long
    Dim c As Long

    ' 1. Dispara o cronômetro do sistema
    dblTempoInicio = Timer

    ' 2. ATIVAÇÃO DO QUARTETO DE ALTA PERFORMANCE
    Application.ScreenUpdating = False
    Application.Calculation = xlCalculationManual
    Application.EnableEvents = False
    Application.DisplayAlerts = False

    ' 3. Processamento intensivo em memória sobre todas as abas
    For Each ws In ThisWorkbook.Worksheets
        ws.Unprotect Password:="FastLog2026"

        ' Simulação de carga de trabalho: preenche uma grade de 50 linhas x 10 colunas
        For r = 4 To 50
            For c = 1 To 10
                ws.Cells(r, c).Value = (r * c) + Rnd()
            Next c
        Next r

        ws.Range("A4:J50").NumberFormat = "#,##0.00"
        ws.Columns("A:J").AutoFit

        ws.Protect Password:="FastLog2026"
    Next ws

    ' 4. RESTAURAÇÃO OBRIGATÓRIA DO ESTADO ORIGINAL DO EXCEL
    Application.ScreenUpdating = True
    Application.Calculation = xlCalculationAutomatic
    Application.EnableEvents = True
    Application.DisplayAlerts = True

    ' 5. Apuração do tempo decorrido
    dblTempoFinal = Timer - dblTempoInicio

    MsgBox "Processamento em Lote Concluído!" & vbCrLf & vbCrLf & _
           "• Total de Abas Processadas: " & ThisWorkbook.Worksheets.Count & vbCrLf & _
           "• Tempo Total de Execução: " & Format(dblTempoFinal, "0.000") & " segundos.", _
           vbInformation, "Benchmark FastLog RPA"
End Sub
  2. Execute com F5:
    • Perceba que a tela permanece perfeitamente estável (sem tremer nem piscar).
    • Uma caixa surge em fração de segundo indicando que centenas de células foram preenchidas, formatadas e protegidas em menos de 0,5 segundo!

✅ Resultado Esperado (Prática 2):

A macro executa uma carga massiva de trabalho sem oscilação visual na interface, registrando o tempo de processamento preciso no pop-up.

📤 Instruções de Entrega (Microsoft Teams)

  1. Salve o arquivo como: Atividade_08_VBA_SeuNome_SeuSobrenome.xlsm.
  2. Acesse a plataforma Microsoft Teams.
  3. Abra a tarefa: “VBA - Cap 08: For Each e Otimização de Performance”.
  4. Anexe sua pasta de trabalho e entregue.

💡 Checkpoint de Gestão & TI

No ambiente corporativo, a percepção de qualidade de um software não reside apenas em ele funcionar, mas em sua velocidade e estabilidade. Um script em VBA que faz a tela piscar repetidamente passa uma sensação de amadorismo e lentidão. Ao encapsular suas rotinas com o quarteto de otimização, seu código ganha o padrão de acabamento dos softwares comerciais de RPA (Robotic Process Automation).

🔥 Desafio de Fixação: Desproteção Global Interativa

Crie uma macro complementar de manutenção chamada DesprotegerTodasAsAbas:

  1. Use InputBox com o título "Segurança FastLog" para solicitar a senha ao operador.
  2. Se a senha digitada for diferente de "FastLog2026", exiba MsgBox "Senha Incorreta!" com ícone de erro e encerre a rotina.
  3. Se a senha estiver correta, percorra todas as abas com For Each ws In Worksheets e execute ws.Unprotect Password:="FastLog2026".
  4. Exiba uma notificação avisando que as planilhas estão liberadas para edição.
🔑 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 (modOtimizacao):

 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

' Procedimento de Desproteção Segura (Desafio de Fixação)
Sub DesprotegerTodasAsAbas()
    Dim strSenha As String
    Dim ws As Worksheet
    
    strSenha = InputBox("Digite a senha de administrador da FastLog:", "Liberação de Pasta")
    
    If strSenha <> "FastLog2026" Then
        MsgBox "Acesso Negado! Senha incorreta.", vbCritical, "Segurança"
        Exit Sub
    End If
    
    For Each ws In ThisWorkbook.Worksheets
        ws.Unprotect Password:=strSenha
    Next ws
    
    MsgBox "Todas as " & ThisWorkbook.Worksheets.Count & " abas foram desprotegidas para manutenção!", _
           vbInformation, "Modo Manutenção Ativo"
End Sub

' Rotina Corporativa Padronizada
Sub ExecutarOtimizacaoCompleta()
    Dim tInicio As Double
    Dim ws As Worksheet
    
    tInicio = Timer
    
    ' Quarteto de Performance
    Application.ScreenUpdating = False
    Application.Calculation = xlCalculationManual
    Application.EnableEvents = False
    Application.DisplayAlerts = False
    
    For Each ws In ThisWorkbook.Worksheets
        ws.Unprotect "FastLog2026"
        
        With ws.Range("A1")
            .Value = "FASTLOG S.A. - " & UCase(ws.Name)
            .Font.Bold = True
            .Font.Color = RGB(0, 51, 102)
        End With
        
        ws.Columns.AutoFit
        ws.Protect Password:="FastLog2026"
    Next ws
    
    ' Restauração
    Application.ScreenUpdating = True
    Application.Calculation = xlCalculationAutomatic
    Application.EnableEvents = True
    Application.DisplayAlerts = True
    
    MsgBox "Otimização e padronização concluídas em " & Format(Timer - tInicio, "0.000") & " s!", _
           vbInformation, "FastLog Performance"
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 você estudou neste capítulo. Ao concluir, apresente o caderno ao professor para visto.

  1. O que é uma Coleção (Collection) no VBA e como ela se diferencia de uma variável comum?
  2. Explique a sintaxe e a dinâmica de funcionamento do laço For Each...Next.
  3. Qual é a principal vantagem de usar For Each ws In Worksheets em vez de um laço For i = 1 To Worksheets.Count?
  4. Quais são as quatro propriedades do objeto Application que compõem o chamado “quarteto de ouro” de performance do VBA?
  5. O que a propriedade Application.ScreenUpdating = False evita durante a execução de uma macro?
  6. Por que alterar Application.Calculation para xlCalculationManual acelera significativamente pastas com fórmulas pesadas?
  7. O que acontece se uma macro falhar no meio e você não restaurar Application.ScreenUpdating = True?
  8. Qual função nativa do VBA é utilizada para capturar o tempo decorrido em frações de segundos?
  9. Como a instrução ws.Protect Password:="Segredo" atua para proteger as células da planilha contra edições indevidas?
  10. No Desafio de Fixação, de que forma o uso da estrutura condicional protegeu a empresa contra acessos não autorizados?