Коришћење објекта радне свеске у програму Екцел ВБА (Отвори, Затвори, Сачувај, Постави)

У овом водичу ћу покрити како се ради са радним свескама у Екцелу помоћу ВБА.

У Екцелу, „Радна свеска“ је објекат који је део колекције „Радне свеске“. Унутар радне свеске имате различите објекте, попут радних листова, листова графикона, ћелија и опсега, објеката графикона, облика итд.

Помоћу ВБА можете много тога да урадите са објектом радне свеске - на пример да отворите одређену радну свеску, сачувате и затворите радне свеске, креирате нове радне свеске, промените својства радне свеске итд.

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

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

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

Референцирање радне свеске помоћу ВБА

Постоје различити начини позивања на објекат радне свеске у ВБА. Метод који одаберете зависиће од тога шта желите да урадите. У овом одељку ћу размотрити различите начине позивања на радну свеску заједно са неким примерима кодова.

Коришћење назива радне свеске

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

Почнимо са једноставним примером.

Ако имате две отворене радне свеске, а радну свеску желите да активирате именом - Екамплес.клск, можете да користите доњи код:

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

Имајте на уму да морате да користите назив датотеке заједно са наставком ако је датотека сачувана. Ако није сачувано, можете користити име без наставка датотеке.

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

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

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

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

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

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

Можете се позвати и на радне свеске на основу њиховог индексног броја.

На пример, ако имате отворене три радне свеске, следећи код ће вам приказати имена три радне свеске у оквиру за поруку (један по један).

Суб ВоркбоокНаме () МсгБок Воркбоокс (1) .Наме МсгБок Воркбоокс (2) .Наме МсгБок Воркбоокс (3) .Наме Енд Суб

Горњи код користи МсгБок - функцију која приказује оквир за поруку са наведеним текстом/вредношћу (што је у овом случају назив радне свеске).

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

Екцел третира прву отворену радну свеску тако да има индексни број 1, а следећа 2 и тако даље.

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

Код испод би прошао кроз све отворене радне свеске и затворио све осим радне свеске која има овај ВБА код.

Суб ЦлосеВоркбоокс () Дим ВбЦоунт Ас Интегер ВбЦоунт = Воркбоокс.Цоунт Фор и = ВбЦоунт до 1 Степ -1 Иф Воркбоокс (и) .Име ТхисВоркбоок.Наме Тхен Воркбоокс (и) .Цлосе Енд Иф Нект и Енд Суб

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

Користи ИФ услов да провери да ли је назив радне свеске исти као и радна свеска у којој се код покреће.

Ако се не подудара, затвара радну свеску и прелази на следећу.

Имајте на уму да смо петљу покренули од ВбЦоунт до 1 са кораком од -1. Ово се ради као и при свакој петљи, број отворених радних свеска се смањује.

Ова радна књига је детаљно обрађена у каснијем одељку.

Коришћење АцтивеВоркбоок -а

АцтивеВоркбоок, као што назив говори, односи се на радну свеску која је активна.

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

Под АцтивеВоркбоокНаме () МсгБок АцтивеВоркбоок.Наме Крај Суб

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

Ево примера овога.

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

Под АцтивеВоркбоокНаме () МсгБок АцтивеВоркбоок.Наме Радне свеске ("Примери.клск"). Активирајте МсгБок АцтивеВоркбоок.Наме Крај Суб

Имајте на уму да када креирате нову радну свеску користећи ВБА, та новостворена радна свеска аутоматски постаје активна радна свеска.

Коришћење ове радне свеске

Ова радна свеска се односи на радну свеску у којој се извршава код.

Свака радна књига би имала део ТхисВоркбоок као део (видљив у Пројецт Екплореру).

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

Свака процедура догађаја коју сачувате у овој „овој радној свесци“ била би доступна у целој радној свесци, у поређењу са догађајима на нивоу листа који су ограничени само на одређене листове.

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

Привате Суб Воркбоок_СхеетБефореДоублеЦлицк (БиВал Сх Ас Објецт, БиВал Тар Ас Ранге, Цанцел Ас Боолеан) МсгБок Таргет.Аддресс Енд Суб

Иако је главна улога ТхисВоркбоок -а складиштење процедуре догађаја, можете је користити и за упућивање на радну свеску у којој се код извршава.

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

Суб ТхисВоркбоокНаме () МсгБок ТхисВоркбоок.Наме Крај Суб

Предност коришћења ТхисВоркбоок -а (преко АцтивеВоркбоок -а) је у томе што би се у свим случајевима односила на исту радну свеску (ону која има код). Дакле, ако користите ВБА код за додавање нове радне свеске, АцтивеВоркбоок би се променио, али ТхисВоркбоок би се и даље односио на ону која има код.

