Para obter o máximo do Excel e do VBA, você precisa saber como usar os loops com eficiência.
No VBA, os loops permitem percorrer um conjunto de objetos / valores e analisá-los um por um. Você também pode realizar tarefas específicas para cada loop.
Aqui está um exemplo simples de uso de loops VBA no Excel.
Suponha que você tenha um conjunto de dados e deseja destacar todas as células em linhas pares. Você pode usar um loop VBA para percorrer o intervalo e analisar cada número de linha de célula. Se ficar uniforme, você dá uma cor, senão deixa como está.
Obviamente, isso é muito simples de loop no Excel VBA (e você também pode fazer isso usando a formatação condicional).
Na vida real, você pode fazer muito mais com loops VBA no Excel que podem ajudá-lo a automatizar tarefas.
Aqui estão alguns exemplos mais práticos em que os loops VBA podem ser úteis:
- Percorrer um intervalo de células e analisar cada célula (realce as células com um texto específico).
- Loop por todas as planilhas e fazer algo com cada uma (como proteger / desproteger).
- Percorra todas as pastas de trabalho abertas (e salve cada uma ou feche todas, exceto a pasta de trabalho ativa).
- Percorre todos os caracteres em uma célula (e extrai a parte numérica de uma string).
- Percorre todos os valores de uma matriz.
- Percorra todos os gráficos / objetos (e forneça uma borda ou altere a cor de fundo).
Agora, para usar melhor os loops no Excel VBA, você precisa saber sobre os diferentes tipos que existem e a sintaxe correta de cada um.
Neste tutorial, vou mostrar diferentes tipos de loops VBA do Excel e cobrir alguns exemplos para cada loop
Nota: Este será um tutorial enorme, onde tentarei cobrir cada loop VBA com alguns detalhes. Eu recomendo que você marque esta página para referência futura.Se você estiver interessado em aprender VBA da maneira mais fácil, confira meu Treinamento Online Excel VBA.
Para o próximo ciclo
O loop ‘For Next’ permite que você passe por um bloco de código pelo número especificado de vezes.
Por exemplo, se eu pedir a você para adicionar os inteiros de 1 a 10 manualmente, você adicionaria os primeiros dois números, então adicionaria o terceiro número ao resultado, então adicionaria o quarto número ao resultado, e assim por diante …
Não é?
A mesma lógica é usada no loop For Next no VBA.
Você especifica quantas vezes deseja que o loop seja executado e também o que deseja que o código faça cada vez que o loop for executado.
Abaixo está a sintaxe do loop For Next:
Para contador = início para fim [valor da etapa] [bloco de código a ser executado] próximo [contador]
No loop For Next, você pode usar um Contador (ou qualquer variável) que será usado para executar o loop. Este contador permite que você execute este loop por um número necessário de vezes.
Por exemplo, se eu quiser adicionar os primeiros 10 inteiros positivos, o valor do meu contador seria de 1 a 10.
Vamos dar uma olhada em alguns exemplos para entender melhor como o loop For Next funciona.
Exemplo 1 - Adicionando os primeiros 10 inteiros positivos
Abaixo está o código que adicionará os primeiros 10 inteiros positivos usando um loop For Next.
Em seguida, exibirá uma caixa de mensagem mostrando a soma desses números.
Sub AddNumbers () Dim Total As Integer Dim Count As Integer Total = 0 For Count = 1 To 10 Total = Total + Count Next Count MsgBox Total End Sub
Neste código, o valor de Total é definido como 0 antes de entrar no loop For Next.
Depois de entrar no loop, ele mantém o valor total após cada loop. Portanto, após o primeiro loop, quando Counter é 1, o valor ‘Total’ torna-se 1 e, após o segundo loop, torna-se 3 (1 + 2) e assim por diante.
E, finalmente, quando o loop termina, a variável ‘Total’ tem a soma dos primeiros 10 inteiros positivos.
Um MsgBox simplesmente exibe o resultado em uma caixa de mensagem.
Exemplo 2 - Adicionando os primeiros 5 números inteiros positivos pares
Para somar os primeiros cinco inteiros positivos pares (ou seja, 2,4,6,8 e 10), você precisa de um código semelhante com uma condição para considerar apenas os números pares e ignorar os números ímpares.
Aqui está um código que fará isso:
Sub AddEvenNumbers () Dim Total As Integer Dim Count As Integer Total = 0 For Count = 2 a 10 Step 2 Total = Total + Count Next Count MsgBox Total End Sub
Observe que começamos o valor de contagem de 2 e também usamos 'Passo 2‘.
Quando você usa 'Passo 2', ele diz ao código para incrementar o valor de ‘Contagem’ em 2 toda vez que o loop for executado.
Portanto, o valor de Contagem começa em 2 e então se torna 4, 6, 8 e 10 conforme ocorre o loop.
NOTA: Outra maneira de fazer isso seria executar o loop de 1 a 10 e, dentro do loop, verificar se o número é par ou ímpar. No entanto, usar Step, neste caso, é uma maneira mais eficiente, pois não exige que o loop seja executado 10 vezes, mas apenas 5 vezes.
O valor do Step também pode ser negativo. Nesse caso, o contador começa com um valor mais alto e continua sendo diminuído pelo valor de etapa especificado.
Exemplo 3 - Inserindo o número de série nas células selecionadas
Você também pode usar o loop For Next para percorrer uma coleção de objetos (como células ou planilhas ou pastas de trabalho),
Aqui está um exemplo que insere rapidamente os números de série em todas as células selecionadas.
Sub EnterSerialNumber () Dim Rng como intervalo Dim Counter como inteiro Dim RowCount como inteiro Definir Rng = Seleção RowCount = Rng.Rows.Count para contador = 1 para RowCount ActiveCell.Offset (contador - 1, 0) .Value = Contador Próximo contador final Sub
O código acima conta primeiro o número de linhas selecionadas e, em seguida, atribui esse valor à variável RowCount. Em seguida, executamos o loop de '1 para RowCount'.
Observe também que, uma vez que a seleção pode ser qualquer número de linhas, definimos a variável Rng para Seleção (com a linha ‘Definir Rng = Seleção’). Agora podemos usar a variável ‘Rng’ para nos referir à seleção em nosso código.
Exemplo 4 - proteger todas as planilhas na pasta de trabalho ativa
Você pode usar o loop ‘For Next’ para percorrer todas as planilhas na pasta de trabalho ativa e proteger (ou desproteger) cada uma das planilhas.
Abaixo está o código que fará isso:
Sub ProtectWorksheets () Dim i As Integer For i = 1 To ActiveWorkbook.Worksheets.Count Worksheets (i) .Protect Next i End Sub
O código acima conta o número de folhas usando ActiveWorkbook.Worksheets.Count. Isso informa ao VBA quantas vezes o loop precisa ser executado.
Em cada instância, ele se refere à Iª pasta de trabalho (usando Planilhas (i)) e a protege.
Você também pode usar este mesmo código para desproteger planilhas. Basta mudar a linha Planilhas (i). Proteger para Planilhas (i) .UnProtect.
Loops ‘For Next’ aninhados
Você pode usar loops aninhados ‘For Next’ para obter uma automação mais complexa feita no Excel. Um loop aninhado ‘For Next’ significaria que há um loop ‘For Next’ dentro de um loop ‘For Next’.
Deixe-me mostrar como usar isso usando um exemplo.
Suponha que eu tenha 5 pastas de trabalho abertas em meu sistema e desejo proteger todas as planilhas em todas essas pastas de trabalho.
Abaixo está o código que fará isso:
Sub ProtectWorksheets () Dim i As Integer Dim j As Integer For i = 1 To Workbooks.Count For j = 1 To Workbooks (i) .Worksheets.Count Workbooks (i) .Worksheets (j) .Protect Next j Next i End Sub
O acima é um loop For Next aninhado, pois usamos um loop For Next dentro de outro.
Declarações ‘SAIR para’ nos próximos loops
A instrução ‘Exit For’ permite que você saia do loop ‘For Next’ completamente.
Você pode usá-lo nos casos em que deseja que o loop For Next termine quando uma determinada condição for atendida.
Vejamos um exemplo em que você tem um conjunto de números na Coluna A e deseja destacar todos os números negativos em fonte vermelha. Nesse caso, precisamos analisar cada célula quanto ao seu valor e, em seguida, alterar a cor da fonte de acordo.
Mas para tornar o código mais eficiente, podemos primeiro verificar se há algum valor negativo na lista ou não. Se não houver valores negativos, podemos usar a instrução Exit For para simplesmente sair do código.
Abaixo está o código que faz isso:
Sub HghlightNegative () Dim Rng As Range Set Rng = Range ("A1", Range ("A1"). End (xlDown)) Counter = Rng.Count For i = 1 To Counter If WorksheetFunction.Min (Rng)> = 0 Então saia para If Rng (i) .Value <0 Then Rng (i) .Font.Color = vbRed Next i End Sub
Quando você usa a instrução ‘Exit For’ dentro de um loop aninhado ‘For Next’, ela sairá do loop em que é executada e continuará a executar a próxima linha no código após o loop For Next.
Por exemplo, no código a seguir, a instrução ‘Exit For’ o tirará do loop interno, mas o loop externo continuará a funcionar.
Sub SampleCode () For i = 1 To 10 For j = 1 to 10 Exit For Next J Next i End Sub
Loop Do While
Um loop ‘Do While’ permite que você verifique se há uma condição e execute o loop enquanto essa condição for atendida (ou VERDADEIRA).
Existem dois tipos de sintaxe no Loop Do While.
Do [While condição] [Bloco de código a ser executado] Loop
e
Faça [bloco de código a ser executado] Loop [condição While]
A diferença entre esses dois é que, no primeiro, a condição While é verificada primeiro antes de qualquer bloco de código ser executado e, no segundo caso, o bloco de código é executado primeiro e, em seguida, a condição While é verificada.
Isso significa que se a condição While for False em ambos os casos, o código ainda será executado pelo menos uma vez no segundo caso (como a condição ‘While’ é verificada após o código ter sido executado uma vez).
Agora vamos ver alguns exemplos de uso de loops Do While no VBA.
Exemplo 1 - Adicione os primeiros 10 inteiros positivos usando VBA
Suponha que você queira adicionar os primeiros dez inteiros positivos usando o loop Do While no VBA.
Para fazer isso, você pode usar o loop Do While até que o próximo número seja menor ou igual a 10. Assim que o número for maior que 1o, o loop para.
Aqui está o código VBA que executará este loop Do While e mostrará o resultado em uma caixa de mensagem.
Sub AddFirst10PositiveIntegers () Dim i As Integer i = 1 Do While i <= 10 Result = Result + i i = i + 1 Loop MsgBox Result End Sub
O loop acima continua a funcionar até que o valor de 'i' se torne 11. Assim que se torna 11, o loop termina (já que a condição While se torna falsa).
Dentro do loop, usamos uma variável Result que contém o valor final. Assim que o loop for concluído, uma caixa de mensagem mostra o valor da variável ‘Result’.
Exemplo 2 - Insira datas para o mês atual
Digamos que você queira inserir todas as datas do mês atual em uma coluna da planilha.
Você pode fazer isso usando o seguinte código de loop Do While:
Sub EnterCurrentMonthDates () Dim CMDate As Date Dim i As Integer i = 0 CMDate = DateSerial (Year (Date), Month (Date), 1) Do While Mês (CMDate) = Mês (Data) Intervalo ("A1"). (i, 0) = CMDate i = i + 1 CMDate = CMDate + 1 Loop End Sub
O código acima deve inserir todas as datas na primeira coluna da planilha (começando em A1). Os loops continuam até que o valor do mês da variável ‘CMDate’ corresponda ao do mês atual.
Declaração de saída de
Você pode usar a instrução Exit Do para sair do loop. Assim que o código executa a linha ‘Exit Do’, ele sai do loop Do While e passa o controle para a próxima linha logo após o loop.
Por exemplo, se você quiser inserir as primeiras 10 datas apenas, poderá sair do loop assim que as primeiras 10 datas forem inseridas.
O código a seguir fará isso:
Sub EnterCurrentMonthDates () Dim CMDate As Date Dim i As Integer i = 0 CMDate = DateSerial (Year (Date), Month (Date), 1) Do While Mês (CMDate) = Mês (Data) Intervalo ("A1"). (i, 0) = CMDate i = i + 1 If i> = 10 Then Exit Do CMDate = CMDate + 1 Loop End Sub
No código acima, a instrução IF é usada para verificar se o valor de i é maior que 10 ou não. Assim que o valor de 'i' torna-se 10, a instrução Exit Do é executada e o loop termina.
Do Até Loop
Os loops ‘Do Until’ são muito parecidos com os loops ‘Do While’.
Em 'Do While', o loop é executado até que a condição dada seja satisfeita, enquanto em 'Do While', ele executa o loop até que a condição especificada seja satisfeita.
Existem dois tipos de sintaxe no Do Until Loop.
Do [Até a condição] [Bloco de código a ser executado] Loop
e
Faça [bloco de código a ser executado] Loop [até a condição]
A diferença entre esses dois é que, no primeiro, a condição até é verificada primeiro antes de qualquer bloco de código ser executado e, no segundo caso, o bloco de código é executado primeiro e, em seguida, a condição até é verificada.
Isso significa que, se a condição Até for TRUE em ambos os casos, o código ainda será executado pelo menos uma vez no segundo caso (como a condição "Até" é verificada após o código ter sido executado uma vez).
Agora vamos ver alguns exemplos de uso de loops Do Until no VBA.
Nota: Todos os exemplos para Do Until são iguais aos do Do While. Eles foram modificados para mostrar como o loop Do Until funciona.
Exemplo 1 - Adicione os primeiros 10 inteiros positivos usando VBA
Suponha que você queira adicionar os primeiros dez inteiros positivos usando o loop Do Until no VBA.
Para fazer isso, você precisa executar o loop até que o próximo número seja menor ou igual a 10. Assim que o número for maior que 1o, o loop para.
Aqui está o código VBA que executará este loop e mostrará o resultado em uma caixa de mensagem.
Sub AddFirst10PositiveIntegers () Dim i As Integer i = 1 Do until i> 10 Result = Result + i i = i + 1 Loop MsgBox Result End Sub
O loop acima continua a funcionar até que o valor de 'i' se torne 11. Assim que se torna 11, o loop termina (quando a condição 'Até' se torna Verdadeira).
Exemplo 2 - Insira datas para o mês atual
Digamos que você queira inserir todas as datas do mês atual em uma coluna da planilha.
Você pode fazer isso usando o seguinte código de loop Do Until:
Sub EnterCurrentMonthDates () Dim CMDate As Date Dim i As Integer i = 0 CMDate = DateSerial (Year (Date), Month (Date), 1) Do Até Month (CMDate) Mês (Date) Range ("A1"). Offset ( i, 0) = CMDate i = i + 1 CMDate = CMDate + 1 Loop End Sub
O código acima deve inserir todas as datas na primeira coluna da planilha (começando em A1). O loop continua até que o Mês da variável CMDate não seja igual ao mês atual.
Declaração de saída de
Você pode usar a instrução ‘Exit Do’ para sair do loop.
Assim que o código executa a linha ‘Exit Do’, ele sai do loop Do Until e passa o controle para a próxima linha logo após o loop.
Por exemplo, se você quiser inserir as primeiras 10 datas apenas, poderá sair do loop assim que as primeiras 10 datas forem inseridas.
O código a seguir fará isso:
Sub EnterCurrentMonthDates () Dim CMDate As Date Dim i As Integer i = 0 CMDate = DateSerial (Year (Date), Month (Date), 1) Do Até Month (CMDate) Mês (Date) Range ("A1"). Offset ( i, 0) = CMDate i = i + 1 If i> = 10 Then Exit Do CMDate = CMDate + 1 Loop End Sub
No código acima, assim que o valor de 'i' torna-se 10, a instrução Exit Do é executada e o loop termina.
Para cada
No VBA, você pode percorrer um conjunto de coleções usando o loop ‘For Each’.
Aqui estão alguns exemplos de coleções no Excel VBA:
- Uma coleção de todas as pastas de trabalho abertas.
- Uma coleção de todas as planilhas em uma pasta de trabalho.
- Uma coleção de todas as células em um intervalo de células selecionadas.
- Uma coleção de todos os gráficos ou formas da pasta de trabalho.
Usando o loop ‘For Each’, você pode percorrer cada um dos objetos em uma coleção e executar alguma ação nele.
Por exemplo, você pode percorrer todas as planilhas em uma pasta de trabalho e protegê-las ou pode percorrer todas as células da seleção e alterar a formatação.
Com o loop ‘For Each’ (também conhecido como loop ‘For Each-Next’), você não precisa saber quantos objetos existem em uma coleção.
O loop ‘For Each’ passaria automaticamente por cada objeto e executaria a ação especificada. Por exemplo, se você deseja proteger todas as planilhas em uma pasta de trabalho, o código seria o mesmo, quer você tenha uma pasta de trabalho com 3 planilhas ou 30 planilhas.
Aqui está a sintaxe do loop For Each-Next no Excel VBA.
Para cada elemento na coleção [Bloco de código a ser executado] Próximo [elemento]
Agora vamos ver alguns exemplos de uso do For Each Loop no Excel.
Exemplo 1 - Percorrer todas as planilhas em uma pasta de trabalho (e protegê-la)
Suponha que você tenha uma pasta de trabalho na qual deseja proteger todas as planilhas.
O loop Below For Each-Next pode fazer isso facilmente:
Sub ProtectSheets () Dim ws como planilha para cada ws em ActiveWorkbook.Worksheets ws.Protect Next ws End Sub
No código acima, definimos a variável ‘ws’ como um objeto de planilha. Isso diz ao VBA que 'ws' deve ser interpretado como um objeto de planilha no código.
Agora usamos a instrução ‘For Each’ para passar por cada ‘ws’ (que é um objeto de planilha) na coleção de todas as planilhas na pasta de trabalho ativa (fornecida por ActiveWorkbook.Worksheets).
Observe que, ao contrário de outros loops em que tentamos proteger todas as planilhas em uma pasta de trabalho, aqui não precisamos nos preocupar com quantas planilhas existem na pasta de trabalho.
Não precisamos contá-los para executar o loop. For Each loop garante que todos os objetos sejam analisados um por um.
Exemplo 2 - Percorra todas as pastas de trabalho abertas (e salve todas)
Se você trabalha com várias pastas de trabalho ao mesmo tempo, pode ser útil salvar todas essas pastas de trabalho de uma vez.
Abaixo o código VBA pode fazer isso por nós:
Sub SaveAllWorkbooks () Dim wb como workbook para cada wb em workbooks wb.Save Next wb End Sub
Observe que, neste código, você não recebe um prompt solicitando que você salve a pasta de trabalho em um local específico (se salvá-la pela primeira vez).
Ele o salva na pasta padrão (era a pasta ‘Documentos’ no meu caso). Este código funciona melhor quando esses arquivos já estão salvos e você está fazendo alterações e deseja salvar todas as pastas de trabalho rapidamente.
Exemplo 3 - Passe por todas as células em uma seleção (realce os valores negativos)
Usando o loop ‘For Each’, você pode percorrer todas as células em um intervalo específico ou no intervalo selecionado.
Isso pode ser útil quando você deseja analisar cada célula e executar uma ação com base nela.
Por exemplo, abaixo está o código que irá percorrer todas as células na seleção e alterar a cor das células das células com valores negativos para vermelho.
Sub HighlightNegativeCells () Dim Cll como intervalo para cada Cll na seleção If Cll.Value <0 Then Cll.Interior.Color = vbRed End If Next Cll End Sub
(Observe que usei Cll como um nome curto de variável para Cell. É aconselhável não usar nomes de objetos, como Folhas ou Intervalo como nomes de variáveis)
No código acima, o loop For Each-Next atravessa a coleção de células na seleção. A instrução IF é usada para identificar se o valor da célula é negativo ou não. Caso seja, a célula recebe uma cor interna vermelha, caso contrário, ela vai para a próxima célula.
Caso você não tenha uma seleção e queira que o VBA selecione todas as células preenchidas em uma coluna, começando de uma célula específica (assim como usamos Control + Shift + tecla de seta para baixo para selecionar todas as células preenchidas), você pode use o código abaixo:
Sub HighlightNegativeCells () Dim Cll As Range Dim Rng As Range Set Rng = Range ("A1", Range ("A1"). End (xlDown)) Para Cada Cll In Rng If Cll.Value <0 Then Cll.Interior.Color = vbRed End If Next Cll End Sub
No exemplo acima, não importa quantas células preenchidas existem. Ele começará na célula A1 e analisará todas as células preenchidas contíguas na coluna.
Você também não precisa ter a célula A1 selecionada. Você pode ter qualquer célula distante selecionada e quando o código for executado, ele ainda irá considerar todas as células na coluna A (começando em A1) e colorir as células negativas.
Declaração de ‘Sair para’
Você pode usar a instrução ‘Exit For’ no loop For Each-Next para sair do loop. Isso geralmente é feito no caso de uma condição específica ser atendida.
Por exemplo, no Exemplo 3, conforme estamos passando por um conjunto de células, pode ser mais eficiente verificar se há algum valor negativo ou não. Caso não haja valores negativos, podemos simplesmente sair do loop e economizar algum tempo de processamento do VBA.
Abaixo está o código VBA que fará isso:
Sub HighlightNegativeCells () Dim Cll As Range Para Cada Cll In Selection If WorksheetFunction.Min (Selection)> = 0 Then Exit For If Cll.Value <0 Then Cll.Interior.Color = vbRed End If Next Cll End Sub
Onde colocar o código VBA
Quer saber para onde o código VBA vai em sua pasta de trabalho do Excel?
O Excel tem um back-end VBA chamado editor VBA. Você precisa copiar e colar o código na janela de código do módulo do Editor VB.
Aqui estão as etapas para fazer isso:
- Vá para a guia Desenvolvedor.
- Clique na opção Visual Basic. Isso abrirá o editor VB no backend.
- No painel Project Explorer no VB Editor, clique com o botão direito em qualquer objeto da pasta de trabalho na qual deseja inserir o código. Se você não vir o Project Explorer, vá para a guia View e clique em Project Explorer.
- Vá para Inserir e clique em Módulo. Isso irá inserir um objeto de módulo para sua pasta de trabalho.
- Copie e cole o código na janela do módulo.
