Retrun a worksheet from a function

[Jean-Pierre Bidon]

Hi,
My problem is rather basic but I am stumped.
Here it is: is it possible to return a worksheet object from a user defined
function (possibly via a ByRef argument)?
I'd appreciate an exemple of syntax in order to try it.
Many thanks in advance.
--
Jean-Pierre Bidon
Interstat
91 rue de Rennes
75006 Paris
Tél: 01 45 49 19 17

 

[Guest]

Here is a quick and simple example:
Public Function NewSheet(SheetName As String) As Worksheet

Set NewSheet = Worksheets.Add()
NewSheet.Name = SheetName

End Function

In Immediate Pane:
Set MySheet = NewSheet("TEST")
? MySheet.Name
TEST

 

[Jean-Pierre Bidon]

Thank you for your help. However I have still two problems.
First, The sheet that I'd like to return belongs to a workbook that I get
with the function GetObject(). With this function, it appears that the
corresponding workbook stay open, but doesn't have exactly the behavior of
an open workbook. I think that I'll drop it (using GetObject) in favour of
the Open() method.
Secondly, I just realized it in running the exemple; I made a small
subroutines to delete the new sheet just added. Each time I execute the
Delete method in this routine, I got a message to confirm. My question: is
it possible to bypass this message and have the order excuted silently?
Thanks again.
Jean-Pierre

 

[Guest]

As for the message, that is easy to deal with.
Application.DisplayAlerts=False will turn the message off, then (when done
deleting) set it back on with Application.DisplayAlerts = True.

As for the first part of your question: Yes, my code assumes you are using
ThisWorkbook, but the principle will work as long as the full worksheet
reference is specified - i.e. Workbooks("WorkbookName").Worksheets....

How best to implement this depends on what you need the function to
accomplish. For my simple example of creating a sheet, I could do this:

Public Function NewSheet(Wbook as Workbook, SheetName As String) As Worksheet

Set NewSheet = Wbook.Worksheets.Add()
NewSheet.Name = SheetName

End Function

Or, your suggestion about using a ByRef argument would also work - but then
you don't necessarily need to return a value and can make it a sub instead:

Public Sub NewSheet(ByRef Wbook As Workbook, SheetName As String)

Set NSheet = Wbook.Worksheets.Add()
NSheet.Name = SheetName

End Sub