Public Function Question

[carl]

I was pointed to this macro:

Public Function AllSheetNames()
Dim Arr() As String
Dim I As Integer
ReDim Arr(Sheets.Count - 1)
For I = 0 To Sheets.Count - 1
Arr(I) = Sheets(I + 1).Name
Next I
AllSheetNames = Arr ' return a row array OR
AllSheetNames = Application.WorksheetFunction.Transpose(Arr)
' return a column array
End Function

I can't seem to get it to work in my workbook.

Can someone walk me through the steps.

Thank you in advance.

 

[Bob Phillips]

What do you want it to do, it creates an array of sheetnames as it should.

--
HTH

Bob Phillips

(replace somewhere in email address with gmail if mailing direct)

 

[Jim Thomlinson]

This might be a silly question but what were you wanting the function to do.
The function itself works just fine. How were you intending to use it? Were
you intending to use this as a user defined function (use it as a fromula in
a spreadsheet cell)?

 

[Allllen]

It works fine. Here is how to do it with a new blank workbook:

First, you need to insert that code in a module:

1) Tools > Macro > VB Editor
2) In the VBE, make sure you can see the project explorer (View > Project
Explorer)
3) Find the project that looks like your book ("VBAProject (Book1)") and
right click on it
4) Insert Module
5) You will see a blank page on the right hand side. Copy the text and put
it in there

Public Function AllSheetNames()
Dim Arr() As String
Dim I As Integer
ReDim Arr(Sheets.Count - 1)
For I = 0 To Sheets.Count - 1
Arr(I) = Sheets(I + 1).Name
Next I
AllSheetNames = Arr ' return a row array OR
AllSheetNames = Application.WorksheetFunction.Transpose(Arr)

End Function

6) Close the VB Editor and go back to Excel.

7) Select cells a1:a3 (for example)

8) WITH THEM STILL ALL SELECTED, type =AllSheetNames(), and press <ALT><ENTER>

You will see that excel writes them all for you in cells a1:a3.
HTH

please rate

 

[carl]

Thanks. When I get to this step:


8) WITH THEM STILL ALL SELECTED, type =AllSheetNames(), and press
<ALT><ENTER>

When I press <ALT><ENTER>, nothing happens.

 

[Jim Thomlinson]

I think you want Shift + Ctrl + Enter... IMO this is not a great UDF as it
will not be dynamic as sheets are added or removed, but it will work in a
pinch...

 

[carl]

Thanks Jim. Still cannot get this to work: Formula returns #Name?.

Ayy thoughts or suggestions on another method ?

 

[Jim Thomlinson]

I am guessing taht you want to fill a range of cells with the names of all of
the worksheets in the book? If that is the case then I would be inclined to
use a button or some event such as sheet activate to re-populate the list. I
guess the big question is what do you want the list for? Place this in a
standard code module and call it whenver you want to repopulate the list...

sub AddSheetNames
dim wks as worksheet
dim rng as range

set rng = sheets("sheet1").range("A1") 'Change this
for each wks in worksheets
rng = wks.name
set rng = rng.offset(1,0)
next wks
end sub