Креирање кориснички дефинисане функције (УДФ) у програму Екцел ВБА (Ултимативни водич)

Помоћу ВБА можете да креирате прилагођену функцију (која се назива и кориснички дефинисана функција) која се може користити на радним листовима као и обичне функције.

Ово је корисно када постојеће Екцел функције нису довољне. У таквим случајевима можете створити властиту прилагођену функцију коју дефинише корисник (УДФ) како би задовољила ваше посебне потребе.

У овом водичу ћу покрити све о креирању и коришћењу прилагођених функција у ВБА.

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

Шта је процедура функција у ВБА?

Поступак функције је ВБА код који врши прорачуне и враћа вредност (или низ вредности).

Помоћу процедуре Фунцтион можете креирати функцију коју можете користити на радном листу (баш као и било коју редовну Екцел функцију као што је СУМ или ВЛООКУП).

Када сте креирали поступак функције помоћу ВБА, можете га користити на три начина:

1. Као формула на радном листу, где може узети аргументе као улазе и вратити вредност или низ вредности.
2. Као део кода ваше потпрограма ВБА или другог кода функције.
3. У условном обликовању.

Иако је на радном листу већ доступно 450+ уграђених Екцел функција, можда ће вам бити потребна прилагођена функција ако:

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

Имајте на уму да прилагођене функције креиране помоћу ВБА могу бити знатно спорије од уграђених функција. Стога су оне најприкладније за ситуације у којима не можете постићи резултат помоћу уграђених функција.

Фунцтион Вс. Потпрограм у ВБА

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

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

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

Помоћу прилагођене функције можете је користити у засебној колони и може вратити ТРУЕ ако је вредност у ћелији негативна и ФАЛСЕ ако је позитивна. Помоћу функције не можете променити својства објекта. То значи да не можете променити боју ћелије помоћу саме функције (међутим, то можете учинити помоћу условног обликовања са прилагођеном функцијом).

Када креирате Кориснички дефинисану функцију (УДФ) користећи ВБА, можете користити ту функцију на радном листу као и било коју другу функцију. О томе ћу више говорити у одељку „Различити начини коришћења кориснички дефинисане функције у Екцелу“.

Креирање једноставне кориснички дефинисане функције у ВБА

Дозволите ми да направим једноставну кориснички дефинисану функцију у ВБА и покажем вам како то функционише.

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

Функција ГетНумериц (ЦеллРеф Ас Стринг) као Лонг Дим СтрингЛенгтх Ас Интегер СтрингЛенгтх = Лен (ЦеллРеф) Фор и = 1 То СтрингЛенгтх Иф ИсНумериц (Мид (ЦеллРеф, и, 1)) Затим Ресулт = Ресулт & Мид (ЦеллРеф, и, 1) Даље и ГетНумериц = Крајња функција резултата

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

Испод је приказана функција ове функције - ГетНумериц - може се користити у Екцелу.

Пре него што вам кажем како је ова функција створена у ВБА -и и како функционише, постоји неколико ствари које бисте требали знати:

• Када креирате функцију у ВБА, она постаје доступна у целој радној свесци, као и свака друга регуларна функција.
• Када унесете назив функције иза којег следи знак једнакости, Екцел ће вам приказати назив функције на листи одговарајућих функција. У горњем примеру, када сам унео = Гет, Екцел ми је показао листу која има моју прилагођену функцију.

Верујем да је ово добар пример када помоћу ВБА можете да креирате функцију једноставну за коришћење у Екцелу. Исту ствар можете учинити и са формулом (као што је приказано у овом водичу), али то постаје компликовано и тешко за разумевање. Са овим УДФ -ом, потребно је само да пренесете један аргумент и добићете резултат.

Анатомија кориснички дефинисане функције у ВБА

У горњем одељку сам вам дао код и показао вам како УДФ функција ради на радном листу.

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

Функција ГетНумериц (ЦеллРеф Ас Стринг) ас Лонг 'Ова функција издваја нумерички део из низа Дим СтрингЛенгтх Ас Интегер СтрингЛенгтх = Лен (ЦеллРеф) Фор и = 1 То СтрингЛенгтх Иф ИсНумериц (Мид (ЦеллРеф, и, 1)) Затим резултат = Резултат и средина (ЦеллРеф, и, 1) Даље и ГетНумериц = Функција краја резултата

