Como se tornar um expert em VBA aprendendo apenas um código básico

Modelo Básico, Simples e Extremamente Versátil de Código VBA para Excel

O VBA (Visual Basic for Applications) é uma poderosa ferramenta para automação de tarefas no Excel. Neste artigo, vamos apresentar um modelo básico, simples e versátil, com poucas linhas de código VBA, que pode ser ajustado para uma infinidade de aplicações com bem poucas mudanças. Além disso, apresentaremos exemplos práticos e inovadores para ilustrar sua utilidade. Posso garantir que, após aprender esse código e utilizá-lo com alguma frequência, você passará a escrever seus programas em VBA com alguma variação desse algoritmo. E o melhor: seus esforços de memorização de sintaxe se reduzirão à compreensão do método.

Apresentação do Modelo Básico

Para utilizar o código VBA, abra o Excel, pressione Alt + F11 para abrir o editor VBA, insira um novo módulo e cole o código abaixo. Depois, ajuste os parâmetros para adequá-lo às suas necessidades. Esse é o código que, com os ajustes necessários em cada caso, será utilizado para inúmeras outras aplicações. Basicamente, a maioria das aplicações VBA envolve um ou mais loops semelhantes aos do modelo. E o método de acesso, leitura e escrita às células adjacentes à célula atual são realizados com muita eficiência utilizando-se o método .offset(0,x), onde x é o deslocamento lateral entre colunas da mesma linha (por isso o 0 no primeiro parâmetro).

Sub ModeloBasico()
    'A colunaOrigem serve como referencial p/ o deslocamento (offset)
    Dim colunaOrigem As Range
    Dim celulaOrigem As Range
    ' Defina a coluna de origem
    Set colunaOrigem = Worksheets("Plan1").Range("A1:A20")
    ' Iteração pelas células referenciadas a partir da coluna origem
    For Each celulaOrigem In colunaOrigem
        ' Adicione a lógica desejada aqui
        ' Por exemplo, supondo-se que na primeira coluna à direita da colunaOrigem
        ' se queira o número da linha, e, ao lado, esse número elevado ao quadrado:
        celulaOrigem.Value = "Célula" & CStr(celulaOrigem.Row)
        celulaOrigem.Offset(0, 1).Value = celulaOrigem.Row
        celulaOrigem.Offset(0, 2).Value = celulaOrigem.Offset(0, 1).Value * celulaOrigem.Offset(0, 1).Value
    Next celulaOrigem
End Sub

Exemplo 1: Filtragem e Comparação de Valores na Mesma Planilha

Este exemplo compara valores da coluna A com os da coluna B, e lista na coluna C os valores não encontrados na coluna B.

Sub CompararValores()
    ' Colunas referenciais
    Dim colunaOrigem As Range, colunaComparacao As Range
    ' Células de trabalho
    Dim celulaOrigem As Range, celulaDestino As Range
    ' Defina as colunas de origem, comparação e a célula de destino
    Set colunaOrigem = Worksheets("Plan1").Range("A2:A101")
    Set colunaComparacao = Worksheets("Plan1").Range("B2:B51")
    Set celulaDestino = Worksheets("Plan1").Range("C1")
    ' Iteração pelos valores da colunaOrigem
    For Each celulaOrigem In colunaOrigem
        ' Verifique se o valor não está presente na coluna de comparação
        If IsError(Application.Match(celulaOrigem.Value, colunaComparacao, 0)) Then
            ' Se não estiver, copie o valor para a celulaDestino
            Set celulaDestino = celulaDestino.Offset(1, 0)
            celulaDestino.Value = celulaOrigem.Value
        End If
    Next celulaOrigem
End Sub

Para testar a macro do exemplo 1, gere dados aleatórios. Para isso, copie e cole este código no editor VBA e o execute:

Sub criaDadosAleatorios()
    Dim frutas As Variant
    Dim indiceAleatorio As Integer
    Dim nomeAleatorio As String
    Dim numeroAleatorio As Integer
    Dim i As Integer
    Dim dict As Object
    Dim valoresUnicos As Collection
    Dim valoresAleatorios As Collection
    Dim j As Integer
    Dim valor As String
    Dim contador As Integer
    Dim existe As Boolean

    ' Array de frutas
    frutas = Array("Abacate", "Banana", "Caju", "Damasco", "Embaúba", "Figo", "Goiaba", "Imbu", "Jaca", "Kiwi", "Limão", "Maçã", "Nêspera", "Pera", "Romã", "Tangerina", "Uva")

    ' Cria um dicionário para evitar duplicatas na coluna A
    Set dict = CreateObject("Scripting.Dictionary")

    ' Cria uma coleção para armazenar valores únicos
    Set valoresUnicos = New Collection

    ' Cabeçalho nas células A1, B1 e C1
    Worksheets("Plan1").Range("A1").Value = "Nomes"
    Worksheets("Plan1").Range("B1").Value = "Nomes a comparar"
    Worksheets("Plan1").Range("C1").Value = "Nomes não selecionados"

    ' Limpa a coluna C
    Worksheets("Plan1").Range("C2:C101").Value = ""

    ' Preenche as células de A2 até A100 com valores únicos
    For i = 2 To 101
        indiceAleatorio = Application.WorksheetFunction.RandBetween(0, UBound(frutas))
        nomeAleatorio = frutas(indiceAleatorio)
        numeroAleatorio = Application.WorksheetFunction.RandBetween(1, 100)
        valor = nomeAleatorio & " " & numeroAleatorio

        If Not dict.exists(valor) Then
            dict.Add valor, True
            valoresUnicos.Add valor
            Worksheets("Plan1").Range("A" & i).Value = valor
        Else
            i = i - 1
        End If
    Next i

    ' Cria uma coleção para armazenar os valores aleatórios que irão para a coluna B
    Set valoresAleatorios = New Collection
    contador = 0

    ' Tenta escolher 50 valores aleatórios da coleção de valores únicos
    Do While valoresAleatorios.Count < 50 And valoresAleatorios.Count < valoresUnicos.Count
        j = Application.WorksheetFunction.RandBetween(1, valoresUnicos.Count)
        valor = valoresUnicos(j)

        existe = False
        For Each v In valoresAleatorios
            If v = valor Then
                existe = True
                Exit For
            End If
        Next v

        If Not existe Then
            valoresAleatorios.Add valor
        End If

        contador = contador + 1
        If contador > 100 Then Exit Do
    Loop

    ' Verifica se conseguimos escolher 50 valores, se não, ajusta para os disponíveis
    If valoresAleatorios.Count < 50 Then
        MsgBox "Apenas " & valoresAleatorios.Count & " valores únicos foram selecionados."
    End If

    ' Copia os valores aleatórios para a coluna B
    For i = 1 To valoresAleatorios.Count
        Worksheets("Plan1").Range("B" & i + 1).Value = valoresAleatorios(i)
    Next i

    ' Cria a tabela
    With ThisWorkbook.Sheets("Plan1")
        .ListObjects.Add(xlSrcRange, .Range("A1:C101"), , xlYes).Range.ColumnWidth = 25
    End With
End Sub

Exemplo 2: Atualização de Dados Baseados em Condições e Cálculos Simples

Este exemplo verifica os valores em uma coluna de origem, calcula um valor condicional com base neles e escreve os resultados em uma nova coluna. Ele utiliza exclusivamente Offset para realizar todas as leituras e escritas, sem a criação de novas variáveis intermediárias.

Sub AtualizarDadosComOffset()
    ' Define a variável celulaOrigem
    Dim celulaOrigem As Range
    ' Define a coluna base de origem
    Dim colunaOrigem As Range
    ' Define a coluna de origem a partir de uma faixa específica na Planilha1
    Set colunaOrigem = Worksheets("Plan1").Range("C3:C102")
    ' Itera sobre cada célula na coluna de origem
    For Each celulaOrigem In colunaOrigem
        ' Verifica se o valor na célula origem é maior que 20000
        If celulaOrigem.Value > 20000 Then
            ' Calcula um novo valor baseado em uma fórmula simples e escreve na coluna destino
            celulaOrigem.Offset(0, 1).Value = celulaOrigem.Value * 0.10
        Else
            ' Escreve um texto indicando que o valor não atingiu a condição
            celulaOrigem.Offset(0, 1).Value = "Valor abaixo do limite de R$ 20000,00"
        End If
        ' Adiciona uma segunda saída em uma terceira coluna, com um texto indicando a análise
        celulaOrigem.Offset(0, 2).Value = "Analisado: " & celulaOrigem.Offset(0, -1).Value
    Next celulaOrigem
    ' Exibe uma mensagem indicando que a atualização foi concluída
    MsgBox "Atualização concluída!"
End Sub

Explicação do Exemplo:

Leitura Base: A coluna C (definida como colunaOrigem) contém os valores iniciais para análise.

Escrita Condicional: Na coluna D (1 coluna à direita da base), é escrito o valor multiplicado por 0.1 se a condição (maior do que R$ 20000,00) for atendida. Isso equivale a 10% do valor considerado.

Caso contrário, o texto "Valor abaixo do limite de R$ 20000,00" é inserido.

Adição de Observações: Na coluna E (2 colunas à direita da base), é inserido um texto contendo uma observação de exemplo quanto aos valores analisados.

