Поиск  Пользователи  Правила  Войти
Форумы "Планета Excel" » Вопросы по Microsoft Excel
Страницы: 1
RSS
Ошибка Out of range при работе с динамическим массивом
 
#1
19.07.2018 09:40:18
Добрый день.

Разбираюсь с динамическими массивами. Связал кнопку с макросом, после нажатия на нее вылетает указанная ошибка.
Код:
Код
Sub Knopka_Click ()
 Dim test
 test = Range("E2:E22")
 ReDim Preserve test (21)
 Range ("B6").Value = test(4)
End Sub
Ошибка вылетает уже на 4 строке, не пойму что не так делаю, в ячейках E2:E22 записаны числа, ячейка B6 - пустая.
Вместо ReDim Preserve test (21) пробовал уже:
ReDim Preserve test (0 to 21)
ReDim Preserve test (1 to 21)
ReDim Preserve test (1 to 22)
Ошибка все та же.
 
 
 
#2
19.07.2018 10:01:34
BapuK, посомтрите на массив после  test = Range("E2:E22") . Он немного двухмерный. Можно только так
ReDim Preserve test(1 To 21, 1 To 2) или траспонировать изменить и снова транспонировать.
Изменено: БМВ - 19.07.2018 10:06:01
По вопросам из тем форума, личку не читаю.
 
 
 
#3
19.07.2018 10:04:27
БМВ, эээ как так?
Часть столбца всего лишь, почему он 2хмерный?
 
 
 
#4
19.07.2018 10:07:42
Цитата
BapuK написал:
почему он 2хмерный
Ну так заложено в excel, и при таком присвоении массив будет  От 1 до  … строк и от 1 до …. столбцов.
Изменено: БМВ - 19.07.2018 10:07:52
По вопросам из тем форума, личку не читаю.
 
 
 
#5
19.07.2018 10:08:20
Цитата
BapuK написал:
Разбираюсь с динамическими массивами
массив, созданный автоматически из ячеек по умолчанию не является динамическим. И к слову - он у Вас даже как массив не объявлен. поэтому сначала желательно бы с теорией чуть плотнее разобраться, прежде чем переходить к живым примерам, да еще с ячейками.
Когда Вы создаете массив этой строкой:
Код
test = Range("E2:E22")
'кстати правильнее test = Range("E2:E22").Value
у Вас получается массив: test(1 to 21, 1 to 1). Т.е. двухмерный. И Вы пробуете изменить кол-во строк в нем. В данном конкретном случае это сделать не получится никак, т.к. размерность строк в массиве test является первой размерностью. А через Preserve можно изменять динамически только последнюю. А последняя - столбцы.
Даже самый простой вопрос можно превратить в огромную проблему. Достаточно не уметь формулировать вопросы...
 
 
 
#6
19.07.2018 10:18:52
замените
Код
Dim test()

здесь тема уже поднималась
Цитата
1. ГЛАВНОЕ ПРАВИЛО

Прежде чем задать вопрос - поищите на него ответ поиском по форумуи посмотрите статьи в Приемах. Здесь уже обсуждалось более 45 000 вопросов - вполне возможно (и даже скорее всего!), кто-то уже нашел решение.
Изменено: TheBestOfTheBest - 19.07.2018 10:30:16
Неизлечимых болезней нет, есть неизлечимые люди.
 
 
 
