# Get 1D Array using Evaluate

#### YasserKhalil

##### Well-Known Member
Hello everyone

I have this simple lines
Code:
``````Dim x as Integer
x=4
a = Evaluate("ROW(1:" & x & ")")``````
The code constructs an array (2D array) .. Is there a trick to get 1D Array using Evaluate?
I tried something like that but doesn't work for me
Code:
``a = Application.Index(Evaluate("ROW(1:" & x & ")"), 0)``
** The thread is posted here too

#### shrivallabha

##### Excel Ninja
You can try:

Code:
``````Dim x As Integer
x = 4
a = Application.Transpose(Evaluate("ROW(1:" & x & ")"))``````
Word of caution: Application.Transpose has limitation beyond 65536 elements.

#### YasserKhalil

##### Well-Known Member
Thanks a lot for great help.
What if the data is beyond limitation: how can the 2D converted to 1D array?

#### shrivallabha

##### Excel Ninja
Since you have variable x which is an integer and therefore will have elements much less than the limit anyway.

However, if in reality you have more elements than the limit then there will be no one-liner code. Instead an array will need to be populated using conventional methods. You can get codes directly by googling fo

#### YasserKhalil

##### Well-Known Member
Thanks a lot. This was just simple question so I used x variable as Integer.
But in the original code I will declare it as long
So you mean no way escaping the loop through the elements of the array so as to convert it ..

#### shrivallabha

##### Excel Ninja
Basically yes!

The issue is not that it cannot transpose beyond 65536 but it doesn't raise any error.

Code:
``````Public Sub TestThis()
Dim x As Long
x = 1048576
a = Evaluate("ROW(1:" & x & ")")
b = Application.Transpose(a)
For i = 1 To x
Cells(i, 1).Value = b(i)
Next i
End Sub``````
Error will come in the loop and not on Application.Transpose line.

If you google then you will get code suggestions like below:
https://stackoverflow.com/questions/20055784/best-workaround-for-vba-transpose-array-length-limit

#### Marc L

##### Excel Ninja
Hi !​
Is there a trick to get 1D Array using Evaluate?
Yes, just use COLUMN instead of ROW !​

#### YasserKhalil

##### Well-Known Member
Thanks a lot Mr. Marc for this trick
Now this line
Code:
``a = Evaluate("COLUMN(1:" & x & ")")``
Although x=4 I found the array is 1 to 16384

#### Marc L

##### Excel Ninja
As your codeline does not use the correct Excel syntax !​
To use Excel feature within VBA it is first necessary to know how Excel works :​
`a = Evaluate("COLUMN(" & [A1].Resize(, x).Address & ")")` …​

#### YasserKhalil

##### Well-Known Member
Thank you very much. That's awesome.

#### Marc L

##### Excel Ninja
No, that's just Excel basics !​
Like `a = [{1,2,3,4}]` …​

#### shrivallabha

##### Excel Ninja
But do not forget the fact that columns are 16384

So if you are going beyond 16384 you will need other logic!

Last edited:

#### Marc L

##### Excel Ninja
No, since Excel 2007 the columns are 16 384 instead of 256 previously …​

#### shrivallabha

##### Excel Ninja
No, since Excel 2007 the columns are 16 384 instead of 256 previously …​
My bad! I have fixed it.

#### Marc L

##### Excel Ninja
it cannot transpose beyond 65536
Beyond the limit a tip is to use a Windows object, I don't remember exactly which one (maybe from Microsoft Forms)
but called by its Window registry key index, I have posted it only once years ago but I don't remember the forum …​
As it's not usual to need to transpose beyond the limit … One day I will search within my xFiles !​

#### salim hasan

##### Member
Hi my Friend Yasser
Can you please try My Macro

Code:
``````Option Explicit
Sub Tajriba()
Dim Mon_Array, s
Dim x%: x = 10
s = "Row( 1:" & x & ")"
Mon_Array = Application.Transpose(Evaluate(s))
Range("A1").Resize(x) = Application.Transpose(Mon_Array)
End Sub
Rem Something else might have been useful
'========================================
Sub Get_Date()
Dim FUl_DATE
Dim Ho_many%: Ho_many = 12

If Month(DateSerial(Year(Date) + 1, 2, 29)) = 3 Then
FUl_DATE = [transpose(today()+row(1:365)-1)]
Else
FUl_DATE = [transpose(today()+row(1:366)-1)]
End If
Range("A1").Resize(UBound(FUl_DATE)) = Application.Transpose(FUl_DATE)
Range("B1").Resize(Ho_many) = Application.Transpose(FUl_DATE)
Range("A:B").NumberFormat = "D/M/YYYY"
End Sub``````

#### YasserKhalil

##### Well-Known Member
Thanks a lot Mr. Salim for your contribution
But you have used Transpose which has limitation .. so this doesn't solve the problem completely

#### Marc L

##### Excel Ninja
Yasser, do you need more than 16 384 elements, more than 65 536 ?!​

#### YasserKhalil

##### Well-Known Member
In fact, the original is about 60,000 rows .. So I am trying all the time to find the best and fastest way to manipulate data

#### Marc L

##### Excel Ninja
Ok so `Evaluate("TRANSPOSE(ROW(1: …` well does the job.​

#### YasserKhalil

##### Well-Known Member
Yes till this moment .. But it nearly to break the number of limited !!

#### Marc L

##### Excel Ninja
If that comes, working with arrays is fast enough and maybe I will find out the old Windows trick …​

#### YasserKhalil

##### Well-Known Member
Thanks a lot my tutor for your interest. I hope too to you to find this trick. I am sure it will be great.

#### salim hasan

##### Member
Thanks a lot Mr. Salim for your contribution
But you have used Transpose which has limitation .. so this doesn't solve the problem completely
Sorry I didn't understand what it means has limitation
Knowing that
you can choose any value of x in the macro

#### Marc L

##### Excel Ninja
I hope too to you to find this trick. I am sure it will be great.
Just done, lucky you are ‼ As it was published here in august 2014 : InverseArray !​
Not so great as I had to create the NbDim function in order the InverseArray function autodetects the sense​
but you can avoid it just using directly the appropriate property (aka Column or List in your case).​
Instead of the object CLSID the CreateObject statement can directly call the object `New:Forms.ListBox.1` …​
The ListBox object always returns a zero base array whatever the source array first index …​
I already tested it with 5 millions elements but it failed with 10 millions but as it was under Excel 2003​
on an old computer maybe the limit is the free RAM the Excel version can handle …​