• Hi All

    Please note that at the Chandoo.org Forums there is Zero Tolerance to Spam

    Post Spam and you Will Be Deleted as a User

    Hui...

  • When starting a new post, to receive a quicker and more targeted answer, Please include a sample file in the initial post.

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
 
Thanks a lot for great help.
What if the data is beyond limitation: how can the 2D converted to 1D array?
 
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
 
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 ..
 
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
 
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
 
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 & ")") …​
 
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 !​
 
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
 
Thanks a lot Mr. Salim for your contribution
But you have used Transpose which has limitation .. so this doesn't solve the problem completely
 
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
 
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 …​
 
Back
Top