I use ExecuteExcel4Macro function with index function since index can retrieve data from a closed workbook without the need of openning it. But, I want a dynamic function which retrieves the file path, the workbook name, sheet name and range info from a bunch of cells to get the values from the closed workbook.

I have the function to get values from a closed workbook which works well with a single cell. But to get values of a range I could only get the values by looping. Here is the part of the code:

If xlapp Is Nothing Then
    'Object must be created only once and not at each function call
    Set xlapp = CreateObject("Excel.application")
End If

rng = "R1C1:R3C1"
f_path = "'" & file_name & "[" & book_name & "]" & sheet_name & "'!" & rng

ReDim list_1(1 To rows_N, 1 To columns_N)
For i = 1 To rows_N
   For j = 1 To columns_N
      list_1(i, j) = xlapp.ExecuteExcel4Macro("INDEX(" & f_path & "," & i & "," & j & ")")
   Next j
Next i

The above code works fine while looping since it gets the values of one cell in each looping line.
Rather than this, I want to make the loop part like:

list_1 = xlapp.ExecuteExcel4Macro("INDEX(" & f_path & ", 0 , 0)")

to get all range without looping (due to being slow when a big range is selected). But this code always returns a single value that corresponds to the 2nd row and 2nd column item (if only 1 row/column then results as 2nd column/row item) which I don’t know why. Is there a way to run this without any loop?