#7
19.07.2018 10:23:40
БМВ,спасибо, буду знать.
Дмитрий(The_Prist) Щербаков, текст набирал с телефона - ошибся при переносе, в исходном коде так и было прописано. Матчасть проходил, просто было давно, некоторые вещи подзабыл, да и гугл не подсказал чет (:

Тогда такой вопрос: если я хочу загнать в динамический одномерный массив данные из столбца, то без цикла не обойтись?
Вообще у меня сейчас вот какая задача стоит: есть именованная таблица с некоторым числом столбцов (назовем ее для удобства "Таблица1").Мне нужно, что после нажатия на кнопку данные из определенного столбца (назовем его "Столбец1") записались в одномерный массив. Думал что прокатит что-то типа:

Код
Test = Range("Таблица1[Столбец1]").Value

Но видимо нужно думать все таки в  сторону циклов...

Изменено: BapuK - 19.07.2018 10:24:52
 
 
 
#8
19.07.2018 10:31:57
Цитата
BapuK написал:
если я хочу загнать в динамический одномерный массив данные из столбца, то без цикла не обойтись?
В принципе можно
Код
Test = worksheetfunction.transpose(Range("Таблица1[Столбец1]").Value)
Но у этой функции есть ограничения.
Цитата
BapuK написал:
Мне нужно, что после нажатия на кнопку данные... записались в одномерный массив.
Почему именно в одномерный? Что мешает работать с двумерным массивом?
 
 
 
#9
19.07.2018 10:36:20
Цитата
Казанский написал:
Почему именно в одномерный? Что мешает работать с двумерным массивом?
Ну насколько я понял работу ReDim - он может менять размерность только последнего измерения, а у меня варьироваться будет количество строк.
 
 
 
#10
19.07.2018 10:42:29
Цитата
BapuK написал:
работу ReDim
ReDim меняет размерность любого массива на новую. Но при этом массив обнуляется. А ключевой Preserve позволяет изменить последнюю размерность без очистки массива от прежних значений.
И я тоже не понял, почему именно динамический нужен? Работать можно и со статическим. Какой смысл его переопределять динамически, если это все равно только значения из таблицы?
Даже самый простой вопрос можно превратить в огромную проблему. Достаточно не уметь формулировать вопросы...
 
 
 
#11
19.07.2018 10:46:33
Дмитрий(The_Prist) Щербаков, Каждый раз при нажатии данной кнопки заполненных строк будет разное количество, зачем выделять лишнюю память под это?
 
 
 
#12
19.07.2018 10:53:46
Цитата
BapuK написал:
зачем выделять лишнюю память под это?
а кто сказал, что будет выделяться лишняя память? Массив очищается сам сразу по завершении процедуры - т.е. один раз нажали кнопку, массив заполнился. После End Sub он вычищается полностью и нет на него даже намека в памяти.
И даже если надо несколько раз перезаполнить его новыми данными внутри одной процедуры из таблицы, строка
Код
Test = Range("Таблица1[Столбец1]").Value
просто перезаписывает его и память под это не грузится больше, чем если бы это был динамический массив, потому что память выделяется в основном под ЗАПОЛНЕННЫЕ элементы массива. А массив у нас только один. И сначала он будет полностью очищен, далее изменена его размерность и потом заполнен. И сделано это будет быстро и без циклов. И в данном случае Вы ничего не выиграете через динамический массив, а наоборот - только гемморой себе наживаете.
Изменено: Дмитрий(The_Prist) Щербаков - 19.07.2018 10:54:53
Даже самый простой вопрос можно превратить в огромную проблему. Достаточно не уметь формулировать вопросы...
 
 
 
#13
19.07.2018 10:58:45
Дмитрий(The_Prist) Щербаков, но если работать со статическим массивом, я не пойму как изменить нужно:
Код
Test = Range("Таблица1[Столбец1]").Value


чтобы, он заполнил массив из определенного столбца, т.к. такое работает только для динамического массива.


Сразу скажу, что от именованных списков отказываться не хочется, т.к. структура таблицы будет дополняться и изменяться, и потом не хочется перелопачивать код из-за того, что поменял структуру.
Изменено: BapuK - 19.07.2018 11:01:45
 
 
 
#14
19.07.2018 11:08:58
Доброе время суток
Цитата
BapuK написал:
зачем выделять лишнюю память под это?
А кто вам сказал, что память не выделяется? Redim - синтаксический сахар.
Выделяется новая область памяти и ассоциируется с переменной массива. Старая область - освобождается. В случае с Preserve дополнительно переписываются перед удалением данные из старой области в новую. Чудес не бывает.
 
 
 
#15
19.07.2018 11:39:18
Андрей VG, ну это понятно, но данная операция в данном случае будет происходить всегда в самом начале алгоритма. Зачем от начала и до конца работы тянуть ненужный кусок массива, если его можно отрубить в самом начале?
В принципе я понял, как можно работать со статическим массивом, но тогда открытым остается вопрос: как исправить данный кусок кода:


Код
Test = Range("Таблица1[Столбец1]").Value

чтобы он сработал для статического массива Test?
Изменено: BapuK - 19.07.2018 11:44:51
 
 
 
#16
19.07.2018 12:34:18
Никто кроме Вас уже не понимает, что Вы хотите сделать с массивом. какие у Вас там ненужные куски? Откуда Вы их берете? Чем не устраивает запись в массив данных в самом начале процедуры? Если постоянно данные будут дополняться - значит в алгоритме Вашем большая дыра или массивы вообще не нужны.
Опишите нормально что хотите сделать, а не то, как пытаетесь это сделать. Может и решение найдется.
Даже самый простой вопрос можно превратить в огромную проблему. Достаточно не уметь формулировать вопросы...
 
 
 
#17
19.07.2018 12:48:24
Дмитрий(The_Prist) Щербаков, ок попробую еще раз: есть именованная таблица ("Таблица1"), в ней есть столбец ("Столбец1").
Мне нужно в массив "Test" записать все значения из именованного диапазона "Таблица1[Столбец1]".
По поводу массива Test - желательно, чтобы он был одномерный, желательно, чтобы нумерация ячеек начиналась с 0, а не с 1.
Так же желательно, чтобы пустого хвоста в данном массиве не было, т.е. если начиная с некоторой ячейки (i-ой по счету) в "Таблица1[Столбец1]" все ячейки пустые, то массив заканчивался на i-ой ячейке.


Почему используется именованный диапазон - потому что внешний вид данной таблицы будет меняться, возможно дополняться новыми столбцами и нужный столбец может съехать с фиксированного адреса, и чтобы не лопатить код заново - хочется работать именно с именованным диапазоном.
После запуска макроса все действия будут происходить на другом листе, на первом листе расположены как-бы "входные данные"
Изменено: BapuK - 19.07.2018 12:51:00
 
 
 
#18
19.07.2018 13:09:21
1. Чтобы начинался с нуля - что за требование? Есть логическое объяснение, кроме "мне так хочется"?
2. Можно искать последнюю заполненную ячейку в столбце - тогда данные будут только те, что реально значимые. Это можно сделать и так:
Код
    Dim llastr As Long, lr As Long, Test
    With Range("Таблица1[Столбец1]")
        lr = .Row
        llastr = .Cells(.Rows.Count, 1).End(xlUp).Row - .Row + 1
        If llastr <= lr Then
            llastr = .Rows.Count
        End If
        Test = .Cells(1, 1).Resize(llastr).Value
    End With
Даже самый простой вопрос можно превратить в огромную проблему. Достаточно не уметь формулировать вопросы...
 
 
 
#19
19.07.2018 13:14:38
Дмитрий(The_Prist) Щербаков, Дмитрий, и сейчас ТС козырем, "и учесть/не учесть фильтр … " :-)
По вопросам из тем форума, личку не читаю.
 
 
 
