Equivalent of INDIRECT for a closed file WITHOUT MACRO

'------------------------------------
'Extended INDIRECT Function v1.0
'------------------------------------
'Copyright (c) 2009 Wilson So.
'E-mail: ***@***
'------------------------------------
'Credits:
'- Designed and written by Wilson So.
'- The 'CreateObject("Excel.Application")' trick was inspired by Harlan Grove's PULL function source code.
'------------------------------------
'This is an open source. You can freely redistribute and modify it, but please kindly give credit to the contributors.
'Please also kindly report any bugs/suggestions through e-mail or in the forums where I posted it.
'------------------------------------
'How to use:
'- Basically same as INDIRECT() in Excel - the same concept for the ref_text parameter.
'- To update the static memory for a particular reference,
' type TRUE in the second parameter (just one of the IndirectEx() containing that reference)
' and calculate it once.
'------------------------------------
'Features:
'- You can refer to the closed workbook data.
'- The retrieved closed workbook data will be stored in the static memory,
' so the next time, the closed workbook will not be opened again for fast retrieve.
'- A range instead of an array will be returned if the path is omitted in the ref_text,
' so it still works fine if the user refers to an enormous array, e.g. "Sheet1!1:65536".
'- You can use it inside INDEX(), VLOOKUP(), MATCH() etc.
'- You can use it with OFFSET(), but only for opened workbook data.
'- The procedure will not blindly retrieve all the data as requested;
' it will not retrieve data beyond the "Ctrl + End" cell, in order to keep the memory as small as possible.
'- #NUM! will be returned in case of lack of memory.
'- #REF! will be returned in case of a wrong path.
'- #VALUE! will be returned in case of other errors.
'------------------------------------
'Known issues:
'- Due to the use of SpecialCells(), #VALUE! will be returned if the worksheet for a closed workbook is protected.
'------------------------------------

Function IndirectEx(ref_text As String, Optional refresh_memory As Boolean = False) As Variant
 On Error GoTo ClearObject

 Dim RefName As String
 Dim SheetName As String
 Dim WBName As String
 Dim FolderName As String
 
 Dim vExcel As Object
 Dim vWB As Workbook
 
 Static dbOutput() As Variant
 Static dbKey() As String
 Static dbTotalOutput As Integer
 Dim dbIndex As Integer
 
 Dim UserEndRow As Long, UserEndCol As Integer
 Dim RealEndRow As Long, RealEndCol As Integer
 Dim EndRow As Long, EndCol As Integer
 Dim RangeHeight As Long, RangeWidth As Integer
 
 GetNames ref_text, RefName, SheetName, WBName, FolderName
 
 If dbTotalOutput = 0 Then
 ReDim dbOutput(1 To 1) As Variant
 ReDim dbKey(1 To 1) As String
 End If
 
 For i = 1 To dbTotalOutput
 If dbKey(i) = FolderName & WBName & "!" & SheetName & "!" & RefName Then
 dbIndex = i
 End If
 Next
 
 If dbIndex = 0 Or refresh_memory Then
 If dbIndex = 0 Then
 dbTotalOutput = dbTotalOutput + 1
 dbIndex = dbTotalOutput
 ReDim Preserve dbOutput(1 To dbTotalOutput) As Variant
 ReDim Preserve dbKey(1 To dbTotalOutput) As String
 dbKey(dbIndex) = FolderName & WBName & "!" & SheetName & "!" & RefName
 End If
 If FolderName = "" Then
 Set dbOutput(dbIndex) = Workbooks(WBName).Worksheets(SheetName).Range(RefName)
 ElseIf Dir(FolderName & WBName) <> "" Then
 Set vExcel = CreateObject("Excel.Application")
 Set vWB = vExcel.Workbooks.Open(FolderName & WBName)
 With vWB.Sheets(SheetName)
 On Error GoTo ClearObject
 UserEndRow = .Range(RefName).Row + .Range(RefName).Rows.Count - 1
 UserEndCol = .Range(RefName).Column + .Range(RefName).Columns.Count - 1
 RealEndRow = .Range("A1").SpecialCells(xlCellTypeLastCell).Row
 RealEndCol = .Range("A1").SpecialCells(xlCellTypeLastCell).Column
 EndRow = IIf(UserEndRow < RealEndRow, UserEndRow, RealEndRow)
 EndCol = IIf(UserEndCol < RealEndCol, UserEndCol, RealEndCol)
 RangeHeight = EndRow - .Range(RefName).Row + 1
 RangeWidth = EndCol - .Range(RefName).Column + 1
 On Error Resume Next
 dbOutput(dbIndex) = .Range(RefName).Resize(RangeHeight, RangeWidth).Value
 If Err.Number <> 0 Then
 IndirectEx = CVErr(xlErrNum)
 GoTo ClearObject
 End If
 End With
 On Error GoTo ClearObject
 vWB.Close False
 vExcel.Quit
 Set vExcel = Nothing
 Else
 IndirectEx = CVErr(xlErrRef)
 Exit Function
 End If
 End If
 
 If TypeOf dbOutput(dbIndex) Is Range Then
 Set IndirectEx = dbOutput(dbIndex)
 Else
 IndirectEx = dbOutput(dbIndex)
 End If

 Exit Function
 
ClearObject:
 On Error Resume Next
 If Not (vExcel Is Nothing) Then
 vWB.Close False
 vExcel.Quit
 Set vExcel = Nothing
 End If
End Function

Private Sub GetNames(ByVal ref_text As String, ByRef RefName As String, ByRef SheetName As String, ByRef WBName As String, ByRef FolderName As String)
 Dim P_e As Integer
 Dim P_b1 As Integer
 Dim P_b2 As Integer
 Dim P_s As Integer
 
 P_e = InStr(1, ref_text, "!")
 P_b1 = InStr(1, ref_text, "[")
 P_b2 = InStr(1, ref_text, "]")
 P_s = InStr(1, ref_text, ":\")
 
 If P_e = 0 Then
 RefName = ref_text
 Else
 RefName = Right$(ref_text, Len(ref_text) - P_e)
 End If
 RefName = Replace$(RefName, "$", "")
 
 If P_e = 0 Then
 SheetName = Application.Caller.Parent.Name
 ElseIf P_b1 = 0 Then
 SheetName = Left$(ref_text, P_e - 1)
 Else
 SheetName = Mid$(ref_text, P_b2 + 1, P_e - P_b2 - 1)
 End If
 SheetName = Replace$(SheetName, "'", "")
 
 If P_b1 = 0 Then
 WBName = Application.Caller.Parent.Parent.Name
 Else
 WBName = Mid$(ref_text, P_b1 + 1, P_b2 - P_b1 - 1)
 End If
 
 If P_s = 0 Then
 FolderName = ""
 Else
 FolderName = Left$(ref_text, P_b1 - 1)
 End If
 If Left$(FolderName, 1) = "'" Then FolderName = Right$(FolderName, Len(FolderName) - 1)
End Sub