Създаване на потребителска функция (UDF) в Excel VBA (Ultimate Guide)

С VBA можете да създадете персонализирана функция (наричана още потребителска функция), която може да се използва в работните листове точно като обикновените функции.

Те са полезни, когато съществуващите функции на Excel не са достатъчни. В такива случаи можете да създадете своя собствена потребителска дефинирана функция (UDF), която да отговаря на вашите специфични нужди.

В този урок ще разгледам всичко за създаването и използването на персонализирани функции във VBA.

Ако се интересувате да научите VBA по лесния начин, вижте моя Онлайн обучение по Excel VBA.

Какво е функционална процедура във VBA?

Процедура за функция е VBA код, който извършва изчисления и връща стойност (или масив от стойности).

Използвайки процедура за функция, можете да създадете функция, която можете да използвате в работния лист (точно както всяка обикновена функция на Excel, като SUM или VLOOKUP).

Когато сте създали процедура за функция с помощта на VBA, можете да я използвате по три начина:

1. Като формула в работния лист, където може да приема аргументи като входове и връща стойност или масив от стойности.
2. Като част от кода на вашата подпрограма VBA или друг код на функция.
3. При условно форматиране.

Въпреки че вече има 450+ вградени функции на Excel в работния лист, може да се нуждаете от персонализирана функция, ако:

• Вградените функции не могат да направят това, което искате да направите. В този случай можете да създадете персонализирана функция въз основа на вашите изисквания.
• Вградените функции могат да свършат работата, но формулата е дълга и сложна. В този случай можете да създадете персонализирана функция, която е лесна за четене и използване.

Обърнете внимание, че персонализираните функции, създадени с помощта на VBA, могат да бъдат значително по -бавни от вградените функции. Следователно, те са най -подходящи за ситуации, при които не можете да получите резултат с помощта на вградените функции.

Функция Vs. Подпрограма във VBA

„Подпрограма“ ви позволява да изпълнявате набор от код, докато „Функция“ връща стойност (или масив от стойности).

За да ви дадем пример, ако имате списък с числа (положителни и отрицателни) и искате да идентифицирате отрицателните числа, ето какво можете да направите с функция и подпрограма.

Подпрограма може да премине през всяка клетка в диапазона и да подчертае всички клетки, които имат отрицателна стойност в нея. В този случай подпрограмата в крайна сметка променя свойствата на обекта range (чрез промяна на цвета на клетките).

С персонализирана функция можете да я използвате в отделна колона и тя може да върне TRUE, ако стойността в клетката е отрицателна, и FALSE, ако е положителна. С функция не можете да променяте свойствата на обекта. Това означава, че не можете да промените цвета на клетката със самата функция (обаче можете да го направите с помощта на условно форматиране с персонализираната функция).

Когато създавате потребителска функция (UDF), използвайки VBA, можете да използвате тази функция в работния лист, както всяка друга функция. Ще разгледам повече за това в раздела „Различни начини за използване на дефинирана от потребителя функция в Excel“.

Създаване на проста дефинирана от потребителя функция във VBA

Позволете ми да създам проста дефинирана от потребителя функция във VBA и да ви покажа как работи.

Кодът по -долу създава функция, която ще извлече числовите части от буквено -цифров низ.

Функция GetNumeric (CellRef As String) като Long Dim StringLength As Integer StringLength = Len (CellRef) For i = 1 To StringLength If IsNumeric (Mid (CellRef, i, 1)) Тогава Result = Result & Mid (CellRef, i, 1) След това i GetNumeric = Крайна функция на резултата

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

По -долу е описана тази функция - GetNumeric - може да се използва в Excel.

Сега, преди да ви кажа как е създадена тази функция във VBA и как работи, има няколко неща, които трябва да знаете:

• Когато създавате функция във VBA, тя става достъпна в цялата работна книга, както всяка друга обикновена функция.
• Когато въведете името на функцията, последвано от знак за равенство, Excel ще ви покаже името на функцията в списъка на съвпадащите функции. В горния пример, когато въведох = Get, Excel ми показа списък с моята персонализирана функция.

Вярвам, че това е добър пример, когато можете да използвате VBA за създаване на лесна за използване функция в Excel. Можете да направите същото и с формула (както е показано в този урок), но това става сложно и трудно за разбиране. С този UDF трябва само да предадете един аргумент и да получите резултата.

Анатомия на потребителска функция във VBA

В горния раздел ви дадох кода и ви показах как UDF функцията работи в работен лист.

Сега нека се потопим дълбоко и да видим как е създадена тази функция. Трябва да поставите кода по -долу в модул във VB Editor. Обхващам тази тема в раздела - „Къде да поставя VBA кода за потребителска функция“.

Функция GetNumeric (CellRef As String) като Long 'Тази функция извлича числовата част от низа Dim StringLength As Integer StringLength = Len (CellRef) For i = 1 To StringLength If IsNumeric (Mid (CellRef, i, 1)) then Result = Резултат и средна (CellRef, i, 1) Следваща i GetNumeric = Крайна функция на резултата

