How do I pass a worksheets name to function?

  • Thread starter Thread starter Phillips
  • Start date Start date
P

Phillips

I would like to make a function that I can pass the name of a work sheet to
it, so I can use the function for many different uses.


Example:

sub test()

recct = wsreccount("sheet1")

msgbox recct



function wsreccount (wsname as workbook)

wsreccount = wsname.UsedRange.Rows.Count

end function


What would be the right way to do this? This ia a very simple example, but
gets the point across

Thanks
Phil
 
Hi Phillips,
I would like to make a function that I can pass the name of a work sheet to
it, so I can use the function for many different uses.
Click to expand...

You could use the Parent property of a range object:

Function Test(MyRange as Range)
Msgbox " Called from sheet '" & MyRange.Parent.Name &"'."
End Function

Then just use
=Test(Sheet1!A1)
in a cell.

Regards,

Jan Karel Pieterse
Excel MVP
www.jkp-ads.com
 
If I understand your question right, you want to rewrite the functio
this way: (changes in bold)

sub test()

recct = wsreccount("sheet1")

msgbox recct



function wsreccount (wsname as *String*)

wsreccount = *Worksheets(wsname)*.UsedRange.Rows.Count

end function

---
using the Worksheets(String) property to choose a worksheet based on
name as string. You could also use

Activesheet.name

to get the current sheet's name, if you'd rather have this be variabl
per sheet
 
Back
Top