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

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

Какво е функция за масив?

Функциите на масива са функциите, които връщат масив, когато се използват. Тези функции се използват с комбинации от клавиши CTRL+SHIFT+ENTER и затова предпочитаме да извикваме функция или формули от масива като CSE функция и формули.

Функцията за масив Excel е често формула за многоклетъчен масив. Един пример е функцията TRANSPOSE.

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

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

Кодът ще изглежда така.

Функция ThreeEven () Като Integer () 'дефинирайте масив Dim числа (2) As Integer' Присвояване на стойности на номера на масива (0) = 0 числа (1) = 2 числа (2) = 4 'връщане на стойности ThreeEven = числа Крайна функция 

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

Виждате, че първо избираме три клетки (хоризонтално, за вертикално трябва да използваме двуизмерен масив. Покриваме го по-долу.). След това започваме да пишем нашата формула. След това натискаме CTRL+SHIFT+ENTER. Това запълва избраните клетки със стойностите на масива.

Забележка:

• На практика няма да знаете колко клетки ще ви трябват. В този случай винаги избирайте повече клетки от очакваната дължина на масива. Функцията ще запълни клетките с масив и допълнителните клетки ще покажат грешка #N/A.
• По подразбиране тази функция за масив връща стойности в хоризонтален масив. Ако се опитате да изберете вертикални клетки, всички клетки ще показват само първата стойност на масива.

Как работи?

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

Функция functionName (променливи) Като returnType () dim resultArray (length) като dataType 'Присвояване на стойности на масива тук functionName = resultArray Крайна функция 

Декларацията на функцията трябва да бъде както е дефинирана по -горе. Това обяви, че това е функция на масив.
Докато го използвате на работния лист, трябва да използвате комбинацията от клавиши CTRL+SHIFT+ENTER. В противен случай той ще върне само първата стойност на масива.

Функция VBA Array за връщане на вертикален масив

За да накарате функцията на UDF масива да работи вертикално, не е нужно да правите много. Просто декларирайте масивите като двуизмерен масив. След това в първото измерение добавете стойностите и оставете другото измерение празно. Ето как го правите:

Функция ThreeEven () Като Integer () 'дефинирайте числата Dim Dim (2,0) As Integer' Задайте стойности на числата на масива (0,0) = 0 числа (1,0) = 2 числа (2,0) = 4 ' връща стойности ThreeEven = числа Крайна функция 

Ето как го използвате в работния лист.

Функция UDF Array с аргументи в Excel

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

Пример Добавете „-done“ към всяка стойност в диапазона

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

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

Функция CONCATDone (rng като диапазон) като вариант () Dim resultArr () като вариант „Създаване на колекция Dim col като нова колекция“ Добавяне на стойности към колекция При грешка Възобновяване Следваща за всеки v В rng col. Добавяне v Следваща при грешка GoTo 0 “ завършване на операцията за всяка стойност и добавянето им към масив ReDim resultArr (col.Count - 1, 0) For i = 0 To col.Count - 1 resultArr (i, 0) = col (i + 1) & "-done" Напред CONCATDone = Функция EndArr End 

Обяснение:

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

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

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

Щракнете върху връзката по -долу, за да изтеглите работния файл:

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

Масиви в Excel Formul | Научете какви масиви има в Excel.

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

Използване на функция, дефинирана от потребителя (UDF) от друга работна книга, използваща VBA в Microsoft Excel | Използвайте дефинираната от потребителя функция в друга работна книга на Excel

Връща стойности на грешки от дефинирани от потребителя функции, използвайки VBA в Microsoft Excel | Научете как можете да върнете стойности за грешка от дефинирана от потребителя функция

Популярни статии:

50 преки пътища в Excel за повишаване на вашата производителност

Функцията VLOOKUP в Excel

COUNTIF в Excel 2016

Как да използвате функцията SUMIF в Excel