Как я могу «ReDim сохранить» 2D-массив в Excel 2007 VBA, чтобы я мог добавлять в массив строки, а не столбцы?

Я работаю с динамическим массивом в Excel VBA. Количество столбцов (m) фиксировано, однако я не знаю, сколько строк (n) потребуется.

В справочных документах указано, что ReDim Preserve myArray (n, m) позволяет мне сделать m больше, но не n. Однако мне нужно увеличить количество строк (n), сохраняя мои данные, а не столбцы (m)!

Например, у меня может быть массив (5,20), который я хотел бы расширить до (10,20), сохраняя при этом мои данные.

Кажется, что если бы был какой-то способ транспонировать мой массив, сделайте резервную копию ReDim, чтобы расширить количество «столбцов», а затем повторно транспонировать мой массив, я мог бы выполнить то, что хочу.

Это правильный способ сделать это? Если да, то как я могу это сделать?

Есть ли лучший способ добиться того, чего я хочу?

Один из способов сделать то, что вы хотите, – использовать 1-D массив, содержащий 1-D массивы вместо 2-мерного массива. Затем вы можете ReDim сохранить внешний массив, который вы хотите. Если вы возвращаете внешний массив из функции, Excel будет делать правильные вещи и принуждать их к 2-мерному массиву.

Например, приведенная ниже функция вернет массив 3×2 в ячейки, из которых он вызван:

Public Function nested() Dim outer outer = Array(Array(1, 2), Array(3, 4)) ReDim Preserve outer(1 To 3) outer(3) = Array(5, 6) nested = outer End Function 

Мой ответ на эти вопросы также может быть вам полезен: передать многомерный массив в Excel UDF в VBA и VBA, вставляя 3-мерный массив в лист

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

 Dim coerced coerced = Application.Index(outer, 0, 0) 

Это просто вызывает встроенную функцию INDEX в Excel, а нули означают, что вы хотите вернуть все свои строки и все ваши столбцы. Excel принудительно объединит ваш 1-мерный массив 1-D массивов в 2-мерный массив. (Предостережение: существуют ограничения по размеру, но они намного больше, чем 10×20.)

Если вы разработчик – в чем разница между строками и столбцами? Использование массива (N, 2) (если у вас есть 2 столбца) совпадает с массивом (2, N), для которого вы можете

 ReDim Preserve arr(1 to 2, 1 to N+1). 

И разница для вас (как разработчика) заключается в том, чтобы поставить переменную из цикла на второе место, а не на первое:

 N = ubound(arr) FOR i=1 to N GetColumn1Value = arr(1, i) GetColumn2Value = arr(2, i) NEXT i 

Или вы хотите это:

 N = ubound(arr) FOR i=1 to N GetColumn1Value = arr(i, 1) GetColumn2Value = arr(i, 2) NEXT i 

В чем разница?