Креирање новог објекта радне свеске

Следећи код ће створити нову радну свеску.

Под ЦреатеНевВоркбоок () Воркбоокс.Адд Енд Суб

Када додате нову радну свеску, она постаје активна.

Следећи код ће додати нову радну свеску, а затим ће вам показати назив те радне свеске (што би био подразумевани назив типа Боок1).

Под ЦреатеНевВоркбоок () Воркбоокс.Адд МсгБок АцтивеВоркбоок.Наме Крај Суб

Отворите радну свеску користећи ВБА

Можете користити ВБА за отварање одређене радне свеске када знате путању датотеке радне свеске.

Код испод ће отворити радну свеску - Екамплес.клск која се налази у фасцикли Доцументс на мом систему.

Суб ОпенВоркбоок () Воркбоокс.Опен ("Ц: \ Усерс \ сумит \ Доцументс \ Екамплес.клск") Крај Суб

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

Суб ОпенВоркбоок () Воркбоокс.Опен ("Екамплес.клск") Енд Суб

У случају да радна свеска коју покушавате да отворите не постоји, видећете грешку.

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

Код испод би проверио локацију датотеке, а ако не постоји, приказаће прилагођену поруку (не поруку о грешци):

Суб ОпенВоркбоок () Иф Дир ("Ц: \ Усерс \ сумит \ Доцументс \ Екамплес.клск") "" Тхен Воркбоокс.Опен ("Ц: \ Усерс \ сумит \ Доцументс \ Екамплес.клск") Елсе МсгБок "Датотека не 'т екист "Енд Иф Енд Суб

Такође можете користити дијалошки оквир Отвори за одабир датотеке коју желите отворити.

Суб ОпенВоркбоок () Он Еррор Ресуме Нект Дим ФилеПатх Ас Стринг ФилеПатх = Апплицатион.ГетОпенФиленаме Воркбоокс.Опен (ФилеПатх) Енд Суб

Горњи код отвара дијалошки оквир Отвори. Када изаберете датотеку коју желите да отворите, она додељује путању датотеке променљивој ФилеПатх. Воркбоокс.Опен затим користи путању датотеке за отварање датотеке.

У случају да корисник не отвори датотеку и кликне на дугме Откажи, ФилеПатх постаје Фалсе. Да бисмо избегли грешку у овом случају, користили смо наредбу „Он Еррор Ресуме Нект“.

Повезан: Сазнајте све о руковању грешкама у програму Екцел ВБА

Чување радне свеске

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

Суб СавеВоркбоок () АцтивеВоркбоок.Саве Енд Суб

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

Ако радну свеску чувате први пут, она ће вам приказати упит као што је приказано испод:

Приликом првог спремања боље је користити опцију „Савеас“.

Код испод би сачувао активну радну свеску као .клсм датотеку на подразумеваној локацији (која је фасцикла са документима у мом систему).

Суб СавеВоркбоок () АцтивеВоркбоок.СавеАс Назив датотеке: = "Тест.клсм", ФилеФормат: = клОпенКСМЛВоркбоокМацроЕнаблед Енд Суб

Ако желите да се датотека сачува на одређеној локацији, морате то напоменути у вредности Филенаме. Код испод спрема датотеку на моју радну површину.

Суб СавеВоркбоок () АцтивеВоркбоок.СавеАс Назив датотеке: = "Ц: \ Усерс \ сумит \ Десктоп \ Тест.клсм", ФилеФормат: = клОпенКСМЛВоркбоокМацроЕнаблед Енд Суб

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

Суб СавеВоркбоок () Дим ФилеПатх Ас Стринг ФилеПатх = Апплицатион.ГетСавеАсФиленаме АцтивеВоркбоок.СавеАс Назив датотеке: = ФилеПатх & ".клсм", ФилеФормат: = клОпенКСМЛВоркбоокМацроЕнаблед Енд Суб

Имајте на уму да уместо да користите ФилеФормат: = клОпенКСМЛВоркбоокМацроЕнаблед, можете користити и ФилеФормат: = 52, где је 52 код клОпенКСМЛВоркбоокМацроЕнаблед.

Чување свих отворених радних свески

Ако имате више отворених радних свесака и желите да сачувате све радне свеске, можете да користите доњи код:

Суб СавеАллВоркбоокс () Дим вб као радна свеска за сваку вб у радним свескама вб.Саве Нект вб Енд Суб

Горе наведено чува све радне свеске, укључујући и оне које никада нису сачуване. Радне свеске које претходно нису сачуване сачувале би се на подразумеваној локацији.

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