#20
19.07.2018 13:18:10
Цитата
БМВ написал:
и сейчас ТС козырем
ну...поиск никто не отменял :) Тема избита уже не по 1 разу. Я лишь привел пример, как это применить к умной таблице. А дальше уже дело техники.
Даже самый простой вопрос можно превратить в огромную проблему. Достаточно не уметь формулировать вопросы...
 
 
 
#21
19.07.2018 13:22:29
Цитата
Дмитрий(The_Prist) Щербаков написал:
Чтобы начинался с нуля - что за требование? Есть логическое объяснение, кроме "мне так хочется"?
Если кратко, то данные самой первой строки в таблице того же типа, что и ниже, но обрабатываться будут немного по-другому.
Если более длинно, то я занимаюсь сейчас моделированием товародвижения розничной сети, и в каждой строке именованной таблицы данные соответствуют некоторому магазину, а самая первая строка - данные РЦ, к примеру в первом столбце записаны остатки на магазинах+РЦ.

P.S.  в принципе все работало (в массивы забивал данные циклом), до того момента, как я решил начать работать с именованным диапазоном (уже местами жалею, но хочется добить)

Цитата
Дмитрий(The_Prist) Щербаков написал: Это можно сделать и так:
Спасибо, попробую разобраться.

Цитата
БМВ написал: и сейчас ТС козырем
К счастью пока не требуется :D
 
 
 
