How to retrieve the value from formula?

[Eric]

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

 

[Toppers]

try:

Function TheValue(Path, WorkbookName, Sheet, Addr) As String
Dim x As Workbook
On Error Resume Next
Set x = Workbooks(WorkbookName)
If Err <> 0 Then
Workbooks.Open Filename:=Path & WorkbookName
End If
TheValue = Workbooks(WorkbookName).Sheets(Sheet).Range(Addr).Value
End Function

 

[Mike H]

Eric,

Try this:-

Sub Sistence()
Dim sourcewb As Workbook
Set sourcewb = Workbooks.Open("C:\book2.xls", False, False) '<===Change to
your path
x = sourcewb.Sheets("table").Range("C3").Value
y = sourcewb.Sheets("table").Range("C4").Value
If x >= y Then
MsgBox ("The value was " & y)
End If
End Sub

 

[Eric]

Thank everyone very much for suggestions
Eric

 

[Socratis]

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(Addr)
TheValue = cell.Value
End Function

HTH.

Cheers,
Socratis