Processamento Eficiente de Dados

Até agora, tudo foi feito célula por célula — adequado para cinco pedidos, mas extremamente lento para cinquenta mil. A solução é parar de manipular a planilha célula por célula e, em vez disso, mover todo o bloco para um array na memória, processá-lo lá e gravá-lo de volta de uma só vez.

Lendo um Intervalo para um Array

Dim dataArr As Variant
dataArr = ws.Range("A2:I6").Value    ' one read, not 45 individual reads

dataArr agora é um array 2D na memória: dataArr(1,1) é ORD1001, dataArr(1,4) é "Laptop Stand", e assim por diante — arrays VBA lidos de um intervalo são baseados em 1, não em 0, o que é uma armadilha comum.

Processando o Array

Ocorre com loops comuns — mas agora você está percorrendo a memória, o que é milhares de vezes mais rápido do que percorrer a planilha:

Dim i As Long
Dim recalculated As Double
 
For i = 1 To UBound(dataArr, 1)
    recalculated = dataArr(i, 5) * dataArr(i, 6) * (1 - dataArr(i, 7))
    If Abs(recalculated - dataArr(i, 8)) > 0.01 Then
        Debug.Print "Mismatch on row " & i & ": sheet says " & dataArr(i, 8) & _
                    ", recalculated " & Format(recalculated, "0.00")
    End If
Next i

Gravando o Array de Volta

Novamente, uma única operação:

ws.Range("A2:I6").Value = dataArr

Evitando Select e Activate

Macros gravadas dependem muito desses comandos (Range("A1").Select e depois Selection.Font.Bold = True), mas eles são desnecessários e lentos — cada .Select força o Excel a redesenhar a tela. Referencie o intervalo ou objeto diretamente:

' Avoid:
ws.Range("A1").Select
Selection.Font.Bold = True
 
' Prefer:
ws.Range("A1").Font.Bold = True

Considerações de Desempenho

Esses pontos se tornam importantes quando seus dados ultrapassam algumas centenas de linhas:

Application.ScreenUpdating = False   ' stop redrawing while the macro runs
Application.Calculation = xlCalculationManual   ' pause recalculation
Application.EnableEvents = False     ' suppress other macros triggering mid-run
 
' ... your fast array-based code here ...
 
Application.ScreenUpdating = True
Application.Calculation = xlCalculationAutomatic
Application.EnableEvents = True

Sempre restaure essas configurações ao final — e envolva a restauração em tratamento de erros para que, caso ocorra uma falha durante a execução, o Excel não fique com a atualização de tela desativada.

Exemplo Prático: o Verificador de Integridade de Pedidos

Este capítulo foi construído para chegar a este ponto:

Option Explicit
 
Sub VerifyOrderTotals()
    Dim ws As Worksheet
    Dim dataArr As Variant
    Dim lastRow As Long
    Dim i As Long
    Dim recalculated As Double
    Dim issues As String
 
    Set ws = ThisWorkbook.Worksheets("Orders")
    lastRow = ws.Cells(ws.Rows.Count, 1).End(xlUp).Row
 
    Application.ScreenUpdating = False
 
    dataArr = ws.Range("A2:I" & lastRow).Value
    issues = ""
 
    For i = 1 To UBound(dataArr, 1)
        recalculated = dataArr(i, 5) * dataArr(i, 6) * (1 - dataArr(i, 7))
        If Abs(recalculated - dataArr(i, 8)) > 0.01 Then
            issues = issues & dataArr(i, 1) & ": sheet=" & dataArr(i, 8) & _
                      ", expected=" & Format(recalculated, "0.00") & vbNewLine
        End If
    Next i
 
    Application.ScreenUpdating = True
 
    If issues = "" Then
        MsgBox "All " & UBound(dataArr, 1) & " order totals check out."
    Else
        MsgBox "Discrepancies found:" & vbNewLine & issues
    End If
End Sub

Execute este código na tabela de exemplo e ele deverá informar que todos os cinco totais estão corretos — tente alterar o Total do ORD1002 na planilha para um valor incorreto e execute novamente para ver o alerta ser exibido.

Tarefa

1. Copie VerifyOrderTotals para sua pasta de trabalho e execute-o nos cinco pedidos de exemplo.
2. Altere manualmente o Total de um pedido na planilha (digite um número obviamente errado) e execute novamente — confirme que a divergência é reportada com o Order ID correto.
3. Adicione mais dez linhas de dados fictícios de pedidos abaixo da linha 6 e confirme que a macro continua funcionando sem alterações no código — isso é o resultado do uso de lastRow e processamento por array.