Um guia rápido para declarações de loop no VBA Excel

Introdução ao VBA For Loop

O loop é de longe o componente mais poderoso do VBA. É como se ele fosse o combustível das macros! Uma de suas caracteristicas é executar tarefas em milissegundos que levariam horas para serem realizadas por nós. Além disso, reduz drasticamente as linhas de código que os aplicativos precisam.

Para quem nunca trabalhou com loops antes, então este post é um ótimo lugar para começar. Eu escrevo aqui, um guia detalhado sobre loops, escrito de forma simples e sem o jargão.

O que significa For Loop no VBA?

Um loop é simplesmente uma maneira de executar as mesmas linhas de código várias vezes. Porém, obviamente, executar o mesmo código repetidamente daria o mesmo resultado.

Portanto, o que é importante entender é que as linhas de código normalmente contêm uma variável que muda cada vez que o loop é executado.

Por exemplo, um loop pode executar um código com os dados da célula A1, A2, A3 e assim por diante, na medida que as linhas forem se alterando.

Veja um exemplo de como isso funciona:

Exemplo 1

O código abaixo imprime os valores de 1 a 5 na Janela de Verificação Imediata (Ctrl + G para visualizar).

A Janela de Verificação Imediata

Caso você ainda não tenha trabalhado com a “Janela de Verificação Imediata” antes, deixo aqui uma breve explicação.

A função Debug.Print grava valores na janela de verificação imediata. Para visualizar esta janela, selecione Exibir > Janela ‘ Verificação imediata’ no menu do VBA ou então utilize o atalho Ctrl + G.

Exemplo 2

Agora imagine que se deseje imprimir os números de 1 a 20. A princípio, seria necessário adicionar mais 15 linhas no exemplo acima. Porém, usando um loop é necessário escrever Debug.Print só uma vez.

Temos como resultado:

Teste o código você mesmo:

Public Sub Mostrar_valor()

For i = 1 To 20

Debug.Print i

Next i

End Sub

Caso queira imprimir os números de 1 a 1000, apenas substitua o número 20 por 1000.

Normalmente, quando se escreve o código é comum usar uma variável em vez de um número como 20 ou 1000. Esta prática oferece maior flexibilidade. O usuário decide o número de vezes que deseja executar o loop quando o código estiver em execução. O exemplo a seguir explica isso:

Exemplo 3

Uma tarefa comum no Excel é ler todas as linhas com dados. Então, a maneira como se deve aborda esta tarefa é a seguinte:

1. Encontre a última linha com dados;
2. Armazene o valor na variável;
3. Use a variável para determinar quantas vezes o loop é executado

O uso de uma variável no loop torna o código flexível. Ele funcionará, não importa quantas linhas existam.

Vamos dar uma olhada em um exemplo: Imagine que você recebe uma folha com uma lista de frutas com a venda diária delas. Deseja-se contar o número de laranjas vendidas, considerando que essa lista varia em tamanho, dependendo das vendas.

A imagem a seguir mostra um exemplo desta lista:

Podemos usar o código para contar as laranjas:

Resultado:

Código:

' https://engenheiradoexcel.com/
Sub Contar_Frutas()

' Obter a última linha com texto
Dim ult_linha As Long
ult_linha = Planilha1.Cells(Planilha1.Rows.Count, 1).End(xlUp).Row

Dim i As Long, Total As Long
' Usar ultima linha no loop
For i = 2 To ult_linha

' Checar se a célula obtem o texto "Laranja"
If Planilha1.Cells(i, 1).Value = "Laranja" Then

' Adicione o valor de unidade vendida na coluna B para a variável Total
Total = Total + Planilha1.Cells(i, 2).Value

End If

Next i

' Mostrar total
Debug.Print "O total de laranjas vendidas é:"; Total

End Sub

Tente alterar o código inserindo mais itens (frutas) na coluna A, e veja como o código funciona bem!

Se aumentado o número de itens para um valor bem maior, como 10.000, dificilmente será notado a diferença no tempo de execução do código. É quase instantâneo, porque os loops são super rápidos, e é isso que os torna tão poderosos. Imagine realizar uma tarefa manual em 10.000 células. Levaria uma quantidade considerável de tempo.

Vantagens do VBA For Loop

Para concluir esta seção, listo abaixo as principais vantagens do uso de loops:

1. Reduzem o código de linhas;
2. São flexíveis;
3. São rápidos.

Nas próximas seções, veremos os diferentes tipos de loops e como usá-los.

