Создание VBA функция возвращала массив

Posted on: 2020年10月22日 2020年11月12日
Tags: custom-functions-in-vba

Как следует из названия, мы узнаем, как создать пользовательскую функцию в Excel, которая возвращает массив. Мы уже узнали, как связать: / vba-user-defined-functions [создать пользовательскую функцию в VBA] `. Итак, не теряя времени, приступим к уроку.

Что такое функция массива?

Функции массива — это функции, которые при использовании возвращают массив. Эти функции используются с комбинациями клавиш CTRL + SHIFT + ENTER, поэтому мы предпочитаем вызывать функцию массива или формулы как функцию CSE и формулы.

Функция массива excel часто представляет собой формулы массива с несколькими ячейками. Одним из примеров является link: / Mathematical-functions-excel-transpose-function [TRANSPOSE function].

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

Итак, сценарий таков, что я просто хочу вернуть первые 3 четных числа с помощью функции ThreeEven ().

Код будет выглядеть так.

Function ThreeEven() As Integer()

'define array

Dim numbers(2) As Integer

'Assign values to array

numbers(0) = 0

numbers(1) = 2

numbers(2) = 4

'return values

ThreeEven = numbers

End Function

Давайте воспользуемся этой функцией на рабочем листе.

You can see that, we first select three cells (horizontally, for vertical we have to use two-dimensional array. We have it covered below.). Then we start writing our formula. Then we hit CTRL+SHIFT+ENTER. This fills the selected cells with the array values.

Примечание:

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

Если вы попытаетесь выбрать вертикальные ячейки, во всех ячейках будет отображаться только первое значение массива.

Как это работает?

Чтобы создать функцию массива, вы должны следовать этому синтаксису.

Function functionName(variables) As returnType()

dim resultArray(length) as dataType

'Assign values to array here

functionName =resultArray

End Function

Объявление функции должно быть таким, как определено выше. Это объявило, что это функция массива.

При использовании его на листе вы должны использовать комбинацию клавиш CTRL + SHIFT + ENTER. В противном случае он вернет только первое значение массива.

Функция массива VBA для возврата вертикального массива

Чтобы ваша функция массива UDF работала вертикально, вам не нужно много делать. Просто объявите массивы как двумерный массив. Затем в первом измерении добавьте значения и оставьте другое измерение пустым. Вот как вы это делаете:

Function ThreeEven() As Integer()

'define array

Dim numbers(2,0) As Integer

'Assign values to array

numbers(0,0) = 0

numbers(1,0) = 2

numbers(2,0) = 4

'return values

ThreeEven = numbers

End Function

Вот как вы используете его на листе.

===

UDF Array Function with Arguments in Excel* In the above examples, we simply printed sum static values on the sheet.

Допустим, мы хотим, чтобы наша функция принимала аргумент диапазона, выполняла над ними некоторые операции и возвращала результирующий массив.

Пример Добавить «-done» к каждому значению в диапазоне

Теперь я знаю, что это можно сделать легко, но просто чтобы показать вам, как можно использовать определяемые пользователем функции массива VBA для решения проблем.

Итак, здесь мне нужна функция массива, которая принимает диапазон в качестве аргумента и добавляет «-done» к каждому значению в диапазоне. Это легко сделать с помощью link: / tips-concatenate-formula-in-microsoft-excel [функция конкатенации], но здесь мы будем использовать функцию массива.

Function CONCATDone(rng As Range) As Variant()

Dim resulArr() As Variant

'Create a collection

Dim col As New Collection

'Adding values to collection

On Error Resume Next

For Each v In rng

col.Add v

Next

On Error GoTo 0

'completing operation on each value and adding them to Array

ReDim resulArr(col.Count - 1, 0)

For i = 0 To col.Count - 1

resulArr(i, 0) = col(i + 1) & "-done"

Next

CONCATDone = resulArr

End Function

Пояснение:

Вышеупомянутая функция примет диапазон в качестве аргумента и добавит «-done» к каждому значению в диапазоне.

Вы можете видеть, что мы использовали здесь коллекцию VBA для хранения значений массива, а затем мы выполнили нашу операцию с каждым значением и добавили их обратно в двумерный массив.

Так что да, ребята, вот как вы можете создать пользовательскую функцию массива VBA, которая может возвращать массив. Надеюсь, это было достаточно объяснительным. Если у вас есть какие-либо вопросы относительно этой статьи, оставьте их в разделе комментариев ниже.

Щелкните ссылку ниже, чтобы загрузить рабочий файл:

`link: /wp-content-uploads-2019-12-Create-VBA-Function-to-Return-Array-1.xls [__ Создать функцию VBA для возврата массива]

Статьи по теме:

link: / vba-user-defined-function [Как создать пользовательскую функцию с помощью VBA] | Узнайте, как создавать пользовательские функции в Excel `link: / custom-functions-userdefined-functions-from-other-workbooks-using-vba-in-microsoft-excel [Использование пользовательской функции (UDF) из другой книги с использованием VBA в Microsoft Excel] `| Используйте пользовательскую функцию в другой книге Excel `link: / custom-functions-return-error-values-from-user-defined-functions-using-vba-in-microsoft-excel [Возврат значений ошибок из пользовательских функции с использованием VBA в Microsoft Excel] `| Узнайте, как можно вернуть значения ошибок из пользовательской функции