Суб СавеАллВоркбоокс () Дим вб као радна свеска за сваку вб у радним свескама Иф вб.Патх "" Тхен вб.Саве Енд Иф Нект вб Енд Суб

Чување и затварање свих радних свезака

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

Суб ЦлосеандСавеВоркбоокс () Дим вб као радна свеска за сваку вб у радним свескама Ако вб.Име ТхисВоркбоок.Наме Затим вб.Цлосе СавеЦхангес: = Труе Енд Иф Нект вб Енд Суб

Горњи код би затворио све радне свеске (осим радне свеске која има код - ТхисВоркбоок). У случају да дође до промена у овим радним свескама, промене би биле сачуване. У случају да постоји радна свеска која никада није сачувана, она ће приказати дијалог за чување као.

Сачувајте копију радне свеске (са временском ознаком)

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

Ево ВБА кода који ће креирати копију ваше радне свеске и сачувати је на наведеној локацији.

Суб ЦреатеаЦопиофВоркбоок () ТхисВоркбоок.СавеЦопиАс Назив датотеке: = "Ц: \ Усерс \ сумит \ Десктоп \ БацкупЦопи.клсм" Крај под

Горњи код би сачувао копију ваше радне свеске сваки пут када покренете овај макро.

Иако ово одлично функционише, било би ми угодније да имам сачуване различите копије кад год покренем овај код. Разлог зашто је ово важно је да ако направим ненамерну грешку и покренем овај макро, то ће уштедети рад са грешкама. И не бих имао приступ делу пре него што направим грешку.

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

Привате Суб Воркбоок_БефореСаве (БиВал СавеАсУИ Као Боолеан, Откажи као Боолеан) ТхисВоркбоок.СавеЦопиАс Назив датотеке: = "Ц: \ Усерс \ сумит \ Десктоп \ БацкупЦопи" & Формат (Нов (), "дд-мм-ии-хх-мм-сс -АМПМ ") &" .клсм "Енд Суб

Горњи код би створио копију сваки пут када покренете овај макро и додао би датум/време у назив радне свеске.

Направите нову радну свеску за сваки радни лист

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

То може бити случај када имате месечне/кварталне извештаје у једној радној свесци и желите да их поделите у једну радну свеску за сваки радни лист.

Или, ако имате извештаје о одељењима и желите да их поделите у појединачне радне свеске, тако да можете те појединачне радне свеске послати шефовима одељења.

Ево кода који ће креирати радну свеску за сваки радни лист, дати јој исто име као и радном листу и сачувати је у наведеној фасцикли.

Под ЦреатеВоркбоокфорВорксхеетс () Затамни вс Као радни лист Дим вб Као радну свеску за сваки вс у ТхисВоркбоок.Ворксхеетс Сет вб = Воркбоокс.Адд вс.Цопи Бефоре: = вб.Схеетс (1) Апплицатион.ДисплаиАлертс = Фалсе вб.Схеетс (2) .Делете Апплицатион.ДисплаиАлертс = Труе вб.СавеАс "Ц: \ Усерс \ сумит \ Десктоп \ Тест \" & вс.Наме & ".клск" вб.Затворити Сљедеће вс Крај Суб

У горњем коду користили смо две променљиве „вс“ и „вб“.

Код пролази кроз сваки радни лист (помоћу петље За сваки следећи) и за њега креира радну свеску. Такође користи метод копирања објекта радног листа за креирање копије радног листа у новој радној свесци.

Имајте на уму да сам користио израз СЕТ да доделим променљиву „вб“ свакој новој радној свесци коју креира код.

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

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

У ВБА можете променљивој доделити објекат, а затим користити променљиву да бисте се позвали на тај објекат.

На пример, у доњем коду користим ВБА за додавање нове радне свеске, а затим додељујем ту радну свеску променљивој вб. Да бих то урадио, морам да користим израз СЕТ.

Када доделим радну свеску променљивој, сва својства радне свеске постају доступна и променљивој.

Суб АссигнтоВариабле () Дим вб Као радна свеска Сет вб = Воркбоокс.Адд вб.СавеАс Филенаме: = "Ц: \ Усерс \ сумит \ Десктоп \ Екамплес.клск" Енд Суб

Имајте на уму да је први корак у коду декларисање „вб“ као променљиве типа радне свеске. Ово говори ВБА да ова променљива може да задржи објекат радне свеске.

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

Понављање кроз отворене радне свеске

Већ смо видели неколико примера кодова који су користили петље у коду.

У овом одељку ћу објаснити различите начине проласка кроз отворене радне свеске помоћу ВБА.

Претпоставимо да желите да сачувате и затворите све отворене радне свеске, осим оне у којој је код, а затим можете користити доњи код:

Суб ЦлосеандСавеВоркбоокс () Дим вб као радна свеска за сваку вб у радним свескама Ако вб.Име ТхисВоркбоок.Наме Затим вб.Цлосе СавеЦхангес: = Труе Енд Иф Нект вб Енд Суб

Горњи код користи петљу За сваку за пролазак кроз сваку радну свеску у збирци Радне свеске. Да бисмо то урадили, прво морамо да декларишемо „вб“ као променљиву типа радне свеске.

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

Исто се може постићи и са другом петљом као што је приказано испод:

Суб ЦлосеВоркбоокс () Дим ВбЦоунт Ас Интегер ВбЦоунт = Воркбоокс.Цоунт Фор и = ВбЦоунт до 1 Степ -1 Иф Воркбоокс (и) .Име ТхисВоркбоок.Наме Тхен Воркбоокс (и) .Цлосе СавеЦхангес: = Труе Енд Иф Нект и Енд Суб

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

Имајте на уму да се у горњем коду петљамо са ВбЦоунт на 1 са Кораком -1. Ово је потребно јер се при свакој петљи радна свеска затвара, а број радних свеска смањује за 1.

Грешка при раду са објектом радне свеске (грешка током извођења „9“)

Једна од најчешћих грешака на коју можете наићи при раду са радним свескама је - Грешка при извођењу '9' - Индекс изван опсега.

Генерално, ВБА грешке нису превише информативне и често остављате вама да схватите шта је пошло по злу.

Ево неких могућих разлога који могу довести до ове грешке:

• Радна свеска којој покушавате да приступите не постоји. На пример, ако покушавам да приступим петој радној свесци користећи радне свеске (5), а отворене су само 4 радне свеске, добићу ову грешку.
• Ако користите погрешан назив за упућивање на радну свеску. На пример, ако је назив ваше радне свеске Екамплес.клск и користите Екампле.клск. онда ће вам показати ову грешку.
• Ако нисте сачували радну свеску, а користите проширење, добићете ову грешку. На пример, ако је назив ваше радне свеске Боок1, а ви користите име Боок1.клск без чувања, добићете ову грешку.
• Радна свеска којој покушавате да приступите је затворена.

Набавите листу свих отворених радних свески

Ако желите да добијете листу свих отворених радних свески у тренутној радној свесци (радна свеска у којој покрећете код), можете да користите доњи код:

Суб ГетВоркбоокНамес () Дим вбцоунт Ас Интегер вбцоунт = Воркбоокс.Цоунт ТхисВоркбоок.Ворксхеетс.Адд АцтивеСхеет.Ранге ("А1"). Ацтивате Фор и = 1 То вбцоунт Ранге ("А1"). Оффсет (и - 1, 0). Вредност = Радне свеске (и). Име Следеће и Крај Под

Горњи код додаје нови радни лист, а затим наводи назив свих отворених радних свезака.

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

Суб ГетВоркбоокНамес () Дим вбцоунт Ас Интегер вбцоунт = Воркбоокс.Цоунт ТхисВоркбоок.Ворксхеетс.Адд АцтивеСхеет.Ранге ("А1"). Ацтивате Фор и = 1 То вбцоунт Ранге ("А1"). Оффсет (и - 1, 0). Вредност = Радне свеске (и). Путања & "\" & Радне свеске (и). Име Следеће и Крај под

Отворите Наведену радну свеску двоструким кликом на ћелију

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

Привате Суб Воркбоок_СхеетБефореДоублеЦлицк (БиВал Сх Ас Објецт, БиВал Таргет ас Ранге, Цанцел Ас Боолеан) Воркбоокс.Опен Таргет.Валуе Енд Суб

Овај код би се ставио у прозор кода ТхисВоркбоок.

Да уради ово:

• Двапут кликните на објект ТхисВоркбоок у истраживачу пројеката. Имајте на уму да би објект ТхисВоркбоок требао бити у радној свесци у којој желите ову функцију.
• Копирајте и залепите горњи код.

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

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

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

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

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

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

Можда ће вам се допасти и следећи Екцел ВБА водичи:

• Како снимити макро у програму Екцел.
• Креирање кориснички дефинисане функције у Екцелу.
• Како да креирате и користите програмски додатак у програму Екцел.
• Како поново покренути макрое стављањем у радну свеску о личним макроима.
• Набавите листу имена датотека из фасцикле у Екцелу (са и без ВБА).
• Како се користи Екцел ВБА ИнСтр функција (са практичним ПРИМЕРИМА).
• Како сортирати податке у Екцелу помоћу ВБА (Корак по корак водич).