Први ред кода почиње речју - Функција.

Ова реч говори ВБА да је наш код функција (а не потпрограм). Иза речи Функција следи назив функције - ГетНумериц. Ово је име које ћемо користити на радном листу за коришћење ове функције.

• Назив функције не може имати размаке. Такође, не можете именовати функцију ако се коси са именом референце ћелије. На пример, не можете именовати функцију АБЦ123 јер се такође односи на ћелију у радном листу Екцел.
• Не бисте требали дати својој функцији исто име као и постојећој функцији. Ако то учините, Екцел би дао предност уграђеној функцији.
• Можете да користите доњу црту ако желите да раздвојите речи. На пример, Гет_Нумериц је прихватљиво име.

Иза имена функције слиједе неки аргументи у загради. Ово су аргументи које би нашој функцији требао корисник. Ово су баш као и аргументи које требамо доставити Екцел -овим уграђеним функцијама. На пример, у ЦОУНТИФ функцији постоје два аргумента (опсег и критеријуми)

У заградама морате навести аргументе.

У нашем примеру постоји само један аргумент - ЦеллРеф.

Такође је добра пракса навести какав аргумент функција очекује. У овом примеру, будући да ћемо функцију хранити референцом ћелије, можемо навести аргумент као тип „Опсег“. Ако не наведете тип података, ВБА би то сматрао варијантом (што значи да можете користити било који тип података).

Ако имате више аргумената, можете их навести у истим заградама - одвојене зарезом. Касније ћемо у овом водичу видети како користити више аргумената у кориснички дефинисаној функцији.

Имајте на уму да је функција наведена као тип података „Стринг“. То би ВБА -и рекло да ће резултат формуле бити типа података Стринг.

Иако овде могу да користим нумерички тип података (попут Лонг или Доубле), то би ограничило опсег бројева које може да врати. Ако имам низ од 20 бројева који морам да извучем из укупног низа, декларисање функције као Лонг или Доубле би дало грешку (јер би број био ван свог опсега). Стога сам задржао излазни тип података функције као Стринг.

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

Трећи ред кода декларише променљиву „СтрингЛенгтх“ као целобројни тип података. Ово је променљива у којој складиштимо вредност дужине низа који се анализира формулом.

Четврти ред декларише променљиву Резултат као Стринг тип података. Ово је променљива у којој ћемо извући бројеве из алфанумеричког низа.

Пети ред додељује дужину низа у улазном аргументу променљивој „СтрингЛенгтх“. Имајте на уму да се „ЦеллРеф“ односи на аргумент који ће дати корисник док користи формулу на радном листу (или је користи у ВБА - што ћемо видети касније у овом водичу).

Шести, седми и осми ред су део петље За следеће. Петља се изводи онолико пута колико знакова има у улазном аргументу. Овај број даје функција ЛЕН и додељује се променљивој „СтрингЛенгтх“.

Дакле, петља иде од „1 до Стрингленгтх“.

Унутар петље, израз ИФ анализира сваки знак низа и ако је нумерички, додаје тај нумерички знак променљивој Резултат. За то користи функцију МИД у ВБА.

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

Последњи ред кода је Енд Фунцтион. Ово је обавезна линија кода која говори ВБА да се код функције завршава овде.

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

Аргументи у кориснички дефинисаној функцији у ВБА

У горњим примерима, где смо креирали кориснички дефинисану функцију за добијање нумеричког дела из алфанумеричког низа (ГетНумериц), функција је дизајнирана да преузме један једини аргумент.

У овом одељку ћу се позабавити начином креирања функција које немају аргумент у односу на оне које узимају више аргумената (обавезних као и опционих аргумената).

Креирање функције у ВБА без икаквих аргумената

У радном листу Екцел имамо неколико функција које не узимају аргументе (попут РАНД, ТОДАИ, НОВ).

Ове функције не зависе ни од једног улазног аргумента. На пример, функција ТОДАИ ће вратити тренутни датум, а функција РАНД ће вратити случајни број између 0 и 1.

Такву сличну функцију можете креирати и у ВБА.

Испод је код који ће вам дати назив датотеке. Не узима никакве аргументе јер резултат који треба да врати не зависи од било ког аргумента.

Функција ВоркбоокНаме () Ас Стринг ВоркбоокНаме = ТхисВоркбоок.Наме Крајња функција

