Hello Everyone,

The help I received last month in this thread got me going. I am
resurrecting the thread now because I want to do something different
now, and again I can't figure out how to do it.

What I want to do different is store values in one .xls file and use
the values in a different .xls file. I will give the user a way to
indicate the path and filename of the .xls file to open. Different
data (roadway profiles) will be stored on different worksheets of the
same format, so the user will pass in the worksheet name for the
function to reference.

My problem is, when I do what I guess I ought to do, something happens
and the watch box says everything is out of context. Also, the debug
mode of VBA stops showing the current line of code with a yellow
background. Here is the key new code:

Function computeEL2(profileName As String, station As Double) As
Double
Application.Volatile
' ... snip ...

profilesFile = "Profiles.xls" ' Breakpoint is set
here. Executes okay
Workbooks.Open Filename:=profilesFile ' Executes okay
Set wb = Workbooks(profilesFile) ' Problem happens when I
execute this line
'Set ws = Application.Caller.Parent
Set ws = wb.Sheets(profileName)

I suppose the problem line is working, but that is changing the active
workbook, hence the problem. Can someone advise me on this? I think
my real question is, how do I read and use values in cells in a
different workbook?

Thanks in advance.

- Paul

On Jun 25, 12:03*pm, "Peter T" <peter_t@discussions wrote:
In the snippet of code below each use of Range(address) will refer to
cell(s) on the active sheet. If you always want to refer to cells on the
same sheet as the formula cell that called the UDF try something like this -

Function computeEL(station As Double) As Double
Dim ws As Worksheet
Set ws = Application.Caller.Parent
' code

x = ws.Range("A1").Value