0

I am using Microsoft 365 Excel. In VBA, WorksheetFunction.Index(arr, rows, 1) doesn't work while Application.Index(arr, rows, 1) works, however, WorksheetFunction.Index(arr, 1, columns) works fine, where columns and rows are arrays.

I am working on these operations with WorksheetFunction.TextJoin(...).

(1) array of rows: working - resulting delimiter separated list where rows are row positions of arr:

result = WorksheetFunction.TextJoin(Delimiter, True, Application.Index(arr, rows, 1))

'' Not Working - resulting 0 where I expect a delimiter separated list:

result = WorksheetFunction.TextJoin(Delimiter, True, WorksheetFunction.Index(arr, rows, 1))

WorksheetFunction.Index(arr, rows, 1) in the code above results in nothing while Application.Index(arr, rows, 1) results in an array of rows-indicated values in arr.

(2) array of columns: working

result = WorksheetFunction.TextJoin(Delimiter, True, WorksheetFunction.Index(arr, 1, columns))

I am looking for a consistent and robust solution and technical explanations if possible. What's the clear distinction among these 3 approaches, Application.Index(), WorksheetFunction.Index(), and Applicaiton.WorksheetFunction.Index()? WorksheetFunction.Index(Arr, rows, 1) doesn't work but WorksheetFunction.Index(Arr, 1, columns) works fine in the VBA. Why? For consistency and robustness, should I keep Application.Index() approach? Is this the choice of Microsoft? Are there any technical documents related to this matter? I appreciate any technical comments.

monkeyquant
  • 1
  • 3
  • 2
    Can you please provide a [mcve], which would include a enough code to reproduce. create a small hard coded array and values for the row and column that does what you are experiencing so we do not need to create something that is not the same as you have. – Scott Craner Jan 06 '23 at 16:10
  • 1
    `WorksheetFunction.SomeFunction` typically throws a run-time error if the evaluation of `SomeFunction` results in an error, whereas `Application.Somefunction` *returns* an error value. Other than that, you've not provided enough info for us to replicate the problem, or told us exactly what happens when it fails. – Tim Williams Jan 06 '23 at 16:30
  • See https://stackoverflow.com/questions/27302794/application-match-gives-type-mismatch – FunThomas Jan 06 '23 at 16:43
  • 2
    I disagree with the closing of this as a duplicate. There are other reasons beside error that Application.Index will work while WorkSheetFunction.Index will not. We do not have enough information to know why it is failing. And beside Match and Vlookup on those links are failing because the matches were not found Index does not look for matches. So those links are not duplicates here. I am not voting to reopen, yet, because it should be closed as needing more information and if the OP will [edit] the post to include a [mcve] I will vote to reopen. – Scott Craner Jan 06 '23 at 16:51

0 Answers0