✅ Рад са радним листовима користећи Екцел ВБА (објашњено са примерима)

Преглед садржаја

Разлика између радних листова и листова у ВБА
Референцирање радног листа у ВБА
Додавање радног листа
Брисање радног листа
Преименовање радних листова
Додељивање објекта радног листа променљивој
Сакриј радне листове помоћу ВБА (скривено + врло скривено)
Сакриј табеле на основу текста у њој
Сортирање радних листова по абецедном реду
Заштитите/уклоните заштиту свих листова одједном
Прављење садржаја свих радних листова (са хипервезама)
Где ставити ВБА код

Осим ћелија и опсега, рад са радним листовима је још једно подручје за које бисте требали знати да ефикасно користите ВБА у Екцелу.

Као и сваки објект у ВБА, радни листови имају различита својства и методе повезане са њим које можете користити док аутоматизујете свој рад са ВБА у Екцелу.

У овом водичу ћу детаљно обрадити „радне листове“ и такође вам показати неке практичне примере.

Па да почнемо.

Сви кодови које помињем у овом водичу морају бити смештени у ВБ Едитор. Идите на одељак „Где ставити ВБА код“ да бисте сазнали како то функционише.

Ако сте заинтересовани за учење ВБА на лак начин, погледајте мој Онлине Екцел ВБА обука.

Разлика између радних листова и листова у ВБА

У ВБА имате две колекције које понекад могу бити помало збуњујуће.

У радној свесци можете имати радне листове, као и листове графикона. Доњи пример има три радна листа и један лист графикона.

У Екцел ВБА:

Збирка „Радни листови“ односила би се на збирку свих објеката радног листа у радној свесци. У горњем примеру, збирка радних листова састојала би се од три радна листа.
Збирка „Таблице“ односила би се на све радне листове, као и на листове графикона у радној свесци. У горњем примеру, он би имао четири елемента - 3 радна листа + 1 лист графикона.

Ако имате радну свеску која има само радне листове, а нема листова графикона, онда су збирке „Радни листови“ и „Таблице“ исте.

Али када имате један или више листова графикона, збирка „Таблице“ била би већа од збирке „Радни листови“

Табеле = Радни листови + Табеле

С обзиром на ову разлику, препоручујем да будете што прецизнији при писању ВБА кода.

Дакле, ако се морате позивати само на радне листове, користите збирку „Радни листови“, а ако се морате односити на све листове (укључујући листове графикона), користите збирку „Таблице“.

У овом водичу ћу користити само збирку „Радни листови“.

Референцирање радног листа у ВБА

Постоји много различитих начина на које можете да се позивате на радни лист у ВБА.

Разумевање начина позивања на радне листове помогло би вам да напишете бољи код, посебно када користите петље у свом ВБА коду.

Коришћење назива радног листа

Најлакши начин за позивање на радни лист је коришћење његовог имена.

На пример, претпоставимо да имате радну свеску са три радна листа - лист 1, лист 2, лист 3.

И желите да активирате Лист 2.

То можете учинити помоћу следећег кода: Суб АцтиватеСхеет () Ворксхеетс ("Схеет2"). Ацтивате Енд Суб

Горњи код тражи од ВБА да се позове на Схеет2 у колекцији Ворксхеетс и активира га.

Пошто користимо тачан назив листа, овде можете користити и збирку листова. Дакле, доњи код би такође урадио исту ствар.

Суб АцтиватеСхеет () Схеетс ("Схеет2"). Активирај Енд Суб

Коришћење индексног броја

Иако је коришћење назива листа једноставан начин позивања на радни лист, понекад можда не знате тачан назив радног листа.

На пример, ако користите ВБА код за додавање новог радног листа у радну свеску, а не знате колико радних листова већ постоји, не бисте знали назив новог радног листа.

У овом случају можете користити индексни број радних листова.

Претпоставимо да имате следеће листове у радној свесци:

Код испод би активирао Схеет2:

Суб АцтиватеСхеет () Радни листови (2) .Активирајте Енд Суб

Имајте на уму да смо користили индекс број 2 у Радни листови (2). Ово би се односило на други објекат у збирци радних листова.

Шта се дешава када користите 3 као индексни број?

Бираће лист 3.

Ако се питате зашто је одабрао Схеет3, јер је очигледно четврти објекат.

То се дешава зато што лист графикона није део колекције радних листова.

Дакле, када користимо индексне бројеве у збирци радних листова, то ће се односити само на радне листове у радној свесци (и занемарити листове графикона).

Напротив, ако користите листове, листови (1) би се односили на листове 1, листови (2) би се односили на лист 2, листови (3) би се односили на графикон 1, а листови (4) би се односили на лист 3.

Ова техника коришћења индексног броја је корисна када желите да прођете кроз све радне листове у радној свесци. Можете избројати број радних листова, а затим их прегледати помоћу овог бројача (видећемо како то учинити касније у овом водичу).

Напомена: Број индекса иде с лева на десно. Дакле, ако померате Лист2 лево од Схеет1, тада ће се Радни листови (1) односити на Схеет2.

Коришћење кодног имена радног листа

Један од недостатака коришћења назива листа (као што смо видели у горњем одељку) је то што га корисник може променити.

А ако је назив листа промењен, ваш код не би радио док не промените назив радног листа и у ВБА коду.

Да бисте решили овај проблем, можете користити кодно име радног листа (уместо уобичајеног имена које смо до сада користили). Кодно име се може доделити у ВБ Едитор -у и не мења се када промените назив листа из области радног листа.

Да бисте свом радном листу дали кодно име, следите доле наведене кораке:

Кликните на картицу Девелопер.
Кликните на дугме Висуал Басиц. Ово ће отворити ВБ Едитор.
Притисните опцију Виев у менију и кликните на Пројецт Виндов. Ово ће учинити окно Својства видљивим. Ако је окно Својства већ видљиво, прескочите овај корак.
Кликните на име листа у истраживачу пројеката које желите преименовати.
У окну Својства промените име у пољу испред (Назив). Имајте на уму да не можете имати размаке у имену.

Горе наведени кораци би променили име вашег радног листа у ВБА позадини. У приказу радног листа Екцел можете да именујете радни лист како год желите, али ће у позадини одговарати на оба имена - назив листа и назив кода.

На горњој слици назив листа је „СхеетНаме“, а кодни назив „ЦодеНаме“. Чак и ако промените назив листа на радном листу, кодни назив остаје исти.

Сада можете користити збирку радних листова да бисте се позвали на радни лист или користити кодно име.

На пример, оба реда ће активирати радни лист.

Радни листови ("Назив листа"). Активирајте ЦодеНаме.Активирајте

Разлика у ова два је у томе што ако промените назив радног листа, први не би радио. Али други ред би наставио да ради чак и са промењеним именом. Друга линија (користећи ЦодеНаме) је такође краћа и лакша за употребу.

Позивање на радни лист у другој радној свесци

Ако желите да се обратите радном листу у другој радној свесци, та радна свеска мора бити отворена док се код покреће, а ви морате да наведете назив радне свеске и радни лист на који желите да се позовете.

На пример, ако имате радну свеску са именом Примери и желите да активирате Лист 1 у Примеру радне свеске, морате да користите доњи код:

Суб СхеетАцтивате () Воркбоокс ("Екамплес.клск"). Ворксхеетс ("Схеет1"). Ацтивате Енд Суб

Имајте на уму да ако је радна свеска сачувана, морате да користите назив датотеке заједно са наставком. Ако нисте сигурни које име да користите, потражите помоћ од Пројецт Екплорера.

У случају да радна свеска није сачувана, не морате да користите екстензију датотеке.

Додавање радног листа

Код испод би додао радни лист (као први радни лист - тј. Као крајњи леви лист на картици листа).

Суб АддСхеет () Ворксхеетс.Адд Енд Суб

