За да извлечете максимума от Excel и VBA, трябва да знаете как да използвате цикли ефективно.
Във VBA цикли ви позволяват да преминете през набор от обекти/стойности и да го анализирате един по един. Можете също така да изпълнявате специфични задачи за всеки цикъл.
Ето един прост пример за използване на VBA цикли в Excel.
Да предположим, че имате набор от данни и искате да маркирате всички клетки в четни редове. Можете да използвате VBA цикъл, за да преминете през диапазона и да анализирате номера на всеки ред на клетката. Ако се окаже четен, му придавате цвят, в противен случай го оставяте такъв, какъвто е.
Това, разбира се, е много лесно за циклиране в Excel VBA (и можете също да направите това, като използвате условно форматиране).
В реалния живот можете да направите много повече с VBA цикли в Excel, които могат да ви помогнат да автоматизирате задачи.
Ето още няколко практически примера, при които VBA цикли могат да бъдат полезни:
- Преглеждане на редица клетки и анализ на всяка клетка (маркирайте клетки с определен текст в нея).
- Преглеждайте всички работни листове и правете нещо с всеки (например да го защитите/премахнете защитата).
- Прегледайте всички отворени работни книги (и запишете всяка работна книга или затворете всички, освен активната работна книга).
- Превъртете всички символи в клетка (и извлечете числовата част от низ).
- Прегледайте всички стойности в масив.
- Прегледайте всички диаграми/обекти (и дайте рамка или променете цвета на фона).
Сега, за да използвате най -добре цикли в Excel VBA, трябва да знаете за различните видове, които съществуват, и правилния синтаксис на всеки от тях.
В този урок ще покажа различни видове цикли на Excel VBA и ще разгледам няколко примера за всеки цикъл
Забележка: Това ще бъде огромен урок, в който ще се опитам да обхвана всеки VBA цикъл в някои подробности. Препоръчвам ви да маркирате тази страница за справка в бъдеще.Ако се интересувате да научите VBA по лесния начин, вижте моя Онлайн обучение по Excel VBA.
За следващия цикъл
Цикълът „За следващия“ ви позволява да преминете през блок код за определен брой пъти.
Например, ако ви помоля да добавите целите числа от 1 до 10 ръчно, ще добавите първите две числа, след това добавете третото число към резултата, след което добавете четвъртото число към резултата, така нататък …
Нали?
Същата логика се използва в цикъла For Next във VBA.
Вие посочвате колко пъти искате цикълът да се изпълнява и също така посочвате какво искате да прави кодът всеки път, когато цикълът се изпълнява.
По -долу е синтаксисът на цикъла For Next:
За брояч = Начало до край [Стъпка Стойност] [Блок код за изпълнение] Напред [брояч]
В цикъла For Next можете да използвате Counter (или всяка променлива), която ще се използва за стартиране на цикъла. Този брояч ви позволява да стартирате този цикъл за необходим брой пъти.
Например, ако искам да добавя първите 10 положителни числа, стойността на Counter ще бъде от 1 до 10.
Нека да разгледаме няколко примера, за да разберем по -добре как работи цикълът For Next.
Пример 1 - Добавяне на първите 10 положителни числа
По -долу е кодът, който ще добави първите 10 положителни числа, използвайки цикъл For Next.
След това ще се покаже поле за съобщение, показващо сумата от тези числа.
Sub AddNumbers () Dim Total Total As Integer Dim Count As Integer Total = 0 For Count = 1 до 10 Total = Total + Count Next Count MsgBox Total End Sub
В този код стойността на Total е зададена на 0, преди да влезе в цикъла For Next.
След като влезе в цикъла, той държи общата стойност след всеки цикъл. Така че след първия цикъл, когато Counter е 1, стойността „Total“ става 1, а след втория цикъл става 3 (1+2) и т.н.
И накрая, когато цикълът приключи, променливата „Total“ има сумата от първите 10 положителни числа.
MsgBox след това просто показва резултата в поле за съобщение.
Пример 2 - Добавяне на първите 5 четни положителни числа
За да сумирате първите пет четни положителни числа (т.е. 2,4,6,8 и 10), имате нужда от подобен код с условие да вземете предвид само четните числа и да игнорирате нечетните числа.
Ето код, който ще го направи:
Sub AddEvenNumbers () Dim Total As Integer Dim Count As Integer Total = 0 For Count = 2 до 10 Стъпка 2 Total = Total + Count Next Count MsgBox Total End Sub
Обърнете внимание, че започнахме стойността Count от 2 и също използвахме „Стъпка 2‘.
Когато използвате 'Стъпка 2', той казва на кода да увеличава стойността „Count“ с 2 всеки път, когато цикълът се изпълнява.
Така че стойността Count започва от 2 и след това става 4, 6, 8 и 10, когато настъпи цикълът.
ЗАБЕЛЕЖКА: Друг начин да направите това може да бъде да стартирате цикъла от 1 до 10 и в рамките на цикъла да проверите дали числото е четно или нечетно. Използването на Step в този случай е по -ефективен начин, тъй като не изисква цикълът да се изпълнява 10 пъти, а само 5 пъти.
Стойността на стъпката също може да бъде отрицателна. В такъв случай броячът започва с по -висока стойност и продължава да намалява с посочената стойност на стъпката.
Пример 3 - Въвеждане на сериен номер в избраните клетки
Можете също да използвате цикъла For Next, за да преминете през колекция от обекти (като клетки или работни листове или работни книги),
Ето пример, който бързо въвежда серийни номера във всички избрани клетки.
Sub EnterSerialNumber () Dim Rng като диапазон Dim Counter As Integer Dim RowCount As Integer Set Rng = Selection RowCount = Rng.Rows.Count For Counter = 1 To RowCount ActiveCell.Offset (Counter - 1, 0) .Value = Counter Следващ край на брояча Под
Горният код първо отчита броя на избраните редове и след това присвоява тази стойност на променливата RowCount. След това изпълняваме цикъла от „1 до RowCount“.
Също така имайте предвид, че тъй като селекцията може да бъде произволен брой редове, ние зададохме променливата Rng на Selection (с реда „Set Rng = Selection“). Сега можем да използваме променливата „Rng“, за да се позовем на селекцията в нашия код.
Пример 4 - Защитете всички работни листове в активната работна книга
Можете да използвате цикъла „За следващия“, за да преминете през всички работни листове в активната работна книга и да защитите (или да премахнете защитата) всеки от работните листове.
По -долу е кодът, който ще направи това:
Sub ProtectWorksheets () Dim i As Integer For i = 1 Към ActiveWorkbook.Worksheets.Count Worksheets (i) .Protect Next i End Sub
Кодът по -горе брои броя листове с помощта на ActiveWorkbook.Worksheets.Count. Това казва на VBA колко пъти трябва да се стартира цикълът.
Във всеки случай той се позовава на I -тата работна книга (използвайки работни листове (i)) и я защитава.
Можете да използвате същия код и за премахване на защитата на работни листове. Просто сменете линията Работни листове (i). Защитете да се Работни листове (i). UnProtect.
Вложени цикли „For Next“
Можете да използвате вложени цикли „За следващо“, за да направите по -сложна автоматизация в Excel. Вложеният цикъл „For Next“ би означавал, че в цикъла „For Next“ има цикъл „For Next“.
Нека ви покажа как да използвате това чрез пример.
Да предположим, че в системата ми има отворени 5 работни книги и искам да защитя всички работни листове във всички тези работни книги.
По -долу е кодът, който ще направи това:
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
Горното е вложен цикъл For Next, тъй като сме използвали един цикъл For Next в друг.
„EXIT For“ Изявления в For Next Loops
Изказването „Изход за“ ви позволява напълно да излезете от цикъла „За следващия“.
Можете да го използвате в случаите, когато искате цикълът For Next да приключи, когато е изпълнено определено условие.
Нека вземем пример, когато имате набор от числа в колона А и искате да подчертаете всички отрицателни числа с червен шрифт. В този случай трябва да анализираме всяка клетка за нейната стойност и след това да променим съответно цвета на шрифта.
Но за да направим кода по -ефективен, първо можем да проверим дали в списъка има отрицателни стойности или не. Ако няма отрицателни стойности, можем да използваме израза Exit For, за да излезем от кода.
По -долу е кодът, който прави това:
Sub HghlightNegative () Dim Rng As Range Set Rng = Range ("A1", Range ("A1"). End (xlDown)) Counter = Rng.Count For i = 1 За да се отблъсне, ако WorksheetFunction.Min (Rng)> = 0 След това излезте за Ако Rng (i). Стойност <0 Тогава Rng (i). Font.Color = vb Червен Следващ i Краен под
Когато използвате израза „Изход за“ в рамките на вложен цикъл „За следващия“, той ще излезе от цикъла, в който се изпълнява, и ще продължи да изпълнява следващия ред в кода след цикъла За следващия.
Например в кода по -долу изявлението „Exit For“ ще ви изведе от вътрешния цикъл, но външният цикъл ще продължи да работи.
Sub SampleCode () For i = 1 To 10 For j = 1 to 10 Exit For Next J Next i End Sub
Направете While Loop
Цикълът „Do While“ ви позволява да проверите за условие и да стартирате цикъла, докато това условие е изпълнено (или е TRUE).
Има два вида синтаксис в цикъла Do While.
Извършете цикъл на [Докато е] [Кодов блок за изпълнение]
и
Направете [Кодов блок за изпълнение] Цикъл [До условие]
Разликата между тези две е, че в първия, условието While се проверява първо преди изпълнението на който и да е кодов блок, а във втория случай кодовият блок се изпълнява първо и след това се проверява условието While.
Това означава, че ако условието While е False и в двата случая, кодът ще продължи да се изпълнява поне веднъж във втория случай (тъй като условието „While“ се проверява след като кодът е изпълнен веднъж).
Сега нека видим някои примери за използване на цикли Do While във VBA.
Пример 1 - Добавете първите 10 положителни числа, използвайки VBA
Да предположим, че искате да добавите първите десет положителни числа, като използвате цикъла Do While във VBA.
За да направите това, можете да използвате цикъла Do While, докато следващото число е по -малко или равно на 10. Веднага щом числото е по -голямо от 1o, вашият цикъл ще спре.
Ето кода на VBA, който ще изпълни този цикъл Do While и ще покаже резултата в поле за съобщение.
Sub AddFirst10PositiveIntegers () Dim i As Integer i = 1 Do While i <= 10 Result = Result + i i = i + 1 Loop MsgBox Резултат End Sub
Горният цикъл продължава да работи, докато стойността на „i“ стане 11. Веднага щом стане 11, цикълът приключва (тъй като условието While става False).
В рамките на цикъла използвахме променлива Result, която държи крайната стойност След като цикълът приключи, кутия със съобщение показва стойността на променливата „Result“.
Пример 2 - Въведете дати за текущия месец
Да предположим, че искате да въведете всички дати на текущия месец в колона на работен лист.
Можете да направите това, като използвате следния код на цикъла Do While:
Sub EnterCurrentMonthDates () Dim CMDate As Date Dim i As Integer i = 0 CMDate = DateSerial (Year (Date), Month (Date), 1) Do While Month (CMDate) = Month (Date) Range ("A1"). Offset (i, 0) = CMDate i = i + 1 CMDate = CMDate + 1 Loop End Sub
Горният код ще въведе всички дати в първата колона на работния лист (започвайки от A1). Циклите продължават, докато стойността на месеца на променливата „CMDate“ не съвпадне с тази на текущия месец.
Излезте от изявлението
Можете да използвате оператора Exit Do, за да излезете от цикъла. Веднага след като кодът изпълни реда „Exit Do“, той излиза от цикъла Do While и предава контрола на следващия ред веднага след цикъла.
Например, ако искате да въведете само първите 10 дати, тогава можете да излезете от цикъла веднага след като бъдат въведени първите 10 дати.
Кодът по -долу ще направи това:
Sub EnterCurrentMonthDates () Dim CMDate As Date Dim i As Integer i = 0 CMDate = DateSerial (Year (Date), Month (Date), 1) Do While Month (CMDate) = Month (Date) Range ("A1"). Offset (i, 0) = CMDate i = i + 1 Ако i> = 10 След това излезте Do CMDate = CMDate + 1 Loop End Sub
В горния код операторът IF се използва за проверка дали стойността на i е по -голяма от 10 или не. Веднага след като стойността на „i“ стане 10, изходът Do се изпълнява и цикълът приключва.
Направете до цикъл
Цикли „Do Until“ много приличат на цикли „Do While“.
В „Do While“ цикълът работи, докато даденото условие не бъде изпълнено, докато в „Do Until“, той се завърта, докато не бъде изпълнено посоченото условие.
Има два вида синтаксис в цикъла Do Do Loop.
Направете [До условие] [Кодов блок за изпълнение] Цикъл
и
Направете [Кодов блок за изпълнение] Цикъл [До условие]
Разликата между тези две е, че в първия, условието До се проверява първо преди изпълнението на който и да е кодов блок, а във втория случай първо се изпълнява кодовият блок и след това се проверява условието До.
Това означава, че ако условието До е TRUE и в двата случая, кодът ще продължи да се изпълнява поне веднъж във втория случай (тъй като условието „До“ се проверява, след като кодът е изпълнен веднъж).
Сега нека видим някои примери за използване на цикли Do Until във VBA.
Забележка: Всички примери за Do Until са същите като тези на Do While. Те са променени, за да ви покажат как работи цикълът „До до“.
Пример 1 - Добавете първите 10 положителни числа, използвайки VBA
Да предположим, че искате да добавите първите десет положителни числа, като използвате цикъла Do Until във VBA.
За да направите това, трябва да стартирате цикъла, докато следващото число е по -малко или равно на 10. Веднага щом числото е по -голямо от 1o, вашият цикъл ще спре.
Ето кода на VBA, който ще стартира този цикъл и ще покаже резултата в поле за съобщение.
Sub AddFirst10PositiveIntegers () Dim i As Integer i = 1 Do While i> 10 Result = Result + i i = i + 1 Loop MsgBox Резултат End Sub
Горният цикъл продължава да работи, докато стойността на „i“ стане 11. Веднага щом стане 11, цикълът приключва (тъй като условието „До“ става True).
Пример 2 - Въведете дати за текущия месец
Да приемем, че искате да въведете всички дати на текущия месец в колона на работен лист.
Можете да направите това, като използвате следния код на цикъла Do Until:
Sub EnterCurrentMonthDates () Dim CMDate As Date Dim i As Integer i = 0 CMDate = DateSerial (Година (Дата), Месец (Дата), 1) До До месец (CMDate) Месец (дата) Диапазон ("A1"). i, 0) = CMDate i = i + 1 CMDate = CMDate + 1 Loop End Sub
Горният код ще въведе всички дати в първата колона на работния лист (започвайки от A1). Цикълът продължава, докато месецът на променливата CMDate не е равен на този на текущия месец.
Излезте от изявлението
Можете да използвате израза „Exit Do“, за да излезете от цикъла.
Веднага след като кодът изпълни реда „Exit Do“, той излиза от цикъла Do Until и предава контрола на следващия ред точно след цикъла.
Например, ако искате да въведете само първите 10 дати, тогава можете да излезете от цикъла веднага след като бъдат въведени първите 10 дати.
Кодът по -долу ще направи това:
Sub EnterCurrentMonthDates () Dim CMDate As Date Dim i As Integer i = 0 CMDate = DateSerial (Година (Дата), Месец (Дата), 1) До До месец (CMDate) Месец (дата) Диапазон ("A1"). i, 0) = CMDate i = i + 1 Ако i> = 10 След това излезте Do CMDate = CMDate + 1 Loop End Sub
В горния код, веднага щом стойността на ‘i’ стане 10, изходът Do се изпълнява и цикълът приключва.
За всеки
Във VBA можете да преминете през набор от колекции, като използвате цикъла „За всеки“.
Ето някои примери за колекции в Excel VBA:
- Колекция от всички отворени работни книги.
- Колекция от всички работни листове в работна книга.
- Събиране на всички клетки в диапазон от избрани клетки.
- Колекция от всички диаграми или форми в работната книга.
Използвайки цикъла „За всеки“, можете да преминете през всеки от обектите в колекция и да извършите някои действия върху него.
Например, можете да преминете през всички работни листове в работна книга и да ги защитите, или можете да преминете през всички клетки в селекцията и да промените форматирането.
С цикъла „За всеки“ (наричан още цикъл „За всеки следващ“) не е нужно да знаете колко обекта има в колекция.
Цикълът „За всеки“ автоматично ще премине през всеки обект и ще извърши определеното действие. Например, ако искате да защитите всички работни листове в работна книга, кодът ще бъде същият, независимо дали имате работна книга с 3 работни листа или 30 работни листа.
Ето синтаксиса на цикъла За всеки следващ в Excel VBA.
За всеки елемент в колекцията [Кодов блок за изпълнение] Следващ [елемент]
Сега нека видим няколко примера за използване на For For Loop в Excel.
Пример 1 - Прегледайте всички работни листове в работна книга (и я защитете)
Да предположим, че имате работна книга, в която искате да защитите всички работни листове.
По-долу за всеки-следващ цикъл можете да направите това лесно:
Sub ProtectSheets () Dim ws като работен лист за всеки ws в ActiveWorkbook.Worksheets ws.Protect Next ws End Sub
В горния код дефинирахме променливата „ws“ като обект на работен лист. Това казва на VBA, че „ws“ трябва да се тълкува като обект на работен лист в кода.
Сега използваме израза „За всеки“, за да преминем през всяко „ws“ (което е обект на работен лист) в колекцията от всички работни листове в активната работна книга (дадена от ActiveWorkbook.Worksheets).
Обърнете внимание, че за разлика от други цикли, където се опитахме да защитим всички работни листове в работна книга, тук не е нужно да се притесняваме колко работни листа има в работната книга.
Не е нужно да ги броим, за да стартираме цикъла. За всеки цикъл гарантира, че всички обекти се анализират един по един.
Пример 2 - Прегледайте всички отворени работни книги (и запишете всички)
Ако работите с няколко работни книги едновременно, може да ви бъде полезно да можете да запазите всички тези работни книги наведнъж.
По -долу кодът VBA може да направи това за нас:
Sub SaveAllWorkbooks () Dim wb като работна книга за всяка wb в работни книги wb.Save Next wb End Sub
Обърнете внимание, че в този код не получавате подкана, която ви моли да запазите работната книга на определено място (ако я запазвате за първи път).
Той го записва в папката по подразбиране (това беше папката „Документи“ в моя случай). Този код работи най -добре, когато тези файлове вече са записани и правите промени и искате бързо да запазите всички работни книги.
Пример 3 - Преминете през всички клетки в селекция (Маркирайте отрицателни стойности)
Използвайки цикъла „За всеки“, можете да преминете през всички клетки в определен диапазон или в избрания диапазон.
Това може да бъде полезно, когато искате да анализирате всяка клетка и да извършите действие въз основа на нея.
Например по -долу е кодът, който ще премине през всички клетки в селекцията и ще промени цвета на клетките на клетките с отрицателни стойности в червено.
Sub HighlightNegativeCells () Dim Cll As Range За всеки Cll в селекция Ако Cll.Value <0 Тогава Cll.Interior.Color = vbRed End Ако следващата Cll End Sub
(Забележете, че съм използвал Cll като кратко име на променлива за Cell. Препоръчително е да не използвате имена на обекти като Sheets или Range като имена на променливи)
В горния код цикълът „За всеки следващ“ преминава през колекцията от клетки в селекцията. Изразът IF се използва за идентифициране дали стойността на клетката е отрицателна или не. В случай, че е така, клетката получава червен вътрешен цвят, в противен случай тя преминава към следващата клетка.
В случай, че нямате селекция и вместо това искате VBA да избере всички запълнени клетки в колона, започвайки от определена клетка (точно както използваме Control + Shift + клавиш със стрелка надолу, за да изберете всички запълнени клетки), можете да използвайте следния код:
Sub HighlightNegativeCells () Dim Cll As Range Dim Rng As Range Set Rng = Range ("A1", Range ("A1"). End (xlDown)) За всеки Cll в Rng Ако Cll.Value <0 Тогава Cll.Interior.Color = vbЧервен край, ако следващ Cll End Sub
В горния пример няма значение колко запълнени клетки има. Той ще започне от клетка А1 и ще анализира всички съседни запълнени клетки в колоната.
Също така не е необходимо да имате избрана клетка А1. Можете да изберете някоя далечна клетка и когато кодът се изпълнява, тя все пак ще вземе предвид всички клетки в колона А (започвайки от А1) и ще оцвети отрицателните клетки.
Статистика „Изход за“
Можете да използвате израза „Изход за“ в цикъла За всеки следващ, за да излезете от цикъла. Това обикновено се прави в случай, че е изпълнено определено условие.
Например в Пример 3, докато преминаваме през набор от клетки, може да бъде по -ефективно да се провери дали има отрицателни стойности или не. В случай, че няма отрицателни стойности, можем просто да излезем от цикъла и да спестим малко време за обработка на VBA.
По -долу е кодът VBA, който ще направи това:
Sub HighlightNegativeCells () Dim Cll As Range За всеки Cll в селекция If WorksheetFunction.Min (Selection)> = 0 Тогава излезте за If Cll.Value <0 Тогава Cll.Interior.Color = vbRed End Ако следващ Cll End Sub
Къде да поставите VBA кода
Чудите се къде отива кодът VBA във вашата работна книга на Excel?
Excel има VBA бекенд, наречен VBA редактор. Трябва да копирате и поставите кода в кодовия прозорец на модула VB Editor.
Ето стъпките за това:
- Отидете в раздела Разработчик.
- Щракнете върху опцията Visual Basic. Това ще отвори VB редактора в задната част.
- В прозореца Project Explorer в редактора на VB щракнете с десния бутон върху всеки обект за работната книга, в който искате да вмъкнете кода. Ако не виждате Project Explorer, отидете в раздела View и щракнете върху Project Explorer.
- Отидете на Вмъкване и щракнете върху Модул. Това ще вмъкне обект на модул за вашата работна книга.
- Копирайте и поставете кода в прозореца на модула.