Горњи код наводи резултат функције као тип података Стринг (као резултат који желимо је име датотеке - што је низ).

Ова функција додељује вредност „ТхисВоркбоок.Наме“ овој функцији, која се враћа када се функција користи на радном листу.

Ако је датотека сачувана, враћа име са наставком датотеке, иначе једноставно даје име.

Горе наведено ипак има један проблем.

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

Ако желите, можете извршити поновно израчунавање помоћу пречице на тастатури - Цонтрол + Алт + Ф9.

Да би се формула поново израчунала кад год дође до промене на радном листу, потребно је да унесете ред кода у њу.

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

Функција ВоркбоокНаме () Као Стринг Апплицатион.Волатиле Труе ВоркбоокНаме = ТхисВоркбоок.Наме Крајња функција

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

Креирање функције у ВБА са једним аргументом

У једном од горе наведених одељака већ смо видели како да креирате функцију која узима само један аргумент (ГетНумериц функција покривена горе).

Креирајмо још једну једноставну функцију која узима само један аргумент.

Функција створена доњим кодом претворила би референцирани текст у велика слова. Сада већ имамо функцију за то у Екцелу, а ова функција је само да вам покаже како то функционише. Ако морате то да урадите, боље је да користите уграђену УППЕР функцију.

Функција ЦонвертТоУпперЦасе (ЦеллРеф као опсег) ЦонвертТоУпперЦасе = УЦасе (ЦеллРеф) Крајња функција

Ова функција користи функцију УЦасе у ВБА за промену вредности променљиве ЦеллРеф. Затим додељује вредност функцији ЦонвертТоУпперЦасе.

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

Креирање функције у ВБА са више аргумената

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

Код испод би створио функцију која ће издвојити текст пре наведеног разграничења. Потребна су два аргумента - референца ћелије која садржи текстуални низ и граничник.

Фунцтион ГетДатаБефореДелимитер (ЦеллРеф Као Ранге Делим Као стринг) Ас Стринг Дим Резултат Ас Стринг Дим ДелимПоситион Као Целочисленное ДелимПоситион = ИнСтр (1, ЦеллРеф, Делим, вбБинариЦомпаре) - 1 Резултат = Лево (ЦеллРеф, ДелимПоситион) ГетДатаБефореДелимитер = Резултат Енд Фунцтион

Када требате да користите више од једног аргумента у кориснички дефинисаној функцији, сви аргументи могу бити у заградама, одвојени зарезом.

Имајте на уму да за сваки аргумент можете навести тип података. У горњем примеру, 'ЦеллРеф' је декларисан као тип података опсега, а 'Делим' је декларисан као Стринг тип података. Ако не наведете ниједан тип података, ВБА сматра да се ради о варијантном типу података.

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

Затим проверава положај граничника помоћу функције ИНСТР у ВБА. Ова позиција се затим користи за издвајање свих знакова испред граничника (помоћу функције ЛЕФТ).

Коначно, додељује резултат функцији.

Ова формула је далеко од савршене. На пример, ако унесете граничник који се не налази у тексту, то би дало грешку. Сада можете користити функцију ИФЕРРОР на радном листу да бисте се решили грешака, или можете користити доњи код који враћа цео текст када не може да пронађе граничник.

Функција ГетДатаБефореДелимитер (ЦеллРеф Ас Ранге, Делим Ас Стринг) као Стринг Дим Ресулт Ас Стринг Дим ДелимПоситион Ас Интегер ДелимПоситион = ИнСтр (1, ЦеллРеф, Делим, вбБинариЦомпаре) - 1 Иф ДелимПоситион <0 тхен ДелимПоситион = Лен (ЦеллР) ЦеллРеф, ДелимПоситион) ГетДатаБефореДелимитер = Крајња функција резултата

Ову функцију можемо додатно оптимизирати.

Ако текст (из којег желите да издвојите део испред граничника) унесете директно у функцију, то ће вам дати грешку. Само напред … пробајте!

То се дешава како смо навели „ЦеллРеф“ као тип података опсега.

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

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

Код испод би ово урадио:

Функција ГетДатаБефореДелимитер (ЦеллРеф, Делим) Као резултат затамњења низа Као низа затамњења ДелимПоситион као цео број ДелимПоситион = ИнСтр (1, ЦеллРеф, Делим, вбБинариЦомпаре) - 1 Ако је ДелимПоситион <0 Тада је ДелимПоситион = Лен (ЦеллРеф) РесолПро (ДелллРеф) Ресулт ГетДатаБефореДелимитер = Крајња функција резултата

Креирање функције у ВБА са опционим аргументима

У Екцелу постоји много функција где су неки од аргумената опционални.

На пример, легендарна функција ВЛООКУП има 3 обавезна аргумента и један опциони аргумент.

Опционални аргумент, као што име говори, није обавезан за навођење. Ако не наведете један од обавезних аргумената, ваша функција ће вам дати грешку, али ако не наведете опционални аргумент, ваша функција би радила.

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

На пример, у функцији ВЛООКУП, ако не наведете четврти аргумент, ВЛООКУП извршава приближну претрагу, а ако последњи аргумент наведете као ФАЛСЕ (или 0), онда се потпуно подудара.

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

Хајде сада да видимо како да креирате функцију у ВБА са опционим аргументима.

Функција са само опционим аргументом

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

Али можемо створити један са ВБА.

Испод је код функције која ће вам дати тренутни датум у формату дд-мм-ггггг ако не унесете ниједан аргумент (тј. Оставите га празно), и у формату „дд мммм, ииии“ ако унесете било шта као аргумент (тј. било шта тако да аргумент није празан).

Функција ЦуррДате (опционално фмт као варијанта) Резултат затамњења Ако недостаје (фмт) Затим ЦуррДате = Формат (Датум, "дд-мм-ггггг") У супротном ЦуррДате = Формат (Датум, "дд мммм, гггг") Енд Иф Енд Функција

Имајте на уму да горња функција користи „ИсМиссинг“ да провери да ли аргумент недостаје или не. Да бисте користили функцију ИсМиссинг, ваш опционални аргумент мора бити варијантног типа података.

Горња функција ради без обзира на то шта унесете као аргумент. У коду само проверавамо да ли је опциони аргумент наведен или не.

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

Функција ЦуррДате (опционално фмт као варијанта) Резултат затамњења Ако недостаје (фмт) Тада је ЦуррДате = Формат (Датум, "дд-мм-ггггг") ЕлсеИф фмт = 1 Затим ЦуррДате = Формат (Датум, "дд мммм, гггг") Остали ЦуррДате = ЦВЕрр (клЕррВалуе) Функција Енд Иф Енд

Горњи код ствара функцију која приказује датум у формату „дд-мм-ггггг“ ако није наведен аргумент, и у формату „дд мммм, ииии“ када је аргумент 1. Он даје грешку у свим осталим случајевима.

Функција са обавезним, као и опционим аргументима

Већ смо видели код који издваја нумерички део из низа.

Погледајмо сада сличан примјер који узима и потребне и изборне аргументе.

Код испод ствара функцију која издваја део текста из низа. Ако је опционални аргумент ТРУЕ, он даје резултат великим словима, а ако је необавезни аргумент ФАЛСЕ или је изостављен, даје резултат такав какав јесте.

Функција ГетТект (ЦеллРеф Ас Ранге, Оптионал ТектЦасе = Фалсе) Ас Стринг Дим СтрингЛенгтх Ас Интегер Дим Ресулт Ас Стринг СтрингЛенгтх = Лен (ЦеллРеф) Фор и = 1 То СтрингЛенгтх Иф Нот (ИсНумериц (Мид (ЦеллРеф, и, 1))) тхен Резултат = Резултат и средина (ЦеллРеф, и, 1) Следеће и Ако је ТектЦасе = Тачно Тада је резултат = УЦасе (Резултат) ГетТект = Крајња функција резултата

Имајте на уму да смо у горњем коду иницијализирали вредност „ТектЦасе“ као Фалсе (погледајте унутар заграда у првом реду).

Тиме смо осигурали да опционални аргумент почиње подразумеваном вредношћу, која је ФАЛСЕ. Ако корисник наведе вредност као ТРУЕ, функција враћа текст великим словима, а ако корисник наведе опционални аргумент као ФАЛСЕ или га изостави, онда ће текст бити такав какав јесте.

Креирање функције у ВБА са низом као аргументом

