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