El tipus de bucle més senzill de la programació d'Excel VBA és un bucle For-Next. El bucle està controlat per una variable de comptador, que comença en un valor i s'atura en un altre valor. Les declaracions entre la instrucció For i la sentència Next són les declaracions que es repeteixen en el bucle.
Un exemple de For-Next
L'exemple següent utilitza un bucle For-Next per sumar els primers 1.000 nombres positius. La variable Total comença com a zero. Aleshores es produeix el bucle. La variable Cnt és el comptador de bucles. Comença com a 1 i s'incrementa en 1 cada vegada a través del bucle. El bucle acaba quan Cnt és 1.000.
Aquest exemple només té una declaració dins del bucle. Aquesta declaració afegeix el valor de Cnt a la variable Total. Quan acaba el bucle, un MsgBox mostra la suma dels números.
Sub AddNumbers()
Dim total com a doble
Dim Cnt As Long
Total = 0
Per a Cnt = 1 a 1000
Total = Total + Cnt
Següent Cnt
MsgBox Total
End Sub
Com que el comptador de bucles és una variable normal, podeu escriure codi per canviar-ne el valor dins del bloc de codi entre les sentències For i Next. Aquesta, però, és una molt mala pràctica.
Exemples de For-Next amb un pas
Podeu utilitzar un valor de pas per saltar alguns valors del comptador en un bucle For-Next. Aquí teniu l'exemple anterior, reescrit per sumar només els nombres senars entre 1 i 1.000:
Sub AddOddNumbers()
Dim total com a doble
Dim Cnt As Long
Total = 0
Per a Cnt = 1 a 1000 Pas 2
Total = Total + Cnt
Següent Cnt
MsgBox Total
End Sub
Aquesta vegada, Cnt comença com a 1 i després pren valors de 3, 5, 7, etc. El valor Step determina com s'incrementa el comptador. Tingueu en compte que el valor del bucle superior (1000) no s'utilitza realment perquè el valor més alt de Cnt serà 999.
Aquí hi ha un altre exemple que utilitza un valor de pas de 3. Aquest procediment funciona amb el full actiu i aplica un ombrejat gris clar a cada tercera fila, des de la fila 1 fins a la fila 100.
Sub-ombraEveryThirdRow()
Dim i As Long
Per i = 1 a 100 Pas 3
Files(i).Interior.Color = RGB(200, 200, 200)
A continuació i
End Sub
Consulteu el resultat d'executar aquesta macro.
Ús d'un bucle per aplicar ombrejat de fons a les files.
Un exemple de For-Next amb una instrucció Exit For
Un bucle For-Next també pot incloure una o més sentències Exit For dins del bucle. Quan VBA troba aquesta declaració, el bucle finalitza immediatament.
L'exemple següent mostra la instrucció Exit For. Aquesta rutina és un procediment de funció, destinat a ser utilitzat en una fórmula de full de treball. La funció accepta un argument (una variable anomenada Str) i retorna els caràcters a l'esquerra del primer dígit numèric. Per exemple, si l'argument és "KBR98Z", la funció retorna "KBR".
Funció TextPart(Str)
Dim i As Long
TextPart = ""
Per i = 1 a Len(Str)
Si IsNumeric(Mid(Str, i, 1)) Aleshores
Sortida per
Altrament
TextPart = TextPart & Mid(Str, i, 1)
Acaba si
A continuació i
Funció final
El bucle For-Next comença amb 1 i acaba amb el número que representa el nombre de caràcters de la cadena. El codi utilitza la funció Mid de VBA per extreure un sol caràcter dins del bucle. Si es troba un caràcter numèric, s'executa la instrucció Exit For i el bucle finalitza abans d'hora.
Si el caràcter no és numèric, s'afegeix al valor retornat (que és el mateix que el nom de la funció). L'única vegada que el bucle examinarà tots els caràcters és si la cadena passat com a argument no conté caràcters numèrics.
Un exemple imbricat For-Next
Podeu tenir qualsevol nombre de declaracions al bucle i anidar bucles For-Next dins d'altres bucles For-Next.
L'exemple següent utilitza un bucle For-Next imbricat per inserir números aleatoris en un rang de cel·les de 12 files per 5 columnes. Observeu que la rutina executa el bucle intern (el bucle amb el comptador de fila) una vegada per cada iteració del bucle exterior (el bucle amb el comptador de Col). En altres paraules, la rutina executa la sentència Cells(Row, Col) = Rnd 60 vegades.
Aquestes cel·les es van omplir mitjançant un bucle For-Next imbricat.
Sub FillRange()
Dim Col As Long
Dim Row As Long
Per a Col = 1 a 5
Per a fila = 1 a 12
Cel·les (Fila, Col) = Rnd
Fila següent
Següent Col
End Sub
L'exemple següent utilitza bucles For-Next imbricats per inicialitzar una matriu tridimensional amb el valor 100. Aquesta rutina executa la instrucció al mig de tots els bucles (la instrucció d'assignació) 1.000 vegades (10 * 10 * 10), cada vegada. amb una combinació diferent de valors per a i, j i k:
Sub NestedLoops ()
Dim MyArray (10, 10, 10)
Dim i As Long
Dim j As Long
Dim k As Long
Per i = 1 a 10
Per a j = 1 a 10
Per a k = 1 a 10
La meva matriu (i, j, k) = 100
Següent k
Següent j
A continuació i
' Altres declaracions van aquí
End Sub
Aquí teniu un exemple final que utilitza bucles For-Next imbricats, amb un valor de pas. Aquest procediment crea un tauler d'escacs canviant el color de fons de les cel·les alternes.
Ús de bucles per crear un patró de tauler d'escacs.
El comptador de fila fa un bucle de l'1 al 8. Una construcció If-Then determina quina estructura imbricada For-Next cal utilitzar. Per a les files senars, el comptador Col comença amb 2. Per a les files parelles, el comptador Col comença amb 1. Els dos bucles utilitzen un valor de pas de 2, de manera que les cel·les alternatives es veuen afectades. Dues declaracions addicionals fan quadrades les cel·les (com un tauler d'escacs real).
Sub MakeCheckerboard ()
Dim R As Long, C As Long
Per a R = 1 a 8
Si WorksheetFunction.IsOdd(R) Aleshores
Per a C = 2 a 8 Pas 2
Cel·les(R, C).Interior.Color = 255
Següent C
Altrament
Per a C = 1 a 8 Pas 2
Cel·les(R, C).Interior.Color = 255
Següent C
Acaba si
Següent R
Files ("1:8"). RowHeight = 35
Columnes ("A:H"). ColumnWidth = 6,5
End Sub