Потребно је подразумевано име Схеет2 (или било који други број на основу тога колико листова већ постоји).

Ако желите да се радни лист дода пре одређеног радног листа (рецимо Схеет2), можете користити доњи код.

Суб АддСхеет () Ворксхеетс.Адд Бефоре: = Ворксхеетс ("Схеет2") Енд Суб

Горњи код говори ВБА -и да дода лист, а затим користи наредбу „Пре“ да наведе радни лист пре којег треба уметнути нови радни лист.

Слично томе, можете додати и лист након радног листа (рецимо Схеет2), користећи доњи код:

Суб АддСхеет () Ворксхеетс.Адд Афтер: = Ворксхеетс ("Схеет2") Енд Суб

Ако желите да се нови лист додаје на крај листова, морате прво знати колико има листова. Следећи код прво броји број листова и додаје нови лист после последњег листа (на који се позивамо помоћу индексног броја).

Суб АддСхеет () Дим СхеетЦоунт Ас Интегер СхеетЦоунт = Ворксхеетс.Цоунт Ворксхеетс.Адд Афтер: = Ворксхеетс (СхеетЦоунт) Енд Суб

Брисање радног листа

Код испод би избрисао активни лист из радне свеске.

Суб Избриши лист () АцтивеСхеет.Делете Енд Суб

Горњи код би приказао упозорење пре брисања радног листа.

Ако не желите да видите упозорење, користите доњи код:

Суб ДелетеСхеет () Апплицатион.ДисплаиАлертс = Нетачно АцтивеСхеет.Делете Апплицатион.ДисплаиАлертс = Труе Енд Суб

Када је Апплицатион.ДисплаиАлертс постављено на Нетачно, неће вам приказати одзив упозорења. Ако га користите, не заборавите да га вратите на Труе на крају кода.

Запамтите да ово брисање не можете поништити, па користите горњи код када сте потпуно сигурни.

Ако желите да избришете одређени лист, то можете учинити помоћу следећег кода:

Суб ДелетеСхеет () Радни листови ("Схеет2"). Избришите Енд Суб

За брисање можете користити и кодно име листа.

Суб Избриши Лист () Лист5.Делете Енд Суб

Преименовање радних листова

Можете променити својство наме радног листа да бисте променили његово име.

Следећи код ће променити име Схеет1 у „Суммари“.

Суб РенамеСхеет () Радни листови ("Схеет1"). Наме = "Суммари" Енд Суб

Ово можете комбиновати са методом додавања листова да бисте имали скуп листова са одређеним именима.

На пример, ако желите да уметнете четири листа са именом2021-2022 К1,2021-2022 К2,2021-2022 К3, и2021-2022 К4, можете користити доњи код.

Суб РенамеСхеет () Затамни табеле као целобројне табеле = Радни листови.Преброји за и = 1 до 4 радна листа.Додај после: = Радни листови (Табеле + и - 1) Радни листови (Бројеви + и). Име = "2018 К" & и Следећи и Енд Суб

У горњем коду прво бројимо број листова, а затим помоћу петље Фор Нект за уметање нових листова на крају. Како се лист додаје, код га такође мења.

Додељивање објекта радног листа променљивој

Када радите са радним листовима, радном листу можете доделити променљиву објекта, а затим користити променљиву уместо референци радног листа.

На пример, ако желите да додате префикс године на све радне листове, уместо да бројите листове и покрећете петљу толико пута, можете користити променљиву објекта.

Ево кода који ће додати 2021-2022 као префикс свим именима радног листа.

Суб РенамеСхеет () Дим Вс као радни лист за сваки Вс у радним листовима Вс.Наме = "2018 -" & Вс.Наме Следећи Вс Енд Суб

Горњи код декларише променљиву Вс као тип радног листа (користећи линију „Дим Вс као радни лист“).

