Retrun a worksheet from a function

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

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
--
- K Dales


"Jean-Pierre Bidon" wrote:

> 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
>
>
>

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

"Jean-Pierre Bidon" <can_369@laposte.net> a écrit dans le message de news:
O3$U7QAAGHA.664@TK2MSFTNGP10.phx.gbl...
> 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
>

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

--
- K Dales


"Jean-Pierre Bidon" wrote:

> 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
>
> "Jean-Pierre Bidon" <can_369@laposte.net> a écrit dans le message de news:
> O3$U7QAAGHA.664@TK2MSFTNGP10.phx.gbl...
> > 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
> >
>
>
>