• 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
 

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
 

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 & ")") …​
 

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
 

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
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 …​
 
Top