Passing Worksheet tab as an argument.

[Kashizzz]

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

 

[Jean-Yves]

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

 

[Norman Jones]

Hi Kashizzz,


One way:

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

End Function

 

[Tim Coddington]

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]

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