Padrão do VBA For Loop

O VBA For Loop é o loop mais comum que o pessoal usa no Excel. Utilizamos esta expressão quando é preciso determinar o número de vezes que um código será executado.

 Formato padrão do VBA For Loop

O formatdo padrão da expressão For Loop é a seguinte:

For <variável> = <valor inicial> to <valor final>
Next <variável>

Os valores inicial e final podem ser variáveis ou invés de números fixos. Além disso, usar a variável após o Next é opcional, porém eu recomendo usa-la porque ela é útil e deixa claro a qual For Loop ela pertence.

Como um funciona o For Loop 

Temos aqui um exemplo simples que imprime os números de 1 a 3:

Isso quer dizer o seguinte:

Eu atribui o valor 1 a variavél “i”, ou seja, o valor de “i” agora passa a ser 1.
O valor de “i” (agora igual a 1) é impresso (na janela de Verificação Imediata).

Após executado o passo acima, o VBA atribui o valor 2 a variavel “i”, ou seja, neste momento “i” passa a valer 2.
O valor de “i” (agora igual a 2) é impresso (na janela de Verificação Imediata).

Após executado o passo acima, o VBA atribui o valor 3 a variavel “i”, ou seja, “i” passa a valer 3.
O valor de “i” (agora igual a 3) é impresso (na janela de Verificação Imediata).

Sem o loop, o código equivalente seria:

A linha i = i + 1 é usada para adicionar o valor 1 a variável “i”. É uma maneira comum na programação de atualizar um contador.

Usando a função Step com o VBA For Loop

Veja que o valor de “i” aumenta em um intervalo de um em um. Esse é o padrão! Caso queira especificar esse intervalo, use a palavra-chave Step junto com a expressão For Loop.

O próximo exemplo mostra como fazer isso:

Podemos ainda usar um número negativo no Step para contar no sentido inverso. Note a diferença:

Importante: se o passo for positivo, o número inicial deve ser menor que o número final. O loop a seguir não será executado porque o número inicial 20 é maior que 10. O VBA, portanto, pensa que já atingiu o valor alvo 10.

Se o Step for negativo, o número inicial deve ser maior que o número final.

Como sair do For Loop

Às vezes, dependendo do resultado de determinada condição, é possivel fazer com que o loop se encerre. Um bom exemplo disso, é quando se lê um dado incorreto. Observe:

Usando o For Loop com uma coleção do VBA

O loop também pode ser usado para ler itens de uma coleção. Por exemplo a seguir, é exibido o nome de todas as pastas de trabalho abertas:

Teste o código você mesmo:

Sub Exemplo_For()

Dim i As Long
For i = 1 To Workbooks.Count

Debug.Print Workbooks(i).FullName

Next i

End Sub

Trabalhando com For Loop aninhado

Às vezes, queremos usar um loop dentro de um outro loop. Um exemplo disso, seria onde se deseja imprimir os nomes das planilhas de cada pasta de trabalho aberta.

Nessa situação, o primeiro loop passaria por cada pasta de trabalho. E cada vez que esse loop é executado, ele usa um segundo loop para percorrer todas as planilhas dessa pasta de trabalho.

Na verdade, é muito mais fácil do que parece. Veja o código a seguir para entender como não dificil: 

O código apresentado acima é:

' https://engenheiradoexcel.com/
Sub Lista_Worksheets()

Dim i As Long, j As Long
' O primeiro loop percorre todas as pastas de trabalho
For i = 1 To Workbooks.Count

' O segundo loop percorre todas as planilhas da pasta de trabalho em "i"
For j = 1 To Workbooks(i).Worksheets.Count

Debug.Print Workbooks(i).Name + ":" + Worksheets(j).Name

Next j

Next i

End Sub

Este código funciona da seguinte maneira:

1.  O primeiro loop define i como 1;
2. O segundo loop usa a pasta de trabalho dentro de 1, para percorrer as planilhas;
3. O primeiro loop define i como 2;
4. O segundo loop usa a pasta de trabalho em 2 para percorrer as planilhas.
5. E assim por diante.

Na próxima seção, usaremos o Loop For Each para executar a mesma tarefa.

Loop For Each no VBA

O Loop For Each é usado para ler itens de uma coleção ou matriz. Ele pode ser usado para acessar todas as pastas de trabalho abertas no Excel. Isso ocorre porque, o Application.Workbooks é uma coleção de pastas de trabalho abertas.

Este é um exemplo simples de uso do Loop For Each:

