Referencing another worksheet

Yep, that code works properly for my single cell refence test, but when I try
to use it for my other formulae it sends excel a bit loopy!

The string i'm referencing with PULL() is a a reference to an array of cells
of size 2Rx200C. The PULL function is itself used in a formula involving IF,
VLOOKUP, INDEX amongst others. It also seems to work if I do find 'INDIRECT'
replace 'PULL' for one cell, but if I do replace all it freezes on me. I
think I'll go ahead with Arvi's mirrored sheets; doubles my file size but i
know it works for my sheet.

Thanks for the code and the advice Bob, PULL() may yet prove to be useful!

"Bob Phillips" wrote:

I just tried it and I see that the version on Harlan's site is not the
latest version. I attach the latest version here.

You need to be very careful in defining the cell data, along the lines of

'C:\[workbook.xls]sheetname'!cell_ref

be especially careful with the leading ', you will probably need two, '', as
one just tells Excel it is text.

'----- 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-30
'still more fixes, this time to address apparent differences between
'XL8/97 and later versions. Specifically, fixed the InStrRev call,
'which is fubar in later versions and was using my own hacked version
'under XL8/97 which was using the wrong argument syntax. Also either
'XL8/97 didn't choke on CStr(pull) called when pull referred to an
'array while later versions do, or I never tested the 2004-03-25 fix
'against multiple cell references.
'-----------------------------------------------------------------
'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-30 changes **
'** begin 2004-05-28 changes **
'** begin 2004-03-25 changes **
n = InStrRev(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)

'** key 2004-05-30 addition **
If IsArray(pull) Then Exit Function
'** end 2004-05-30 changes **

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



--
HTH

Bob Phillips

"rmellison" wrote in message
...
My VBA knowledge is very limited (usually to the record/stop button on the
macro toolbar). I have copied the code to a new module in my workbook, and
the pull function appears in my UDF list. As a way of a test, I am just
trying to reference one cell in a closed workbook using a string in A1,
and
=pull(a1) in another cell. Howewer, all i get is #value. Have tried with
an
open workbook, with and without the file path, with & without
apostrophes....
Any suggestions?

I get the feeling I'm over-complicating things now, but i've got this
far....

"Bob Phillips" wrote:


"Arvi Laanemets" wrote in message
...

b) Some frequent resident of Excel NG's (I don't remember who) has an
UDF
or
Add-In on his site to download, which is an equivalent for INDIRECT,
but
works with closed workbooks too. But when you want to design a
workbook
for
use by several users, all of them must have it in their computers -
which
can be a problem.

That would be Harlan Grove who wrote a UDF called PULL.

You can find the function at his FTP site:
ftp://members.aol.com/hrlngrv/
Look for pull.zip