Първият ред на кода започва с думата - Функция.

Тази дума казва на VBA, че нашият код е функция (а не подпрограма). Думата Функция е последвана от името на функцията - GetNumeric. Това е името, което ще използваме в работния лист, за да използваме тази функция.

• Името на функцията не може да съдържа интервали в него. Също така не можете да назовете функция, ако тя се сблъсква с името на препратка към клетка. Например, не можете да дадете име на функцията ABC123, тъй като тя се отнася и за клетка в работния лист на Excel.
• Не трябва да давате на функцията си същото име като това на съществуваща функция. Ако направите това, Excel би предпочел вградената функция.
• Можете да използвате подчертаване, ако искате да разделите думите. Например Get_Numeric е приемливо име.

Името на функцията е последвано от някои аргументи в скоби. Това са аргументите, от които нашата функция ще се нуждае от потребителя. Това са точно като аргументите, които трябва да предоставим на вградените функции на Excel. Например във функция COUNTIF има два аргумента (диапазон и критерии)

В скобите трябва да посочите аргументите.

В нашия пример има само един аргумент - CellRef.

Добра практика е също така да се уточни какъв аргумент очаква функцията. В този пример, тъй като ще захранваме функцията с препратка към клетка, можем да посочим аргумента като тип „Range“. Ако не посочите тип данни, VBA ще го приеме за вариант (което означава, че можете да използвате всеки тип данни).

Ако имате повече от един аргумент, можете да ги посочите в една и съща скоба - разделени със запетая. По-късно в този урок ще видим как да използваме множество аргументи в дефинирана от потребителя функция.

Обърнете внимание, че функцията е посочена като тип данни „String“. Това би казало на VBA, че резултатът от формулата ще бъде от типа данни String.

Въпреки че мога да използвам числов тип данни тук (като дълъг или двоен), това би ограничило диапазона от числа, които може да върне. Ако имам дълъг низ от 20 числа, който трябва да извлека от общия низ, декларирането на функцията като Дълъг или Двоен би дало грешка (тъй като числото ще бъде извън неговия диапазон). Следователно запазих изходния тип данни на функцията като String.

Вторият ред на кода - този в зелено, който започва с апостроф - е коментар. При четене на кода VBA игнорира този ред. Можете да използвате това, за да добавите описание или подробности за кода.

Третият ред на кода декларира променлива „StringLength“ като целочислен тип данни. Това е променливата, в която съхраняваме стойността на дължината на низа, който се анализира по формулата.

Четвъртият ред декларира променливата Result като тип данни String. Това е променливата, в която ще извлечем числата от буквено -цифровия низ.

Петият ред определя дължината на низа във входния аргумент на променливата „StringLength“. Обърнете внимание, че „CellRef“ се отнася до аргумента, който ще бъде даден от потребителя, докато използва формулата в работния лист (или като я използва във VBA - което ще видим по -късно в този урок).

Шести, седми и осми ред са част от цикъла For Next. Цикълът се изпълнява толкова пъти, колкото знаци има във входния аргумент. Този номер се дава от функцията LEN и се присвоява на променливата „StringLength“.

Така цикълът работи от „1 до Stringlength“.

В рамките на цикъла операторът IF анализира всеки знак от низ и ако е цифров, той добавя този цифров знак към променливата Result. Той използва функцията MID във VBA, за да направи това.

Вторият последен ред на кода приписва стойността на резултата на функцията. Този ред код гарантира, че функцията връща стойността „Резултат“ обратно в клетката (откъдето е извикана).

Последният ред на кода е End Function. Това е задължителен ред код, който казва на VBA, че кодът на функцията завършва тук.

Горният код обяснява различните части на типична персонализирана функция, създадена във VBA. В следващите раздели ще се задълбочим в тези елементи и също така ще видим различните начини за изпълнение на функцията VBA в Excel.

Аргументи в потребителска функция във VBA

В горните примери, където създадохме дефинирана от потребителя функция, за да получим числовата част от буквено-цифров низ (GetNumeric), функцията е проектирана да приема един единствен аргумент.

В този раздел ще разгледам как да създавам функции, които не приемат аргументи за тези, които приемат множество аргументи (задължителни, както и незадължителни аргументи).

Създаване на функция във VBA без никакви аргументи

В работния лист на Excel имаме няколко функции, които не приемат аргументи (като RAND, TODAY, NOW).

Тези функции не зависят от никакви входни аргументи. Например функцията TODAY ще върне текущата дата, а функцията RAND ще върне случайно число между 0 и 1.

Можете да създадете подобна функция и във VBA.

По -долу е кодът, който ще ви даде името на файла. Той не приема никакви аргументи, тъй като резултатът, който трябва да върне, не зависи от нито един аргумент.

Функция WorkbookName () Като String WorkbookName = ThisWorkbook.Name Крайна функция

