Para tirar o máximo partido do Excel e VBA, é necessário saber como usar os loops de forma eficiente.
Em VBA, os loops permitem passar por um conjunto de objectos/valores e analisá-los um a um. Também pode executar tarefas específicas para cada laço.
Aqui está um exemplo simples de utilização de loops VBA no Excel.
Se tiver um conjunto de dados e quiser destacar todas as células em linhas pares. Pode usar um laço VBA para percorrer o intervalo e analisar cada número de linha de células. Se se tornar uniforme, dá-lhe uma cor, ou então deixa-o como está.
Agora isto, claro, é muito simples de looping em Excel VBA (e também pode fazer isto usando formatação condicional).
Na vida real, pode fazer muito mais com loops VBA em Excel que o podem ajudar a automatizar tarefas.
Aqui estão alguns exemplos mais práticos onde loops VBA podem ser úteis:
- Looping através de uma gama de células e analisando cada célula (destacar células com um texto específico no mesmo).
- Looping através de todas as folhas de trabalho e fazer algo com cada uma (tal como protegê-la/desproteger).
- Looping através de todas as pastas de trabalho abertas (e guardar cada pasta de trabalho ou fechar todas excepto a pasta de trabalho activa).
li>Loop through all the characters in a cell (and extract the numeric part from a string).li>Loop through all the values an array.li>Loop through all the charts/objects (and give a border or change the background color).
Agora a melhor utilização dos loops em Excel VBA, é necessário conhecer os diferentes tipos que existem e a sintaxe correcta de cada um.
p>
Neste tutorial, vou mostrar diferentes tipos de loops em Excel VBA e cobrir alguns exemplos para cada loop
Se estiver interessado em aprender VBA da forma mais fácil, consulte o meu Treino Online de VBA em Excel.
Este Tutorial Cobre:
Para o Próximo Laço
O laço ‘Para o Próximo’ permite-lhe passar por um bloco de código para o número de vezes especificado.
Por exemplo, se lhe pedir para adicionar os números inteiros de 1 a 10 manualmente, adicionaria os dois primeiros números, depois adicionaria o terceiro número ao resultado, e depois adicionaria o quarto número ao resultado, como assim por diante..
Não é?
A mesma lógica é usada no laço Para Próximo laço em VBA.
Especifique quantas vezes quer que o laço seja executado e também especifique o que quer que o código faça cada vez que o laço é executado.
Below é a sintaxe do For Next loop:
For Counter = Start To End Next
No For Next loop, pode usar um Contador (ou qualquer variável) que será usado para correr o loop. Este contador permite-lhe executar este laço por um número de vezes.
Por exemplo, se eu quiser adicionar os primeiros 10 inteiros positivos, então o meu valor de Contador seria de 1 a 10.
Vamos ver alguns exemplos para compreender melhor como funciona o laço Para o Próximo.
Exemplo 1 – Adicionando os primeiros 10 inteiros positivos
Below é o código que irá adicionar os primeiros 10 inteiros positivos usando um Para Próximo laço.
Ihe será então mostrada uma caixa de mensagem mostrando a soma destes números.
Sub AddNumbers()Dim Total As IntegerDim Count As IntegerTotal = 0For Count = 1 To 10Total = Total + CountNext CountMsgBox TotalEnd Sub
Neste código, o valor do Total é definido para 0 antes de entrar no laço do For Next loop.
Após entrar no laço, mantém o valor total após cada laço. Assim, após o primeiro laço, quando o Contador é 1, o valor ‘Total’ torna-se 1, e após o segundo laço torna-se 3 (1+2), e assim por diante.
E finalmente, quando o laço termina, a variável ‘Total’ tem a soma dos primeiros 10 inteiros positivos.
A MsgBox então simplesmente exibe o resultado numa caixa de mensagens.
Exemplo 2 – Adicionando os primeiros 5 inteiros mesmo positivos
Para somar os primeiros 5 inteiros mesmo positivos (ou seja, 2,4,6,8, e 10), é necessário um código semelhante com a condição de considerar apenas os números pares e ignorar os números ímpares.
Aqui está um código que o fará:
Sub AddEvenNumbers()Dim Total As IntegerDim Count As IntegerTotal = 0For Count = 2 To 10 Step 2Total = Total + CountNext CountMsgBox TotalEnd Sub
Nota que iniciámos o valor de ‘Contagem a partir de 2 e também usámos ‘Passo 2’.
Quando se usa ‘Passo 2’, diz-se ao código para aumentar o valor de ‘Contagem’ em 2 cada vez que o laço é executado.
Então o valor de Contagem começa a partir de 2 e depois passa a 4, 6, 8 e 10 à medida que o laço ocorre.
NOTE: Outra forma de o fazer poderia ser correr o laço de 1 a 10 e dentro do laço verificar se o número é par ou ímpar. No entanto, utilizar Step, neste caso, é uma forma mais eficiente, uma vez que não requer que o laço funcione 10 vezes, mas apenas 5 vezes.
O valor de Step também pode ser negativo. Neste caso, o Contador começa com um valor mais alto e continua a ser decrescido pelo valor de Passo especificado.
Exemplo 3 – Introduzir Número de Série nas Células Seleccionadas
P>Pode também usar o Para Próximo laço para percorrer uma colecção de objectos (tais como células ou folhas de trabalho ou livros de trabalho),
Aqui está um exemplo que introduz rapidamente números de série em todas as células seleccionadas.
Sub EnterSerialNumber()Dim Rng As RangeDim Counter As IntegerDim RowCount As IntegerSet Rng = SelectionRowCount = Rng.Rows.CountFor Counter = 1 To RowCountActiveCell.Offset(Counter - 1, 0).Value = CounterNext CounterEnd Sub
O código acima conta primeiro o número de linhas seleccionadas e depois atribui este valor à variável RowCount. Executamos então o laço de ‘1 a RowCount’.
Antes note-se que uma vez que a selecção pode ser qualquer número de linhas, definimos a variável Rng como Selection (com a linha ‘Set Rng = Selection’). Agora podemos usar a variável ‘Rng’ para nos referirmos à selecção no nosso código.
Exemplo 4 – Proteger todas as folhas de trabalho na pasta de trabalho activa
P>Pode usar o laço ‘For Next’ para percorrer todas as folhas de trabalho na pasta de trabalho activa, e proteger (ou desproteger) cada uma das folhas de trabalho.
Below é o código que fará isto:
Sub ProtectWorksheets()Dim i As IntegerFor i = 1 To ActiveWorkbook.Worksheets.CountWorksheets(i).ProtectNext iEnd Sub
O código acima conta o número de folhas usando ActiveWorkbook.Worksheets.Count. Isto diz a VBA quantas vezes o laço precisa de ser executado.
Em cada instância, refere-se à Ith workbook (usando Worksheets(i)) e protege-a.
P>Pode usar este mesmo código para Desproteger também as folhas de trabalho. Basta alterar a linha Worksheets(i).Protect to Worksheets(i).UnProtect.
Loops ‘For Next’ Aninhados
P>Pode usar loops ‘For Next’ aninhados para obter uma automatização mais complexa em Excel. Um loop ‘For Next’ aninhado significaria que existe um loop ‘For Next’ dentro de um loop ‘For Next’.
Deixe-me mostrar-lhe como usar isto usando um exemplo.
Suponha que tenho 5 pastas de trabalho abertas no meu sistema e quero proteger todas as folhas de trabalho em todas estas pastas de trabalho.
Below é o código que fará isto:
Sub ProtectWorksheets()Dim i As IntegerDim j As IntegerFor i = 1 To Workbooks.CountFor j = 1 To Workbooks(i).Worksheets.CountWorkbooks(i).Worksheets(j).ProtectNext jNext iEnd Sub
O acima é um loop Aninhado Para Próximo, uma vez que utilizámos um Para Próximo loop dentro de outro.
‘EXIT For’ Declarações em Para Próximos Laços
A declaração ‘Exit For’ permite-lhe sair completamente do laço ‘For Next’.
Pode utilizá-lo nos casos em que pretende que o laço For Next termine quando uma determinada condição é satisfeita.
Vamos dar um exemplo onde tem um conjunto de números na Coluna A e pretende destacar todos os números negativos em fonte vermelha. Neste caso, precisamos de analisar cada célula pelo seu valor e depois alterar a cor da fonte em conformidade.
Mas para tornar o código mais eficiente, podemos primeiro verificar se existem ou não valores negativos na lista. Se não houver valores negativos, podemos usar a instrução ‘Exit For’ para simplesmente sair do código.
Below é o código que faz isto:
Sub HghlightNegative()Dim Rng As RangeSet Rng = Range("A1", Range("A1").End(xlDown))Counter = Rng.CountFor i = 1 To CounterIf WorksheetFunction.Min(Rng) >= 0 Then Exit ForIf Rng(i).Value < 0 Then Rng(i).Font.Color = vbRedNext iEnd Sub
Quando se usa a instrução ‘Exit For’ dentro de um laço aninhado ‘For Next’, ele sairá do laço no qual é executado e continuará a executar a linha seguinte no código após o laço For Next.
Por exemplo, no código abaixo, a instrução ‘Exit For’ tirá-lo-á do laço interno, mas o laço externo continuará a funcionar.
Sub SampleCode()For i = 1 To 10For j = 1 to 10Exit ForNext JNext iEnd Sub
Faz Enquanto Loop
Um loop ‘Faz Enquanto’ permite-lhe verificar uma condição e executar o loop enquanto essa condição é satisfeita (ou é VERDADEIRA).
Existem dois tipos de sintaxe no loop Faz Enquanto.
Do Loop
and
DoLoop
A diferença entre estes 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 depois a condição While é verificada.
Isso significa que se a condição While for False é ambos os casos, o código ainda será executado pelo menos uma vez no segundo caso (uma vez que a condição While é verificada após o código ter sido executado uma vez).
Agora vamos ver alguns exemplos de utilização de laços de Do While em VBA.
Exemplo 1 – Adicionar os primeiros 10 inteiros positivos utilizando VBA
Componha-se a adicionar os primeiros dez inteiros positivos utilizando o laço Do While em VBA.
Para o fazer, pode utilizar o laço Do While até que o próximo número seja inferior ou igual a 10. Assim que o número for superior a 1o, o seu loop pararia.
Aqui está o código VBA que irá executar este loop Do While e mostrar o resultado numa caixa de mensagem.
Sub AddFirst10PositiveIntegers()Dim i As Integeri = 1Do While i <= 10Result = Result + ii = i + 1LoopMsgBox ResultEnd Sub
O loop acima continua a funcionar até o valor de ‘i’ se tornar 11. Assim que se torna 11, o laço termina (como a condição While se torna False).
Within the loop, utilizámos uma variável Resultado que detém o valor final Uma vez concluído o laço, uma caixa de mensagem mostra o valor da variável ‘Resultado’.
Exemplo 2 – Introduzir datas para o mês corrente
Digamos que pretende introduzir todas as datas do mês corrente numa coluna da folha de trabalho.
Pode fazê-lo usando o seguinte código Fazer Enquanto o loop é código:
Sub EnterCurrentMonthDates()Dim CMDate As DateDim i As Integeri = 0CMDate = DateSerial(Year(Date), Month(Date), 1)Do While Month(CMDate) = Month(Date)Range("A1").Offset(i, 0) = CMDatei = i + 1CMDate = CMDate + 1LoopEnd Sub
O código acima introduzirá todas as datas na primeira coluna da folha de trabalho (a partir de A1). Os loops continuam até ao valor do mês da variável “CMDate” corresponder ao do mês corrente.
Exit Do Statement
P>Pode usar a declaração Exit Do para sair do loop. Assim que o código executa a linha ‘Exit Do’, sai do laço do Do While e passa o controlo para a linha seguinte logo a seguir ao laço.
Por exemplo, se quiser introduzir apenas as primeiras 10 datas, então pode sair do laço assim que as primeiras 10 datas forem introduzidas.
O código abaixo fará isto:
Sub EnterCurrentMonthDates()Dim CMDate As DateDim i As Integeri = 0CMDate = DateSerial(Year(Date), Month(Date), 1)Do While Month(CMDate) = Month(Date)Range("A1").Offset(i, 0) = CMDatei = i + 1If i >= 10 Then Exit DoCMDate = CMDate + 1LoopEnd Sub
No código acima, a declaração IF é utilizada para verificar se o valor de i é superior a 10 ou não. Assim que o valor de “i” se torna 10, a declaração de Saída Do é executada e o laço termina.
Do Until Loop
‘Do Until’ loops são muito semelhantes aos loops ‘Do While’.
Em ‘Do While’, o laço corre até que a condição dada seja satisfeita, enquanto que em ‘Do Until’, o laço corre até que a condição especificada seja satisfeita.
Existem dois tipos de sintaxe no laço Do Until Loop.
Do Loop
and
DoLoop
A diferença entre estes 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 depois a condição Até é verificada.
Isto significa que se a condição Até for VERDADEIRA é 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 utilização de loops Do Until em VBA.
Nota: Todos os exemplos para Do Until são os mesmos que o de Do While. Estes foram modificados para lhe mostrar como funciona o loop Do Until.
Exemplo 1 – Adicionar os primeiros 10 inteiros positivos usando VBA
P>Se quiser adicionar os primeiros dez inteiros positivos usando o loop Do Until em VBA.
Para o fazer, precisa de correr o loop até que o próximo número seja inferior ou igual a 10. Assim que o número for superior a 1o, o seu laço pararia.
Aqui está o código VBA que irá executar este laço e mostrar o resultado numa caixa de mensagem.
Sub AddFirst10PositiveIntegers()Dim i As Integeri = 1Do Until i > 10Result = Result + ii = i + 1LoopMsgBox ResultEnd Sub
O laço acima continua a funcionar até que o valor de ‘i’ se torne 11. Assim que se torna 11, o laço termina (como a condição ‘Até’ se torna Verdadeiro).
Exemplo 2 – Introduzir datas para o Mês Actual
Vamos dizer que pretende introduzir todas as datas do mês actual numa coluna de folha de trabalho.
P>Pode fazer isso usando o seguinte código Do Until loop:
Sub EnterCurrentMonthDates()Dim CMDate As DateDim i As Integeri = 0CMDate = DateSerial(Year(Date), Month(Date), 1)Do Until Month(CMDate) <> Month(Date)Range("A1").Offset(i, 0) = CMDatei = i + 1CMDate = CMDate + 1LoopEnd Sub
O código acima introduziria todas as datas na primeira coluna da folha de trabalho (a partir de A1). O laço continua até ao Mês da variável CMDate não é igual ao do mês corrente.
Sair Do Statement
Pode usar a declaração ‘Exit Do’ para sair do laço.
Assim que o código executa a linha ‘Exit Do’, sai do laço Do Until e passa o controlo para a linha seguinte logo após o laço.
Por exemplo, se quiser introduzir apenas as primeiras 10 datas, então pode sair do laço assim que as primeiras 10 datas forem introduzidas.
O código abaixo fará isto:
Sub EnterCurrentMonthDates()Dim CMDate As DateDim i As Integeri = 0CMDate = DateSerial(Year(Date), Month(Date), 1)Do Until Month(CMDate) <> Month(Date)Range("A1").Offset(i, 0) = CMDatei = i + 1If i >= 10 Then Exit DoCMDate = CMDate + 1LoopEnd Sub
No código acima, assim que o valor de ‘i’ se torna 10, a estatística de Saída do Do é executada e o laço termina.
Para Cada
Em VBA, pode fazer um laço através de um conjunto de colecções usando o laço ‘Para Cada’.
Aqui estão alguns exemplos de colecções em Excel VBA:
- Uma colecção de todas as pastas de trabalho abertas.
- Uma colecção de todas as pastas de trabalho.
- Uma colecção de todas as células de uma gama de células seleccionadas.
- Uma colecção de todos os gráficos ou formas da pasta de trabalho.
Utilizando o laço ‘Para Cada’, é possível percorrer cada um dos objectos de uma colecção e realizar alguma acção sobre ela.
Por exemplo, pode percorrer todas as folhas de trabalho de uma pasta de trabalho e protegê-las, ou pode percorrer todas as células da selecção e alterar a formatação.
Com o laço ‘For Each’ (também referido como o laço ‘For Each-Next’), não precisa de saber quantos objectos existem numa colecção.
‘For Each’ laço percorreria automaticamente cada objecto e executaria a acção especificada. Por exemplo, se quiser proteger todas as folhas de trabalho de uma pasta de trabalho, o código seria o mesmo quer tenha uma pasta de trabalho com 3 folhas de trabalho ou 30 folhas de trabalho.
Aqui está a sintaxe de For Each-Next loop in Excel VBA.
For Each element In collectionNext
Agora vejamos alguns exemplos de utilização do For Each Loop in Excel.
Exemplo 1 – Percorrer todas as folhas de trabalho de uma Pasta de Trabalho (e protegê-la)
Se tiver uma pasta de trabalho onde queira proteger todas as folhas de trabalho.
Below For Each-Next loop pode fazer isto facilmente:
Sub ProtectSheets()Dim ws As WorksheetFor Each ws In ActiveWorkbook.Worksheetsws.ProtectNext wsEnd Sub
No código acima, definimos a variável ‘ws’ como um objecto de Folha de Trabalho. Isto diz à VBA que ‘ws’ deve ser interpretado como um objecto de folha de trabalho no código.
Agora utilizamos a declaração ‘For Each’ para percorrer cada ‘ws’ (que é um objecto de folha de trabalho) na colecção de todas as folhas de trabalho da pasta de trabalho activa (dada por ActiveWorkbook.Worksheets).
Nota que, ao contrário de outros loops em que tentámos proteger todas as folhas de trabalho de uma pasta de trabalho, aqui não precisamos de nos preocupar com quantas folhas de trabalho existem na pasta de trabalho.
Não precisamos de as contar para correr o loop. Para cada laço assegura que todos os objectos são analisados um a um.
Exemplo 2 – Passar por Todas as Pastas de Trabalho Abertas (e Guardar Todas)
Se trabalhar com várias pastas de trabalho ao mesmo tempo, pode ser útil poder guardar todas estas pastas de trabalho ao mesmo tempo.
Below código VBA pode fazer isto por nós:
Sub SaveAllWorkbooks()Dim wb As WorkbookFor Each wb In Workbookswb.SaveNext wbEnd Sub
Nota que neste código, não recebe uma solicitação que lhe peça para guardar a pasta de trabalho num local específico (se a guardar pela primeira vez).
Guarda-a na pasta por defeito (no meu caso, era a pasta ‘Documentos’). Este código funciona melhor quando estes ficheiros já estão guardados e está a fazer alterações e pretende guardar todas as pastas de trabalho rapidamente.
Exemplo 3 – Passar por todas as células numa selecção (Realçar valores negativos)
Usando o laço ‘Para cada’, pode passar por todas as células de um intervalo específico ou no intervalo seleccionado.
Isto pode ser útil quando se pretende analisar cada célula e executar uma acção baseada nela.
Por exemplo, abaixo está o código que irá percorrer todas as células da selecção e alterar a cor das células com valores negativos para vermelho.
Sub HighlightNegativeCells()Dim Cll As RangeFor Each Cll In SelectionIf Cll.Value < 0 ThenCll.Interior.Color = vbRedEnd IfNext CllEnd Sub
(Nota: usei Cll como um pequeno nome de variável para Célula. É aconselhável não usar nomes de objectos como Folhas ou Gama como nomes de variáveis)
No código acima, o laço Para Each-Next passa pela recolha de células na selecção. A declaração IF é utilizada para identificar se o valor da célula é negativo ou não. Caso o seja, a célula recebe uma cor interior vermelha, caso contrário vai para a célula seguinte.
No caso de não ter uma selecção, e em vez disso querer que VBA seleccione todas as células preenchidas numa coluna, começando por uma célula específica (tal como usamos Control + Shift + tecla de seta para baixo para seleccionar todas as células preenchidas), pode usar o código abaixo:
Sub HighlightNegativeCells()Dim Cll As RangeDim Rng As RangeSet Rng = Range("A1", Range("A1").End(xlDown))For Each Cll In RngIf Cll.Value < 0 ThenCll.Interior.Color = vbRedEnd IfNext CllEnd Sub
No exemplo acima, não importa quantas células preenchidas existem. Começará a partir da célula A1 e analisará todas as células contíguas preenchidas na coluna.
Também não precisa de ter a célula A1 seleccionada. Pode ter qualquer célula distante seleccionada e quando o código for executado, ainda considerará todas as células da coluna A (começando por A1) e colorirá as células negativas.
‘Exit For’ Statment
Pode usar a declaração ‘Exit For’ no laço For Each-Next para sair do laço. Isto é normalmente feito no caso de uma condição específica ser satisfeita.
Por exemplo, no Exemplo 3, como estamos a passar por um conjunto de células, pode ser mais eficiente verificar se existem ou não valores negativos. Caso não existam valores negativos, podemos simplesmente sair do loop e poupar algum tempo de processamento VBA.
Below é o código VBA que o fará:
Sub HighlightNegativeCells()Dim Cll As RangeFor Each Cll In SelectionIf WorksheetFunction.Min(Selection) >= 0 Then Exit ForIf Cll.Value < 0 ThenCll.Interior.Color = vbRedEnd IfNext CllEnd Sub
Onde colocar o código VBA
Interrogando-se para onde vai o código VBA na sua pasta de trabalho Excel?
Excel tem um backend VBA chamado editor VBA. Tem de copiar e colar o código na janela de código do módulo Editor VB.
Aqui estão os passos para o fazer:
- Vá para a aba Desenvolvedor.
- Clique na opção Visual Basic. Isto abrirá o editor VB no backend.
- No painel Project Explorer no Editor VB, clique com o botão direito do rato em qualquer objecto da pasta de trabalho em que deseja inserir o código. Se não vir o Project Explorer vá ao separador View e clique em Project Explorer.
- Copiar e colar o código na janela do módulo.
li>I>Vá a Insert e clique em Module. Isto irá inserir um objecto módulo para a sua pasta de trabalho.
Pode também gostar dos seguintes tutoriais do Excel:
- Como gravar uma macro no Excel.
- Criando funções definidas pelo utilizador no Excel.
- Excel VBA Msgbox
- Como executar uma macro no Excel.
- Como Criar e Usar Add-ins no Excel.
- Eventos Excel VBA – Um Guia Fácil (e Completo).
- Como Ordenar Dados no Excel usando VBA (Um Guia Passo a Passo).
- 24 Exemplos úteis de Macro Excel para Iniciantes em VBA (Pronto a usar).
- Como Usar a Função InStr do Excel VBA (com exemplos práticos).
- Excel Personal Macro Workbook | Save & Use Macros em todas as Pastas de Trabalho.
- Usando Caso Seleccionado em Excel VBA.