Xtreme Visual Basic Talk

Xtreme Visual Basic Talk (http://www.xtremevbtalk.com/)
-   Excel (http://www.xtremevbtalk.com/excel/)
-   -   IndirectEx() - my INDIRECT() for closed workbooks (http://www.xtremevbtalk.com/excel/312473-indirectex-indirect-closed-workbooks.html)

wilsonso 12-07-2009 02:33 AM

IndirectEx() - my INDIRECT() for closed workbooks
 
1 Attachment(s)
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


serrr88 05-19-2012 07:37 AM

thanks, it works! :)

Ciraxis 08-03-2012 10:14 AM

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?

BoneyD 09-30-2014 07:51 AM

#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.

derekk 10-12-2017 04:19 AM

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?

badonkadonk 11-02-2017 04:18 PM

Quote:

Originally Posted by derekk (Post 1411229)
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?

Same! Would love for this to work


All times are GMT -6. The time now is 11:42 AM.

Powered by vBulletin® Version 3.8.9
Copyright ©2000 - 2017, vBulletin Solutions, Inc.
Search Engine Optimisation provided by DragonByte SEO v2.0.15 (Lite) - vBulletin Mods & Addons Copyright © 2017 DragonByte Technologies Ltd.
All site content is protected by the Digital Millenium Act of 1998. Copyright©2001-2011 MAS Media Inc. and Extreme Visual Basic Forum. All rights reserved.
You may not copy or reproduce any portion of this site without written consent.