Горният код указва резултата на функцията като тип данни String (тъй като резултатът, който искаме, е името на файла - което е низ).

Тази функция присвоява стойността на „ThisWorkbook.Name“ на функцията, която се връща, когато функцията се използва в работния лист.

Ако файлът е записан, той връща името с разширението на файла, иначе просто дава името.

Горното обаче има един проблем.

Ако името на файла се промени, той няма да се актуализира автоматично. Обикновено функция се опреснява винаги, когато има промяна във входните аргументи. Но тъй като в тази функция няма аргументи, функцията не се преизчислява (дори ако промените името на работната книга, затворете я и след това отворете отново).

Ако искате, можете да принудите преизчисляване, като използвате клавишната комбинация - Control + Alt + F9.

За да накарате формулата да се изчисли отново, когато има промяна в работния лист, трябва да въведете ред код към него.

Кодът по -долу прави функцията да се изчислява отново, когато има промяна в работния лист (точно както други подобни функции на работен лист, като например функцията TODAY или RAND).

Функция WorkbookName () Като String Application.Volatile True WorkbookName = ThisWorkbook.Name Крайна функция

Сега, ако промените името на работната книга, тази функция ще се актуализира винаги, когато има някаква промяна в работния лист или когато отворите отново тази работна книга.

Създаване на функция във VBA с един аргумент

В един от горните раздели вече видяхме как да създадем функция, която приема само един аргумент (функцията GetNumeric, обхваната по -горе).

Нека създадем друга проста функция, която приема само един аргумент.

Функцията, създадена с кода по -долу, ще преобразува референтния текст в главни букви. Сега вече имаме функция за нея в Excel и тази функция е само за да ви покаже как работи. Ако трябва да направите това, по -добре е да използвате вградената UPPER функция.

Функция ConvertToUpperCase (CellRef като диапазон) ConvertToUpperCase = UCase (CellRef) Крайна функция

Тази функция използва функцията UCase във VBA, за да промени стойността на променливата CellRef. След това присвоява стойността на функцията ConvertToUpperCase.

Тъй като тази функция приема аргумент, не е необходимо да използваме частта Application.Volatile тук. Веднага след като аргументът се промени, функцията автоматично ще се актуализира.

Създаване на функция във VBA с множество аргументи

Точно като функциите на работен лист, можете да създавате функции във VBA, които приемат множество аргументи.

Кодът по -долу ще създаде функция, която ще извлече текста преди посочения разделител. Необходими са два аргумента - препратката към клетката с текстов низ и разделителят.

Функция GetDataBeforeDelimiter (CellRef Както Range, разделител Както стринга) като String Dim резултат като String Dim DelimPosition Като цяло число DelimPosition = инстр (1, CellRef, разделител, vbBinaryCompare) - 1 Резултат = Left (CellRef, DelimPosition) GetDataBeforeDelimiter = Резултат End Function

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

Имайте предвид, че за всеки аргумент можете да посочите тип данни. В горния пример „CellRef“ е деклариран като тип данни за диапазон, а „Delim“ е деклариран като тип данни String. Ако не посочите никакъв тип данни, VBA смята, че това са варианти на тип данни.

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

След това той проверява позицията на разделителя, като използва функцията INSTR във VBA. След това тази позиция се използва за извличане на всички знаци преди разделителя (използвайки функцията LEFT).

И накрая, той присвоява резултата на функцията.

Тази формула далеч не е съвършена. Например, ако въведете разделител, който не е намерен в текста, това ще даде грешка. Сега можете да използвате функцията IFERROR в работния лист, за да се отървете от грешките, или можете да използвате кода по -долу, който връща целия текст, когато не може да намери разделителя.

Функция GetDataBeforeDelimiter (CellRef As Range, Delim As String) като String Dim Result As String Dim DelimPosition As Integer DelimPosition = InStr (1, CellRef, Delim, vbBinaryCompare) - 1 Ако DelimPosition <0 Тогава DelimPosition = Len (CellR) CellRef, DelimPosition) GetDataBeforeDelimiter = Крайна функция на резултата

Можем допълнително да оптимизираме тази функция.

Ако въведете текста (от който искате да извлечете частта преди разделителя) директно във функцията, това ще ви даде грешка. Продължавай … пробвай!

Това се случва, тъй като определихме „CellRef“ като тип данни за диапазон.

Или, ако искате разделителят да е в клетка и да използвате препратката към клетката, вместо да я кодирате трудно във формулата, не можете да направите това с горния код. Това е така, защото Delim е деклариран като низов тип данни.

Ако искате функцията да има гъвкавостта да приема директно въвеждане на текст или препратки към клетки от потребителя, трябва да премахнете декларацията за типа данни. Това в крайна сметка ще превърне аргумента като вариант на тип данни, който може да приема всеки тип аргумент и да го обработва.

Кодът по -долу ще направи това:

Функция GetDataBeforeDelimiter (CellRef, Delim) Като резултат от низово затъмняване Като низово затъмняване DelimPosition като цяло число DelimPosition = InStr (1, CellRef, Delim, vbBinaryCompare) - 1 Ако DelimPosition <0 Тогава DelimPosition = Len (CellRef) GetDataBeforeDelimiter = Крайна функция на резултата

Създаване на функция във VBA с незадължителни аргументи

В Excel има много функции, при които някои от аргументите са незадължителни.

Например, легендарната функция VLOOKUP има 3 задължителни аргумента и един незадължителен аргумент.

Незадължителен аргумент, както подсказва името, не е задължителен за посочване. Ако не посочите един от задължителните аргументи, функцията ви ще ви даде грешка, но ако не посочите незадължителния аргумент, функцията ви ще работи.

Но незадължителните аргументи не са безполезни. Те ви позволяват да избирате от редица опции.

Например във функцията VLOOKUP, ако не посочите четвъртия аргумент, VLOOKUP прави приблизително търсене и ако посочите последния аргумент като FALSE (или 0), той прави точно съвпадение.

Не забравяйте, че незадължителните аргументи винаги трябва да идват след всички необходими аргументи. Не можете да имате незадължителни аргументи в началото.

Сега нека видим как да създадем функция във VBA с незадължителни аргументи.

Функция само с незадължителен аргумент

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

Но можем да създадем такъв с VBA.

По-долу е кодът на функцията, която ще ви даде текущата дата във формат dd-mm-yyyy, ако не въведете никакъв аргумент (т.е. оставете го празно), и във формат „dd mmmm, yyyy“, ако въведете нещо като аргумент (т.е. всичко, така че аргументът да не е празен).

Функция CurrDate (незадължителен fmt като вариант) Dim Result If IsMissing (fmt) then CurrDate = Format (Date, "dd-mm-yyyy") Else CurrDate = Format (Date, "dd mmmm, yyyy") End If End Function

Обърнете внимание, че горната функция използва „IsMissing“, за да провери дали аргументът липсва или не. За да използвате функцията IsMissing, незадължителният ви аргумент трябва да е от варианта тип данни.

Горната функция работи независимо какво въведете като аргумент. В кода проверяваме само дали незадължителният аргумент е предоставен или не.

Можете да направите това по -стабилно, като вземете само конкретни стойности като аргументи и покажете грешка в останалите случаи (както е показано в кода по -долу).

Функция CurrDate (незадължителен fmt като вариант) Dim резултат Резултат, ако липсва (fmt) Тогава CurrDate = Format (Дата, "dd-mm-yyyy") ElseIf fmt = 1 Тогава CurrDate = Format (Date, "dd mmmm, yyyy") Else CurrDate = CVErr (xlErrValue) Функция End If End

Горният код създава функция, която показва датата във формат „dd-mm-yyyy“, ако не е предоставен аргумент, и във формат „dd mmmm, yyyy“, когато аргументът е 1. Той дава грешка във всички останали случаи.

Функция с задължителни, както и незадължителни аргументи

Вече видяхме код, който извлича числовата част от низ.

Нека сега разгледаме подобен пример, който взема както задължителни, така и незадължителни аргументи.

Кодът по -долу създава функция, която извлича текстовата част от низ. Ако незадължителният аргумент е TRUE, той дава резултата с главни букви, а ако незадължителният аргумент е FALSE или е пропуснат, той дава резултата такъв, какъвто е.

Функция GetText (CellRef като диапазон, незадължителен TextCase = False) Като низ Dim StringLength Като цяло число Dim резултат Резултат като String StringLength = Len (CellRef) За i = 1 To StringLength Ако не (IsNumeric (Mid (CellRef, i, 1))) Тогава Резултат = Резултат и в средата (CellRef, i, 1) Следващ i Ако TextCase = True, тогава Резултат = UCase (Резултат) GetText = Резултат Крайна функция

Обърнете внимание, че в горния код сме инициализирали стойността на „TextCase“ като False (погледнете в скобите в първия ред).

По този начин ние гарантираме, че незадължителният аргумент започва със стойността по подразбиране, която е FALSE. Ако потребителят посочи стойността като TRUE, функцията връща текста с главни букви, а ако потребителят посочи незадължителния аргумент като FALSE или го пропусне, тогава върнатият текст е такъв, какъвто е.

Създаване на функция във VBA с масив като аргумент

Досега видяхме примери за създаване на функция с незадължителни/задължителни аргументи - където тези аргументи бяха една стойност.

Можете също така да създадете функция, която може да вземе масив като аргумент. Във функциите на работния лист на Excel има много функции, които приемат аргументи на масива, като SUM, VLOOKUP, SUMIF, COUNTIF и т.н.

По -долу е кодът, който създава функция, която дава сумата от всички четни числа в определения диапазон на клетките.

Функция AddEven (CellRef като диапазон) Dim Cell като диапазон за всяка клетка в CellRef If IsNumeric (Cell.Value) Тогава If Cell.Value Mod 2 = 0 Тогава Result = Result + Cell.Value End If End Ако следващата клетка AddEven = Result End Функция

