This works wonderfully!
I've no idea how, so I shall enjoy disecting the code and hopefully gain
some insight.
I've tried for days to set this in motion and have found it difficult to
find applicable documentaion.
Thank you for your prompt and helpful replies.


"Tim Zych" <tzych@nospam at earthlink dot net wrote in message
...
This returns an array of worksheet names.

To use it, add the function to a regular module.
Then select 1:N cells, either in one row or one column, and type
=GetWorksheets() and press Ctrl + Shift + Enter which creates an array
formula. Be sure to select at least as many cells as there are worksheets
or the result will omit some names.

Function GetWorksheets() As Variant
Application.Volatile
Dim vRet As Variant, i As Integer, wkb As Workbook
Set wkb = Application.Caller.Parent.Parent
ReDim vRet(0 To Application.Caller.Cells.Count - 1)
For i = 0 To UBound(vRet)
If i < wkb.Worksheets.Count Then
vRet(i) = wkb.Worksheets(i + 1).Name
Else
vRet(i) = CVErr(xlErrValue) ' More cells selected than sheets
End If
Next
If Application.Caller.Rows.Count = 1 Then
vRet = Application.Transpose(Application.Transpose(vRet))
Else
vRet = Application.Transpose(vRet)
End If
GetWorksheets = vRet
End Function

To programmatically add the formula:

Sub AddUDF()
With Range("A1").Resize(Worksheets.Count)
.FormulaArray = "=GetWorksheets()"
' .Value = .Value 'optional to convert to values
End With
End Sub


--
Tim Zych
http://www.higherdata.com
Workbook Compare - Excel data comparison utility
Free and Pro versions

"Bassman62" wrote in message
...
Tim,
Thank you for your reply. I am new to UDFs.
I pasted the function into a module in my workbook.
I'd like to call the function in order to list and refer to the
worksheets.
I tried =GetWorksheets but I'm only getting a #Value error.
Can I call the function in cell formulas? How?
Thank you.

"Tim Zych" <tzych@nospam at earthlink dot net wrote in message
...
Here's a UDF which returns a collection of worksheet names. It takes an
optional parameter for the workbook. If not specified, it defaults to
the ActiveWorkbook.

Function GetWorksheets(Optional ByVal wkb As Workbook = Nothing) As
Collection
Dim wks As Worksheet
' Default to ActiveWorkbook
If wkb Is Nothing Then Set wkb = ActiveWorkbook
Set GetWorksheets = New Collection
For Each wks In wkb.Worksheets
GetWorksheets.Add wks.Name
Next
End Function

' Sample access code

Sub Tester()
Dim n As Long, c As New Collection
Set c = GetWorksheets ' No workbook specified, so default to
ActiveWorkbook
' Set c = GetWorksheets(ActiveWorkbook) ' Alternative way to get the
ActiveWorkbook
' Set c = GetWorksheets(Workbooks("Book2.xls")) ' Or specify a
particular workbook
For n = 1 To c.Count
Debug.Print c(n)
Next
End Sub

--
Tim Zych
http://www.higherdata.com
Workbook Compare - Excel data comparison utility
Free and Pro versions

"Bassman62" wrote in message
...
Using xl-2007;
Is there a UDF one could use to return all of the names of the
worksheets in
the active workbook?
Thanks.