До сада смо видели примере стварања функције са опционим/обавезним аргументима - где су ти аргументи били једна вредност.

Такође можете креирати функцију која може узети низ као аргумент. У функцијама радног листа програма Екцел постоји много функција које узимају аргументе низа, као што су СУМ, ВЛООКУП, СУМИФ, ЦОУНТИФ итд.

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

Функција АддЕвен (ЦеллРеф као опсег) Дим Целл Ас Ранге За сваку ћелију у ЦеллРеф Иф ИсНумериц (Целл.Валуе) Тхен Иф Целл.Валуе Мод 2 = 0 тхен Ресулт = Ресулт + Целл.Валуе Енд Иф Енд Ако следећа ћелија АддЕвен = Ресулт Енд Функција

Ову функцију можете користити на радном листу и обезбедити опсег ћелија које имају бројеве као аргумент. Функција би вратила једну вредност - збир свих парних бројева (као што је приказано испод).

У горњој функцији, уместо једне вредности, дали смо низ (А1: А10). Да би ово функционисало, морате бити сигурни да ваш тип података аргумента може да прихвати низ.

У горњем коду сам навео аргумент ЦеллРеф као Ранге (који може узети низ као улаз). Овде можете користити и варијантни тип података.

У коду постоји петља за сваку која пролази кроз сваку ћелију и проверава да ли је број не. Ако није, ништа се не дешава и прелази у следећу ћелију. Ако је то број, проверава да ли је паран или није (помоћу функције МОД).

На крају се додају сви парни бројеви и збир се поново додељује функцији.

Креирање функције са неодређеним бројем аргумената

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

Пример такве функције радног листа је функција СУМ. Можете му навести више аргумената (попут овог):

= СУМ (А1, А2: А4, Б1: Б20)

Горња функција би додала вредности у све ове аргументе. Такође имајте на уму да то могу бити једна ћелија или низ ћелија.

Такву функцију можете да креирате у ВБА тако што последњи аргумент (или би могао бити једини аргумент) није обавезан. Такође, овом опционом аргументу треба да претходи кључна реч „ПарамАрраи“.

„ПарамАрраи“ је модификатор који вам омогућава да прихватите онолико аргумената колико желите. Имајте на уму да коришћење речи ПарамАрраи пре аргумента чини аргумент опционим. Међутим, овде не морате да користите реч Опционално.

Сада креирајмо функцију која може прихватити произвољан број аргумената и додала би све бројеве у наведеним аргументима:

Функција АддАргументс (ПарамАрраи арглист () Ас Вариант) За сваки арглист на арглист АддАргументс = АддАргументс + арг Следећи арг Крај функција

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

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

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

Функција АддАргументс (ПарамАрраи арглист () Ас Вариант) За сваки арглист на арглист -у За сваку ћелију у арг -у АддАргументс = АддАргументс + Ћелија Следећа ћелија Следећа арг Заврши функцију

Имајте на уму да ова формула ради са више ћелија и референцама низа, међутим, не може обрадити тврдо кодиране вредности или изразе. Можете створити робуснију функцију тако што ћете проверити и третирати ова стања, али то овде није намера.

Намера је да вам покаже како ПарамАрраи ради тако да дозволите неограничен број аргумената у функцији. У случају да желите бољу функцију од оне коју је створио горњи код, користите функцију СУМ на радном листу.

Креирање функције која враћа низ

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

Помоћу ВБА можете да креирате функцију која враћа варијанту која може да садржи читав низ вредности.

Формуле низа су такође доступне као уграђене функције у радним листовима програма Екцел. Ако сте упознати са формулама поља у Екцелу, знали бисте да се оне уносе помоћу Цонтрол + Схифт + Ентер (уместо само Ентер). Више о формулама низа можете прочитати овде. Ако не знате за формуле низа, не брините, наставите читати.

Креирајмо формулу која враћа низ од три броја (1,2,3).

Код испод би ово урадио.

Функција ТхрееНумберс () Као варијанта Дим НумберВалуе (1 до 3) НумберВалуе (1) = 1 НумберВалуе (2) = 2 НумберВалуе (3) = 3 ТхрееНумберс = НумберВалуе Крајња функција

У горњем коду смо навели функцију „ТхрееНумберс“ као варијанту. То му омогућава да држи низ вредности.

