VBA application.index Array() in rows doesn't work

Question

I am trying to use the Application.Index function in excel VBA.

I currently have an array(4,11139) that I am trying to splice into two separate arrays one containing the first 2 rows (2,11139) and the other containing the 3rd/4th rows (2, 11139). Various searches have suggested this code is the correct method but it's not working properly: (I've condensed the code to what is relevant)

Dim Adjusted_Data_Array() As Single
Dim Final_Data_Array() As Variant
Dim Sys1_Data_Array() As Variant
Dim Sys2_Data_Array() As Variant
'Function that creates a 4,11139 variant array
Final_Data_Array = Strip_Erroneous(Adjusted_Data_Array, 1)
'Splice first two rows
Sys1_Data_Array = Application.Index(Final_Data_Array, Array(1, 2), 0)
'splice 3/4th rows
Sys2_Data_Array = Application.Index(Final_Data_Array, Array(3, 4), 0)

So I should now have 2 arrays each containing half the data from the first array. But all I get in the new arrays is a one dimensional array with two rows. Like this:

Screenshot of the watches from the 3 arrays discussed above.

I've tried it with the Array(1,2) in the columns and that doesn't work either.

I can solve the issue in this regard by simply splitting into 4 separate one dimensional arrays (that works fine, it's the Array(1,2) or Array(3,4) that doesn't seem to. But I know in a future work coming up I'll be needing to splice much bigger arrays so would like to understand why it's not working ready for that. Big thanks in advance.

Edit: Tried using:

Sys1_Data_Array = Application.Index(Final_Data_Array, Application.Transpose(Array(1, 2)), 0)

as per charles suggestion below but end up with the array's simply switched indices as shown:

Screenshot of watches from transpose(array(1,2) attempt

I do not believe the INDEX function can accept an array as an argument for row_num or column_num. If one of them is zero; the other has to point to a single row/column. — Ron Rosenfeld
– Ron Rosenfeld, Commented Mar 8, 2017 at 13:29
Thanks Ron I was starting to wonder that, I'm not sure how to explain this though: usefulgyaan.wordpress.com/2013/06/12/… then, and there are many more examples out there of people instructing others to use it in that form. — Matt Carter
– Matt Carter, Commented Mar 8, 2017 at 13:34
In the comments are a number of users who were unable to get the example in the article to work, when using an array argument for row_num. Also note that if you use the fully qualified application.worksheetfunction.index... you will get a type mis-match when you use the Array(1,2) argument. — Ron Rosenfeld
– Ron Rosenfeld, Commented Mar 8, 2017 at 13:50
In any event, its probably more efficient to do this using VBA array loops which would only require one copy operation rather than passing a copy of the array to Index and then Index passing a subset copy back. — Charles Williams
– Charles Williams, Commented Mar 8, 2017 at 13:57
as a side note, the most efficient is to not copy the array at all and just use values in the original array. Index can't be used to return more than one row/column AFAIK — Slai
– Slai, Commented Mar 8, 2017 at 14:43

Ryan Wildry · Accepted Answer · 2017-03-08 18:25:08Z

1

You can use the INDEX approach if you wrap the ARRAY inside a TRANSPOSE

var1 = Application.Index(varData, Application.Transpose(Array(1, 2)), 0)
var2 = Application.Index(varData, Application.Transpose(Array(3, 4)), 0)

Apologies: actually this does not work!

But its about 2.5 times slower than just copying the relevant parts of the array using VBA.

For k = 1 To UBound(varData, 2)
    For j = 1 To 2
        var1(j, k) = varData(j, k)
        var2(j, k) = varData(j + 2, k)
    Next 
Next

edited Mar 8, 2017 at 18:25

Ryan Wildry

5,6971 gold badge17 silver badges36 bronze badges

answered Mar 8, 2017 at 14:11

Charles Williams

23.6k5 gold badges40 silver badges39 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

Matt Carter Over a year ago

Thanks Charles, still not working properly though. I now have a 2,1 array rather than the 1,2 that was there before. Edited original post above to show it. Looks like I'm writing a function with loops to do it so my code stays nice and neat.

Docmarti · Accepted Answer · 2017-04-25 03:46:45Z

1

TheAddress = "row(1:2)"
TheRows = Application.Evaluate(TheAddress) 'must be a 2 dimensions array 
TheColumns = Array(1, 2, 3)'must be a 1 dimension array 
Sys1_Data_Array = Application.Index(Final_Data_Array, TheRows, TheColumns) 
Cells(9, 1).Resize(UBound(Sys1_Data_Array), UBound(Sys1_Data_Array, 2)) = Sys1_Data_Array

answered Apr 25, 2017 at 3:46

Docmarti

3862 silver badges6 bronze badges

Collectives™ on Stack Overflow

VBA application.index Array() in rows doesn't work

Screenshot of the watches from the 3 arrays discussed above.

Screenshot of watches from transpose(array(1,2) attempt

2 Answers 2

1 Comment

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

Screenshot of the watches from the 3 arrays discussed above.

Screenshot of watches from transpose(array(1,2) attempt

2 Answers 2

1 Comment

Comments

Your Answer

Sign up or log in

Post as a guest

Related