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