Capítulo 10: Matrizes Bidimensionais em Memória para Big Data no Excel

🎯 Objetivo da Aula

Nesta aula, você dominará o ápice da performance no desenvolvimento com VBA: o processamento de Matrizes Bidimensionais (2D Arrays) na memória RAM. Você aprenderá como carregar dezenas de milhares de linhas do Excel para a memória com uma única instrução de atribuição, iterar sobre linhas e colunas através de índices matriciais (r, c), e despejar blocos inteiros de dados (Block Dump) de volta na planilha sem realizar gravações célula a célula.

Ao final desta aula, você terá desenvolvido o motor analítico de telemetria de combustível da FastLog S.A., capaz de auditar frotas inteiras em frações de segundo.

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

Empresa: FastLog Transportes & Logística Integrada S.A.
Problema: A equipe de telemetria e gestão de ativos analisa mensalmente uma base de mais de 10.000 registros de abastecimento de óleo diesel da frota rodoviária. Para cada registro, é necessário calcular:

O consumo médio de combustível (Km/L).
O custo por quilômetro rodado (R$/Km).
O alerta de anomalia de motor (consumo abaixo de 1,8 Km/L indica vazamento de bico injetor ou desvio de combustível).
O método tradicional de varrer linha por linha com Cells(i, c) e gravar os resultados célula por célula demorava quase 50 segundos, travando o computador do setor e corrompendo pastas de trabalho compartilhadas.
Sua Missão: Construir a rotina em VBA de alta escala ProcessarTelemetriaCombustivel, que carrega a tabela inteira para uma matriz 2D na memória RAM em 0,02 segundo, processa todos os cálculos matemáticos na CPU e devolve a tabela inteira preenchida em menos de 0,1 segundo.

🧠 Fundamentos: A Teoria Traduzida

1. A Técnica Secreta do “Despejo em Bloco” (Block Transfer)

No VBA, existe uma funcionalidade extremamente poderosa: quando você atribui um intervalo retangular de células diretamente a uma variável do tipo Variant, o Excel transfere todos os dados para uma Matriz Bidimensionada de base 1 na memória RAM de forma atômica e instantânea:

flowchart LR
    RANGE["📊 Planilha Excel<br/>Range('A1:F10000')"] -- "1. varDados = Range.Value (0,02 s)" --> RAM["🧠 Matriz 2D na RAM<br/>varDados(linha, coluna)"]
    RAM -- "2. Cálculos na CPU (Nanossegundos)" --> RAM
    RAM -- "3. Range.Value = varDados (0,03 s)" --> RANGE

    style RANGE fill:#217346,stroke:#fff,stroke-width:2px,color:#fff
    style RAM fill:#1a365d,stroke:#fff,stroke-width:2px,color:#fff

2. Sintaxe de Coordenadas Bidimensionais

Diferente de um vetor unidimensional simples vetor(i), uma matriz 2D possui duas dimensões cartesianas:

1
varMatriz(linha, coluna)

Para saber a quantidade de linhas: UBound(varMatriz, 1)
Para saber a quantidade de colunas: UBound(varMatriz, 2)

3. Comparativo de Performance no Big Data do Excel

Operação (10.000 Registros)	Método Tradicional (`Cells`)	Método Matricial em RAM (`Array 2D`)	Ganho de Velocidade
Leitura dos Dados	~ 15,2 segundos	0,02 segundo	760x mais rápido
Cálculo Matemático	Feito em conjunto	0,01 segundo	Quase instantâneo
Gravação na Planilha	~ 32,8 segundos	0,03 segundo	1.090x mais rápido
Tempo Total	48 segundos	0,06 segundo	800x de melhoria!

📖 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 Big Data

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

Passo 2: Estruturação dos Dados de Telemetria no Excel

Na primeira aba da planilha, configure os seguintes cabeçalhos na linha 1:

A1: ID_Abastecimento
B1: Placa_Veiculo
C1: Km_Rodados
D1: Litros_Diesel
E1: Valor_Total_Nota
F1: Consumo_KmL (Calculado via código)
G1: Custo_Por_Km (Calculado via código)
H1: Status_Injecao (Calculado via código)

Preencha 5 linhas de abastecimento de teste:

Linha 2: AB-901 | ABC-1234 | 1850 | 925 | 5550
Linha 3: AB-902 | XYZ-9988 | 2100 | 840 | 5040
Linha 4: AB-903 | BRA-2E19 | 1200 | 800 | 4800 (Consumo 1,5 km/L: Anomalia!)
Linha 5: AB-904 | KTM-5544 | 3100 | 1240 | 7440
Linha 6: AB-905 | JUR-8800 | 950 | 700 | 4200 (Consumo 1,35 km/L: Vazamento!)

