How to retrieve the value from formula?

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

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
 
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
 
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,

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
 
Back
Top