application transpose large array

[MetLife]

hi,

i want to take an array and transpose it but am having issues. the problem appears to be the size of the array, does anyone know a fix?




Sub stochastic()
'commutation functions
Dim surv(1 To 100000) As Double
Dim survv(1 To 100) As Double
Dim i As Long, j As Long


For i = 1 To 10000
If i > 99 Then
j = 99
Else
j = i
End If
surv(i) = Rnd
survv(j) = Rnd
Next i
Range("b2:b10000") = Application.Transpose(surv) 'error type mismatch
Range("b2:b100") = Application.Transpose(survv) 'no error

end sub

 

[Norie]

I originally used the exact code you posted, which worked, and then I tried this, which also worked.

Sub stochastic()
'commutation functions
Dim surv(1 To 100000) As Double
Dim i As Long

    For i = 1 To 100000
        surv(i) = Rnd
    Next i
    
    Range("B2:B100001") = Application.Transpose(surv)    'error type mismatch

End Sub

 

[Rick Rothstein]

yes the code is the exact one posted

Alright, I see the problem (which you sort of discovered on your own... as I posted in Message #6 , "The Transpose function has a maximum limit (cannot be overridden) of 65535 elements that it can transpose." Even though you only filled 10,000 elements and are assigning them to 10,000 cells, your code is attempting to transpose the entire array of 100,000 element first even though 90,000 elements of it are empty. You have no choice if you want to use the Transpose function... you will have to limit the array to 65535 elements maximum.

 

[Rick Rothstein]

I originally used the exact code you posted, which worked, and then I tried this, which also worked.

Sub stochastic()
'commutation functions
Dim surv(1 To 100000) As Double
Dim i As Long

    For i = 1 To 100000
        surv(i) = Rnd
    Next i
    
    Range("B2:B100001") = Application.Transpose(surv)    'error type mismatch

End Sub

Hmm, it does not work for me using XL2010. What version of Excel are you using? I have not heard that the 65,535 element limit for Transpose had been lifted, but if that code is working for you, then I have to conclude that Microsoft quietly lifted the limit at some point in time... I wonder when?

 

[Norie]

Rick

If there's a 65535 limit why does the code I posted in post #11 work?

 

[Norie]

Rick

Looks like our posts crossed.

I'm using Excel 2016 32-bit.

 

[Fluff]

I'm on Xl 2013 32-bit & when I tried Norie's code, I got values down to B34465 followed by #N/A

 

[Rick Rothstein]

Rick

Looks like our posts crossed.

I'm using Excel 2016 32-bit.

I'm on Xl 2013 32-bit & when I tried Norie's code, I got values down to B34465 followed by #N/A

I just did a quick Google search and came up with this link which says you (and other users of XL2013 and above) need to be very careful when using the VBA Transpose function...

https://newtonexcelbach.com/2016/01...ose-changed-behaviour-in-excel-2013-and-2016/

Isn't nice when Microsoft makes these kinds of changes and decides not to tell anyone about them?

 

[DougJ]

I just did a quick Google search and came up with this link which says you (and other users of XL2013 and above) need to be very careful when using the VBA Transpose function...

https://newtonexcelbach.com/2016/01...ose-changed-behaviour-in-excel-2013-and-2016/

Isn't nice when Microsoft makes these kinds of changes and decides not to tell anyone about them?

Especially when it used to give a clear error, but now loses part of the data with no indication that anything is wrong.

For those who don't like clicking links, the main message from my blog post was: if you must transpose a large array in VBA, use a loop rather than worksheetfunction.transpose.