Type Mismatch when Transposing 65556 size Array into Range

[tngengineer]

I was looking for a worksheet with vba to do FFT beyond the Excel internal 4096 limit. With this Forum's help I did find one (without vba) that would do the trick.
I wanted to try my hand at writing code to do it faster. The attached does just that.
Everything worked fine until I went beyond 32k samples. The next higher 2^n value for data size to feed the FFT is 65556. When I tried this quantity of samples I got Runtime Error (13) type mismatch when attempting to write the array back to the worksheet using the "transpose" method.
If I remember correctly, 32k worked.
I bookmarked the line in question in the attached file.

Destination.Value = Application.Transpose(ReData) 'Transpose Array and write to destination range

Is there some limit to the size of the array that can be transposed and placed into a range? If so, is there another way to do this?

I do not need to re-write the data, but I use this same method elsewhere to write the FFT output. It it fails writing the input data back, it will probably fail when writing the FFT results.

PS. Please excuse the sloppy code. My first vba project since about 1997 and my second in total.

[watersev]

hi tngengineer, welcome to Excelforum.

Transpose function you use does have a limitation of 65536 items. Unfortunately there is no trick to overcome this limitation and still use Transpose function.

The solution I can suggest would require pretty much changes to the code. As I see in you code you create one-dimensional array with values and then use transpose it to make it vertical. You can create that array vertical from the start (two-dimensional) and avoid Transpose.

Example (simplified) to show what I mean:

Sub example()

Dim arr(1 To 100), i As Long

For i = 1 To 100
    arr(i) = i
Next

Range("a1:a100") = Application.Transpose(arr)

End Sub

Sub example_no_transpose()

Dim arr(1 To 100, 1 To 1), i As Long

For i = 1 To 100
    arr(i, 1) = i
Next

Range("a1:a100") = arr

End Sub