Променљива „НумберВалуе“ је декларисана као низ са 3 елемента. Он садржи три вредности и додељује га функцији „Три броја“.

Ову функцију можете користити на радном листу тако што ћете ући у функцију и притиснути тастере Цонтрол + Схифт + Ентер (држите тастере Цонтрол и Схифт, а затим притисните Ентер).

Када то учините, вратиће 1 у ћелију, али у стварности садржи све три вредности. Да бисте то проверили, користите формулу испод:

= МАКС (Три броја ())

Користите горњу функцију са Цонтрол + Схифт + Ентер. Приметићете да је резултат сада 3, јер су то највеће вредности у низу које враћа функција Мак, која добија три броја као резултат наше кориснички дефинисане функције - ТхрееНумберс.

Можете користити исту технику за креирање функције која враћа низ имена месеци како је приказано у доњем коду:

Функција Месеци () Као варијанта Дим Месечно име (1 до 12) Име месеца (1) = "Јануарски" Месечни назив (2) = "Фебруарски" Месечни назив (3) = "Март" Месечно име (4) = "Април" Месечно име (5) = Месечни назив „мај“ (6) = „јунски“ месечни назив (7) = „јулски“ месечни назив (8) = „августовски“ месечни назив (9) = „септембарски“ месечни назив (10) = „октобарски“ месечни назив (11) = „новембар "МонтхНаме (12) =" Децембер "Месеци = Функција краја месеца

Сада када унесете функцију = Монтхс () у Екцел радни лист и употребите Цонтрол + Схифт + Ентер, она ће вратити цео низ имена месеци. Имајте на уму да у ћелији видите само јануар јер је то прва вредност у низу. То не значи да низ враћа само једну вредност.

Да бисте вам показали чињеницу да враћа све вредности, урадите следеће - изаберите ћелију са формулом, идите на траку са формулама, изаберите целу формулу и притисните Ф9. Ово ће вам показати све вредности које функција враћа.

Ово можете користити помоћу доње формуле ИНДЕКС да бисте одједном добили листу свих назива месеци.

= ИНДЕКС (Месеци (), РОВ ())

Сада, ако имате много вредности, није добра пракса додељивати ове вредности једну по једну (као што смо већ урадили горе). Уместо тога, можете користити функцију Арраи у ВБА.

Дакле, исти код у којем креирамо функцију „Месеци“ би постао краћи као што је приказано испод:

Месеци функција () Као варијантни месеци = низ ("јануар", "фебруар", "март", "април", "мај", "јун", _ "јул", "август", "септембар", "октобар" , "Новембар", "Децембар") Крај функције

Горња функција користи функцију Арраи за додељивање вредности директно функцији.

Имајте на уму да све функције креиране изнад враћају хоризонтални низ вредности. То значи да ако одаберете 12 хоризонталних ћелија (рецимо А1: Л1), и унесете формулу = Монтхс () у ћелију А1, добићете све називе месеци.

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

То можете учинити помоћу формуле ТРАНСПОСЕ на радном листу.

Једноставно изаберите 12 вертикалних ћелија (суседне) и унесите формулу испод.

Разумевање опсега кориснички дефинисане функције у Екцелу

Функција може имати два опсега - Јавно или Приватни.

• А. Јавни опсег значи да је функција доступна за све листове у радној свесци, као и за све процедуре (под и функција) за све модуле у радној свесци. Ово је корисно када желите да позовете функцију из потпрограма (видећемо како се то ради у следећем одељку).
• А. Приватни опсег значи да је функција доступна само у модулу у којем постоји. Не можете га користити у другим модулима. Такође га нећете видети на листи функција на радном листу. На пример, ако је назив ваше функције „Месеци ()“, а функцију унесете у Екцел (после знака =), неће вам приказати назив функције. Међутим, ипак га можете користити ако унесете назив формуле.

Ако ништа не наведете, функција је подразумевано јавна функција.

Испод је функција која је приватна функција:

Приватебоок ВоркбоокНаме () Ас Стринг ВоркбоокНаме = ТхисВоркбоок.Наме Крајња функција

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

Код испод би ову функцију учинио јавном. Такође ће се појавити на радном листу.

Функција ВоркбоокНаме () Ас Стринг ВоркбоокНаме = ТхисВоркбоок.Наме Крајња функција

Различити начини коришћења кориснички дефинисане функције у Екцелу

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