Можете да използвате тази функция в работния лист и да предоставите диапазона от клетки, които имат числата като аргумент. Функцията ще върне единична стойност - сумата от всички четни числа (както е показано по -долу).

В горната функция, вместо единична стойност, сме предоставили масив (A1: A10). За да работи това, трябва да се уверите, че вашият тип данни на аргумента може да приеме масив.

В горния код посочих аргумента CellRef като Range (който може да вземе масив като вход). Можете също да използвате варианта тип данни тук.

В кода има цикъл „За всяка“, който преминава през всяка клетка и проверява дали е номер на не. Ако не е, нищо не се случва и се премества в следващата клетка. Ако е номер, той проверява дали е четен или не (с помощта на функцията MOD).

В крайна сметка всички четни числа се добавят и сумата се присвоява обратно на функцията.

Създаване на функция с неопределен брой аргументи

Докато създавате някои функции във VBA, може да не знаете точния брой аргументи, които потребителят иска да предостави. Така че необходимостта е да се създаде функция, която да приема колкото се може повече предоставени аргументи и да ги използва за връщане на резултата.

Пример за такава функция на работен лист е функцията SUM. Можете да предоставите множество аргументи (като този):

= SUM (A1, A2: A4, B1: B20)

Горната функция би добавила стойностите във всички тези аргументи. Също така имайте предвид, че те могат да бъдат единична клетка или масив от клетки.

Можете да създадете такава функция във VBA, като имате последния аргумент (или може да е единственият аргумент) като незадължителен. Освен това този незадължителен аргумент трябва да бъде предшестван от ключовата дума „ParamArray“.

„ParamArray“ е модификатор, който ви позволява да приемате колкото искате аргументи. Обърнете внимание, че използването на думата ParamArray преди аргумент прави аргумента по избор. Не е необходимо обаче да използвате думата Незадължително тук.

Сега нека създадем функция, която може да приеме произволен брой аргументи и ще добави всички числа в посочените аргументи:

Функция AddArguments (ParamArray arglist () Като вариант) За всеки arg в arglist AddArguments = AddArguments + arg Следващ arg Крайна функция

Горната функция може да приема произволен брой аргументи и да добавя тези аргументи, за да даде резултата.

Обърнете внимание, че можете да използвате само една стойност, препратка към клетка, булева стойност или израз като аргумент. Не можете да предоставите масив като аргумент. Например, ако един от вашите аргументи е D8: D10, тази формула ще ви даде грешка.

Ако искате да можете да използвате и двата многоклетъчни аргумента, трябва да използвате кода по-долу:

Функция AddArguments (ParamArray arglist () като вариант) За всеки arg в arglist За всяка клетка в arg AddArguments = AddArguments + Cell Next Cell Next arg End Функция

Имайте предвид, че тази формула работи с множество клетки и препратки към масиви, но не може да обработва твърдо кодирани стойности или изрази. Можете да създадете по -стабилна функция, като проверите и лекувате тези условия, но това не е намерението тук.

Намерението тук е да ви покаже как работи ParamArray, за да можете да разрешите неопределен брой аргументи във функцията. В случай, че искате по -добра функция от тази, създадена от горния код, използвайте функцията SUM в работния лист.

Създаване на функция, която връща масив

Досега сме виждали функции, които връщат една -единствена стойност.

С VBA можете да създадете функция, която връща вариант, който може да съдържа цял масив от стойности.

Формулите за масиви също са налични като вградени функции в работни листове на Excel. Ако сте запознати с формулите за масиви в Excel, ще знаете, че те се въвеждат с помощта на Control + Shift + Enter (вместо само Enter). Можете да прочетете повече за формулите на масива тук. Ако не знаете за формули на масиви, не се притеснявайте, продължете да четете.

Нека създадем формула, която връща масив от три числа (1,2,3).

Кодът по -долу би направил това.

Функция ThreeNumbers () Като вариант Dim Dim NumberValue (1 до 3) NumberValue (1) = 1 NumberValue (2) = 2 NumberValue (3) = 3 ThreeNumbers = NumberValue Крайна функция

В горния код ние определихме функцията „ThreeNumbers“ като вариант. Това му позволява да съхранява масив от стойности.

Променливата „NumberValue“ е декларирана като масив с 3 елемента. Той съдържа трите стойности и го присвоява на функцията „ThreeNumbers“.

Можете да използвате тази функция в работния лист, като влезете във функцията и натиснете клавиша Control + Shift + Enter (задръжте клавишите Control и Shift и след това натиснете Enter).

Когато направите това, той ще върне 1 в клетката, но в действителност той съдържа и трите стойности. За да проверите това, използвайте следната формула:

= MAX (Три номера ())

Използвайте горната функция с Control + Shift + Enter. Ще забележите, че резултатът вече е 3, тъй като това е най -големите стойности в масива, върнат от функцията Max, която получава трите числа в резултат на нашата дефинирана от потребителя функция - ThreeNumbers.

