• Hi All

    Please note that at the Chandoo.org Forums there is Zero Tolerance to Spam

    Post Spam and you Will Be Deleted as a User

    Hui...

  • When starting a new post, to receive a quicker and more targeted answer, Please include a sample file in the initial post.

Alternative to "more func" indirect.ext

AlexWinfield

AlexWinfield

New Member
Hello all,
Been having some problems with a a function called "indirect.ext" (part of the no-longer-supported add-in suite, 'more func').
Our department is on Excel 2010, and I am looking for a compatible function that serves the same purpose.

Is anyone familiar with this?

Are there any additional details I can provide?

Thank you for your time.
 
Hi Alex, and welcome to the forum! :awesome:

There is a built-in INDIRECT function within XL. Is that that you want? Or better yet, can you describe what the indirect.ext function did?
 
Thank you for your response, Luke.
This is copied from the help menu's description:

"Returns the value of a cell or range specified by its address (text string).

Unlike the INDIRECT built-in function, INDIRECT.EXT can also return the value of a cell whose workbook is closed."

Additionally, I have copied the remainder of the page from the help menu's "syntax" portion below (in case this information is helpful)



=INDIRECT.EXT(Reference,Volatile,A1-Style,Mode)

- Reference (string) : Range reference (address or name) (see below)

- Volatile (boolean, optional) : if TRUE or omitted, the function is volatile. If FALSE, it is not volatile (hit Ctrl-Alt-F9 to update the results).

- Style (integer, optional) : used reference style.

  • 0 : A1 style (default),

  • 1 : international R1C1 style,

  • 2 : local R1C1 style (for instance, it will be L1C1 with a French Excel version).
- Mode (integer, optional) : specifies the life time of the secundary Excel instance (see below).

  • 0 : session (default),

  • 1 : recalculation,

  • 2 : call
RETURNED VALUE :
Contents of the cell or range. If this value can't be retrieved (for instance because the workbook or the sheet doesn't exist), it returns #VALUE!

"REFERENCE" ARGUMENT :
The Reference argument can be one of the following :

- Another range in the same workbook :
If the range is in the same workbook, the Reference argument should contain its address in the usual form, like "A1", "Sheet1!A1", "'Sheet 1'!A1" and so on.

- A range in another workbook (absolute path) :
The pattern of the Reference argument is : "'Drive:\Folder\[WorkbookName.xls]SheetName'!A1". Don't forget the quotes !

- A range in another workbook (relative path) :
If the workbook is in the same directory : "'[WorkbookName.xls]SheetName'!A1".

If it is in another directory, for instance the parent directory : "'..\[WorkbookName.xls]SheetName'!A1".

Beware, the path is relative to the path of the "calling workbook", not to the directory which is currently active in Excel.

- A workbook-level name :
If RangeName is a workbook-level name (not sheet-level) : "'Path\WorkbookName.xls'!RangeName (don't put the name of the workbook in []).

"MODE" ARGUMENT :
This argument specifies how frequent the secundary Excel instance should be suppressed and recreated.

