Eric,

If your workbook (file.xls) is already opened, you don't really need to pass
the path, so here is the modified function.

Function TheValue(WorkbookName, Sheet, Addr) As Variant
Dim cell As Range

Set cell = Workbooks(WorkbookName).Worksheets(Sheet).Range(Ad dr)
TheValue = cell.Value
End Function

HTH.

Cheers,
Socratis
"Eric" wrote:

The following function returns formula as a string, does anyone have any
suggestions on how to retrieve the value from formula and store into x and y
variable?

Working with an opened file under E:\dir\file.xls, I try to compare the
value under worksheet Table C3 & C4 for if statement.
Does anyone have any suggestions on how to make the code work?
Thank in advance 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

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

End Sub