🛠️ Prática Obrigatória 1: Carga em Memória e Processamento Matricial 2D

Implementaremos a rotina que absorve a tabela para a memória, calcula o consumo e identifica anomalias mecânicas na RAM.

Passo a Passo no Computador:

No módulo modBigDataMatrizes, digite a rotina de alto desempenho:

 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

Sub ProcessarTelemetriaCombustivel()
    Dim ws As Worksheet
    Dim lngUltimaLinha As Long
    Dim varDados As Variant
    Dim r As Long
    Dim dblKm As Double
    Dim dblLitros As Double
    Dim curValor As Currency
    Dim dblKmL As Double
    Dim curCustoKm As Currency
    Dim tInicio As Double
    Dim tFim As Double

    Set ws = ThisWorkbook.Worksheets(1)
    lngUltimaLinha = ws.Cells(ws.Rows.Count, "A").End(xlUp).Row

    If lngUltimaLinha < 2 Then
        MsgBox "Nenhum dado encontrado para processar!", vbExclamation, "Aviso"
        Exit Sub
    End If

    tInicio = Timer
    Application.ScreenUpdating = False

    ' 1. CARGA MASSIVA: Transfere do Excel para a Matriz RAM em 1 comando
    varDados = ws.Range("A2:H" & lngUltimaLinha).Value

    ' 2. PROCESSAMENTO MATRICIAL NA CPU
    ' varDados(linha, coluna):
    ' Col 1: ID, Col 2: Placa, Col 3: Km, Col 4: Litros, Col 5: Valor, Col 6: Km/L, Col 7: R$/Km, Col 8: Status
    For r = 1 To UBound(varDados, 1)
        dblKm = CDbl(varDados(r, 3))
        dblLitros = CDbl(varDados(r, 4))
        curValor = CCur(varDados(r, 5))

        ' Cálculo de Eficiência
        If dblLitros > 0 Then
            dblKmL = dblKm / dblLitros
        Else
            dblKmL = 0
        End If

        ' Cálculo de Custo por Km
        If dblKm > 0 Then
            curCustoKm = curValor / dblKm
        Else
            curCustoKm = 0
        End If

        ' Gravação na própria Matriz em Memória
        varDados(r, 6) = Round(dblKmL, 2)
        varDados(r, 7) = Round(curCustoKm, 2)

        ' Diagnóstico de Injeção Eletrônica
        If dblKmL < 1.8 Then
            varDados(r, 8) = "ANOMALIA: ALTO CONSUMO / REVISAR"
        Else
            varDados(r, 8) = "PADRÃO EFICIENTE"
        End If
    Next r

    ' 3. DESPEJO EM BLOCO: Devolve a matriz inteira calculada para a planilha em 1 comando!
    ws.Range("A2:H" & lngUltimaLinha).Value = varDados

    ' Formatação estética rápida
    ws.Range("F2:F" & lngUltimaLinha).NumberFormat = "0.00 'km/L'"
    ws.Range("G2:G" & lngUltimaLinha).NumberFormat = "R$ #,##0.00 '/km'"
    ws.Columns("A:H").AutoFit

    Application.ScreenUpdating = True
    tFim = Timer - tInicio

    MsgBox "Telemetria processada na memória RAM com sucesso!" & vbCrLf & vbCrLf & _
           "• Total de Registros Processados: " & UBound(varDados, 1) & vbCrLf & _
           "• Tempo Total de Execução: " & Format(tFim, "0.0000") & " segundos!", _
           vbInformation, "FastLog Telemetria & Big Data"
End Sub

Posicione o cursor no procedimento e tecle F5.
Observe que as colunas F, G e H são preenchidas instantaneamente:
- Linha 2 (ABC-1234): 2,00 km/L | R$ 3,00 /km | PADRÃO EFICIENTE
- Linha 4 (BRA-2E19): 1,50 km/L | R$ 4,00 /km | ANOMALIA: ALTO CONSUMO / REVISAR
- Linha 6 (JUR-8800): 1,36 km/L | R$ 4,42 /km | ANOMALIA: ALTO CONSUMO / REVISAR

✅ Resultado Esperado (Prática 1):

A tabela é totalmente processada e diagnosticada com precisão matemática em frações mínimas de segundo.

🛠️ Prática Obrigatória 2: Filtragem Matricial e Exportação de Anomalias