Можете да използвате същата техника, за да създадете функция, която връща масив от имена на месеци, както е показано в кода по -долу:

Функция Месеци () Като вариант Dim MenameName (1 до 12) MonthName (1) = "January" MonthName (2) = "February" MonthName (3) = "March" MonthName (4) = "April" MonthName (5) = „Май“ Име на месец (6) = „Юни“ Име на месец (7) = „Юли“ Месечно име (8) = „Август“ Месечно име (9) = „Септември“ Месечно име (10) = „Октомври“ Месечно име (11) = „Ноември "MonthName (12) =" Декември "Месеци = Функция за край на MonthName

Сега, когато въведете функцията = Months () в работния лист на Excel и използвате Control + Shift + Enter, тя ще върне целия масив от имена на месеци. Обърнете внимание, че виждате само януари в клетката, тъй като това е първата стойност в масива. Това не означава, че масивът връща само една стойност.

За да ви покажем факта, че връща всички стойности, направете това - изберете клетката с формулата, отидете на лентата с формули, изберете цялата формула и натиснете F9. Това ще ви покаже всички стойности, които функцията връща.

Можете да използвате това, като използвате формулата INDEX по -долу, за да получите списък с всички имена на месеци наведнъж.

= INDEX (Месеци (), ROW ())

Сега, ако имате много стойности, не е добра практика да присвоявате тези стойности една по една (както направихме по -горе). Вместо това можете да използвате функцията Array във VBA.

Така че същият код, където създаваме функцията „Месеци“, ще стане по -кратък, както е показано по -долу:

Функционални месеци () Като варианти на месеци = масив („януари“, „февруари“, „март“, „април“, „май“, „юни“, _ „юли“, „август“, „септември“, „октомври“ , "Ноември", "Декември") Крайна функция

Горната функция използва функцията Array за присвояване на стойностите директно на функцията.

Имайте предвид, че всички функции, създадени по -горе, връщат хоризонтален масив от стойности. Това означава, че ако изберете 12 хоризонтални клетки (да речем A1: L1) и въведете формулата = Months () в клетка A1, тя ще ви даде всички имена на месеци.

Но какво ще стане, ако искате тези стойности във вертикален диапазон от клетки.

Можете да направите това, като използвате формулата TRANSPOSE в работния лист.

Просто изберете 12 вертикални клетки (непрекъснати) и въведете формулата по -долу.

Разбиране на обхвата на потребителска функция в Excel

Функцията може да има два обхвата - Обществен или Частни.

• А Обществен обхват означава, че функцията е достъпна за всички листове в работната книга, както и за всички процедури (под и функция) във всички модули в работната книга. Това е полезно, когато искате да извикате функция от подпрограма (ще видим как се прави това в следващия раздел).
• А Частен обхват означава, че функцията е достъпна само в модула, в който съществува. Не можете да го използвате в други модули. Също така няма да го видите в списъка с функции в работния лист. Например, ако името на функцията ви е „Месеци ()“ и въведете функция в Excel (след знака =), тя няма да ви покаже името на функцията. Все пак можете да го използвате, ако въведете името на формулата.

Ако не посочите нищо, функцията е публична функция по подразбиране.

По -долу е частна функция:

Частна функция WorkbookName () Като String WorkbookName = ThisWorkbook.Name Крайна функция

Можете да използвате тази функция в подпрограмите и процедурите в същите модули, но не можете да я използвате в други модули. Тази функция също няма да се показва в работния лист.

Кодът по -долу ще направи тази функция публична. Той също така ще се появи в работния лист.

Функция WorkbookName () Като String WorkbookName = ThisWorkbook.Name Крайна функция

Различни начини за използване на дефинирана от потребителя функция в Excel

След като създадете дефинирана от потребителя функция във VBA, можете да я използвате по много различни начини.

Нека първо разгледаме как да използваме функциите в работния лист.

Използване на UDF в работни листове

Вече видяхме примери за използване на функция, създадена във VBA, в работния лист.

Всичко, което трябва да направите, е да въведете името на функциите и то се показва в intellisense.

Обърнете внимание, че за да се покаже функцията в работния лист, тя трябва да бъде публична функция (както е обяснено в горния раздел).

Можете също да използвате диалоговия прозорец Вмъкване на функция, за да вмъкнете дефинираната от потребителя функция (като използвате стъпките по -долу). Това ще работи само за публични функции.

• Отидете в раздела Данни.
• Щракнете върху опцията „Вмъкване на функция“.
• В диалоговия прозорец Вмъкване на функция изберете Потребителско определено като категория. Тази опция се показва само когато имате функция във VB Editor (и функцията е Public).
• Изберете функцията от списъка на всички функции, публично дефинирани от потребителя.
• Щракнете върху бутона Ok.

Горните стъпки биха вмъкнали функцията в работния лист. Той също така показва диалогов прозорец Функционални аргументи, който ще ви даде подробности за аргументите и резултата.

