Функция VBA, возвращающая в таблицу массив

[Alev]

В Excel есть встроенные матричные функции (например, MINV), которые возвращают в таблицу не одно значение, а массив.
Чтобы вывзать такую функцию, нужно маркировать в таблице правильное число клеток, вписать в активную клетку формулу, а потом задействовать клавиши Control, Schift и Enter.

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

1) Как сделать, чтобы функция возвращала значения как в строку, так и в столбец (в соответствии с тем, что маркировано - строка или столбец)?
2) Что делать, если размер возвращаемого массива заранее неизвестен? Что маркировать?

[Дмитрий Щербаков(The_Prist)]

Немного теории: по умолчанию создаваемый одномерный массив в VBA - одномерный горизонтальный. И чтобы записать его в столбец, надо транспонировать. Например, так:

Код: (vb)

Range("A1:A10").value = Application.Transpose(Array)

но у этого метода есть недостатки: не работает со строками, длина которых более 255 символов. Все остальные символы в этом случае будут обрезаны. Плюс, массив для транспонирование не должен превышать 65536 строк. Поэтому лучше написать свою процедуру транспонирования и использовать её.
Если массив неизвестен - надо понимать, как он вообще получается. Как правило здесь достаточно функции UBound(Array).
Чтобы дать более конкретный ответ - от Вас нужен хотя бы пример функции и как(откуда) Вы её вызываете и используете, чтобы посоветовать что и как там определять.

[Alev]

Большое спасибо за помощь.
Только дело в том, что функция не знает, захочу я при ее вызове получить массив в виде строки, или в виде столбца. Эту информацию приходится сообщать функции в явном виде в качестве второго аргумента, что не очень элегантно.
Хотелось бы, чтобы функция каким-то образом сама различала, что было маркировано - строка или столбец.
Такую функцию я тоже запрограммировал, но при снятии маркировки она перестает правильно работать. Нужно, чтобы она запоминала маркировку, которая существовала на момент ее первого вызова.

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

Кстати: вы уверены, что команда Range("A1:A10").value = Application.Transpose(Array)  будет работать именно внутри функции? Мне кажестся, что эта команда возможна только в макросе (Sub).

[Дмитрий Щербаков(The_Prist)]

вы уверены, что команда Range("A1:A10").value = Application.Transpose(Array)  будет работать именно внутри функции?

Уверен более чем. Transpose - переворачивает массив из строки в столбец. Я привел лишь пример этой функции. Range("A1:A10").value использовалось для понимания синтаксиса.
А чтобы дать конкретный ответ - нужен текст функции и понимание как Вы её используете(с листа, вызовом из другой процедуры и т.д.). Я не могу рассказать как дать понять функции чтобы она что-то различала, если нет ни самой функции, ни понимания как она используется и вызывается.
Есть предположение, что здесь поможет Application.Caller. Можете почитать подробнее(ту часть, где как раз про использовании внутри функции): Кто вызвал функцию или процедуру?
Вы задаете вопрос без единой строки кода, а ответ планируете получить прям под свои нужды. Так не получится - я не умею угадывать что и как Вы хотите сделать.

[Alev]

Спасибо!
Кажется, я придумал, как выводить одномерный массив или в строку, или в столбец в зависимости от маркировки.
Вот текст пробной функции:

Function Test(Choice As Range)
'   Разворачивает элементы двумерного массива (Choice) в строку или столбец (Vektor)
Dim Vektor(), Element, N As Byte
N = 0
For Each Element In Choice
      N = N + 1
Next Element
ReDim Vektor(1 To N, 1 To N)
N = 0
For Each Element In Choice
        N = N + 1
        Vektor(N, 1) = Element.Value
        Vektor(1, N) = Element.Value
Next Element
Test = Vektor
End Function

Конечно, программа не слишком экономна: вместо одномерного массива пришлось декларировать двумерный (матрицу), при этом используется только первая строка и первый столбец матрицы.
Недостаточно элегантно на мой вкус: перерасход ресурсов.

[Дмитрий Щербаков(The_Prist)]

