Eric,
With what you know now, I think the very first Function you posted in this
discussion should work if you just change the type definition for the
function. Something like

Function TheValue(thePath As String, _
WorkbookName As String, theSheet, cellAddr As String) As Variant
Application.DisplayAlerts = False
Application.ScreenUpdating = False
Worksheets.Add
Range("A1").Formula = "='" & thePath & _
"\[" & WorkbookName & "]" & theSheet & _
"'!" & cellAddr
TheValue = Range("A1").Value
ActiveSheet.Delete
Application.ScreenUpdating = True
Application.DisplayAlerts = True
End Function

By declaring the function as type Variant, it will allow it to return both
numbers and strings correctly. If you know that the cell in the other
workbook is always going to be a number, you could set it to the specific
number type: Single, Long, Double, etc. Then in the worksheets, you call it
using
=TheValue("E:\dir","file.xls","Table","C3")
or you can substitute cell addresses for the 4 parameters if those are
somewhere in the workbook or on that sheet. Example (the " " are just used
to show what would be in these cells, the double-quotes wouldn't actually be
in them):
A1 = "E:\dir"
A2 = "file.xls"
A3 = "Table"
A4 = "C3"
Then you could put this in another cell to get the result from the other
workbook:
=TheValue(A1,A2,A3,A4)

"Eric" wrote:

Thank everyone for suggestions
The function TheValue returns string and store in y variable
Does anyone know how to convert this string [path] in y variable into a
number for comparsion? If I can get the number from this path, then my
problem will be solved.
Thank everyone for any suggestions
Eric

"JLatham" wrote:

Things are working as coded, the problem is that the coding is not doing what
you want it to. Your code
TheValue = "[" & WorkbookName & "]" & Sheet & "'!" & Addr
returns a string that would be suitable for use as a formula in a cell on a
worksheet, if it had an = symbol in front of it. Plus you left the path out
of the string build up.

You need to go back to your original posting and set that up the way you had
things there, as:
Range("A1").Formula = "='" & Path & "\[" & WorkbookName & "]" & Sheet &
"'!" & Addr


"Eric" wrote:

Thank everyone for suggestions.

I open the file E:\dir\file.xls, under the table sheet, cell C3 is 5, and
cell C4 is 3.
When I run following codes, the MsgBox displays [file.xls]Table!C4 instead
of the value from this cell 3.
Could anyone give me any suggestions on how to fix it?
Thank everyone very much for any suggestions
Eric

Sub Test
x = TheValue("E:\dir", "file.xls", "Table", "C3")
y = TheValue("E:\dir", "file.xls", "Table", "C4")
If x = y Then
MsgBox ("The value was " & y)
End If
End Sub

Function TheValue(Path, WorkbookName, Sheet, Addr) As String
TheValue = "[" & WorkbookName & "]" & Sheet & "'!" & Addr
End Function

"Halim" wrote:

Hi,

I think you wish to make a UDF, just try the simple one

Function TheValue(Path, WorkbookName, Sheet, Addr) As String
TheValue = Path & "\[" & WorkbookName & "]" & Sheet & "'!" & Addr
End Function


--

Regards,

Halim


"Eric" wrote:

For the following function, does anyone have any suggestion on how to use
Addr instead of "A1" as an input? I try to replace "A1" with Addr,
but this does not work. Could anyone please give me any suggestions?
Thank in advance for any suggestions
Eric

Function TheValue(Path, WorkbookName, Sheet, Addr) As String
Application.DisplayAlerts = False
Application.ScreenUpdating = False
Worksheets.Add
Range("A1").Formula = "='" & Path & "\[" & WorkbookName & "]" & Sheet &
"'!" & Addr
TheValue = Range("A1").Value
ActiveSheet.Delete
Application.ScreenUpdating = True
End Function