Можете да използвате дефинирана от потребителя функция, както всяка друга функция в Excel. Това също означава, че можете да го използвате с други вградени функции на Excel. Например. формулата по -долу би дала името на работната книга с главни букви:

= ГОРНА (Име на работна книга ())

Използване на дефинирани от потребителя функции във VBA процедури и функции

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

Ако функцията е публична, тя може да се използва във всяка процедура в същия или различен модул. Ако е частен, може да се използва само в същия модул.

По -долу е функция, която връща името на работната книга.

Функция WorkbookName () Като String WorkbookName = ThisWorkbook.Name Крайна функция

Процедурата по -долу извиква функцията и след това показва името в полето за съобщение.

Sub ShowWorkbookName () MsgBox WorkbookName End Sub

Можете също да извикате функция от друга функция.

В кодовете по -долу първият код връща името на работната книга, а вторият връща името с главни букви чрез извикване на първата функция.

Функция WorkbookName () Като String WorkbookName = ThisWorkbook.Name Крайна функция

Функция WorkbookNameinUpper () WorkbookNameinUpper = UCase (WorkbookName) Крайна функция

Извикване на потребителска функция от други работни книги

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

Има няколко начина да направите това:

1. Създаване на добавка
2. Функция за запазване в личната работна книга за макроси
3. Позоваване на функцията от друга работна книга.

Създаване на добавка

Създавайки и инсталирайки добавка, ще имате персонализирана функция в нея, налична във всички работни книги.

Да предположим, че сте създали персонализирана функция - „GetNumeric“ и я искате във всички работни книги. За да направите това, създайте нова работна книга и поставете кода на функцията в модул в тази нова работна книга.

Сега следвайте стъпките по-долу, за да го запишете като добавка и след това да го инсталирате в Excel.

• Отидете в раздела Файл и кликнете върху Запазване като.
• В диалоговия прозорец Запазване като променете типа „Запазване като“ на .xlam. Името, което задавате на файла, би било името на вашата добавка. В този пример файлът се записва с името GetNumeric.
  • Ще забележите, че пътят на файла, където се записва, се променя автоматично. Можете да използвате стандартния или да го промените, ако искате.
• Отворете нова работна книга на Excel и отидете в раздела Разработчик.
• Щракнете върху опцията Excel Add-ins.
• В диалоговия прозорец Добавки прегледайте и намерете записания от вас файл и щракнете върху OK.

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

Сега можете да използвате персонализираната функция във всички работни книги.

Запазване на функцията в лична работна книга за макроси

Работна книга за лични макроси е скрита работна книга във вашата система, която се отваря всеки път, когато отворите приложението Excel.

Това е място, където можете да съхранявате макро кодове и след това да имате достъп до тези макроси от всяка работна книга. Това е чудесно място за съхранение на тези макроси, които искате да използвате често.

По подразбиране няма лична работна книга за макроси във вашия Excel. Трябва да го създадете, като запишете макрос и го запазите в личната работна книга за макроси.

Можете да намерите подробните стъпки за това как да създавате и запазвате макроси в личната работна книга за макроси тук.

Позоваване на функцията от друга работна книга

Докато първите два метода (създаване на добавка и използване на лична работна книга за макроси) ще работят във всички ситуации, ако искате да се позовавате на функцията от друга работна книга, тази работна книга трябва да бъде отворена.

Да предположим, че имате работна книга с името „Работна книга с формула 'и има функцията с името „GetNumeric '.

За да използвате тази функция в друга работна книга (докато Работна книга с формула е отворен), можете да използвате формулата по -долу:

= „Работна книга с формула“! GetNumeric (A1)

Горната формула ще използва дефинираната от потребителя функция в Работна книга с формула файл и ще ви даде резултата.

Имайте предвид, че тъй като името на работната книга има интервали, трябва да го оградите в единични кавички.

Използване на инструкция за функция за изход VBA

Ако искате да излезете от функция, докато кодът се изпълнява, можете да направите това, като използвате оператора „Exit Function“.

Кодът по -долу ще извлече първите три цифрови знака от буквено -цифров текстов низ. Веднага щом получи трите знака, функцията завършва и връща резултата.

Функция GetNumericFirstThree (CellRef като диапазон) като дълъг Dim StringLength като Integer StringLength = Len (CellRef) For i = 1 To StringLength Ако J = 3 Тогава излезте от функцията Ако еNumeric (Mid (CellRef, i, 1)) Тогава J = J + 1 Резултат = Резултат и в средата (CellRef, i, 1) GetNumericFirstThree = Резултат Край Ако Следващ i Край Функция

Горната функция проверява броя на символите, които са цифрови, и когато получи 3 цифрови знака, излиза от функцията в следващия цикъл.

Отстраняване на грешки в дефинирана от потребителя функция

Има няколко техники, които можете да използвате, докато отстранявате грешки в дефинирана от потребителя функция във VBA:

Отстраняване на грешки в персонализирана функция чрез полето за съобщения

Използвайте функцията MsgBox, за да покажете поле за съобщение с конкретна стойност.

Стойността, която показвате, може да се основава на това, което искате да тествате. Например, ако искате да проверите дали кодът се изпълнява или не, всяко съобщение ще работи и ако искате да проверите дали циклите работят или не, можете да покажете конкретна стойност или брояча на цикъла.

Отстраняване на грешки в персонализирана функция чрез задаване на точката на прекъсване

Задайте точка на прекъсване, за да можете да преминете през всеки ред един по един. За да зададете точка на прекъсване, изберете реда, където искате, и натиснете F9, или щракнете върху сивата вертикална област, която е вляво от кодовите редове. Всеки от тези методи би вмъкнал точка на прекъсване (ще видите червена точка в сивата зона).

След като сте задали точката на прекъсване и изпълните функцията, тя отива до линията на точката на прекъсване и след това спира. Сега можете да преминете през кода с помощта на клавиша F8. Натискането на F8 веднъж се придвижва до следващия ред в кода.

Отстраняване на грешки в персонализирана функция чрез Debug.Print в кода

Можете да използвате оператора Debug.Print във вашия код, за да получите стойностите на посочените променливи/аргументи в непосредствения прозорец.

Например в кода по -долу използвах Debug.Print, за да получа стойността на две променливи - „j“ и „Резултат“

Функция GetNumericFirstThree (CellRef като диапазон) като дълъг Dim StringLength като Integer StringLength = Len (CellRef) For i = 1 To StringLength Ако J = 3 Тогава излезте от функцията Ако еNumeric (Mid (CellRef, i, 1)) Тогава J = J + 1 Резултат = Резултат и в средата (CellRef, i, 1) Debug.Print J, Резултат GetNumericFirstThree = Резултат Край Ако Следващ i Крайна функция

Когато този код се изпълнява, той показва следното в непосредствения прозорец.

Вградени функции на Excel Vs. VBA дефинирана от потребителя функция

Има няколко силни предимства от използването на вградени функции на Excel пред персонализираните функции, създадени във VBA.

• Вградените функции са много по -бързи от функциите на VBA.
• Когато създавате отчет/табло за управление чрез функции VBA и го изпращате на клиент/колега, те няма да се притесняват дали макросите са активирани или не. В някои случаи клиентите/клиентите се плашат, като видят предупреждение в жълтата лента (която просто ги моли да разрешат макроси).
• С вградените функции на Excel не е нужно да се притеснявате за разширенията на файлове. Ако имате макроси или дефинирани от потребителя функции в работната книга, трябва да ги запишете в .xlsm.

Въпреки че има много сериозни причини да използвате вградените функции на Excel, в някои случаи е по-добре да използвате дефинирана от потребителя функция.

• По-добре е да използвате дефинирана от потребителя функция, ако вградената формула е огромна и сложна. Това става още по -актуално, когато имате нужда от някой друг, който да актуализира формулите. Например, ако имате огромна формула, съставена от много различни функции, дори промяната на препратка към клетка може да бъде досадна и склонна към грешки. Вместо това можете да създадете персонализирана функция, която приема само един или два аргумента и върши цялата тежка работа.
• Когато трябва да свършите нещо, което не може да се направи от вградените функции на Excel. Пример за това може да бъде, когато искате да извлечете всички цифрови знаци от низ. В такива случаи ползата от използването на дефинирана от потребителя функция gar надвишава нейните негативи.

Къде да поставите VBA кода за дефинирана от потребителя функция

Когато създавате персонализирана функция, трябва да поставите кода в кодовия прозорец на работната книга, в която искате функцията.

По -долу са стъпките за поставяне на кода за функцията „GetNumeric“ в работната книга.

1. Отидете в раздела Разработчик.
2. Щракнете върху опцията Visual Basic. Това ще отвори VB редактора в задната част.
3. В прозореца Project Explorer в редактора на VB щракнете с десния бутон върху всеки обект за работната книга, в който искате да вмъкнете кода. Ако не виждате Project Explorer, отидете в раздела View и щракнете върху Project Explorer.
4. Отидете на Вмъкване и щракнете върху Модул. Това ще вмъкне обект на модул за вашата работна книга.
5. Копирайте и поставете кода в прозореца на модула.

Може да харесате и следните уроци по Excel VBA:

• Работа с клетки и диапазони в Excel VBA.
• Работа с работни листове в Excel VBA.
• Работа с работни книги с помощта на VBA.
• Как да използвате цикли в Excel VBA.
• Събития на Excel VBA - Лесно (и пълно) ръководство
• Използване на IF след това Други изявления във VBA.
• Как да запишете макрос в Excel.
• Как да стартирате макрос в Excel.
• Как да сортирате данни в Excel с помощта на VBA (Ръководство стъпка по стъпка).
• Excel VBA InStr функция - обяснено с примери.