В языке программирования VBA имеется полезная функция под названием Array. Эта функция возвращает значение типа Variant, содержащее массив – набор значений. Если вы знакомы с формулами массивов в Excel, то разберетесь в использовании этой функции VBA. Формула массива вводится в ячейку с нажатием комбинации клавиш Ctrl+Shift+Enter (если ноутбук то, Ctrl+Shift+Fn+Enter). Excel автоматически обрамляет формулу в фигурные скобки, указывая, что это формула массива.
Важно отметить, что массив, возвращаемый функцией Array, отличается от обычного массива, состоящего из элементов типа Variant. Иными словами, массив типа Variant не является эквивалентом массива значений типа Variant.
Приведенная ниже пользовательская функция НАЗВАНИЯ_МЕСЯЦЕВ представляет собой пример использования функции VBA Array:
Эта функция возвращает горизонтальный массив, содержащий названия месяцев. Ее можно использовать для создания формулы массива в нескольких ячейках. Перед использованием убедитесь, что код функции введен в модуле VBA. Затем на листе Excel выделите необходимое количество ячеек (например, 12), введите формулу и нажмите Ctrl+Shift+Enter (если ноутбук то, Ctrl+Shift+Fn+Enter):
{=НАЗВАНИЯ_МЕСЯЦЕВ()}
Если нужен вертикальный массив названий месяцев, выделите соответствующий вертикальный диапазон и введите формулу, затем нажмите Ctrl+Shift+Enter (если ноутбук то, Ctrl+Shift+Fn+Enter):
{=TРАНСП(НАЗВАНИЯ_МЕСЯЦЕВ())}
Но давайте улучшим функцию.
В улучшенной версии функции можно использовать необязательный аргумент для выполнения различных задач. Если аргумент не указан, функция возвращает горизонтальный массив названий месяцев. Если аргумент меньше или равен 0, функция возвращает вертикальный массив. Если аргумент больше или равен 1, функция возвращает название месяца по указанному значению.
Давайте разберем построчно функцию НАЗВАНИЯ_МЕСЯЦЕВ:
- Function НАЗВАНИЯ_МЕСЯЦЕВ(Optional MIndex) - Определяет начало пользовательской функции с именем НАЗВАНИЯ_МЕСЯЦЕВ. Принимает один необязательный аргумент MIndex.
- Dim ВсеИмена As Variant - Объявляет переменную ВсеИмена типа Variant, которая будет использоваться для хранения массива названий месяцев.
- Dim НомерМесяца As Long - Объявляет переменную НомерМесяца типа Long, которая будет использоваться для хранения значения месяца.
- ВсеИмена = Array("Янв", "Фев", "Мар", "Апр", "Май", "Июн", "Июл", "Авг", "Сен", "Окт", "Ноя", "Дек") - Заполняет массив ВсеИмена названиями месяцев.
- If IsMissing(MIndex) Then - Проверяет, был ли передан аргумент MIndex. Если аргумент не передан, выполняется блок кода внутри If.
- НАЗВАНИЯ_МЕСЯЦЕВ = ВсеИмена - Возвращает горизонтальный массив названий месяцев, так как аргумент MIndex не был передан.
- Else - Если аргумент был передан, выполняется блок кода внутри Else.
- Select Case MIndex - Начинает блок оператора Select Case, который обрабатывает различные значения аргумента MIndex.
- Case Is >= 1 - Если MIndex больше или равен 1, выполняется блок кода внутри этого Case.
- НомерМесяца = ((MIndex - 1) Mod 12) - Определяет значение месяца, используя оператор Mod для циклического определения месяца в пределах 0-11.
- НАЗВАНИЯ_МЕСЯЦЕВ = ВсеИмена(НомерМесяца) - Возвращает название месяца, соответствующее значению НомерМесяца.
- Case Is <= 0 - Если MIndex меньше или равен 0, выполняется блок кода внутри этого Case.
- НАЗВАНИЯ_МЕСЯЦЕВ = Application.Transpose(ВсеИмена) - Возвращает вертикальный массив названий месяцев с использованием функции Transpose.
- End Select - Завершает блок оператора Select Case.
- End If - Завершает блок условия If.
- End Function - Завершает определение пользовательской функции.
Обратите внимание на использование функции VBA IsMissing для проверки отсутствия аргумента. В данном случае невозможно задать значение по умолчанию для отсутствующего аргумента в списке аргументов функции, так как значение по умолчанию определяется внутри самой функции. Функцию IsMissing можно использовать только в случае, если необязательный аргумент имеет тип Variant.
Усовершенствованная функция, представленная выше, использует необязательный аргумент для решения различных задач, так:
- Если аргумент не задан, функция возвращает горизонтальный массив названий месяцев.
- Если аргумент меньше или равен 0, функция возвращает вертикальный массив названий месяцев. Для преобразования массива используется функция Excel ТРАНСП ().
- Если аргумент больше или равен 1, функция возвращает название месяца, соответствующее значению аргумента.
Примечание. В этой процедуре используется оператор Mod для определения значения месяца. Оператор Mod возвращает остаток от деления первого операнда на второй. Учтите, что нумерация индексов в массиве ВсеИмена начинается с нуля, а их диапазон простирается от 0 до 11. Поэтому из аргумента функции вычитается единица. Таким образом, аргумент 13 возвращает 0 (январь), аргумент 24 – 11 (декабрь).
Примеры использования функции:
- Диапазон A1:L1 содержит следующую формулу массива. Выделите диапазон A1:L1, введите формулу и нажмите Ctrl+Shift+Enter (если ноутбук то, Ctrl+Shift+Fn+Enter):
{=НАЗВАНИЯ_МЕСЯЦЕВ()} (см. скриншот выше)
- В диапазоне G1:G12 находятся целые числа от 1 до 12. Ячейка H1 содержит обычную формулу, которая скопирована в 11 следующих за ней ячеек:
=НАЗВАНИЯ_МЕСЯЦЕВ(G1)
- В диапазоне H1:H12 располагается следующая формула массива:
{=НАЗВАНИЯ_МЕСЯЦЕВ(-1)}
- Ячейка G1 содержит следующую формулу (не массив):
=НАЗВАНИЯ_МЕСЯЦЕВ(12)
Примечание. Для ввода формулы массива используйте комбинацию клавиш Ctrl+Shift+Enter (если ноутбук то, Ctrl+Shift+Fn+Enter), НО НЕ ВВОДИТЕ ФИГУРНЫЕ СКОБКИ ВРУЧНУЮ.
И ещё
Нижняя граница массива, созданного с использованием функции Array, определяется нижней границей, указанной в директиве Option Base в верхней части модуля. Если директива Option Base отсутствует, то по умолчанию нижняя граница равна 0, но если Option Base = 1, то нижняя граница равна 1.
