Passing Worksheet tab as an argument.
 

Hi,

I have the following function requiring ws as a worksheet...


Code
-------------------
Function LastCell(ws As Worksheet)
...
End Functio
-------------------


This works fine if I call it using Sheet1, Sheet2,...

I want to pass worksheet tab names to the function. Is it possible?

Regard

--
Message posted from http://www.ExcelForum.com

Passing Worksheet tab as an argument.
 

Hi,

You want to pass the worksheet name as argument, so the function argument is
a string.
Function LastCell(ws As Stringt)
worksheets(ws).range( ;;;)
End Function

call the function

return = LastCell(sheet1.name)
or
return = LastCell("YOUR sheet name")
or
return = LastCell(worksheets("YOUR sheet name").name)

Regards

JY

"Kashizzz " wrote in message
...
Hi,

I have the following function requiring ws as a worksheet...


Code:
--------------------
Function LastCell(ws As Worksheet)
...
End Function
--------------------


This works fine if I call it using Sheet1, Sheet2,...

I want to pass worksheet tab names to the function. Is it possible?

Regards


---
Message posted from http://www.ExcelForum.com/

Passing Worksheet tab as an argument.
 

Hi Kashizzz,


One way:

Function LastCell(strWSName As String) As Range
Dim ws As Worksheet
Set ws = Worksheets(strWSName)
'your code

End Function


---
Regards,
Norman



"Kashizzz " wrote in message
...
Hi,

I have the following function requiring ws as a worksheet...


Code:
--------------------
Function LastCell(ws As Worksheet)
...
End Function
--------------------


This works fine if I call it using Sheet1, Sheet2,...

I want to pass worksheet tab names to the function. Is it possible?

Regards


---
Message posted from http://www.ExcelForum.com/

Passing Worksheet tab as an argument.
 

Not certain, examining the other replies, that I have understood your
question.
It seem to me that you simply want to know the name of the worksheet
within the 'LastCell' function. If that is so, you already have it. Try
....
msgbox ws.name

"Kashizzz " wrote in message
...
Hi,

I have the following function requiring ws as a worksheet...


Code:
--------------------
Function LastCell(ws As Worksheet)
...
End Function
--------------------


This works fine if I call it using Sheet1, Sheet2,...

I want to pass worksheet tab names to the function. Is it possible?

Regards


---
Message posted from http://www.ExcelForum.com/

Passing Worksheet tab as an argument.
 

Thanks for your replies folks....

The issue was that the LastCell function needed ws to be passed as a
worksheet object.

Excel expects the name of the sheet, (which is defaulted as Sheet1 fo
the first, Sheet2 for the second and so on, and found under Projec
Explorer (Ctrl+R) - Properties (F4)- ) under (Name).

I passed the object using the following code:

Code
-------------------
Worksheets("SheetName").Activate
LastRow = LastCell(ActiveSheet).Ro
-------------------


...and this worked!!!

Regards

--
Message posted from http://www.ExcelForum.com