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

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

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

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-мерный массив. (Предостережение: существуют ограничения по размеру, но они намного больше, чем 10x20.)

Если вы разработчик - в чем разница между строками и столбцами? Использование массива (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 (теперь количество столбцов, но сохраняя значения строк) больше, когда вам нужно.

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

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

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

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

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

Run-Time error '13':

Type Mismatch

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

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