Problem using .Index in Array

[thekeel]

I am having a strange issue when using .index with an array in vba.

I am using ado to obtain a recordset from a closed workbook. This is a function that I have defined that returns an array to the main sub. The array is then transposed into another array to restructure into the row, col format. Everything works fine up until i try to utilize the .Index function.

When i try to use the Application.Index function, it never returns a value from the transposed array.

What I have done to work around this is to copy the transposed array into a worksheet, then copy the range values into yet another array. When doing this, I am able to fully utilize the .Index function.

So the index function will NOT work unless I copy the data to a worksheet, then back into an array.

Does this sound right?

[Leith Ross]

Hello thekeel,

Welcome to the Forum!

In my experience, it would be better to use the WorksheetFunction.Index method in VBA. The Application.Function calls are not supported by VBA. You can invoke them but the object model only supports the WorksheetFunction method. Try using this and let me know the results.

[snb]

As far as I know application.index and worksheetfunction.index are the same (like cells(1,1), range("A1") and [A1] are the same).

Sub tst()
  sn = Range("A1:F10")
  MsgBox Application.Index(sn, 3, 4)
  MsgBox WorksheetFunction.Index(sn, 3, 4)
End Sub

[thekeel]

Hello thekeel,

Welcome to the Forum!

In my experience, it would be better to use the WorksheetFunction.Index method in VBA. The Application.Function calls are not supported by VBA. You can invoke them but the object model only supports the WorksheetFunction method. Try using this and let me know the results.

Thanks for your help.

I have tried using WorksheetFunction with the same results. Its really odd, when I load the array from a RecordSet, neither .Index or .Match will work.

However, if I load the array from a worksheet, both functions work fine.

[Leith Ross]

Hello thekeel,

I am not an SQL expert but I would think that an SQL array would not be any different from a VBA array. I have used the WorksheetFunction.Index call many times on arrays in VBA with no problem.

It could be failing because the array you are returning from the record set is a 1-D array. Arrays returned from worksheets are always 2-D.