1. Коды оформляйте тегами VBCode. п.п. 4.25 Правил форума. Обратите внимание как они выделены у меня и как у Вас.
2. Я не просто так просил текст функции, а оптимально и файл с тем, как Вы её применяете. Но Вы упорно этого не пишите и не делаете. Вы с листа функцию вызываете? Т.е. записываете её как стандартную? Или нет? Если да - то я выше дал ссылку на статью с подсказкой - там все можно определить.
Мне вот так же непонятно в каких случаях надо транспонировать переданный массив, а в каких нет. Поэтому ориентир могу делать только на то, сколько ячеек на листе выделено перед вводом формулы массива и сколько при этом передано:

Код: (vb)

Function Test(Choice As Range)
    '   Разворачивает элементы двумерного массива (Choice) в строку или столбец (Vektor)
    Dim Vektor
    Dim rSource As Range 'массив ячеек, выделенный на листе при вводе функции
    Dim lSourceRowsCount&, lSourceColumnsCount&, lRowsCount&, lColumnsCount&
    'определяем сколько строк и столбцов выделено на листе для ввода функции
    Set rSource = Application.Caller
    lSourceRowsCount = rSource.Rows.Count
    lSourceColumnsCount = rSource.Columns.Count
    'определяем сколько строк и столбцов в диапазоне переданном функции
    lRowsCount = Choice.Rows.Count
    lColumnsCount = Choice.Columns.Count
    
    'определяем надо ли разворачивать массив или нет
    If lSourceRowsCount = 1 And lRowsCount > 1 Or lSourceColumnsCount = 1 And lColumnsCount > 1 Then
        Vektor = Application.Transpose(Choice.Value)
    Else
        Vektor = Choice.Value
    End If
    Test = Vektor
End Function

[Alev]

Супер! Огромное спасибо за подсказку.
Хотя ваша функция YourTest и не работает, как надо, в ней содержатся ценные указания и подсказки, которые я использовал для улучшения моей функции MyTest.

Текст функции:

Код: (vb)

Function MyTest(Choice As Range)
Dim Vektor(), Element, Z As Byte, S As Byte, N As Byte, Anz_Z As Byte
Dim Markierung As Range ' Массив ячеек, выделенный на листе при вводе функции
Set Markierung = Application.Caller
Anz_Z = Markierung.Rows.Count    ' Число строк в поле вызова функции
Z = Choice.Rows.Count
S = Choice.Columns.Count
N = Z * S
ReDim Vektor(1 To N)
N = 0
For Each Element In Choice
        N = N + 1
        Vektor(N) = Element.Value
Next Element
If Anz_Z = 1 Then
        MyTest = Vektor
Else
        MyTest = Application.Transpose(Vektor)
End If
End Function

Посылаю также и файл Test.xlsm

[Дмитрий Щербаков(The_Prist)]

Хотя ваша функция YourTest и не работает, как надо

что вполне логично, т.к. функция может работать как надо только при условии, что было известно как именно надо А этого я от Вас так и не узнал Все, что было озвучено - надо что-то где-то транспонировать. Все остальное пришлось тянуть из Вас и гадать. Ну главное, что все получилось в итоге.
Однако, если бы сразу было написано, что надо брать массив ячеек на входе и записывать исключительно в одну строку или столбец(в зависимости от того, что было выделено) - то и конкретный ответ был бы дан значительно раньше.
P.S. Без обид, но я бы рекомендовал ознакомиться с этой темой: Зачем темам давать осмысленное название?. Особое внимание на пару последних абзацев, которые больше не про название темы, а как раз про содержание стартового сообщения и описание проблемы.
P.P.S. А зачем Вы применяете Byte? Боитесь, что памяти ПК не хватит? Я бы на Вашем месте больше переживал за тот факт, что Byte выдаст ошибку, если элементов в итоговом массиве станет более 255. Вот на этой вот строке: N = Z * S. Или единоразово строк или столбцов будет выделено больше этого значения. Если уж так дорога память - используйте хотя бы Integer - те же 2 байта в памяти, а числа может хранить до 32767. Ну а 4-байтового Long хватит вообще на все случаи

[Alev]

А зачем Вы применяете Byte?..

Просто я привык экономить ресурсы. Число i редко превышает 250.
Вперед я дурень таков не буду.

Кстати: что означает значок & в вашей программе (Dim lSourceRowsCount&)?

[Дмитрий Щербаков(The_Prist)]

что означает значок & в вашей программе

Это краткое обозначение типа Long. Можете ознакомиться с правилами объявления переменных в статье: Что такое переменная и как правильно её объявить?