Сада, не морамо да бројимо број листова да их прођемо. Уместо тога, можемо користити петљу „За сваки В у радним листовима“. Ово ће нам омогућити да прођемо кроз све листове у збирци радних листова. Није важно да ли постоје 2 листа или 20 листова.

Иако нам горњи код омогућава да прођемо кроз све листове, променљивој можете доделити и одређени лист.

У доњем коду додељујемо променљиву Вс Схеет2 и користимо је за приступ свим својствима Схеет2.

Суб РенамеСхеет () Дим Вс Ас Сет радног листа Вс = Радни листови ("Схеет2") Вс.Наме = "Суммари" Вс.Протецт Енд Суб

Када поставите референцу радног листа на променљиву објекта (користећи израз СЕТ), тај објекат се може користити уместо референце радног листа. Ово може бити корисно ако имате дугачак компликован код и желите да промените референцу. Уместо да мењате свуда, можете једноставно унети промену у израз СЕТ.

Имајте на уму да код декларише Вс објекат као променљиву типа радног листа (користећи линију Дим Вс као радни лист).

Сакриј радне листове помоћу ВБА (скривено + врло скривено)

Скривање и откривање радних листова у програму Екцел једноставан је задатак.

Можете сакрити радни лист и корисник то неће видети када отвори радну свеску. Међутим, они могу лако открити радни лист десним кликом на било коју картицу листа.

Али шта ако не желите да могу да открију радне листове.

То можете учинити помоћу ВБА.

Доњи код би сакрио све радне листове у радној свесци (осим активног листа), тако да га не можете открити десним кликом на назив листа.

Под ХидеАллЕкцетАцтивеСхеет () Затамни В као радни лист за сваки В у овој радној књизи.Радни листови Ако се Вс.Име АцтивеСхеет.Наме Затим Вс.Висибле = клСхеетВериХидден Следећи Вс Енд Суб

У горњем коду, својство Вс.Висибле се мења у клСхеетВериХидден.

Када је својство Висибле постављено на клСхеетВисибле, лист је видљив у области радног листа (као картице радног листа).
Када је својство Висибле постављено на клСхеетХидден, лист је скривен, али корисник га може открити десним кликом на било коју картицу листа.
Када је својство Висибле постављено на клСхеетВериХидден, лист је скривен и не може се сакрити из подручја радног листа. Морате да користите ВБА код или прозор својстава да бисте га открили.

Ако желите једноставно сакрити листове, који се лако могу открити, користите доњи код:

Под ХидеАллЕкцептАцтивеСхеет () Затамни В као радни лист за сваки В у овој радној књизи.Радни листови Ако се Вс.Наме АцтивеСхеет.Наме Затим Вс.Висибле = клСхеетХидден Следећи Вс Енд Суб

Код испод би открио све радне листове (и скривене и врло скривене).

Суб УнхидеАллВоксхеетс () Затамни В као радни лист за сваки Вс у овој радној свесци. Радни листови Вс.Висибле = клСхеетВисибле Следећи Вс Енд Суб

Везани чланак: Откријте све листове у Екцелу (одједном)

Сакриј табеле на основу текста у њој

Претпоставимо да имате више листова са именом различитих одељења или година и желите да сакријете све листове осим оних који у себи имају годину 2021-2022.

То можете учинити помоћу функције ВБА ИНСТР.

Код испод би сакрио све листове осим оних са текстом 2021-2022.

Суб ХидеВитхМатцхингТект () Затамни В као радни лист за сваки Вс у радним листовима Ако је ИнСтр (1, Вс.Наме, "2018", вбБинариЦомпаре) = 0 Тада је Вс.Висибле = клСхеетХидден Енд Ако Следећи Вс Енд Суб

У горњем коду, функција ИНСТР враћа позицију знака где налази одговарајући низ. Ако не пронађе одговарајући низ, враћа 0.

Горњи код проверава да ли име садржи текст 2021-2022. Ако се то догоди, ништа се неће догодити, иначе је радни лист скривен.

