HELP for coding?

[Eric]

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

[Leith Ross]

Hello Eric,

For your code to work, Addr must be a String type, and not a Variant
of String Type. It is a good practice to type your arguments. VBA
will then show what the expected argument types are. Setting the
return value type doesn't affect the argument types. That is, setting
the return type to string doesn't force the arugments to be strings
also. When the arguments are passed as literals like A1, enclose them
in quotes like this "A1".

Function TheValue(Path As String, WorkbookName As String, Sheet As
String, Addr As String) 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
Application.DisplayAlerts = True

End Function

Sincerely,
Leith Ross

[Dave Peterson]

Are you trying to use this function in a worksheet cell?

If you are, then this won't work. Functions in cells in worksheets can't insert
new sheets or change values in other cells. With very minor exceptions, these
functions can only return values to the cells that hold the function.

Harlan Grove wrote a UDF called PULL that will retrieve the value from a closed
workbook.

You can find the function at Harlan's FTP site:
ftp://members.aol.com/hrlngrv/
Look for pull.zip

Laurent Longre has an addin (morefunc.xll) at:
http://xcell05.free.fr/

That includes =indirect.ext() that may help you.

-----------------
If I guessed wrong about what you're trying to do, then you may want to explain
it again. Addr is the address of the cell whose value should be returned.

A1 was just a cell that was used as a helper cell to obtain that value.

--

Dave Peterson

[Halim]

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]

Thank everyone for suggestions
I get the file for pull.bas. Could you please give me any suggestions on how
to install this file for excel?
Thank everyone for suggtestions
Eric

[Eric]

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

[@jlathamsite.com JLatham]

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

[Dave Peterson]

If you're new to macros, you may want to read David McRitchie's intro at:
http://www.mvps.org/dmcritchie/excel/getstarted.htm

--

Dave Peterson

[Eric]

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

[@jlathamsite.com JLatham]

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]

Thank everyone very much for suggestions
Eric