Добрый день! Пишу UDF в Excel 2007. Нужна функция, которая (1) имеет на входе Range, (2) изменяет его размер, (3) изменяет значение отдельных элементов, (4) выдает Rangе. Пункт 4 очень важен, чтобы выдавал именно Range, а не Array, т.к. в дальнейшем функции подцепляют именно Range из данной.
Составил код (смысловой нагрузки не несет): ****************************** [vba]
Код
Function RangeTest(Structure As Range) As Variant
i = 3 Dim temp1 As Range Dim temp2 As Range Set temp1 = Structure.Resize(i, 3)
Dim arr1() ReDim arr1(1 To i, 1 To 3) As Variant arr1 = temp1 arr1(2, 2) = 100 temp2.Value = arr1
RangeTest = temp1
End Function
[/vba] ******************************
Но он не работает. Варианты с использованием temp2.Value тоже не проходят.
Помогите разобраться с проблемой, пожалуйста.
Добрый день! Пишу UDF в Excel 2007. Нужна функция, которая (1) имеет на входе Range, (2) изменяет его размер, (3) изменяет значение отдельных элементов, (4) выдает Rangе. Пункт 4 очень важен, чтобы выдавал именно Range, а не Array, т.к. в дальнейшем функции подцепляют именно Range из данной.
Составил код (смысловой нагрузки не несет): ****************************** [vba]
Код
Function RangeTest(Structure As Range) As Variant
i = 3 Dim temp1 As Range Dim temp2 As Range Set temp1 = Structure.Resize(i, 3)
Dim arr1() ReDim arr1(1 To i, 1 To 3) As Variant arr1 = temp1 arr1(2, 2) = 100 temp2.Value = arr1
RangeTest = temp1
End Function
[/vba] ******************************
Но он не работает. Варианты с использованием temp2.Value тоже не проходят.
1. Как может не работать код, который не несёт смысловой нагрузки? 2. Что конкретно не работает и в чём это выражается? Результаты не те? Тогда что ожидали и что получили? Ошибку выдаёт? Тогда какую и на каком операторе? 3. Где размещены данные и код? Если в разных модулях, то при обращении к диапазону для определённости лучше ещё и лист указывать (он почему-то не всегда автоматом определяется по аргументу-диапазону). Т.е. в Вашем случае вместо [vba]
Код
Set temp1 = Structure.Resize(i, 3)
[/vba] лучше писать [vba]
Код
Set temp1 = Parent(Structure).Structure.Resize(i, 3)
[/vba] 4. Замените [vba]
Код
ReDim arr1(1 To i, 1 To 3) As Variant arr1 = temp1
[/vba] на [vba]
Код
arr1 = temp1.Value
[/vba] 5. Если Вы хотите, чтобы функция вернула диапазон, то почему у Вас определено [vba]
Код
Function RangeTest(Structure As Range) As Variant
[/vba] а не [vba]
Код
Function RangeTest(Structure As Range) As Range
[/vba]
1. Как может не работать код, который не несёт смысловой нагрузки? 2. Что конкретно не работает и в чём это выражается? Результаты не те? Тогда что ожидали и что получили? Ошибку выдаёт? Тогда какую и на каком операторе? 3. Где размещены данные и код? Если в разных модулях, то при обращении к диапазону для определённости лучше ещё и лист указывать (он почему-то не всегда автоматом определяется по аргументу-диапазону). Т.е. в Вашем случае вместо [vba]
Код
Set temp1 = Structure.Resize(i, 3)
[/vba] лучше писать [vba]
Код
Set temp1 = Parent(Structure).Structure.Resize(i, 3)
[/vba] 4. Замените [vba]
Код
ReDim arr1(1 To i, 1 To 3) As Variant arr1 = temp1
[/vba] на [vba]
Код
arr1 = temp1.Value
[/vba] 5. Если Вы хотите, чтобы функция вернула диапазон, то почему у Вас определено [vba]
Mike, что-то как-то мудрёно всё... Предлагаю вот так: [vba]
Код
Function RangeTest(Structure As Range) As Range
Dim i As Integer Dim arr1 As Variant
i = 3 Set RangeTest = Structure.Resize(i, 3)
'и всё! возвращаемый диапазон определен! и вычисления ниже 'никак на его определение уже НЕ ВЛИЯЮТ! 'ведь Range - это не набор значений (для этого есть массивы), 'а набор ячеек рабочего листа (почувствуйте разницу!)
Mike, что-то как-то мудрёно всё... Предлагаю вот так: [vba]
Код
Function RangeTest(Structure As Range) As Range
Dim i As Integer Dim arr1 As Variant
i = 3 Set RangeTest = Structure.Resize(i, 3)
'и всё! возвращаемый диапазон определен! и вычисления ниже 'никак на его определение уже НЕ ВЛИЯЮТ! 'ведь Range - это не набор значений (для этого есть массивы), 'а набор ячеек рабочего листа (почувствуйте разницу!)
Спасибо за комментарии! Уточню вопрос. Есть Range в Excel, задается через Name и условно называется Structure. Изначально обращался к этому Range для вычислений на основе элементов Range, например,
[vba]
Код
Function NumberofRows(Structure As Range) As Variant i = 2 Set NumberofRows= Structure.Rows.Count End Function
[/vba]
где вычисляется количество строк в начальном Range. Используются и другие функции, но все берут Structure As Range в качестве аргумента.
Далее - появилась необходимость изменить начальный Range: сократить его и изменить значения отдельных элементов. При этом делать на spreadsheet'е новый Range и называть его, скажем - Structure2, не надо. Предполагается, что будет использована функция вида NumberofRows(Rangetest(Structure)) или SumOfCells(Rangetest(Structure)). Для этого создана функция Rangetest.
Сейчас функция имеет вид (после работы над ошибками:)
[vba]
Код
Function Rangetest(Structure As Range) As Range Dim i As Long i = 3 Set Rangetest= Structure.Resize(i, 3) End Function
[/vba]
То есть сейчас функция Rangetest меняет размер Range. Более того функция NumberofRows(Rangetest(Structure)) выдает 3, т.е. количество строк в новом Range, как и должно быть.
Однако если изменять значения в новом Range, например, так
[vba]
Код
Function Rangetest(Structure As Range) As Range Dim i As Long i = 3 Set Rangetest= Structure.Resize(i, 3) Rangetest.Cells(2.2).Value = 100 End Function
[/vba]
то выдается ошибка #VALUE!.
Собственно вопрос: какой вид должна иметь функция Rangetest, чтобы корректно работала NumberofRows(Rangetest(Structure))? Важно: функция Rangetest должна не только менять размер начального Range, но и значения отдельных элементов.
Комментарии: 1) 2) постарался описать выше 3) весь код - в одном модуле, данные - на spreadsheet. Данным присвоено значание через Name. 4) как писал выше, элемент .Value вызывает ошибку. 5) исправил, спасибо
Спасибо за комментарии! Уточню вопрос. Есть Range в Excel, задается через Name и условно называется Structure. Изначально обращался к этому Range для вычислений на основе элементов Range, например,
[vba]
Код
Function NumberofRows(Structure As Range) As Variant i = 2 Set NumberofRows= Structure.Rows.Count End Function
[/vba]
где вычисляется количество строк в начальном Range. Используются и другие функции, но все берут Structure As Range в качестве аргумента.
Далее - появилась необходимость изменить начальный Range: сократить его и изменить значения отдельных элементов. При этом делать на spreadsheet'е новый Range и называть его, скажем - Structure2, не надо. Предполагается, что будет использована функция вида NumberofRows(Rangetest(Structure)) или SumOfCells(Rangetest(Structure)). Для этого создана функция Rangetest.
Сейчас функция имеет вид (после работы над ошибками:)
[vba]
Код
Function Rangetest(Structure As Range) As Range Dim i As Long i = 3 Set Rangetest= Structure.Resize(i, 3) End Function
[/vba]
То есть сейчас функция Rangetest меняет размер Range. Более того функция NumberofRows(Rangetest(Structure)) выдает 3, т.е. количество строк в новом Range, как и должно быть.
Однако если изменять значения в новом Range, например, так
[vba]
Код
Function Rangetest(Structure As Range) As Range Dim i As Long i = 3 Set Rangetest= Structure.Resize(i, 3) Rangetest.Cells(2.2).Value = 100 End Function
[/vba]
то выдается ошибка #VALUE!.
Собственно вопрос: какой вид должна иметь функция Rangetest, чтобы корректно работала NumberofRows(Rangetest(Structure))? Важно: функция Rangetest должна не только менять размер начального Range, но и значения отдельных элементов.
Комментарии: 1) 2) постарался описать выше 3) весь код - в одном модуле, данные - на spreadsheet. Данным присвоено значание через Name. 4) как писал выше, элемент .Value вызывает ошибку. 5) исправил, спасибоMike
Просматривал пост, нашел ошибку. Разумеется, функция NumberofRows не имеет Set и выглядит так:
[vba]
Код
Function NumberofRows(Structure As Range) As Variant i = 2 NumberofRows= Structure.Rows.Count End Function
[/vba]
Gustav, понимаю ваше замечение относительно Range и Array. Думаю, именно в этом и проблема. Ваш код выдает у меня ошибку #VALUE!. Кроется ошибка во второй части.
Без этого блока менятся размер Range. С этим блоком - ошибка.
Просматривал пост, нашел ошибку. Разумеется, функция NumberofRows не имеет Set и выглядит так:
[vba]
Код
Function NumberofRows(Structure As Range) As Variant i = 2 NumberofRows= Structure.Rows.Count End Function
[/vba]
Gustav, понимаю ваше замечение относительно Range и Array. Думаю, именно в этом и проблема. Ваш код выдает у меня ошибку #VALUE!. Кроется ошибка во второй части.
а вы всё продолжаете настаивать... или вы думаете, что вас подло обманывают?
наверное, имеет смысл озвучить вашу исходную задачу и поискать её решение другим способом? чем вообще вас не устраивает работа с массивами? зачем весь этот огород?
ну я не знаю уже... разве вам и здесь, и на stackoverflow не объяснили, что
Цитата
You can't create a Range from scratch: you can only refer to an existing range.
а вы всё продолжаете настаивать... или вы думаете, что вас подло обманывают?
наверное, имеет смысл озвучить вашу исходную задачу и поискать её решение другим способом? чем вообще вас не устраивает работа с массивами? зачем весь этот огород?ikki
помощь по Excel и VBA ikki@fxmail.ru, icq 592842413, skype alex.ikki
Сообщение отредактировал ikki - Суббота, 06.04.2013, 20:31
Исходная задача такова. Есть Range на листе в Excel: 1 2 3 5 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 На основе данного Range выполняются функции, скажем, сумма всех элементов. Таких функций около двух десятков (пример был выше), а самих Range несколько тысяч. Большинство функций имеют Range в качестве аргумента. Появилась задача работать с измененным Range, без того, чтобы менять исходный вручную. Значения в Range могут менятся при соблюдении определенных условий, но будут иметь вид: 1 2 3 4 20 7 9 10 100 То есть Range укорачивается, а один или несколько элементов должны поменть значение.
Вопрос: как преобразовать исходный Range, чтобы новый подходил в качестве аргумента для существующих функций. Вопрос 2: если это невозможно, есть ли альтернативное решение с помощью массивов? предложите, пожалуйста.
Да, на StackOverFlow тоже мой пост, там тоже пока без решений. Не явлюсь профессиональным программистом, обратился за помощью к экспертам.
Исходная задача такова. Есть Range на листе в Excel: 1 2 3 5 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 На основе данного Range выполняются функции, скажем, сумма всех элементов. Таких функций около двух десятков (пример был выше), а самих Range несколько тысяч. Большинство функций имеют Range в качестве аргумента. Появилась задача работать с измененным Range, без того, чтобы менять исходный вручную. Значения в Range могут менятся при соблюдении определенных условий, но будут иметь вид: 1 2 3 4 20 7 9 10 100 То есть Range укорачивается, а один или несколько элементов должны поменть значение.
Вопрос: как преобразовать исходный Range, чтобы новый подходил в качестве аргумента для существующих функций. Вопрос 2: если это невозможно, есть ли альтернативное решение с помощью массивов? предложите, пожалуйста.
Да, на StackOverFlow тоже мой пост, там тоже пока без решений. Не явлюсь профессиональным программистом, обратился за помощью к экспертам.Mike
Без этого блока менятся размер Range. С этим блоком - ошибка.
Да, я упустил, что это UDF и что Вы эту функцию хотите на рабочем листе использовать. Вне рабочего листа работает нормально, со всеми присвоениями.
А при использовании на рабочем листе (в ячейках) - не будут работать ни присвоения значений другим ячейкам внутри функции, ни какое-либо форматирование других ячеек. Это и пытаться не стоит. Можно не копать в эту сторону - зря потраченное время.
Мне известен лишь один специальный пример, когда удается сформатировать соседние ячейки. Приводил его я, разумеется Сейчас попробую найти ссылку... О! Нашёл - функция ЗакрасьСоседа: http://www.excelworld.ru/forum/2-1805-1#20032
Цитата (Mike)
Без этого блока менятся размер Range. С этим блоком - ошибка.
Да, я упустил, что это UDF и что Вы эту функцию хотите на рабочем листе использовать. Вне рабочего листа работает нормально, со всеми присвоениями.
А при использовании на рабочем листе (в ячейках) - не будут работать ни присвоения значений другим ячейкам внутри функции, ни какое-либо форматирование других ячеек. Это и пытаться не стоит. Можно не копать в эту сторону - зря потраченное время.
Мне известен лишь один специальный пример, когда удается сформатировать соседние ячейки. Приводил его я, разумеется Сейчас попробую найти ссылку... О! Нашёл - функция ЗакрасьСоседа: http://www.excelworld.ru/forum/2-1805-1#20032Gustav
Дата: Воскресенье, 07.04.2013, 10:42 |
Сообщение № 11
Группа: Гости
Проблему решил. Надо поменять аргументы в функциях типа NumberofRows с Range на массивы, и вычисления во всех функциях вести в массивах, а не в диапазонах. Может, не самый эффективный способ, но с задачей справляется. Спасибо всем за комментарии!
Проблему решил. Надо поменять аргументы в функциях типа NumberofRows с Range на массивы, и вычисления во всех функциях вести в массивах, а не в диапазонах. Может, не самый эффективный способ, но с задачей справляется. Спасибо всем за комментарии!Mike
Вход
Регистрация
Напомнить пароль
Автосумма
Часто используемые
ВПР
ЕСЛИ
СУММЕСЛИ
Финансовые
Логические
Текстовые
Даты и времени 
Ссылки и массивы 
Математические
Другие функции
Проверка свойств и значений
Баз данных
Математические
Обработки массивов
Обработки строк
Преобразования типов данных 
Загрузки данных
Работы с файлами
Обработки системных параметров
Обработки цвета
Работы с датами и временем
Преобразования в системы счисления
Финансовые