#22
19.07.2018 13:43:10
Цитата
BapuK написал:
Если кратко, то
то это не объясняет, зачем именно с нуля так важно. На конечную обработку это не влияет никак, кроме Вашего желания как бы игнорировать первый элемент только на основании нумерации с нуля. В коде можно определять первый элемент массива:
Код
lb = lBound(test,1)
это нижняя граница. Для массивов, начинающихся с нуля будет равна 0, а с единицы - 1.
Даже самый простой вопрос можно превратить в огромную проблему. Достаточно не уметь формулировать вопросы...
 
 
 
#23
19.07.2018 13:50:47
Это удобно, т.к. 0-й элемент массива соответствует данным РЦ (а там не один массив, а несколько), а с 1-й по i-й соответствуют 1-му - i-му магазину. В некоторых местах вызывается номер магазина, чтобы не было путаницы при написании формул и писанины из разряда
Код
"Магазин № "& i - 1
 
 
 
#24
19.07.2018 13:55:42
Не думаю, что это сильная проблема. Это всегда можно обработать опять же, опираясь на определение нижней границы массива. Но утверждать не буду - весь код не видел, в логику не вникал. Вам виднее, будем от этого отталкиваться.
в общем по сути тема массива в данном направлении себя исчерпала и дальше обсуждать по сути нечего. Динамический массив в Вашем случае можно создать только циклом. Можно даже сначала забрать значения в автомассив test, а потом циклом по нему загнать в другой, рабочий массив.
Даже самый простой вопрос можно превратить в огромную проблему. Достаточно не уметь формулировать вопросы...
 
 
 
#25
19.07.2018 14:03:13
BapuK,  Ну если удобно то сделайте функцию ,чтоб не писать каждый раз что-то типа
Код
Function arrShift(ByVal arr As Range)
arr1 = arr.Value
ReDim arr2(UBound(arr1, 1) - 1)
For i = 1 To UBound(arr1)
arr2(i - 1) = arr1(i, 1)
Next
arrShift = arr2
End Function
Sub test()
a = arrShift(Range("b1:b6"))
End Sub
По вопросам из тем форума, личку не читаю.
 
 
 
#26
19.07.2018 15:23:09
Скрещиваем #25 и #18  :)
Код
' Формирует одномерный массив из первого столбца диапазона r. Пустые ячейки в конце отсекаются.
Function arrShift(ByVal r As Range)
    Dim arr1(), arr2(), i As Long
    arr1 = Range(r.Cells(1), r.Cells(1).Offset(r.Rows.Count - 1).End(xlUp)).Value
    ReDim arr2(0 To UBound(arr1, 1) - 1)
    For i = 1 To UBound(arr1)
        arr2(i - 1) = arr1(i, 1)
    Next i
    arrShift = arr2
End Function

Sub test()
    Dim a
    a = arrShift(Range("b1:b6000"))
End Sub
Владимир
 
 
 
#27
19.07.2018 15:28:49
БМВ, sokol92, спасибо большое  :oops:  буду разбираться и пробовать, если появятся вопросы - напишу  :)  
 
 
 
Страницы: 1
Читают тему
Наверх