Хајде да прво размотримо како се користе функције на радном листу.

Коришћење УДФ -а у радним листовима

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

Све што требате учинити је унијети назив функције и он ће се појавити у интеллисенсеу.

Имајте на уму да за приказивање функције на радном листу мора бити јавна функција (као што је објашњено у горњем одељку).

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

• Идите на картицу Подаци.
• Кликните на опцију „Уметни функцију“.
• У дијалогу Уметање функције изаберите категорију коју је дефинисао корисник. Ова опција се приказује само ако имате функцију у ВБ Едитор -у (а функција је јавна).
• Изаберите функцију са листе свих јавних кориснички дефинисаних функција.
• Кликните на дугме У реду.

Горе наведени кораци би уметнули функцију у радни лист. Такође приказује дијалог Фунцтион Аргументс који ће вам дати детаље о аргументима и резултату.

Кориснички дефинисану функцију можете користити као и било коју другу функцију у Екцелу. То такође значи да га можете користити са другим уграђеним Екцел функцијама. На пример. доња формула би дала назив радне свеске великим словима:

= ГОРЊИ (Име радне свеске ())

Кориштење кориснички дефинираних функција у ВБА процедурама и функцијама

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

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

Испод је функција која враћа назив радне свеске.

Функција ВоркбоокНаме () Ас Стринг ВоркбоокНаме = ТхисВоркбоок.Наме Крајња функција

Поступак у наставку позива функцију, а затим приказује име у оквиру за поруку.

Под СховВоркбоокНаме () МсгБок ВоркбоокНаме Крај Суб

Такође можете позвати функцију из друге функције.

У доњим кодовима први код враћа назив радне свеске, а други враћа велика слова позивањем прве функције.

Функција ВоркбоокНаме () Ас Стринг ВоркбоокНаме = ТхисВоркбоок.Наме Крајња функција

Функција ВоркбоокНамеинУппер () ВоркбоокНамеинУппер = УЦасе (ВоркбоокНаме) Крај функција

Позивање кориснички дефинисане функције из других радних свезака

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

Постоји више начина да се то уради:

1. Креирање додатка
2. Функција чувања у радној свесци личних макроа
3. Референцирање функције из друге радне свеске.

Креирање додатка

Креирањем и инсталирањем додатка, прилагођена функција у њему ће бити доступна у свим радним свескама.

Претпоставимо да сте креирали прилагођену функцију - „ГетНумериц“ и желите је у свим радним свескама. Да бисте то урадили, направите нову радну свеску и унесите шифру функције у модул у овој новој радној свесци.

Сада следите доле наведене кораке да бисте га сачували као додатак, а затим га инсталирали у Екцел.

• Идите на картицу Датотека и кликните на Сачувај као.
• У дијалогу Сачувај као, промените тип „Сачувај као“ у .клам. Име које доделите датотеци било би име вашег програмског додатка. У овом примеру, датотека је сачувана под именом ГетНумериц.
  • Приметићете да се путања датотеке у којој се чува аутоматски мења. Можете користити подразумевани или га променити ако желите.
• Отворите нову Екцел радну свеску и идите на картицу Девелопер.
• Кликните на опцију Додаци програма Екцел.
• У дијалогу Додаци прегледајте и пронађите датотеку коју сте сачували, а затим кликните на дугме У реду.

Сада је додатак активиран.

Сада можете користити прилагођену функцију у свим радним свескама.

Чување функције у личној макро радној свесци

Персонал Мацро Воркбоок је скривена радна свеска у вашем систему која се отвара сваки пут када отворите Екцел апликацију.

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

Подразумевано, у вашем Екцелу нема личне радне свеске за макрое. Морате да га креирате тако што ћете снимити макро и сачувати га у радној свесци Лични макро.

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

Референцирање функције из друге радне свеске

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

Претпоставимо да имате радну свеску са именом „Радна свеска са формулом ', и има функцију са именом 'ГетНумериц '.

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

= 'Радна свеска са формулом'! ГетНумериц (А1)

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

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

Коришћење Изјаве о функцији излаза ВБА

Ако желите да изађете из функције док је код покренут, то можете учинити помоћу наредбе „Изађи функцију“.

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