Uso Intenso do Offset: Todas as leituras e escritas são feitas com deslocamento a partir da célula de origem, sem a criação de variáveis adicionais.

Este exemplo é particularmente útil em análises que envolvem cálculos simples e geração de relatórios diretamente no Excel. Ele demonstra como o Offset pode ser utilizado de maneira eficiente para manipular dados adjacentes em uma planilha.

Para testar o exemplo 2, gere códigos aleatórios. Rode a macro abaixo para isso:

Sub GerarDadosAleatorios()
    Dim i As Long
    Dim ws As Worksheet
    Set ws = Worksheets("Plan1")

    ' Limpa os dados existentes
    ws.Range("B1:E102").ClearContents

    ' Preenche os cabeçalhos:
    ws.Range("B2:E2").Value = Array("Nome", "Valor", "10%", "Observacao")

    ' Preenche a coluna C com valores aleatórios entre 10.000 e 50.000
    For i = 3 To 102
        ws.Cells(i, 2).Value = "Nome" & ws.Cells(i, 1).Row - 2
        ws.Cells(i, 3).Value = Int((50000 - 10000 + 1) * Rnd + 10000)
    Next i
End Sub

Exemplo 3: Totalização de Valores de Planilhas Diferentes

Neste exemplo, a macro realiza a totalização das vendas ocorridas por vendedor e produto. Para isso, faz a leitura dos valores constantes de 3 planilhas de dados e gera uma planilha de resultados com a totalização dessas vendas.

Sub criaTotalizacaoSimples()
    Dim novaPlanilha As Worksheet
    Dim planilha As Worksheet
    Dim colunaOrigem As Range
    Dim celulaOrigem As Range
    Dim linhaDestino As Long
    Dim linhaAtual As Long
    Dim total As Double

    ' Cria a planilha totalizacaoVendas
    Set novaPlanilha = ThisWorkbook.Worksheets.Add
    novaPlanilha.Name = "totalizacaoVendas"
    novaPlanilha.Cells.Clear

    ' Configura cabeçalho
    novaPlanilha.Range("A1").Value = "Vendedor"
    novaPlanilha.Range("B1").Value = "Produto"
    novaPlanilha.Range("C1").Value = "Total de Vendas ($)"
    linhaDestino = 2

    ' Itera pelas planilhas Plan*
    For Each planilha In ThisWorkbook.Worksheets
        If planilha.Name Like "Plan*" Then
            Set colunaOrigem = planilha.Range("A2:A21")
            For Each celulaOrigem In colunaOrigem
                ' Busca se já existe uma linha para o vendedor/produto
                total = celulaOrigem.Offset(0, 2).Value
                For linhaAtual = 2 To linhaDestino - 1
                    If novaPlanilha.Cells(linhaAtual, 1).Value = celulaOrigem.Value And novaPlanilha.Cells(linhaAtual, 2).Value = celulaOrigem.Offset(0, 1).Value Then
                        novaPlanilha.Cells(linhaAtual, 3).Value = novaPlanilha.Cells(linhaAtual, 3).Value + total
                        Exit For
                    End If
                Next linhaAtual
                ' Se não encontrou, adiciona uma nova linha
                If linhaAtual = linhaDestino Then
                    novaPlanilha.Cells(linhaDestino, 1).Value = celulaOrigem.Value
                    novaPlanilha.Cells(linhaDestino, 2).Value = celulaOrigem.Offset(0, 1).Value
                    novaPlanilha.Cells(linhaDestino, 3).Value = total
                    linhaDestino = linhaDestino + 1
                End If
            Next celulaOrigem
        End If
    Next planilha

    ' Formata a coluna de valores como moeda
    novaPlanilha.Columns(3).NumberFormat = "$ #,##0.00"
End Sub

Para testar a macro do exemplo 3, gere dados aleatórios. Para isso, copie e cole este código no editor VBA e o execute. Esta mesma planilha poderá também ser usada nos testes do exemplo 4 (a seguir):