Agora construiremos uma rotina que lê a matriz na memória RAM e extrai apenas as carretas com anomalia de consumo para uma segunda matriz de contingência, despejando o relatório de manutenção preventiva.

Passo a Passo no Computador:

No mesmo módulo, insira a rotina de triagem de manutençã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
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
Sub GerarRelatorioOficinaMecanica()
    Dim wsOrigem As Worksheet
    Dim wsOficina As Worksheet
    Dim varOrigem As Variant
    Dim varOficina() As Variant
    Dim lngLinhas As Long
    Dim r As Long
    Dim intAnomalias As Long

    Set wsOrigem = ThisWorkbook.Worksheets(1)
    lngLinhas = wsOrigem.Cells(wsOrigem.Rows.Count, "A").End(xlUp).Row

    If lngLinhas < 2 Then Exit Sub

    ' Carrega matriz completa
    varOrigem = wsOrigem.Range("A2:H" & lngLinhas).Value
    intAnomalias = 0

    ' Conta primeiro quantas anomalias existem
    For r = 1 To UBound(varOrigem, 1)
        If InStr(varOrigem(r, 8), "ANOMALIA") > 0 Then
            intAnomalias = intAnomalias + 1
        End If
    Next r

    If intAnomalias = 0 Then
        MsgBox "Nenhuma carreta com anomalia mecânica detectada!", vbInformation, "Frota OK"
        Exit Sub
    End If

    ' Dimensiona a matriz da oficina na RAM (Linhas x 4 Colunas)
    ReDim varOficina(1 To intAnomalias, 1 To 4)

    Dim k As Long
    k = 1
    For r = 1 To UBound(varOrigem, 1)
        If InStr(varOrigem(r, 8), "ANOMALIA") > 0 Then
            varOficina(k, 1) = varOrigem(r, 1)  ' ID
            varOficina(k, 2) = varOrigem(r, 2)  ' Placa
            varOficina(k, 3) = varOrigem(r, 6)  ' Km/L
            varOficina(k, 4) = "Agendar revisão de bicos injetores"
            k = k + 1
        End If
    Next r

    ' Cria ou limpa a aba de manutenção
    On Error Resume Next
    Set wsOficina = ThisWorkbook.Worksheets("Ordem_Servico_Oficina")
    On Error GoTo 0

    If wsOficina Is Nothing Then
        Set wsOficina = ThisWorkbook.Worksheets.Add(After:=wsOrigem)
        wsOficina.Name = "Ordem_Servico_Oficina"
        wsOficina.Tab.Color = RGB(230, 126, 34) ' Laranja Mecânica
    End If

    wsOficina.Cells.Clear

    ' Cabeçalhos
    wsOficina.Range("A1:D1").Value = Array("ID_ABAST", "PLACA_VEICULO", "CONSUMO_APURADO", "PARECER_TECNICO")
    With wsOficina.Range("A1:D1")
        .Font.Bold = True
        .Interior.Color = RGB(230, 126, 34)
        .Font.Color = RGB(255, 255, 255)
    End With

    ' DESPEJO EM BLOCO DA MATRIZ DA OFICINA
    wsOficina.Range("A2").Resize(intAnomalias, 4).Value = varOficina
    wsOficina.Columns("A:D").AutoFit

    MsgBox "Ordem de serviço gerada com sucesso na aba 'Ordem_Servico_Oficina'!" & vbCrLf & _
           "Total de carretas encaminhadas para manutenção: " & intAnomalias, _
           vbInformation, "Oficina FastLog"
End Sub

Execute a macro com F5:
- Observe a nova aba "Ordem_Servico_Oficina" surgir com a tabela contendo perfeitamente as carretas BRA-2E19 e JUR-8800.
- Todo o processo ocorreu em milissegundos através do método .Resize(linhas, colunas).Value = varOficina.

✅ Resultado Esperado (Prática 2):

A matriz secundária gerada e filtrada em memória RAM é despejada em bloco na planilha da oficina mecânica sem repetições nem lentidão.

📤 Instruções de Entrega (Microsoft Teams)

Salve o arquivo como: Atividade_10_VBA_SeuNome_SeuSobrenome.xlsm.
Acesse a plataforma Microsoft Teams.
Abra a tarefa: “VBA - Cap 10: Matrizes 2D em Memória e Big Data”.
Anexe o arquivo .xlsm e conclua a entrega.

💡 Checkpoint de Gestão & TI

Em auditorias de desempenho de TI empresarial, a técnica de atribuição de intervalo matricial (Variant = Range.Value) é tida como o padrão profissional supremo. Um analista que processa 50.000 linhas célula a célula trava a rede corporativa e consome gigabytes de buffer de renderização do Windows. Já o analista sênior que carrega os dados para a RAM entrega o mesmo cálculo em 0,15 segundo, economizando tempo de servidores e estações de trabalho.

