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

On Jun 19, 6:33 pm, 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

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

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.

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

--

Dave Peterson

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

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

"Dave Peterson" wrote:

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.

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

--

Dave Peterson

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

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


"Eric" wrote:

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

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

Eric wrote:

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

"Dave Peterson" wrote:

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.

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

--

Dave Peterson


--

Dave Peterson

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

"JLatham" wrote:

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


"Eric" wrote:

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

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" wrote:

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

"JLatham" wrote:

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


"Eric" wrote:

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