Один из способов, как вы могли это осознать, – это, в сущности, двойная транспозиция с изменением количества столбцов между ними. Однако это будет работать только для двумерных массивов. Это делается следующим образом:

 ' Adding one row is done by a double transposing and adding a column in between. ' (Excel VBA does not allow to change the size of the non-last dimension of a ' multidimensional array.) myArray = Application.Transpose(myArray) ReDim Preserve myArray(1 To m, 1 To n + 1) myArray= Application.Transpose(myArray) 

Разумеется, m и n можно вывести следующим образом:

 m = UBound(myArray, 1) n = UBound(myArray, 2) 

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

Слово «транспонировать» сразу бросается в глаза. Вы можете просто вводить данные в 2D-массив, переворачивая столбцы и строки (т. Е. Транспонировать), эффективно позволяя вам делать n (теперь количество столбцов, но сохраняя значения строк) больше, когда вам нужно.

Чтобы ссылаться на значения, скажем, в двойном цикле, обменивайте индексы вокруг. Например, скорее перейдите от i = 1 к n и j = 1 к m, где вы ссылаетесь на значение (i, j), используйте i = 1 to m и j = 1 – n.

Решил мой собственный вопрос; вот как я обошел свою проблему. Я создал временный массив, скопировал содержимое myArray во временный массив, изменил размер myArray, а затем скопировал содержимое из массива temp в myArray.

 tempArray = myArray ReDim myArray(1 To (UBound(myArray()) * 2), 1 To m) For i = 1 To n For j = 1 To m myArray(i, j) = tempArray(i, j) Next j Next i 

Если кто-то может предложить более эффективный способ сделать это, я бы хотел его услышать.

Массив с двумя размерами, где число столбцов фиксировано и число строк является динамическим, может быть создано следующим образом:

 Sub test2DimArray() Dim Arr2D() As String Dim NumberOfCol As Long Dim I As Long, J As Long, x As Long Dim tmpValue As String, tmpValue2 As String, tmpValue3 As String NumberOfCol = 3 J = 1 Debug.Print "Run " & Now() Debug.Print "Sheet content" Debug.Print "Row col1 col2 col3" For I = 1 To 10 tmpValue = Cells(I, 1).Value tmpValue2 = Cells(I, 2).Value tmpValue3 = Cells(I, 3).Value Debug.Print I & " = " & tmpValue & " " & tmpValue2 & " " & tmpValue3 If Len(tmpValue) > 0 Then ReDim Preserve Arr2D(NumberOfCol, 1 To J) Arr2D(1, J) = tmpValue Arr2D(2, J) = tmpValue2 Arr2D(3, J) = tmpValue3 J = J + 1 End If Next 'check array values Debug.Print vbLf; "arr2d content" Debug.Print "Row col1 col2 col3" For x = LBound(Arr2D, 2) To UBound(Arr2D, 2) Debug.Print x & " = " & Arr2D(1, x) & " " & Arr2D(2, x) & " " & Arr2D(3, x) Next Debug.Print "=========================" End Sub 

TempValue читается из ячеек A1: A10, если в ячейке Ax есть значение, оно перечеркивает массив с +1 и добавляет Tempvalue в массив col1, добавляет содержимое в Bx в массив col2 и содержимое в Cx в массив col3. Если длина значения Ax равна 0, она ничего не добавляет к массиву.

Debug.print показывает результаты в «непосредственном окне» в редакторе VB.

Без линий тестирования и добавления динамического диапазона данных код может быть:

 Sub my2DimArray() Dim Arr2D() As String Dim NumberOfCol As Long, NumberOfRow As Long Dim FirstCol As Long, FirstRow As Long, LastCol As Long, LastRow As Long Dim I As Long, J As Long, X As Long Dim tmpValue As String, tmpValue2 As String, tmpValue3 As String 'if cells with values start in A1 With ActiveSheet.UsedRange NumberOfCol = .Columns.Count NumberOfRow = .Rows.Count End With 'if cells with values starts elsewhere With ActiveSheet.UsedRange FirstCol = .Column FirstRow = .Row LastCol = .Column + .Columns.Count - 1 LastRow = .Row + .Rows.Count - 1 End With J = 1 For I = 1 To NumberOfRow 'or For I = FirstRow to LastRow tmpValue = Cells(I, 1).Value 'or tmpValue = Cells(I, FirstCol).Value If Len(tmpValue) > 0 Then ReDim Preserve Arr2D(NumberOfCol, 1 To J) For X = 1 To NumberOfCol 'or For X = FirstCol to LastCol Arr2D(X, J) = Cells(I, X).Value Next X J = J + 1 End If Next I End Sub 

Невозможно определить количество элементов в первом измерении? Облом. Для двумерного массива с фиксированным вторым измерением вы можете захотеть сделать его массивом типов («structs» на других языках). Это позволит вам использовать Redim Preserve и по-прежнему оставляет вас разумным способом добавления и доступа к значениям, хотя теперь вы будете обращаться ко второму измерению как к именованным членам типа, а не к значениям индекса.

принуждение или Slicing, похоже, не работают с Index (или Match (Index) (если я хочу фильтровать массив (без циклов) на основе нескольких критериев, когда размер данных превышает 2 ^ 16 строк (~ 92000 строк).

 Run-Time error '13': Type Mismatch 

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

Я думаю о попытке использования словаря или ADO с Excel.

Interesting Posts

Inno Setup – Регистрация компонентов в качестве администратора

Можно ли отсортировать столбец B в столбце C, используя только функции / формулы?

Сохранить документ Excel Apache POI

VBA – Как я могу дать код ошибки на основе суммы моих значений текстового поля в форме пользователя?

Формула DSUM в Excel Challenge не суммирует точные критерии, но аналогичные

Скопируйте рабочий лист в другую книгу, заменив текущие данные

Excel с использованием db доступа (~ 2gb) в качестве backend, подпадающего под R с блестящей или уценкой?

Если столбец A содержит определенную строку, суммируйте количество ячеек со строкой в ​​столбце D

Получение индекса вне диапазона для массива байтов

Подождите, пока не закончится Excel RefreshAll (Ctrl + Alt + F5) – VBA

Получение значений из столбца в excel с использованием openpyxl

четкая функция в excel не работает

Создать группуRows с SXSSFWorkbook

excel – вложенное форматирование состояния

как автоматически отображать данные Excel в листе excel в пользовательской форме при нажатии на кнопку

Давайте будем гением компьютера.