Sub criaDadosAleatorios()
    Dim pessoas As Variant
    Dim produtos As Variant
    Dim planilha As Worksheet
    Dim i As Integer
    Dim j As Integer
    Dim indicePessoa As Integer
    Dim indiceProduto As Integer
    Dim valorVenda As Double
    Dim nomePessoa As String
    Dim nomeProduto As String
    pessoas = Array("João", "Maria", "Carlos", "Ana", "Luís", "Paula", "Roberto")
    produtos = Array("Produto A", "Produto B", "Produto C", "Produto D", "Produto E", "Produto F", "Produto G")
    For Each planilha In ThisWorkbook.Worksheets
        If planilha.Name Like "Plan*" Then
            planilha.Cells.Clear
            planilha.Cells(1, 1).Value = "Nome"
            planilha.Cells(1, 2).Value = "Produto"
            planilha.Cells(1, 3).Value = "Valor de Venda (R$)"
            For i = 2 To 21
                For j = 1 To 7
                    indicePessoa = Application.WorksheetFunction.RandBetween(0, UBound(pessoas))
                    indiceProduto = Application.WorksheetFunction.RandBetween(0, UBound(produtos))
                    valorVenda = Application.WorksheetFunction.RandBetween(10000, 300000) / 10
                    nomePessoa = pessoas(indicePessoa)
                    nomeProduto = produtos(indiceProduto)
                    planilha.Cells(i, 1).Value = nomePessoa
                    planilha.Cells(i, 2).Value = nomeProduto
                    planilha.Cells(i, 3).Value = valorVenda
                Next j
            Next i
            ' Formata a coluna 3 (valor de venda) como Moeda
            planilha.Columns(3).NumberFormat = "$ #,##0.00"
        End If
    Next planilha
End Sub

Exemplo 4: Criação de Nova Tabela com Filtragem

Para testar este exemplo, pode-se aproveitar a mesma tabela de dados gerada para o exemplo 3, visto acima. Este exemplo lê dados de "Plan1", "Plan2" e "Plan3", aplica uma filtragem, e cria uma nova tabela de resultados com os valores que atendem aos critérios definidos. Por exemplo, podemos criar uma tabela apenas com as vendas superiores a R$ 20.000,00.

Sub CriarTabelaFiltrada()
    Dim colunasOrigem(1 To 3) As Range
    Dim planilhasOrigem(1 To 3) As String
    Dim planilhaDestino As Worksheet
    Dim celulaOrigem As Range
    Dim celulaDestino As Range
    Dim linhaAtual As Long
    Dim ws As Worksheet
    Dim planilhaExiste As Boolean

    ' Nomes das planilhas de origem e configuração das colunas de origem
    planilhasOrigem(1) = "Plan1"
    planilhasOrigem(2) = "Plan2"
    planilhasOrigem(3) = "Plan3"

    ' Verifica se a planilha "Resultados" já existe
    planilhaExiste = False
    For Each ws In ThisWorkbook.Worksheets
        If ws.Name = "Resultados" Then
            planilhaExiste = True
            Set planilhaDestino = ws
            Exit For
        End If
    Next ws

    ' Se não existir, cria a planilha "Resultados"
    If Not planilhaExiste Then
        Set planilhaDestino = ThisWorkbook.Worksheets.Add
        planilhaDestino.Name = "Resultados"
    End If

    ' Limpa os dados da planilha de destino antes de iniciar
    planilhaDestino.Cells.Clear
    linhaAtual = 3 ' Inicia na linha 3 para o resultado

    ' Configura o cabeçalho da tabela de destino
    planilhaDestino.Range("B2:D2").Value = Array("Vendedor", "Produto", "Valor da Venda")
    planilhaDestino.Range("B2:D2").Font.Bold = True
    planilhaDestino.Columns("B:D").AutoFit

    ' Itera pelas planilhas de origem
    Dim i As Integer
    For i = 1 To 3
        ' Define o intervalo de origem
        Set colunasOrigem(i) = Worksheets(planilhasOrigem(i)).Range("C2:C21")

        ' Itera pelas células da coluna de origem
        For Each celulaOrigem In colunasOrigem(i)
            ' Condição para valores numéricos maiores que 20000
            If IsNumeric(celulaOrigem.Value) And celulaOrigem.Value > 20000 Then
                ' Copia os dados para a linha atual da planilha de destino
                Set celulaDestino = planilhaDestino.Cells(linhaAtual, 2)
                celulaDestino.Value = celulaOrigem.Offset(0, -2).Value ' Vendedor
                celulaDestino.Offset(0, 1).Value = celulaOrigem.Offset(0, -1).Value ' Produto
                celulaDestino.Offset(0, 2).Value = celulaOrigem.Value ' Valor da Venda
                linhaAtual = linhaAtual + 1
            End If
        Next celulaOrigem
    Next i

    ' Formatação final
    planilhaDestino.Columns("C").NumberFormat = "$ #,##0.00"
    planilhaDestino.Columns("B:D").AutoFit

    MsgBox "Tabela de resultados criada com sucesso na planilha 'Resultados'!", vbInformation
End Sub

Esses exemplos demonstram a flexibilidade e o poder do modelo básico apresentado. Com pequenos ajustes, ele pode ser adaptado para uma infinidade de aplicações no Excel, otimizando tarefas e poupando tempo.

Confira o vídeo tutorial detalhado sobre este processo no nosso canal do YouTube: Clique aqui para assistir (https://youtu.be/upc43h6g8nI).