Código:

' https://engenheiradoexcel.com/
Sub Exemplo_Loop_For_Each()

Dim wk As Workbook
For Each wk In Workbooks

Debug.Print wk.FullName

Next wk

End Sub

Formato do VBA para cada loop

Para criar um Loop For Each, precisamos usar uma variável do mesmo tipo que a coleção. No exemplo que será demostrado abaixo, criamos uma variável do tipo Workbook.

Se a coleção tiver diferentes tipos de itens, a variável pode ser declarada como uma variante.

O VBA contém uma coleção chamada Sheets. Esta é uma coleção de planilhas do tipo Worksheet. Para passar por essa coleção, é preciso declarar a variável como uma variante.

O código a seguir usa o For Each para imprimir o nome de todas as planihas na pasta de trabalho atual:

O código trabalhado acima é:

' https://engenheiradoexcel.com/
Sub Exemplo_Loop_For_Each()

Dim sh As Variant
For Each sh In ThisWorkbook.Sheets

Debug.Print sh.Name

Next sh

End Sub

Podemos ver que o Loop For Each é mais fácil de escrever. No entanto, caso queira ler as planilhas em qualquer outra ordem, por exemplo, da direita para a esquerda, o Loop For deve ser usado de outra maneira:

Código:

' https://engenheiradoexcel.com/
Sub Exemplo_Loop_For()

' Lendo as planilhas da direita para a esquerda
Dim i As Long
For i = ThisWorkbook.Worksheets.Count To 1 Step -1

Debug.Print ThisWorkbook.Worksheets(i).Name

Next

End Sub

VBA For Each Loop com matrizes

Uma coisa que deve ser esclarecida, é o fato de que o Loop For Each usado como matriz executa somente leitura.

O exemplo a seguir demonstra isso:

No primeiro loop, tentamos atribuir a variável x a “Z”. Feito isso, significa que x agora está se referindo à string “Z” e não mais ao item na matriz.

No segundo loop, quando imprimimos a matriz , repare que nenhum dos valores mudou.

A verdade é que, quando se usa o Loop For é possível alterar o item da matriz. Caso for alterado o código anterior para uso do Loop For, será alterarado todos os valores da matriz para “Z”.

O código é:

' https://engenheiradoexcel.com/
Sub Exemplo_Loop_For_com_Matriz()

'Crie uma matriz e adicione três valores
Dim arr() As Variant
arr = Array("A", "B", "C")

Dim i As Long
For i = LBound(arr) To UBound(arr)

' Altera o valor na posição para Z
arr(i) = "Z"

Next

' Imprimir itens para mostrar que os valores da matriz foram alterados
For i = LBound(arr) To UBound(arr)

Debug.Print arr(i)

Next

End Sub

Se a coleção estiver armazenando objetos, é possível alterar os itens usando um Loop For Each.

Loop For Each aninhado

Já vimos que é possível ter um loop dentro de outros loops. A princípio, temos um exemplo já demostrado acima:

Desta vez, usaremos o Loop For Each para executar a mesma tarefa:

Segue o código usado acima:

' https://engenheiradoexcel.com/
Sub Ler_todas_Worksheets()

Dim wk As Workbook, sh As Worksheet
' Ler cada pasta de trabalho (workbook)
For Each wk In Workbooks

' Ler cada planilha na pasta de trabalho atribuida a wk
For Each sh In wk.Worksheets

' Imprimir o nome da pasta de trabalho e o nome da planilha

Debug.Print wk.Name + ": " + sh.Name

Next sh

Next wk

End Sub

' Ler um intervalo do Excel usando o VBA For Loop
' https://engenheiradoexcel.com/
Sub Mega_Exemplo()

' Obter planilha
Dim sh As Worksheet
Set sh = ThisWorkbook.Worksheets("Planilha1")

' Obter intervalo
Dim rg As Range
Set rg = sh.Range("A1").CurrentRegion

' Excluir saída existente
sh.Range("J1").CurrentRegion.ClearContents

' Defina a primeira linha de saída
Dim lin As Long
lin = 1

' Leia todas as linhas usando o Loop For
Dim i As Long
For i = 2 To rg.Rows.Count

' Verifique se o valor é maior que 200,000
If rg.Cells(i, 4).Value > 200 Then

' Copiar valor para a coluna J
sh.Cells(lin, "J").Value = rg.Cells(i, 4).Value

' Mover para a próxima linha de saída
lin = lin + 1

End If

Next i

End Sub