Writing very large 1D arrays to the worksheet

Question

My code creates over ~40 different arrays across many subroutines, each one being 1-dimensional using the best datatype for memory.

Dim N As Long
N = 40000
Dim my_array() As Double
ReDim my_array(1 To N)

Dim i as Long

For i = 1 to N
    my_array(i) = Rnd
Next i

In most cases, the process runs at ~ 40,000 rows, and I can write to worksheets fine using transpose rng.Resize(N, 1).Value = Application.Worksheet.Transpose(my_array)

But now that I am have scenarios over ~ 65k rows, up to 200,000 in some cases, I’ve been noticing #N/As when printed to the sheet (as opposed to the documented Transpose array problem type mismatch for certain Excel Versions).

I would like to be able to optimally get the arrays onto the sheet and avoid losing information.

Ideas I have thought about:

Do I reloop all variables into a 2d variant arrays so I can write directly without transpose? This may drastically increase my memory usage.

Do I break my transposes programmatically into sectioned loops that fit the ~65k size limit? This seems cumbersome but interesting.

And then should I write a function to do either and employ it to each array or wrap all arrays into the process at the same time within the general subroutine?

Any thoughts of what I should do or code examples that have been useful to you for this issue? Thanks

Relevant link where user faces similar issue but is happy with variants https://stackoverflow.com/questions/20055784/best-workaround-for-vba-transpose-array-length-limit — Evan Friedland, Jun 13 '20 at 22:26
40k elements is not really very large so I would just use arrays you don't need to transpose - `ReDim my_array(1 To N, 1 to 1)` — Tim Williams, Jun 13 '20 at 23:17
@TimWilliams thanks foe your comment - at 200k rows, would you advise changing all 40 string/double arrays into variants? — Evan Friedland, Jun 14 '20 at 03:32
@evan Tims' advice is the right answer. A 2D (even if it was 1000000 rows) array with the second dim `1 To 1` is really no different from a 1D array , and avoids the complexity of conversion. — chris neilsen, Jun 14 '20 at 03:42

chris neilsen · Accepted Answer · 2020-06-14T05:17:20.777

2

To demonstrate the point Tim and I made, you should work only with 2D arrays, and do away with the 1D's alltogether. No need to conversion at all.

You'll find this faster (because of no conversion 1D to 2D) and insignificant memory use diffference.

Also, you can use the data types you want (it's Transpose that requires a Variant, not the placement on the sheet) And Transpose fails for row counts > ~65000

Your test code could be

Sub testing_T()
    Dim N As Long
    N = 100000
    Dim dbl_arr() As Double
    ReDim dbl_arr(1 To N, 1 To 1)
    Dim i As Long
    For i = 1 To N
        dbl_arr(i, 1) = Rnd
    Next i

    Cells(2, 1).Resize(N, 1) = dbl_arr
End Sub

edited Jun 14 '20 at 05:17

answered Jun 14 '20 at 05:09

chris neilsen

52,446
10
84
123

Was totally blanking that you could do a multidimensional array of a non-variant type!! Thank you for your explanation! – Evan Friedland Jun 14 '20 at 15:30
Marvellous!!! Great job. Thanks. – Nigel Foster Nov 12 '21 at 15:18

score 0 · Answer 2 · answered Jun 14 '20 at 03:57

A potential solution based on favoring using a variant (as per Tim and Chris’ comments) that might be better than changing all arrays to variants—

Public Function create_2d(arr As Variant) As Variant
    Dim N As Long, new_arr As Variant, i As Long
    N = UBound(arr)
    ReDim new_arr(1 To N, 1 To 1)
    For i = 1 To N
        new_arr(i, 1) = arr(i)
    Next i
    create_2d = new_arr
End Function

Sub testing_T()

    Dim N As Long: N = 100000
    Dim dbl_arr() As Double: ReDim dbl_arr(1 To N)
    Dim i As Long
    For i = 1 To N
        dbl_arr(i) = Rnd
    Next i

    Cells(2, 1).Resize(N, 1) = _ 
     Application.WorksheetFunction.Transpose(dbl_arr)
    Cells(2, 3).Resize(N, 1) = create_2d(dbl_arr)
End Sub

Writing very large 1D arrays to the worksheet

2 Answers2