Ово можете учинити корак даље тако што ћете текст ставити у ћелију и користити ту ћелију у коду. Ово ће вам омогућити да имате вредност у ћелији, а затим када покренете макро, сви листови, осим оног са одговарајућим текстом, остају видљиви (заједно са листовима у које уносите вредност у мобилни).

Сортирање радних листова по абецедном реду

Помоћу ВБА можете брзо сортирати радне листове према њиховим називима.

На пример, ако имате радну свеску која има листове за различита одељења или године, онда можете користити доњи код за брзо сортирање ових листова по растућем редоследу.

Суб СортСхеетсТабНаме () Апплицатион.СцреенУпдатинг = Фалсе Дим СхЦоунт Ас Интегер, и Ас Интегер, ј Ас Интегер СхЦоунт = Схеетс.Цоунт За и = 1 За СхЦоунт - 1 За ј = и + 1 За СхЦоунт Ако листови (ј). Име < Схеетс (и) .Назив Затим Схеетс (ј) .Премести пре: = Схеетс (и) Енд Иф Нект ј Нект и Апплицатион.СцреенУпдатинг = Труе Енд Суб

Имајте на уму да овај код добро функционира с називима текста, а у већини случајева и с годинама и бројевима. Али може вам дати погрешне резултате у случају да имате називе листова као 1,2,11. Он ће сортирати и дати вам низ 1, 11, 2. То је зато што врши поређење као текст и сматра 2 већим од 11.

Заштитите/уклоните заштиту свих листова одједном

Ако имате много радних листова у радној свесци и желите да заштитите све листове, можете користити доњи ВБА код.

Омогућава вам да наведете лозинку унутар кода. Ова лозинка ће вам требати за уклањање заштите са радног листа.

Суб ПротецтАллСхеетс () Дим вс Ас Ворксхеет Дим пассворд ас Стринг пассворд = "Тест123" 'замените Тест123 лозинком коју желите За сваки вс У радним листовима вс.Заштита лозинке: = лозинка Следећа вс Крај Суб

Следећи код би уклонио заштиту свих листова одједном.

Суб ПротецтАллСхеетс () Дим вс Ас Ворксхеет Дим пассворд Ас Стринг пассворд = "Тест123" 'замените Тест123 лозинком коју сте користили при заштити За сваки вс У радним листовима вс.Унпротецт лозинка: = лозинка Следећа вс Крајња под

Прављење садржаја свих радних листова (са хипервезама)

Ако имате скуп радних листова у радној свесци и желите брзо да уметнете резиме који садржи везе до свих листова, можете користити доњи код.

Суб АддИндекСхеет () Ворксхеетс.Адд АцтивеСхеет.Наме = "Индек" Фор и = 2 То Ворксхеетс.Цоунт АцтивеСхеет.Хиперлинкс.Адд Анцхор: = Целлс (и - 1, 1), _ Аддресс: = "", СубАддресс: = Ворксхеетс (и) .Назив & "! А1", _ ТектТоДисплаи: = Радни листови (и). Име Следеће и Крај под

Горњи код убацује нови радни лист и даје му назив Индек.

Затим се петља кроз све радне листове и ствара хипервезу за све радне листове у индексном листу.

Где ставити ВБА код

Питате се где се ВБА код налази у вашој Екцел радној свесци?

Екцел има ВБА позадину која се зове ВБА едитор. Морате да копирате и залепите код у прозор кода модула ВБ Едитор.

Ево корака за то:

Идите на картицу Девелопер.
Кликните на опцију Висуал Басиц. Ово ће отворити ВБ едитор у позадини.
У окну Пројецт Екплорер у ВБ Едитор-у кликните десним тастером миша на било који објекат радне свеске у који желите да уметнете код. Ако не видите Пројецт Екплорер, идите на картицу Виев и кликните на Пројецт Екплорер.
Идите на Уметни и кликните на Модул. Ово ће уметнути објект модула за вашу радну свеску.
Копирајте и залепите код у прозор модула.