Использование массива возвращаемого функцией

МалышОК

Гость

03.12.2008 11:59:12

Суть проблемы: функция VBA формирует большой массив значений (это может быть результат сортировки, отбора уникальных значений и т.п). Как можно было бы использовать его результаты в N-ом количестве ячеек без повторных вызовов этой функции?
Вариант формирования отдельного списка на основе данных функции с использованием Worksheet_Change меня морально-эстетически напрягает.
Идеальный вариант, реализовать который мне не удается - функция вычисляется в ячейке, к которой обращаются все остальные ячейки. Но Excel на горА выдает в ячейке только первое значение массива. Можно ли это побороть?

Артем

Гость

03.12.2008 12:06:10

не очень понимаю, что значит "на гора", вот такой пример использования массива
mArr = range("A1:A5").value'формирование массива
for i = lbound(mArr) to ubound(mArr)'перебор всех элементов массива
debug.print mArr(i)'выдача на "гора" всех элементов в окне отладки
next

Гость

03.12.2008 13:04:55

Если функция возвращает массив то и вводиться она должна как функция массива
Function Test() As Variant
Dim V() As Variant
Dim N As Long
Dim R As Long
Dim C As Long
ReDim V(1 To 3, 1 To 4)
For R = 1 To 3
For C = 1 To 4
N = N + 1
V(R, C) = N
Next C
Next R
Test = V
End Function

выделяем диапазон 3х4 ячеек, вводим =test(), жмём ctrl-shift-enter,
видим на листе наш массив 3х4

Guest Гость	#4 03.12.2008 13:49:58 Так? Функция, возвращающая результатом массив - ttt Результат от ttt в имени CalcTTT. ИНДЕКС обращается к результату от ttt используя имя. Прикрепленные файлы post_41201.xls (16 КБ)

Гость

03.12.2008 17:05:15

если есть желание в функции контролировать размерность диапазона которуму пользователь назначил функцию возвращающую массив, то можно воспользоваться
Application.Caller возвращающий как раз этот диапазон ячеек.
Но без дополнительного контроля такую функцию можно будет использовать только как функцию листа.
Function Test() As Variant
Dim V() As Variant
Dim N As Long
Dim R As Long
Dim C As Long
ReDim V(1 To 3, 1 To 4)
For R = 1 To 3
For C = 1 To 4
N = N + 1
V(R, C) = N
Next C
Next R
If Application.Caller.Rows.Count < 3 Then V(1, 1) = "мало строк, хочу 3х4"
Test = V
End Function

МалышОК

Гость

03.12.2008 20:42:20

Судя по ответам вопрос непонят.
Допустим есть функция типа:
Function Test() As Variant
Dim ms, i
ReDim ms(6)
For i = LBound(ms) To UBound(ms)
ms(i) = i + 9
Next
Test = ms
End Function
Результат ее работы можно получить копируя Ctrl+Shift+Enter в ареал 1х7. Для исключаения ошибок это лучше делать процедурой через FormulaArray и лучше на другом листе, чтобы не пугать юзера. На а как быть с массивом 20000х400? Да еще и многомерным? Можно процедурой и на связанные блоки разбить и по листам рассовать - но все это как-то маразмом отдает. Такие действия оправданы для более-менее долговременного хранения данных, а не для обработки результатов функции.
Самое интересное, что при размещении формулы даже в одиночной ячейке ёксель бодро формирует весь массив полностью, только делится этой информацией не хочет, выдавая только первое значение.
В этом и проблема: как воспользоваться массивом созданным в VBA без формирования ареала ее значений?

Guest

Гость

03.12.2008 21:02:36

{quote}{login=МалышОК}{date=03.12.2008 08:42}{thema=RE: Артём & DL}{post}В этом и проблема: как воспользоваться массивом созданным в VBA без формирования ареала ее значений?{/post}{/quote}
Чем не подходит предложенное решение здесь?: http://www.planetaexcel.ru/forum_upload/post_41201.xls

Если же проблема в том, что не хочется запускать пересчет функции (и перестроение всего большого массива с самого начала) от каждого обращения к ней на листе, то нужно выделить задачу формирования массива в процедуру или функцию (или часть функции), которая бы запускалась только по мере необходимости. Т.е. если массив еще не сформирован - функция вызывает процедуру (или другую фунцию или часть ее), которая формирует массив в переменной Public. А если переформировывать массив ненужно, то функция только возвращает значение нужного элемнта из массива....

Guest

Гость

04.12.2008 10:56:13

{quote}{login=}{date=03.12.2008 09:02}{thema=Re: RE: Артём & DL}{post}{quote}
Чем не подходит предложенное решение здесь?: http://www.planetaexcel.ru/forum_upload/post_41201.xls

Если же проблема в том, что не хочется запускать пересчет функции (и перестроение всего большого массива с самого начала) от каждого обращения к ней на листе, то нужно выделить задачу формирования массива в процедуру или функцию (или часть функции), которая бы запускалась только по мере необходимости. Т.е. если массив еще не сформирован - функция вызывает процедуру (или другую фунцию или часть ее), которая формирует массив в переменной Public. А если переформировывать массив ненужно, то функция только возвращает значение нужного элемнта из массива....{/post}{/quote}

Собственно, на сей момент функция приблизительно так и реализована как Вы предлагаете. Но именно от физического построения Range-массива из-за его величины я и хочу уйти, т.к. дополнительные 8-10 Sheet мне не особенно нужны.

При этом где-то в VBA болтается Variant-массив созданный моей функцией.
Достучаться до него без создания Range-массива, в принципе, возможно. Например, создавая имена типа:
Names.Add Name:="вася", RefersToR1C1:="={1,2,3}"
,где {1,2,3} можно менять на массив таких же имен, чтобы обойти ограничения String типа у RefersTo.

Этот вариант значительно шустрее построения Range-массива и в данный момент я его заканчиваю. Но он тоже, по моим понятиям, какой-то промежуточный, т.к. из-за размера массива его приходится разбивать на кучу имен.

Может ёксель имеет какие-то недокументированные возможности ХРАНЕНИЯ Variant-массива для последующего доступа ?

Гость

04.12.2008 12:03:22

1) Массив 20000*400 находящийся в памяти для быстрой обработки данных это уже не ексель а либо математические пакеты, либо базы данных
2)Собственно говоря все способы хранения данных которые я знаю были перечислены: лист, имя, public. Осталось ёще рассмотреть вопрос присоединяемой базы данных, но здесь не силён ...

И вобще пока не озвучена предметная область сложно что подсказать. Какие операции над данным массивом и как часто будут производиться (включая сюда и операции изменения элементов массива)?

Почему вместо function() as variant (20000*400)
нельзя использовать function(массивi,массивj) для генерации только тех элементов массива которые необходимы?

ps ну и нужно ждать советов от "тяжёлой артиллерии" данного форума.

Guest Гость	#10 04.12.2008 12:46:31 Да, собственно... забыли о главном... о вопросе "Зачем?"... Если предложенные варианты не подходят, без понимания "Зачем?", трудно продолжать общение, т.к. получается игра "попади пальцев в небо"... Зачем возникла необходимость такого большого массива? Просто чтобы работать именно с массивом вместо данных листа... ради чего? Скорость? На сколько я знаю, незадокументированных способов хранения/организации массивов в VBA нет. А кроме предложенных вариантов остались только коллекции (если дело в скорости, то это явно не подойдет) и Recordset (а вот над этим можно подумать). Вобщем, зачем?

Читают тему