Да бисте најбоље искористили Екцел и ВБА, морате знати како ефикасно да користите петље.
У ВБА, петље вам омогућавају да прођете кроз скуп објеката/вредности и анализирате их један по један. Такође можете извршити одређене задатке за сваку петљу.
Ево једноставног примера коришћења ВБА петљи у Екцелу.
Претпоставимо да имате скуп података и желите да истакнете све ћелије у парним редовима. Можете користити ВБА петљу да бисте прошли опсег и анализирали сваки ред реда ћелије. Ако се испостави да је уједначено, дајете му боју, у супротном остављате како јесте.
Ово је, наравно, врло једноставно петље у Екцел ВБА (а то можете учинити и помоћу условног обликовања).
У стварном животу можете учинити много више помоћу ВБА петљи у Екцелу које вам могу помоћи у аутоматизацији задатака.
Ево још неколико практичних примера где ВБА петље могу бити корисне:
- Прелажење кроз низ ћелија и анализа сваке ћелије (означите ћелије са одређеним текстом у њој).
- Прелистајте све радне листове и учините нешто са сваким (као што је заштита/уклањање заштите).
- Прелистајте све отворене радне свеске (и сачувајте сваку радну свеску или затворите све осим активне радне свеске).
- Прођите кроз све знакове у ћелији (и извуците нумерички део из низа).
- Прођите кроз све вредности низ.
- Прелистајте све графиконе/објекте (и поставите оквир или промените боју позадине).
Да бисте најбоље користили петље у програму Екцел ВБА, морате знати о различитим врстама које постоје и исправној синтакси сваке од њих.
У овом водичу ћу приказати различите врсте Екцел ВБА петљи и покрити неколико примера за сваку петљу
Напомена: Ово ће бити огроман водич у којем ћу покушати детаљно покрити сваку ВБА петљу. Препоручујем да обележите ову страницу за будућу употребу.Ако сте заинтересовани за учење ВБА на лак начин, погледајте мој Онлине Екцел ВБА обука.
За следећу петљу
Петља „За следеће“ вам омогућава да прођете кроз блок кода одређени број пута.
На пример, ако вас замолим да ручно додате целе бројеве од 1 до 10, додали бисте прва два броја, затим додали трећи број у резултат, а затим у резултат додали четврти број, итд.
Зар не?
Иста логика се користи у петљи Фор Нект у ВБА.
Одређујете колико пута желите да се петља покрене, као и шта желите да код ради сваки пут када се петља покрене.
Испод је синтакса петље За следеће:
За бројач = Почетак до краја [Вредност корака] [Блок кода за извршавање] Следећи [бројач]
У петљи За следеће можете користити бројач (или било коју променљиву) која ће се користити за покретање петље. Овај бројач вам омогућава да покренете ову петљу потребан број пута.
На пример, ако желим да додам првих 10 позитивних целих бројева, онда би моја вредност бројача била од 1 до 10.
Погледајмо неколико примера да бисмо боље разумели како ради петља Фор Нект.
Пример 1 - Додавање првих 10 позитивних целих бројева
Испод је код који ће додати првих 10 позитивних целих бројева помоћу петље За следеће.
Затим ће се приказати оквир за поруку који приказује збир ових бројева.
Подбројеви додатака () Дим укупно као цео број Дим број као цео број укупно = 0 за пребројавање = 1 до 10 укупно = укупно + број следећи број мсгБок укупно крај под
У овом коду, вредност Тотал је постављена на 0 пре него што уђе у петљу За следеће.
Једном када уђе у петљу, она држи укупну вредност након сваке петље. Дакле, после прве петље, када је бројач 1, вредност „Укупно“ постаје 1, а након друге петље постаје 3 (1+2) итд.
И на крају, када се петља заврши, променљива „Укупно“ има збир првих 10 позитивних целих бројева.
МсгБок тада једноставно приказује резултат у оквиру за поруку.
Пример 2 - Додавање првих 5 парних позитивних целих бројева
Да бисте сабрали првих пет парних позитивних целих бројева (тј. 2,4,6,8 и 10), потребан вам је сличан код са условом да узмете у обзир само парне бројеве и занемарите непарне бројеве.
Ево кода који ће то учинити:
Суб АддЕвенНумберс () Дим Тотал Ас Интегер Дим Цоунт Ас Интегер Тотал = 0 Фор Цоунт = 2 то 10 Степ 2 Тотал = Тотал + Цоунт Нект Цоунт МсгБок Тотал Енд Суб
Имајте на уму да смо започели вредност Цоунт од 2 и такође користили „Корак 2‘.
Када користите 'Корак 2', говори коду да повећа вредност „Цоунт“ за 2 сваки пут када се петља покрене.
Дакле, вредност Цоунт почиње од 2, а затим постаје 4, 6, 8 и 10 када дође до петље.
НАПОМЕНА: Други начин за то може бити покретање петље од 1 до 10 и унутар петље провера да ли је број паран или непаран. Међутим, коришћење Степ -а је у овом случају ефикаснији начин јер не захтева да се петља извршава 10 пута, већ само 5 пута.
Вредност корака такође може бити негативна. У том случају бројач почиње са већом вредношћу и наставља да се смањује за наведену вредност корака.
Пример 3 - Унос серијског броја у изабране ћелије
Такође можете користити петљу За следеће да бисте прошли кроз збирку објеката (као што су ћелије или радни листови или радне свеске),
Ево примера који брзо уноси серијске бројеве у све изабране ћелије.
Суб ЕнтерСериалНумбер () Дим Рнг као опсег Дим Цоунтер Ас Интегер Дим РовЦоунт Ас Интегер Сет Рнг = Селецтион РовЦоунт = Рнг.Ровс.Цоунт Фор Цоунтер = 1 За РовЦоунт АцтивеЦелл.Оффсет (Бројач - 1, 0) .Вредност = Бројач Следећи крај бројача Суб
Горњи код прво броји број изабраних редова, а затим додељује ову вредност променљивој РовЦоунт. Затим покрећемо петљу од „1 до РовЦоунт“.
Такође имајте на уму да, пошто избор може бити било који број редова, променљиву Рнг смо поставили на Селецтион (са линијом „Сет Рнг = Селецтион“). Сада можемо користити променљиву „Рнг“ да се позивамо на избор у нашем коду.
Пример 4 - Заштитите све радне листове у активној радној свесци
Можете користити петљу „За следеће“ да бисте прегледали све радне листове у активној радној свесци и заштитили (или уклонили заштиту) сваки од радних листова.
Испод је код који ће то учинити:
Под ПротецтВорксхеетс () Дим и Ас Интегер Фор и = 1 За АцтивеВоркбоок.Ворксхеетс.Цоунт Ворксхеетс (и) .Заштитите Нект и Енд Суб
Горњи код броји број листова помоћу АцтивеВоркбоок.Ворксхеетс.Цоунт. Ово говори ВБА колико пута је потребно покренути петљу.
У сваком случају, односи се на И радну свеску (помоћу радних листова (и)) и штити је.
Овај исти код можете користити и за уклањање заштите радних листова. Само промени линију Радни листови (и) .Заштитити до Радни листови (и) .УнПротецт.
Уметнуте петље „За следеће“
Можете користити угнежђене петље „За следеће“ да бисте извршили сложенију аутоматизацију у Екцелу. Уметнута петља „За следеће“ би значила да постоји петља „За следеће“ унутар петље „За следећу“.
Дозволите ми да вам покажем како то користити на примеру.
Претпоставимо да имам 5 радних свеска отворених у свом систему и желим да заштитим све радне листове у свим овим свескама.
Испод је код који ће то учинити:
Суб ПротецтВорксхеетс () Дим и Ас Интегер Дим ј Ас Интегер Фор и = 1 То Воркбоокс.Цоунт Фор ј = 1 То Воркбоокс (и) .Ворксхеетс.Цоунт Воркбоокс (и) .Ворксхеетс (ј) .Протецт Нект ј Нект и Енд Суб
Горе наведено је угнежђена петља Фор Нект јер смо користили једну петљу Фор Нект у другој.
Наредбе „ЕКСИТ Фор“ у наредним петљама
Наредба „Екит Фор“ вам омогућава да у потпуности изађете из петље „Фор Нект“.
Можете га користити у случајевима када желите да се петља За следеће заврши када се испуни одређени услов.
Узмимо пример где имате скуп бројева у колони А и желите да означите све негативне бројеве црвеним фонтом. У овом случају морамо анализирати сваку ћелију на њену вредност, а затим према томе променити боју фонта.
Али да би код био ефикаснији, прво можемо да проверимо да ли на листи постоје негативне вредности или не. Ако нема негативних вредности, можемо користити израз Екит Фор да једноставно изађемо из кода.
Испод је код који то ради:
Суб ХгхлигхтНегативе () Дим Рнг Ас Ранге Сет Рнг = Ранге ("А1", Ранге ("А1"). Енд (клДовн)) Цоунтер = Рнг.Цоунт Фор и = 1 За одбројавање ако је ВорксхеетФунцтион.Мин (Рнг)> = 0 Затим изађите за Ако је Рнг (и) .Вредност <0 Затим Рнг (и). Фонт.Цолор = вбРед Следеће и Крај под
Када користите наредбу „Екит Фор“ у угнежђеној петљи „Фор Нект“, она ће изаћи из петље у којој се извршава и наставити са извршавањем следеће линије у коду после петље Фор Нект.
На пример, у доњем коду наредба „Екит Фор“ ће вас извући из унутрашње петље, али ће спољна петља наставити да ради.
Суб СамплеЦоде () За и = 1 до 10 За ј = 1 до 10 Излаз За следећи Ј Следећи и Крај под
Уради Док Лооп
Петља „До Вхиле“ вам омогућава да проверите стање и покренете петљу док је тај услов испуњен (или је ТРУЕ).
У петљи До Вхиле постоје пет врста синтаксе.
Урадите [Док је стање] [Кодни блок за извршавање] Петљу
и
Учинити [Блокирање кода за извршавање] Петља [Док је стање]
Разлика између ова два је у томе што се у првом, стање Иф прво проверава пре него што се изврши било који кодни блок, ау другом случају, прво се извршава кодни блок, а затим се проверава услов Вхиле.
То значи да ако је услов Док је нетачно у оба случаја, код ће се и даље покренути најмање једном у другом случају (пошто се услов „Док“ проверава након што се код једном изврши).
Погледајмо сада неке примере употребе петљи До Вхиле у ВБА.
Пример 1 - Додајте првих 10 позитивних целих бројева користећи ВБА
Претпоставимо да желите да додате првих десет позитивних целих бројева помоћу петље До Вхиле у ВБА.
Да бисте то урадили, можете користити петљу Уради док следећи број не буде мањи или једнак 10. Чим је број већи од 1о, ваша петља би се зауставила.
Ево ВБА кода који ће покренути ову петљу До Вхиле и приказати резултат у оквиру за поруку.
Суб АддФирст10ПоситивеИнтегерс () Дим и Ас Интегер и = 1 До Вхиле и <= 10 Ресулт = Ресулт + и и = и + 1 Лооп МсгБок Резултат Крај Суб
Горња петља наставља да ради све док вредност „и“ не постане 11. Чим постане 11, петља се завршава (пошто услов Док постане Фалсе).
Унутар петље смо користили променљиву Резултат која садржи коначну вредност Када се петља заврши, оквир за поруку приказује вредност променљиве „Резултат“.
Пример 2 - Унесите датуме за текући месец
Рецимо да желите да унесете све датуме текућег месеца у колону радног листа.
То можете учинити помоћу следећег кода петље До Вхиле:
Суб ЕнтерЦуррентМонтхДатес () Дим ЦМДате Ас Дате Дим и Ас Интегер и = 0 ЦМДате = ДатеСериал (Година (Датум), Месец (Датум), 1) До Вхиле Месец (ЦМДате) = Месец (Датум) Распон ("А1"). Оффсет (и, 0) = ЦМДате и = и + 1 ЦМДате = ЦМДате + 1 Лооп Енд Суб
Горњи код би унео све датуме у прву колону радног листа (почевши од А1). Петље се настављају све док се вредност месеца променљиве „ЦМДате“ не подудара са вредношћу текућег месеца.
Изађите из Изјаве
Да бисте изашли из петље, можете користити наредбу Екит До. Чим код изврши линију „Екит До“, излази из петље До Вхиле и преноси контролу у следећи ред одмах након петље.
На пример, ако желите да унесете само првих 10 датума, онда можете изаћи из петље чим се унесе првих 10 датума.
Код испод ће то учинити:
Суб ЕнтерЦуррентМонтхДатес () Дим ЦМДате Ас Дате Дим и Ас Интегер и = 0 ЦМДате = ДатеСериал (Година (Датум), Месец (Датум), 1) До Вхиле Месец (ЦМДате) = Месец (Датум) Распон ("А1"). Оффсет (и, 0) = ЦМДате и = и + 1 Ако је и> = 10 Затим изађите До ЦМДате = ЦМДате + 1 Лооп Енд Суб
У горњем коду, израз ИФ се користи за проверу да ли је вредност и већа од 10 или не. Чим вредност „и“ постане 10, наредба Екит До се извршава и петља се завршава.
Уради до петље
Петље „До До“ су веома сличне петљама „До Вхиле“.
У „Уради док“ петља ради све док се не испуни задати услов, док у „Уради до“ петља ради све док се не испуни наведени услов.
У петљи Уради до петље постоје две врсте синтаксе.
Урадите [До услова] [Блок кода за извршавање] Петљу
и
Учинити [Блокирање кода за извршавање] Петља [До услова]
Разлика између ова два је у томе што се у првом, услов Док се прво проверава пре него што се изврши било који кодни блок, ау другом случају, прво се извршава кодни блок, а затим се проверава услов До.
То значи да ако је услов Док је ТРУЕ у оба случаја, код ће се и даље покренути најмање једном у другом случају (пошто се услов „До“ проверава након што се код једном изврши).
Хајде сада да видимо неке примере коришћења петљи До Допуна у ВБА.
Напомена: Сви примери за До Вхиле исти су као и за До Вхиле. Оне су измењене да би вам показале како ради петља Уради до.
Пример 1 - Додајте првих 10 позитивних целих бројева користећи ВБА
Претпоставимо да желите да додате првих десет позитивних целих бројева помоћу петље До До у ВБА.
Да бисте то урадили, морате покренути петљу док следећи број не буде мањи или једнак 10. Чим је број већи од 1о, ваша петља би се зауставила.
Ево ВБА кода који ће покренути ову петљу и приказати резултат у оквиру за поруку.
Суб АддФирст10ПоситивеИнтегерс () Дим и Ас Интегер и = 1 До Вхиле и> 10 Ресулт = Ресулт + и и = и + 1 Лооп МсгБок Резултат Крај Суб
Горња петља наставља да ради све док вредност „и“ не постане 11. Чим постане 11, петља се завршава (пошто услов „До“ постане Тачно).
Пример 2 - Унесите датуме за текући месец
Рецимо да желите да унесете све датуме текућег месеца у колону радног листа.
То можете учинити помоћу следећег кода петље До До:
Суб ЕнтерЦуррентМонтхДатес () Дим ЦМДате Ас Дате Дим и Ас Интегер и = 0 ЦМДате = ДатеСериал (Година (Датум), Месец (Датум), 1) Учинити До месеца (ЦМДате) Месец (Датум) Опсег ("А1"). и, 0) = ЦМДате и = и + 1 ЦМДате = ЦМДате + 1 Лооп Енд Суб
Горњи код би унео све датуме у прву колону радног листа (почевши од А1). Петља се наставља све док месец променљиве ЦМДате није једнак оном у текућем месецу.
Изађите из Изјаве
Да бисте изашли из петље, можете користити израз „Екит До“.
Чим код изврши линију „Екит До“, излази из петље До Унтил и преноси контролу у следећи ред одмах након петље.
На пример, ако желите да унесете само првих 10 датума, онда можете изаћи из петље чим се унесе првих 10 датума.
Код испод ће то учинити:
Суб ЕнтерЦуррентМонтхДатес () Дим ЦМДате Ас Дате Дим и Ас Интегер и = 0 ЦМДате = ДатеСериал (Година (Датум), Месец (Датум), 1) Учинити До месеца (ЦМДате) Месец (Датум) Опсег ("А1"). и, 0) = ЦМДате и = и + 1 Ако је и> = 10 Затим изађите До ЦМДате = ЦМДате + 1 Лооп Енд Суб
У горе наведеном коду, чим вредност 'и' постане 10, Екит До статмент се извршава и петља се завршава.
За сваки
У ВБА можете проћи кроз скуп колекција помоћу петље „За свако“.
Ево неколико примера збирки у програму Екцел ВБА:
- Збирка свих отворених радних свески.
- Збирка свих радних листова у радној свесци.
- Збирка свих ћелија у низу изабраних ћелија.
- Збирка свих графикона или облика у радној свесци.
Помоћу петље „За сваки“ можете проћи кроз сваки од објеката у збирци и извршити неку радњу на њему.
На пример, можете проћи кроз све радне листове у радној свесци и заштитити их, или можете проћи кроз све ћелије у избору и променити обликовање.
Са петљом „За сваки“ (која се назива и петља „За сваку следећу“), не морате да знате колико објеката постоји у колекцији.
Петља „За сваки“ би аутоматски прошла кроз сваки објекат и извршила наведену радњу. На пример, ако желите да заштитите све радне листове у радној свесци, код би био исти ако имате радну свеску са 3 радна листа или 30 радних листова.
Ево синтаксе петље За сваки следећи у Екцелу ВБА.
За сваки елемент у колекцији [Блок кода за извршење] Следећи [елемент]
Хајде сада да видимо неколико примера коришћења петље За сваку петљу у Екцелу.
Пример 1 - Прегледајте све радне листове у радној свесци (и заштитите је)
Претпоставимо да имате радну свеску у којој желите да заштитите све радне листове.
Испод за петљу Евери-Нект то можете учинити лако:
Под ПротецтСхеетс () Затамни као радни лист за сваки вс у АцтивеВоркбоок.Ворксхеетс вс.Заштити Следећи вс Енд Суб
У горњем коду смо дефинисали променљиву „вс“ као објекат радног листа. Ово говори ВБА да „вс“ треба тумачити као објекат радног листа у коду.
Сада користимо израз „За сваки“ да прођемо кроз сваки „вс“ (који је објекат радног листа) у збирци свих радних листова у активној радној свесци (коју даје АцтивеВоркбоок.Ворксхеетс).
Имајте на уму да за разлику од других петљи у којима смо покушали да заштитимо све радне листове у радној свесци, овде не морамо да бринемо о томе колико радних листова постоји у радној свесци.
Не морамо да их бројимо да бисмо покренули петљу. За сваку петљу осигурава се да се сви објекти анализирају један по један.
Пример 2 - Прегледајте све отворене радне свеске (и сачувајте све)
Ако радите са више радних свезака истовремено, може вам добро доћи ако можете сачувати све ове радне свеске одједном.
Испод ВБА кода то можете учинити за нас:
Суб СавеАллВоркбоокс () Дим вб као радна свеска за сваку вб у радним свескама вб.Саве Нект вб Енд Суб
Имајте на уму да у овом коду не добијате упит који од вас тражи да сачувате радну свеску на одређеној локацији (ако је чувате први пут).
Чува га у подразумеваној фасцикли (у мом случају то је била фасцикла „Документи“). Овај код најбоље функционише када су ове датотеке већ сачуване и уносите измене и желите брзо да сачувате све радне свеске.
Пример 3 - Прођите кроз све ћелије у избору (означите негативне вредности)
Користећи петљу „За сваки“ можете проћи кроз све ћелије у одређеном опсегу или у изабраном опсегу.
Ово може бити корисно када желите анализирати сваку ћелију и на основу ње извршити радњу.
На пример, испод је код који ће проћи кроз све ћелије у избору и променити боју ћелија ћелија са негативним вредностима у црвену.
Суб ХигхлигхтНегативеЦеллс () Дим Цлл Ас Ранге фор еацх Цлл Ин Селецтион Иф Цлл.Валуе <0 тхен Цлл.Интериор.Цолор = вбРед Енд Иф Нект Цлл Енд Суб
(Имајте на уму да сам користио Цлл као кратак назив променљиве за ћелију. Препоручљиво је да не користите имена објеката као што су табеле или опсег као имена променљивих)
У горњем коду, петља За сваки следећи пролази кроз збирку ћелија у избору. Наредба ИФ се користи за идентификацију да ли је вредност ћелије негативна или не. У случају да јесте, ћелија добија унутрашњу црвену боју, иначе прелази у следећу ћелију.
У случају да немате избор, а уместо тога желите да ВБА одабере све испуњене ћелије у колони, почевши од одређене ћелије (баш као што користимо Цонтрол + Схифт + тастер са стрелицом надоле за избор свих попуњених ћелија), можете користите доњи код:
Суб ХигхлигхтНегативеЦеллс () Дим Цлл Ас Ранге Дим Рнг Ас Ранге Сет Рнг = Ранге ("А1", Ранге ("А1"). Енд (клДовн)) За сваки Цлл у Рнг Ако је Цлл.Валуе <0 Затим Цлл.Интериор.Цолор = вбРед Енд Иф Нект Цлл Енд Суб
У горњем примеру није важно колико испуњених ћелија има. Он ће почети од ћелије А1 и анализирати све суседне испуњене ћелије у колони.
Такође не морате да имате изабрану ћелију А1. Можете изабрати било коју удаљену ћелију и када се код покрене, и даље ће узети у обзир све ћелије у колони А (почевши од А1) и обојити негативне ћелије.
Излаз „Излаз за“
За излазак из петље можете користити наредбу „Екит Фор“ у петљи Фор Евери-Нект. То се обично ради у случају да је испуњен одређени услов.
На пример, у примеру 3, док пролазимо кроз скуп ћелија, може бити ефикасније проверити да ли постоје негативне вредности или не. У случају да нема негативних вредности, једноставно можемо изаћи из петље и уштедети неко време обраде ВБА.
Испод је ВБА код који ће то учинити:
Суб ХигхлигхтНегативеЦеллс () Дим Цлл Ас Ранге фор еацх Цлл Ин Селецтион Иф ВорксхеетФунцтион.Мин (Селецтион)> = 0 тхен Екит Фор Иф Цлл.Валуе <0 тхен Цлл.Интериор.Цолор = вбРед Енд Иф Нект Цлл Енд Суб
Где ставити ВБА код
Питате се где се ВБА код налази у вашој Екцел радној свесци?
Екцел има ВБА позадину која се зове ВБА едитор. Морате да копирате и залепите код у прозор кода модула ВБ Едитор.
Ево корака за то:
- Идите на картицу Девелопер.
- Кликните на опцију Висуал Басиц. Ово ће отворити ВБ едитор у позадини.
- У окну Пројецт Екплорер у ВБ Едитор-у кликните десним тастером миша на било који објекат радне свеске у који желите да уметнете код. Ако не видите Пројецт Екплорер, идите на картицу Виев и кликните на Пројецт Екплорер.
- Идите на Уметни и кликните на Модул. Ово ће уметнути објект модула за вашу радну свеску.
- Копирајте и залепите код у прозор модула.
