За да работите ефективно във VBA, трябва да разбирате цикли.
Цикли ви позволяват да повтаряте кодов блок определен брой пъти или да повтаряте кодов блок за всеки обект в набор от обекти.
Първо ще ви покажем няколко примера, за да ви покажем на какво са способни контурите. След това ще ви научим на всичко за контурите.
Бързи примери за цикъл на VBA
За всеки цикъл
За всеки цикъл преминава през всеки обект в колекция, като например всеки работен лист в работна книга или всяка клетка в диапазон.
Прегледайте всички работни листове в работна книга
Този код ще премине през всички работни листове в работната книга, разкривайки всеки лист:
| 12345678 | Sub LoopThroughSheets ()Затъмнете като работен листЗа всеки ws в работни листовеws.Visible = ВярноСледващияEnd Sub |
Превъртете през всички клетки в обхвата
Този код ще премине през диапазон от клетки, като проверява дали стойността на клетката е отрицателна, положителна или нула:
| 1234567891011121314 | Sub If_Loop ()Dim Cell като диапазонЗа всяка клетка в обхвата ("A2: A6")Ако Cell.Value> 0 ТогаваCell.Offset (0, 1) .Value = "Положително"ElseIf Cell.Value <0 ТогаваCell.Offset (0, 1) .Value = "Отрицателно"ИначеCell.Offset (0, 1) .Value = "Нула"Край АкоСледваща клеткаEnd Sub |
За следващите цикли
Друг вид цикъл „For“ е For Next Loop. For Next Loop ви позволява да преминете през цели числа.
Този код ще премине през цели числа от 1 до 10, показвайки всеки с поле за съобщение:
| 123456 | Sub ForLoop ()Dim i As IntegerЗа i = 1 до 10MsgBox iСледва iEnd Sub |
Направете цикли „Докато“
Do While Loops ще циклира, докато е изпълнено условие. Този код също ще премине през цели числа от 1 до 10, показвайки всеки с поле за съобщение.
| 12345678 | Sub DoWhileLoop ()Dim n като цяло числоn = 1Направете Докато n <11MsgBox nn = n + 1ЦикълEnd Sub |
Направете до цикли
Обратно, цикълът Do Do Loops ще циклира, докато не бъде изпълнено условие. Този код прави същото като предишните два примера.
| 12345678 | Sub DoUntilLoop ()Dim n като цяло числоn = 1Направете, докато n> = 10MsgBox nn = n + 1ЦикълEnd Sub |
Ще обсъдим това по -долу, но трябва да бъдете изключително внимателни, когато създавате цикли Do While или Do Until, за да не създавате безкраен цикъл.
VBA Loop Builder
Това е екранна снимка на „Loop Builder“ от нашата добавка Premium VBA: AutoMacro. Конструкторът на цикли ви позволява бързо и лесно да изграждате цикли, за да преминете през различни обекти или числа. Можете да извършвате действия върху всеки обект и/или да избирате само обекти, които отговарят на определени критерии.
Добавката съдържа също много други конструктори на кодове, обширна библиотека с VBA код и асортимент от инструменти за кодиране. Това е задължително за всеки VBA разработчик.
Сега ще разгледаме различните видове контури в дълбочина.
VBA за следващия цикъл
За синтаксис на цикъл
For Next Loop ви позволява да повторите блок код определен брой пъти. Синтаксисът е:
| 12345 | [Dim Counter as Integer]For Counter = Начало до край [Стойност на стъпката][Направи нещо]Следващ [Брояч] |
Където елементите в скоби не са задължителни.
- [Dim Counter as Long] - Обявява променливата брояч. Изисква се, ако Option Explicit е декларирана в горната част на вашия модул.
- Брояч - Целочислена променлива, използвана за броене
- Старт - Началната стойност (Пример 1)
- Край - Крайната стойност (Пример 10)
- [Стойност на стъпката] - Позволява ви да броите всяко n цяло число вместо всяко 1 цяло число. Можете също да преминете в обратна посока с отрицателна стойност (напр. Стъпка -1)
- [Направи нещо] - Кодът, който ще се повтори
- Следващ [Брояч] - Заключително изявление за For Next Loop. Можете да включите брояча или не. Силно препоръчвам да включите брояча, тъй като прави кода ви по -лесен за четене.
Ако това е объркващо, не се притеснявайте. Ще разгледаме някои примери:
Пребройте до 10
Този код ще брои до 10 с помощта на For-Next Loop:
| 12345678 | Sub ForEach_CountTo10 ()Dim n като цяло числоЗа n = 1 до 10MsgBox nСледващ nEnd Sub |
За Loop Step
Пребройте до 10 - само четни числа
Този код ще брои до 10, като брои само четни числа:
| 12345678 | Sub ForEach_CountTo10_Even ()Dim n като цяло числоЗа n = 2 до 10 Стъпка 2MsgBox nСледващ nEnd Sub |
Забележете, че добавихме „Стъпка 2“. Това казва на цикъла For да „стъпва“ през брояча с 2. Можем също да използваме отрицателна стойност на стъпката, за да стъпваме обратно:
За Loop Step - обратно
Отброяване от 10
Този код ще брои от 10:
| 123456789 | Sub ForEach_Countdown_Inverse ()Dim n като цяло числоЗа n = 10 до 1 Стъпка -1MsgBox nСледващ nMsgBox "Повдигане"End Sub |
Изтриване на редове, ако клетката е празна
Най-често съм използвал отрицателна стъпка For-Loop, за да преминавам през диапазони от клетки, изтривайки редове, които отговарят на определени критерии. Ако се въртите от горните редове към долните редове, докато изтривате редове, ще объркате брояча си.
Този пример ще изтрие редове с празни клетки (започвайки от долния ред):
| 12345678910 | Sub ForEach_DeleteRows_BlankCells ()Dim n като цяло числоЗа n = 10 до 1 Стъпка -1Ако Range ("a" & n) .Value = "" ТогаваRange ("a" & n) .EntireRow.DeleteКрай АкоСледващ nEnd Sub |
Вложено за цикъл
Можете да „вмъкнете“ един For Loop в друг For Loop. Ще използваме Nsted For Loops, за да създадем таблица за умножение:
| 1234567891011 | Sub Nested_ForEach_MultiplicationTable ()Затъмняване на ред като цяло число, колона като цяло числоЗа ред = 1 до 9За col = 1 до 9Клетки (ред + 1, колона + 1). Стойност = ред * колонаСледващ колСледващият редEnd Sub |
Излезте за
Инструкцията Exit For ви позволява незабавно да излезете от цикъла For Next.
Обикновено бихте използвали Exit For заедно с оператор If, излизайки от For Next Loop, ако е изпълнено определено условие.
Например, можете да използвате For Loop, за да намерите клетка. След като тази клетка бъде намерена, можете да излезете от цикъла, за да ускорите кода си.
Този код ще премине през редове 1 до 1000, търсейки „грешка“ в колона А. Ако бъде намерен, кодът ще избере клетката, ще ви предупреди за намерената грешка и ще излезе от цикъла:
| 12345678910111213 | Sub ExitFor_Loop ()Dim i As IntegerЗа i = 1 до 1000Ако Range ("A" & i) .Value = "error" ТогаваДиапазон ("A" & i). ИзберетеMsgBox „Грешка е намерена“Излезте заКрай АкоСледва iEnd Sub |
Важно: В случай на вложени за цикли, Exit For излиза само от текущия For Loop, не всички активни цикли.
Продължете за
VBA няма командата „Продължи“, която се намира във Visual Basic. Вместо това ще трябва да използвате „Изход“.
VBA за всеки цикъл
VBA за всеки цикъл ще премине през всички обекти в колекция:
- Всички клетки в диапазон
- Всички работни листове в работна книга
- Всички форми в работен лист
- Всички отворени работни книги
Можете също да използвате „Вложено за всеки цикъл“, за да:
- Всички клетки в диапазон на всички работни листове
- Всички фигури на всички работни листове
- Всички листове във всички отворени работни книги
- и така нататък…
Синтаксисът е:
| 123 | За всеки обект в колекцията[Направи нещо]Следващ [обект] |
Където:
- Обект - Променлива, представляваща диапазон, работен лист, работна книга, форма и т.н. (напр. Rng)
- колекция - Събиране на обекти (напр. Диапазон („a1: a10“)
- [Направи нещо] - Кодов блок за изпълнение на всеки обект
- Следващ [обект] - Заключително изказване. [Обект] не е задължителен, но силно се препоръчва.
За всяка клетка в обхвата
Този код ще премине през всяка клетка в диапазон:
| 123456789 | Sub ForEachCell_inRange ()Затъмняване на клетката като обхватЗа всяка клетка в обхвата ("a1: a10")cell.Value = cell.Offset (0,1) .ValueСледващата клеткаEnd Sub |
За всеки работен лист в работна книга
Този код ще премине през всички работни листове в работна книга, като ще премахне защитата на всеки лист:
| 123456789 | Sub ForEachSheet_inWorkbook ()Затъмнете като работен листЗа всеки ws в работни листовеws. Премахнете защитата на „паролата“Следващата wsEnd Sub |
За всяка отворена работна книга
Този код ще запише и затвори всички отворени работни книги:
| 123456789 | Sub ForEachWB_inWorkbooks ()Dim wb Като работна книгаЗа всяка wb в работни книгиwb.Close SaveChanges: = ИстинаСледваща wbEnd Sub |
За всяка форма в работен лист
Този код ще изтрие всички форми в активния лист.
| 123456789 | Sub ForEachShape ()Dim shp As ShapeЗа всеки shp в ActiveSheet.Shapesshp.DeleteСледващ шпEnd Sub |
За всяка форма във всеки работен лист в работна книга
Можете също така да вложите за всеки цикъл. Тук ще преминем през всички форми във всички работни листове в активната работна книга:
| 1234567891011 | Sub ForEachShape_inAllWorksheets ()Dim shp като форма, ws като работен листЗа всеки ws в работни листовеЗа всеки shp В ws.Shapesshp.DeleteСледващ шпСледващата wsEnd Sub |
За всеки - IF цикъл
Както споменахме по -горе, можете да използвате оператор If в цикъл, изпълнявайки действия само ако са изпълнени определени критерии.
Този код ще скрие всички празни редове в диапазон:
| 12345678910 | Sub ForEachCell_inRange ()Затъмняване на клетката като обхватЗа всяка клетка в обхвата ("a1: a10")Ако cell.Value = "" Тогава _cell.EntireRow.Hidden = ВярноСледващата клеткаEnd Sub |
VBA Do While Loop
VBA Do While и Do Do (вижте следващия раздел) са много сходни. Те ще повтарят цикъл, докато (или докато) е изпълнено условие.
Цикълът До докато ще повтори цикъл, докато е изпълнено условие.
Ето синтаксиса на Do While:
| 123 | Направи условие[Направи нещо]Цикъл |
Където:
- Състояние - Условието за тестване
- [Направи нещо] - Кодовият блок да се повтаря
Можете също така да настроите цикъл Do While с условието в края на цикъла:
| 123 | Направете[Направи нещо]Цикъл докато състояние |
Ще демонстрираме всеки от тях и ще покажем как се различават:
Правете До
Ето примера на цикъла Do While, който демонстрирахме по -рано:
| 12345678 | Sub DoWhileLoop ()Dim n като цяло числоn = 1Направете Докато n <11MsgBox nn = n + 1ЦикълEnd Sub |
Цикъл Докато
Сега нека изпълним същата процедура, освен че ще преместим условието в края на цикъла:
| 12345678 | Sub DoLoopWhile ()Dim n като цяло числоn = 1НаправетеMsgBox nn = n + 1Цикъл Докато n <11End Sub |
VBA Do Do Loop
Do Do Loops ще повтаря цикъл, докато не бъде изпълнено определено условие. Синтаксисът е по същество същият като циклите Do While:
| 123 | Направете до състояние[Направи нещо]Цикъл |
и по подобен начин условието може да отиде в началото или в края на цикъла:
| 123 | Направете[Направи нещо]Цикъл до състояние |
Правете до
Този цикъл do Until ще брои до 10, както в предишните ни примери
| 12345678 | Sub DoUntilLoop ()Dim n като цяло числоn = 1Правете до n> 10MsgBox nn = n + 1ЦикълEnd Sub |
Цикъл до
Този цикъл до цикъл до ще се брои до 10:
| 12345678 | Sub DoLoopUntil ()Dim n като цяло числоn = 1НаправетеMsgBox nn = n + 1Цикъл до n> 10End Sub |
Излезте от Do Loop
Подобно на използването на Exit For за излизане от For Loop, използвате командата Exit Do, за да излезете от Do Loop незабавно
| 1 | Излезте от Do |
Ето пример за Exit Do:
| 123456789101112131415 | Sub ExitDo_Loop ()Dim i As Integeri = 1Правете, докато i> 1000Ако Range ("A" & i) .Value = "error" ТогаваДиапазон ("A" & i). ИзберетеMsgBox „Грешка е намерена“Излезте от DoКрай Акоi = i + 1ЦикълEnd Sub |
Край или прекъсване на цикъла
Както споменахме по -горе, можете да използвате Exit For или Exit Do за изход от цикли:
| 1 | Излезте за |
| 1 | Излезте от Do |
Тези команди обаче трябва да бъдат добавени към кода ви, преди да изпълните цикъла си.
Ако се опитвате да „прекъснете“ цикъл, който се изпълнява в момента, можете да опитате да натиснете ESC или CTRL + Почивка на клавиатурата. Това обаче може да не работи. Ако не работи, ще трябва да изчакате приключването на вашия цикъл или, в случай на безкраен цикъл, да използвате CTRL + ALT + Изтрий за принудително затваряне на Excel.
Ето защо се опитвам да избягвам Do цикли, по -лесно е случайно да създам безкраен цикъл, принуждавайки ви да рестартирате Excel, което потенциално ще загуби работата ви.
Още примери за цикли
Циклични редове
Това ще премине през всички редове в колона:
| 123456789 | Public Sub LoopThroughRows ()Затъмняване на клетката като обхватЗа всяка клетка в обхвата ("A: A")Ff cell.value "" след това MsgBox cell.address & ":" & cell.valueСледващата клеткаEnd Sub |
Цикъл през колони
Това ще премине през всички колони в един ред:
| 123456789 | Public Sub LoopThroughColumns ()Затъмняване на клетката като обхватЗа всяка клетка в обхвата ("1: 1")If cell.Value "" Тогава MsgBox cell.Address & ":" & cell.ValueСледващата клеткаEnd Sub |
Превъртайте файлове в папка
Този код ще премине през всички файлове в папка, създавайки списък:
| 12345678910111213141516171819 | Sub LoopThroughFiles ()Dim oFSO As ObjectЗатъмняване на папка като обектDim oFile As ObjectDim i As IntegerЗадайте oFSO = CreateObject ("Scripting.FileSystemObject")Задайте oFolder = oFSO.GetFolder ("C: \ Demo)i = 2За всеки oFile In oFolder.FilesRange ("A" & i) .value = oFile.Namei = i + 1Следващ oFileEnd Sub |
Цикъл през масив
Този код ще премине през масива „arrList“:
| 123 | За i = LBound (arrList) Към UBound (arrList)MsgBox arrList (i)Следва i |
Функцията LBound получава „долната граница“ на масива, а UBound получава „горната граница“.
Цикли в Access VBA
Повечето от горните примери също ще работят в Access VBA. Въпреки това, в Access, ние се въртим през Recordset Object, а не Range Object.
| 123456789101112131415161718 | Sub LoopThroughRecords ()На грешка възобновяване следващоDim dbs като база данниDim rst As RecordsetЗадайте dbs = CurrentDbЗадайте rst = dbs.OpenRecordset ("tblClients", dbOpenDynaset)С първи.Преместване последно.Преместете първоНаправете до .EOF = ВярноMsgBox (rst.Fields ("ClientName")).ПреместванеСледЦикълКрай спърво ЗатвориЗадайте rst = НищоЗадайте dbs = НищоEnd Sub |