🔥 Desafio de Fixação: Média Global Ponderada de Consumo

Calcule a média ponderada de consumo da frota na memória RAM:

Durante a iteração matricial, acumule duas variáveis: dblSomaKm e dblSomaLitros.
Após o laço, calcule dblMediaFrota = dblSomaKm / dblSomaLitros.
Exiba uma notificação via MsgBox informando a média consolidada de consumo de toda a transportadora.

🔑 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 (`modBigDataMatrizes`):

 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
Option Explicit

Sub CalcularMediaPonderadaConsumo()
    Dim ws As Worksheet
    Dim varMatriz As Variant
    Dim r As Long
    Dim dblSomaKm As Double
    Dim dblSomaLitros As Double
    Dim dblMediaGlobal As Double
    Dim lngLinhas As Long
    
    Set ws = ThisWorkbook.Worksheets(1)
    lngLinhas = ws.Cells(ws.Rows.Count, "A").End(xlUp).Row
    
    If lngLinhas < 2 Then Exit Sub
    
    varMatriz = ws.Range("A2:E" & lngLinhas).Value
    dblSomaKm = 0
    dblSomaLitros = 0
    
    For r = 1 To UBound(varMatriz, 1)
        dblSomaKm = dblSomaKm + CDbl(varMatriz(r, 3))
        dblSomaLitros = dblSomaLitros + CDbl(varMatriz(r, 4))
    Next r
    
    If dblSomaLitros > 0 Then
        dblMediaGlobal = dblSomaKm / dblSomaLitros
    Else
        dblMediaGlobal = 0
    End If
    
    MsgBox "MÉDIA GLOBAL DE CONSUMO FASTLOG:" & vbCrLf & vbCrLf & _
           "• Total de Km Percorridos: " & FormatNumber(dblSomaKm, 0) & " km" & vbCrLf & _
           "• Total de Diesel Consumido: " & FormatNumber(dblSomaLitros, 0) & " litros" & vbCrLf & _
           "• Média Ponderada da Frota: " & Format(dblMediaGlobal, "0.00") & " km/L", _
           vbInformation, "Indicador Estratégico ESG"
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.

O que é uma Matriz Bidimensional (2D Array) e em que ela se diferencia de um vetor unidimensional?
Explique a instrução mágica varDados = Range("A1:G10000").Value e por que ela é tão rápida na leitura de dados.
Como descobrimos o número total de linhas e o número total de colunas de uma matriz 2D no VBA?
Por que matrizes geradas a partir de intervalos de células do Excel possuem índices de base 1 em vez de base 0?
Qual é o conceito de “Despejo em Bloco” (Block Dump) na escrita de dados de volta para a planilha?
Como o método .Resize(linhas, colunas) auxilia no dimensionamento exato do intervalo que receberá a matriz de saída?
Qual é a ordem de magnitude do ganho de velocidade ao substituir leituras célula a célula por operações em matriz na memória RAM?
No Cenário da FastLog, qual regra de negócio indicava anomalia mecânica ou suspeita de desvio no caminhão?
Como a função Round(valor, casas) foi empregada no cálculo de consumo por quilômetro?
O que significa o indicador de “média ponderada” de consumo em comparação com uma simples média aritmética dos veículos?

Capítulo 10: Matrizes Bidimensionais em Memória para Big Data no Excel

🎯 Objetivo da Aula

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

🧠 Fundamentos: A Teoria Traduzida

1. A Técnica Secreta do “Despejo em Bloco” (Block Transfer)

2. Sintaxe de Coordenadas Bidimensionais

3. Comparativo de Performance no Big Data do Excel

📖 Exemplo Guiado: Passo a Passo no Laboratório

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

Passo 2: Estruturação dos Dados de Telemetria no Excel

🛠️ Prática Obrigatória 1: Carga em Memória e Processamento Matricial 2D

Passo a Passo no Computador:

✅ Resultado Esperado (Prática 1):

🛠️ Prática Obrigatória 2: Filtragem Matricial e Exportação de Anomalias

Passo a Passo no Computador:

✅ Resultado Esperado (Prática 2):

📤 Instruções de Entrega (Microsoft Teams)

💡 Checkpoint de Gestão & TI

🔥 Desafio de Fixação: Média Global Ponderada de Consumo

Código Consolidado (modBigDataMatrizes):

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

Código Consolidado (`modBigDataMatrizes`):