I call the following function. It gets the correct Sheet in the
function, but the argument that Function passes
in
Set VARIABLE = Get_Sheet(...)
says "<No Variables" when I look at it in "Watch".

Function Get_Sheet(DirName, FileName, SheetName) As Worksheet
Workbooks(DirName, FileName).Open
Set Get_Sheet = Workbooks(FileName).Sheets(SheetName)
Workbooks(FileName).Close
End Function ' Get_Sheet


Any idea it doesn't work?


Tnx, Mac
Berkeley, CA

I had to change to open statement to

Workbooks.Open (DirName & FileName)

to work on my machine, but if it works fine on yours (are using Mac?)

Anyway, try not closing the source workbook, that seemed to work for me.

"Mac Lingo" wrote:

I call the following function. It gets the correct Sheet in the
function, but the argument that Function passes
in
Set VARIABLE = Get_Sheet(...)
says "<No Variables" when I look at it in "Watch".

Function Get_Sheet(DirName, FileName, SheetName) As Worksheet
Workbooks(DirName, FileName).Open
Set Get_Sheet = Workbooks(FileName).Sheets(SheetName)
Workbooks(FileName).Close
End Function ' Get_Sheet


Any idea it doesn't work?


Tnx, Mac
Berkeley, CA

Didn't catch the username first time around - never mind the first question!

"Mac Lingo" wrote:

I call the following function. It gets the correct Sheet in the
function, but the argument that Function passes
in
Set VARIABLE = Get_Sheet(...)
says "<No Variables" when I look at it in "Watch".

Function Get_Sheet(DirName, FileName, SheetName) As Worksheet
Workbooks(DirName, FileName).Open
Set Get_Sheet = Workbooks(FileName).Sheets(SheetName)
Workbooks(FileName).Close
End Function ' Get_Sheet


Any idea it doesn't work?


Tnx, Mac
Berkeley, CA

If you close the workbook that the Get_Sheet variable is pointing to, the
reference is destroyed. So what are you trying to do exactly?

--
HTH

Bob Phillips

(replace somewhere in email address with gmail if mailing direct)

"Mac Lingo" wrote in message
.com...
I call the following function. It gets the correct Sheet in the
function, but the argument that Function passes
in
Set VARIABLE = Get_Sheet(...)
says "<No Variables" when I look at it in "Watch".

Function Get_Sheet(DirName, FileName, SheetName) As Worksheet
Workbooks(DirName, FileName).Open
Set Get_Sheet = Workbooks(FileName).Sheets(SheetName)
Workbooks(FileName).Close
End Function ' Get_Sheet


Any idea it doesn't work?


Tnx, Mac
Berkeley, CA