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