INDIRECT.EXT uses another hidden Excel instance (let's name it "Excel2"), which retrieves the cell values in the closed workbook, and then returns the results to the primary Excel instance.

The Mode argument indicates when Excel2 should be created and destroyed :

  • Session (0) : created during the first call to INDIRECT.EXT, destroyed when you quit Excel or when you unload Morefunc,

  • Recalculation (1) : created by the first call to INDIRECT.EXT during a recalculation pass; destroyed after the last call to the function in the recalculation process,

  • Call (2) : created and destroyed by each call to INDIRECT.EXT.
The Session mode is the fastest, followed by Recalculation. The Call mode is of course extremely slow, but it doesn't consume permanently the memory needed by Excel2 and leaves always a "clean" system.

REMARKS :
If the Volatile argument is TRUE (or omitted), the returned values are automatically updated when the workbook is opened. Notice that the function takes about 0.005 second to get the value of a cell in a closed workbook (which is relatively slow for a worksheet function), therefore it should'nt be used in too many cells.

If Volatile = FALSE, the returned values are not automatically updated. If you want to update them, press Ctrl+Alt+F9.

The calculations speed is significantly improved if you use INDIRECT.EXT in an array formula instead of separate non-array formulae.

EXAMPLES :
=INDIRECT.EXT("'C:\[My workbook.xls]Table 5'!E34,FALSE)

returns the value of the cell "Table 5!E34" of the workbook "C:\My workbook.xls". This value is not automatically updated, unless you change the argument or press Ctrl-Alt-F9.

=INDIRECT.EXT("'C:\My Documents\[Workbook5.xls]Sheet1'!A9")

Returns the value of the cell Sheet1!A9 of the workbook "My Documents\Workbook5.xls". This value is automatically updated (volatile function).

=INDIRECT.EXT("'..\Temp\Test\[MyWorkbook.xls]Sheet1'!A1") returns the value of cell Sheet1!A1 in the workbook "MyWorkbook.xls" located in the parent directory.

{=INDIRECT.EXT("'[Book2.xls]Sheet1'!A1:A10")} returns the values (array) of the range Sheet1!A1:A10 in the workbook "Book2.xls" located in the same directory.
 
Hi everyone, I have the same problem here as AlexWinfield, but unfortunately Luke M's link no longer works. Does anyone know what the linked UDF looked like? Thank you very much in advance!
 
I don't have the INDIRECT.EXT code anymore, but here's one for "pull" that might work?

Code: 
'----- begin VBA -----
Function pull(xref As String) As Variant
'inspired by Bob Phillips and Laurent Longre
'but written by Harlan Grove
'-----------------------------------------------------------------
'Copyright (c) 2003 Harlan Grove.
'
'This code is free software; you can redistribute it and/or modify
'it under the terms of the GNU General Public License as published
'by the Free Software Foundation; either version 2 of the License,
'or (at your option) any later version.
'-----------------------------------------------------------------
'2004-05-28
'fixed the previous fix - replaced all instances of 'expr' with 'xref'
'also now checking for initial single quote in xref, and if found
'advancing past it to get the full pathname [dumb, really dumb!]
'-----------------------------------------------------------------
'2004-03-25
'revised to check if filename in xref exists - if it does, proceed;
'otherwise, return a #REF! error immediately - this avoids Excel
'displaying dialogs when the referenced file doesn't exist
'-----------------------------------------------------------------

Dim xlapp As Object, xlwb As Workbook
Dim b As String, r As Range, C As Range, n As Long

'** begin 2004-05-28 changes **
'** begin 2004-03-25 changes **
n = InStrRev(Len(xref), xref, "\")

If n > 0 Then
If Mid(xref, n, 2) = "\[" Then
b = Left(xref, n)
n = InStr(n + 2, xref, "]") - n - 2
If n > 0 Then b = b & Mid(xref, Len(b) + 2, n)

Else
n = InStrRev(Len(xref), xref, "!")
If n > 0 Then b = Left(xref, n - 1)

End If

'** key 2004-05-28 addition **
If Left(b, 1) = "'" Then b = Mid(b, 2)

On Error Resume Next
If n > 0 Then If Dir(b) = "" Then n = 0
Err.Clear
On Error GoTo 0

End If

If n <= 0 Then
pull = CVErr(xlErrRef)
Exit Function
End If
'** end 2004-03-25 changes **
'** end 2004-05-28 changes **

pull = Evaluate(xref)

If CStr(pull) = CStr(CVErr(xlErrRef)) Then
On Error GoTo CleanUp 'immediate clean-up at this point

Set xlapp = CreateObject("Excel.Application")
Set xlwb = xlapp.Workbooks.Add 'needed by .ExecuteExcel4Macro

On Error Resume Next 'now clean-up can wait

n = InStr(InStr(1, xref, "]") + 1, xref, "!")
b = Mid(xref, 1, n)

Set r = xlwb.Sheets(1).Range(Mid(xref, n + 1))

If r Is Nothing Then
pull = xlapp.ExecuteExcel4Macro(xref)

Else
For Each C In r
C.Value = xlapp.ExecuteExcel4Macro(b & C.Address(1, 1, xlR1C1))
Next C

pull = r.Value

End If

CleanUp:
If Not xlwb Is Nothing Then xlwb.Close 0
If Not xlapp Is Nothing Then xlapp.Quit
Set xlapp = Nothing

End If

End Function
'----- end VBA -----
 
You must log in or register to reply here.
Back
Top