Функција ГетНумерицФирстТхрее (ЦеллРеф Ас Ранге) Ас Лонг Дим СтрингЛенгтх Ас Интегер СтрингЛенгтх = Лен (ЦеллРеф) Фор и = 1 То СтрингЛенгтх Иф Ј = 3 тхен Екит Фунцтион Иф ИсНумериц (Мид (ЦеллРеф, и, 1)) тхен Ј = Ј + 1 Резултат = Резултат и средина (ЦеллРеф, и, 1) ГетНумерицФирстТхрее = Резултат завршава ако је следеће функција и завршава

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

Отклањање грешака кориснички дефинисане функције

Постоји неколико техника које можете користити при отклањању грешака кориснички дефинисане функције у ВБА:

Отклањање грешака у прилагођеној функцији помоћу оквира за поруке

Користите функцију МсгБок да прикажете оквир за поруку са одређеном вредношћу.

Вредност коју приказујете може бити заснована на ономе што желите да тестирате. На пример, ако желите да проверите да ли се код извршава или не, свака порука би радила, а ако желите да проверите да ли петље раде или не, можете приказати одређену вредност или бројач петљи.

Отклањање грешака у прилагођеној функцији постављањем тачке прекида

Подесите тачку прекида да бисте могли да пролазите кроз сваку линију један по један. Да бисте поставили тачку прекида, изаберите линију на којој желите и притисните Ф9 или кликните на сиву вертикалну област која је остављена до линија кода. Било који од ових метода би убацио тачку прекида (видећете црвену тачку у сивој зони).

Када поставите тачку прекида и извршите функцију, она иде до линије тачке прекида, а затим се зауставља. Сада можете проћи кроз код помоћу тастера Ф8. Притиском на Ф8 једном прелазите на следећи ред у коду.

Отклањање грешака у прилагођеној функцији помоћу програма Дебуг.Принт у коду

Можете користити Дебуг.Принт наредбу у свом коду да бисте добили вредности наведених променљивих/аргумената у непосредном прозору.

На пример, у доњем коду сам користио Дебуг.Принт да добијем вредност две променљиве - „ј“ и „Резултат“

Функција ГетНумерицФирстТхрее (ЦеллРеф Ас Ранге) Ас Лонг Дим СтрингЛенгтх Ас Интегер СтрингЛенгтх = Лен (ЦеллРеф) Фор и = 1 То СтрингЛенгтх Иф Ј = 3 тхен Екит Фунцтион Иф ИсНумериц (Мид (ЦеллРеф, и, 1)) тхен Ј = Ј + 1 Резултат = Резултат и средина (ЦеллРеф, и, 1) Дебуг.Принт Ј, Резултат ГетНумерицФирстТхрее = Резултат завршава ако је следећа и завршава функција

Када се овај код изврши, у непосредном прозору приказује следеће.

Екцел уграђене функције вс. ВБА Кориснички дефинисана функција

Постоји неколико предности коришћења уграђених функција програма Екцел у односу на прилагођене функције креиране у ВБА.

• Уграђене функције су много брже од ВБА функција.
• Када креирате извештај/контролну таблу користећи ВБА функције и шаљете га клијенту/колеги, они не би морали да брину о томе да ли су макрои омогућени или не. У неким случајевима, клијенти/купци се уплаше видећи упозорење у жутој траци (која их једноставно тражи да омогуће макрое).
• Са уграђеним Екцел функцијама, не морате да бринете о екстензијама датотека. Ако у радној свесци имате макрое или кориснички дефинисане функције, потребно је да их сачувате у .клсм.

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

• Боље је користити функцију коју дефинише корисник ако је ваша уграђена формула огромна и компликована. Ово постаје још релевантније када вам је потребан неко други да ажурира формуле. На пример, ако имате огромну формулу сачињену од много различитих функција, чак и промена референце на ћелију може бити досадна и склона грешкама. Уместо тога, можете креирати прилагођену функцију која узима само један или два аргумента и обавља све тешке послове.
• Када морате да урадите нешто што не могу да ураде уграђене функције програма Екцел. Пример овога може бити када желите да извучете све нумеричке знакове из низа. У таквим случајевима корист од кориснички дефиниране функције гар надмашује њене негативне стране.

Где ставити ВБА код за кориснички дефинисану функцију

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

Испод су кораци за унос кода за функцију „ГетНумериц“ у радну свеску.

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

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

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