How to return an array to a macro sheet
 

Apologies to all, as this may be blindingly obvious, but I'm having problems
getting my head around Excel's arrays.

What I need to happen (in an XML macro on Excel 2003) is to delete several
(~100) worksheets.

Simple you (and Microsoft Excel Macro Functions Help) say use SET.NAME():
"If you need to return an array to a macro sheet (for example, if the macro
needs a list of all open windows), assign a name to the array instead of
placing the array information in a range of cells."

Then use WORKBOOK.DELETE():
"If you want to delete Sheet1:Sheet10, you must select them first with
WORKBOOK.SELECT(). You can also place the sheets in an array first, as in
{"Sheet1", "Sheet2", "Sheet3",...}."

My rather basic question is how do I place the sheets in an array without of
placing the array information in a range of cells and then assign a name to
this array worksheet names into an array?

The worksheet structure is typically:
1st worksheet: "Contents" - without "s of course
2nd: "(A Summary)"
3rd: "(All)"
4th to 4th from last: location based names, so don't follow any pattern
3rd from last: "z_Progress Breakdown"
2nd from last: "z_Summary"
last: "z_Upload Set"

many (anticipatory) thanks

How to return an array to a macro sheet
 

Excle already has a collection - the worksheets object


Option Explicit
Sub DeleteSheets()
Dim shts() As Worksheet
Dim ws As Worksheet
Dim index As Long
ReDim shts(1 To Worksheets.Count)
For Each ws In Worksheets
index = index + 1
Set shts(index) = ws
Debug.Print ws.Name
Next

' do something with shts


End Sub



"Pico58" wrote:

Apologies to all, as this may be blindingly obvious, but I'm having problems
getting my head around Excel's arrays.

What I need to happen (in an XML macro on Excel 2003) is to delete several
(~100) worksheets.

Simple you (and Microsoft Excel Macro Functions Help) say use SET.NAME():
"If you need to return an array to a macro sheet (for example, if the macro
needs a list of all open windows), assign a name to the array instead of
placing the array information in a range of cells."

Then use WORKBOOK.DELETE():
"If you want to delete Sheet1:Sheet10, you must select them first with
WORKBOOK.SELECT(). You can also place the sheets in an array first, as in
{"Sheet1", "Sheet2", "Sheet3",...}."

My rather basic question is how do I place the sheets in an array without of
placing the array information in a range of cells and then assign a name to
this array worksheet names into an array?

The worksheet structure is typically:
1st worksheet: "Contents" - without "s of course
2nd: "(A Summary)"
3rd: "(All)"
4th to 4th from last: location based names, so don't follow any pattern
3rd from last: "z_Progress Breakdown"
2nd from last: "z_Summary"
last: "z_Upload Set"

many (anticipatory) thanks

.

How to return an array to a macro sheet
 

Many thanks Patrick,

Unfortunately I need to code this in XML/Macro 4.0.
I could call the VBA routine, but I'm pants at VBA and our IT security suites
seem to get upset when I do so!

Patrick Molloy wrote:
Excle already has a collection - the worksheets object

Option Explicit
Sub DeleteSheets()
Dim shts() As Worksheet
Dim ws As Worksheet
Dim index As Long
ReDim shts(1 To Worksheets.Count)
For Each ws In Worksheets
index = index + 1
Set shts(index) = ws
Debug.Print ws.Name
Next

' do something with shts

End Sub

Apologies to all, as this may be blindingly obvious, but I'm having problems
getting my head around Excel's arrays.
[quoted text clipped - 28 lines]

.