Did you know that there is a limit to the array that can be passed into WorksheetFunction.Index? I read a really big range into an array and I wanted to split out a row. I used the INDEX(array, rownum, 0) feature, but I kept getting a Type Mismatch error. I couldn’t figure out what the heck was going on. I ended up testing my code on a smaller range and it worked as expected.
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 |
Sub IndexTest() Dim vaValues As Variant Dim i As Long Dim vaNew As Variant vaValues = Sheet1.UsedRange.Resize(2 ^ 16 + 1).Value For i = LBound(vaValues, 1) To UBound(vaValues, 1) vaNew = Application.WorksheetFunction.Index(vaValues, i, 0) Stop Next i End Sub |
A little experimenting and I discovered that if my range is 65,537 rows long, I can’t pass it into Index. By the way, there does not appear to be any such limitation when the function is called from a worksheet cell, e.g. =COUNTA(INDEX(A2:G1007425,1,0)).
You may have noticed that the limit is the same number of rows in a 97-2003 file format. I wonder if that’s related. Am I missing something? Am I doing something wrong?
The row limitation you encountered does not appear to be an issue here:
http://www.mrexcel.com/forum/excel-questions/597538-problem-application-worksheetfunction-index.html
Hmmm, it’s only when it’s an array and not a range. This fails
vaNew = Application.WorksheetFunction.Index(Sheet1.Range("A1").Resize(2 ^ 16 + 1, 7).Value, 1, 0)this works
vaNew = Application.WorksheetFunction.Index(Sheet1.Range("A1").Resize(2 ^ 16 + 1, 7), 1, 0)But I assume that means I'm reading in 66,000 rows every time I call it.
When you pass it a range the range does not get dereferenced so you are not reading in 66000 rows – should be much more efficient than using an array:
the array version does
1) get this range reference and pass the coerced values to a variant in vba and
2) pass this variant array back to the INDEX function
The range version does
1) get this range reference but do not coerce to values and
2) pass the range reference to the INDEX function
To fix the specific problem, just don’t use index:
vaNew = vaValues(i, 1)
works and will be way quicker.
But the 65,536 row limit does appear in other contexts; for instance you can’t pass an array greater than 65,536 rows to a UDF. I also ran into the problem recently when calling a Python function from VBA (using application.run). I could pass a range of up to 2^20 rows, but an array was limited to 2^16 rows. I think it’s a problem with COM, and it’s still the same in Excel 2013.
One other thing, use .Value2, rather than .Value, it’s significantly faster (thank Charles for that one).
The specific problem is that I wanted to send each line to a join to get a unique identifier using everything on a line. Basically I was seeing how fast an all VBA implementation of Excel’s Remove Duplicates feature would go. I would slice a large array into 1d arrays so I could join them. But I couldn’t get the slicing done.
I found these boudaries:
Sub M_array_boundaries()ReDim sp(1385, Columns.Count)
sn = Application.Index(Sheet1.Cells, [row(1:585)], [transpose(row(1:16384))])
End Sub
re: “One other thing, use .Value2, rather than .Value, it’s significantly faster (thank Charles for that one).”
Why is that ?
For M. Simms:
http://fastexcel.wordpress.com/2011/11/30/text-vs-value-vs-value2-slow-text-and-how-to-avoid-it/
In short, .value2 transfers just the value, .value also transfers some other stuff, which slows it down.
I’m guessing the 65536 array limit may be due to VBA calling into xlcall32.dll, which only supports the smaller grid, as this limit applies to other worksheetfunction methods too like vlookup. You can register the function library and call a function by referencing xlcall.h.
To replicate worksheetfunction.index, run the sub code first and then try wfIndex from the sheet or VBA code:
This method can also be used to call other macro functions like SET.NAME and EVALUATE directly from the worksheet.
correction: “=1” was omitted from the code above, it should have been “Optional Arg4 = 1”.
another curiosity, from the sheet with CTRL+SHIFT+ENTER:
=COLUMNS(TRANSPOSE(ROW(A:A))) = 1048576
but from VBA:
?UBound([transpose(row(a:a))]) = 65536
I’ve been having a play with this:
I started with the code as posted, but with the stop removed, and using only 2^12 rows (4096). This was very slow:
Time for 4096 rows = 20.7 seconds.
Using .value2 instead of .value was even slower:
Time for 4096 rows = 23.4 seconds.
Using a range, instead of a variant array:
Dim vaValues as Range
Set vaValues = Sheet1.UsedRange.Resize(2 ^ 16 + 1)
Time for 4096 rows = 0.0625 seconds.
Time for 65537 rows = 0.56 seconds.
That’s 330 times faster than array+index on 4096 rows, and for 16 x more rows the time only increases by 9x.
Using a variant array with .value, but a for loop to extract each row instead of .index, and a range of 10 columns:
Time for 65537 rows = 0.14 seconds.
Using .value2 instead of .value:
Time for 65537 rows = 0.094 seconds.
With 20 columns and 65537 rows I get:
Range + index: 0.60 seconds
Variant array.value2 + loop: 0.17 seconds
So if you have enough columns using a range + Index will eventually be faster, but for a moderate number of columns and lots or rows using a variant array with .value2 and a for loop to extract each row is the way to go.
I’m going to have a look at doing it with a Python function now.
I think is because an array is an short unsigned integer and the limit for the integer is 0-65535 and if you use 2 ^ 16 + 1 the data types not match because the limit has been overridden (65537).
just delete “+1” and must to work. but i have one answer, you have examples to use this function?
index for me is only a whay to acces to the range property of the sheet. it is correct?