IndirectEx() - my INDIRECT() for closed workbooks
IndirectEx() - my INDIRECT() for closed workbooks
IndirectEx() - my INDIRECT() for closed workbooks
IndirectEx() - my INDIRECT() for closed workbooks
IndirectEx() - my INDIRECT() for closed workbooks
IndirectEx() - my INDIRECT() for closed workbooks IndirectEx() - my INDIRECT() for closed workbooks IndirectEx() - my INDIRECT() for closed workbooks IndirectEx() - my INDIRECT() for closed workbooks IndirectEx() - my INDIRECT() for closed workbooks IndirectEx() - my INDIRECT() for closed workbooks IndirectEx() - my INDIRECT() for closed workbooks IndirectEx() - my INDIRECT() for closed workbooks
IndirectEx() - my INDIRECT() for closed workbooks IndirectEx() - my INDIRECT() for closed workbooks
IndirectEx() - my INDIRECT() for closed workbooks
Go Back  Xtreme Visual Basic Talk > > > > IndirectEx() - my INDIRECT() for closed workbooks


Reply
 
Thread Tools Display Modes
  #1  
Old 12-07-2009, 01:33 AM
wilsonso wilsonso is offline
Newcomer
 
Join Date: Sep 2009
Posts: 11
Default IndirectEx() - my INDIRECT() for closed workbooks


This is my version of INDIRECT().

It can retrieve data from a closed workbook, and be used with VLOOKUP(), INDEX, OFFSET() etc.

On top of that, it is much faster than PULL and INDIRECT.EXT.

For more details, please refer to the source code.

Enjoy and comment! =)

Code:
'------------------------------------
'Extended INDIRECT Function v1.0
'------------------------------------
'Copyright (c) 2009 Wilson So.
'E-mail: shwskm@yahoo.com.hk
'------------------------------------
'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 contributers.
'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 in 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
Attached Files
File Type: bas Indirect_Extended.bas (6.5 KB, 171 views)
Reply With Quote
  #2  
Old 05-19-2012, 06:37 AM
serrr88 serrr88 is offline
Newcomer
 
Join Date: May 2012
Posts: 1
Default

thanks, it works!
Reply With Quote
  #3  
Old 08-03-2012, 09:14 AM
Ciraxis Ciraxis is offline
Newcomer
 
Join Date: Aug 2012
Posts: 1
Default IndirectEx and Small function

this is awesome, I'm trying to get it to work with "small" but it doesn't seem to want to do it. Do you know if it should be working?
Reply With Quote
  #4  
Old 09-30-2014, 06:51 AM
BoneyD BoneyD is offline
Newcomer
 
Join Date: Sep 2014
Posts: 1
Default #N/A errors aplenty

Is the sytax for this supposed to be identical to that used in the standard Indirect function?

Adding the "Ex" to existing indirect formulas results in nothing but #N/A errors for me.
Reply With Quote
  #5  
Old 10-12-2017, 03:19 AM
derekk derekk is offline
Newcomer
 
Join Date: Oct 2017
Posts: 1
Default

trying to resume this thread
I'm trying to use this code, but it doesn't work form me. It returns always 0.
What's the wrong?
Reply With Quote
Reply


Currently Active Users Viewing This Thread: 1 (0 members and 1 guests)
 
Thread Tools
Display Modes

Posting Rules
You may not post new threads
You may not post replies
You may not post attachments
You may not edit your posts

BB code is On
Smilies are On
[IMG] code is Off
HTML code is Off

Forum Jump

Advertisement:





Free Publications
The ASP.NET 2.0 Anthology
101 Essential Tips, Tricks & Hacks - Free 156 Page Preview. Learn the most practical features and best approaches for ASP.NET.
subscribe
Programmers Heaven C# School Book -Free 338 Page eBook
The Programmers Heaven C# School book covers the .NET framework and the C# language.
subscribe
Build Your Own ASP.NET 3.5 Web Site Using C# & VB, 3rd Edition - Free 219 Page Preview!
This comprehensive step-by-step guide will help get your database-driven ASP.NET web site up and running in no time..
subscribe
IndirectEx() - my INDIRECT() for closed workbooks
IndirectEx() - my INDIRECT() for closed workbooks
IndirectEx() - my INDIRECT() for closed workbooks IndirectEx() - my INDIRECT() for closed workbooks
IndirectEx() - my INDIRECT() for closed workbooks
IndirectEx() - my INDIRECT() for closed workbooks
IndirectEx() - my INDIRECT() for closed workbooks IndirectEx() - my INDIRECT() for closed workbooks IndirectEx() - my INDIRECT() for closed workbooks IndirectEx() - my INDIRECT() for closed workbooks IndirectEx() - my INDIRECT() for closed workbooks IndirectEx() - my INDIRECT() for closed workbooks IndirectEx() - my INDIRECT() for closed workbooks
IndirectEx() - my INDIRECT() for closed workbooks
IndirectEx() - my INDIRECT() for closed workbooks
 
IndirectEx() - my INDIRECT() for closed workbooks
IndirectEx() - my INDIRECT() for closed workbooks
 
-->