Workaround to define an array of constants?

[deltaquattro]

Hi,

I've written a VBA code which, as an output, adds many sheets to the
current workbook. Before running the code, I'd like to delete all old
sheets in the workbook, save for the worksheets which the user needs
to provide input to the code. So I wrote the following snippet:

Sub DeleteOutputSheets()
Dim sht As Object

' Delete old sheets TODO
For Each sht In Sheets
If sht.name <> "Options" And sht.name <> "xPlot" Then
'Delete worksheet if it exists
On Error Resume Next
Application.DisplayAlerts = False
Sheets(sht.name).Delete
On Error GoTo 0
End If
Next
Set sht = Nothing
' TODO

End Sub

Questions follow:
1. Would you have coded the thing in the same way, e.g. using a For...
Each construct and an If , or do you think there's something quicker/
more robust/etc.?
2. I hardcoded the name of the sheets which must be deleted, which I
reckon Is Not A Good Thing. I'd like to pass an array of strings
InputSheets to the subroutine which contains the name of the sheets to
be spared from deletion (would it be better to pass a collection
containing the sheets?). However, the sheets are always the same, so
I'd like to define InputSheets as a Const in the declaration section
of the module. Alas, that's not possible because Const arrays are not
allowed. What do you suggest? Do I declare InputSheets as a Const and
then I allocate the names in a routine which must be launched at the
very start of the code?

Thanks in advance for your help and feel free to add comments/
suggestions/questions on coding style, structure, etc. if you have
any.

Best Regards

deltaquattro

 

[joel]

first, I hatte the ON Error statements. I think it bad programmin
practive to use an error to find a non-error condition. To test fo
sheets I often use a for loop

LastSht = sheets.count
for shtCount = LastSht to 1 step -1
Set Sht = sheets(Shtcount)
If sht.name <> "Options" And sht.name <> "xPlot" Then
'add your code here
end if
next ShtCount


To get an Array of sheet names use the code below. there isn't a goo
way of getting an array of constants.


ShtNames = Array("Sheet1","Sheet3",'Sheet5")
for each ShtName in ShtNames
Set Sht = sheets(ShtNames)

nextt ShtName

 

[deltaquattro]

first, I hatte the ON Error statements.  I think it bad programming
practive to use an error to find a non-error condition.  To test for
sheets I often use a for loop

LastSht = sheets.count
for shtCount = LastSht to 1 step -1
Set Sht = sheets(Shtcount)
If sht.name <> "Options" And sht.name <> "xPlot" Then
'add your code here  
end if
next ShtCount

To get an Array of sheet names use the code below.  there isn't a good
way of getting an array of constants.

ShtNames = Array("Sheet1","Sheet3",'Sheet5")
for each ShtName in ShtNames
Set Sht = sheets(ShtNames)

nextt ShtName

--
joel
------------------------------------------------------------------------
joel's Profile: 229
View this thread:http://www.thecodecage.com/forumz/showthread.php?t=181370

Microsoft Office Help

You're right, Joel. The same job could be done without using On Error.
Here's my new code

Sub DeleteSheets(SheetsToBePreserved() As String)
' Delete all sheets in a workbook, except the SheetsToBePreserved
Dim sht As Object, DeleteSheet As Boolean, I As Long

For Each sht In Sheets

' assume the sheet is to be deleted
DeleteSheet = True

' check if the sheet must be preserved
For I = 1 To UBound(SheetsToBePreserved)
If sht.Name = SheetsToBePreserved(I) Then
DeleteSheet = False
End If
Next I

If DeleteSheet Then
' delete sheet
Application.DisplayAlerts = False
Sheets(sht.Name).Delete
End If

Next

End Sub

I don't use Set because with the construct For Each ..., sht is
automatically set to each element of the collection Sheets, in
succession. Apart from this, it looks like our codes are quite
similar, so I'll go for this solution. Thanks

Best Regards

deltaquattro

 

[joel]

I don't think your code will work when you are deleting sheets..When
deleting you must go from the last sheet to the 1st sheet.

If you delete sheet 1 your code will skip sheet 2 (sheet 2 is now 1)
and go to sheet 3.

 

[Chip Pearson]

Here's a generic proc you can use to delete all sheets except one or
more specified sheets. The SheetsToKeep variant can be a scalar
sheet identifier (either a String sheet name or a Integer position) or
an array of sheet identifiers (each of which can be a String name or
Integer position). WhichWorkbook is optional and if present specifies
which workbook in which to get the sheets.

The sheet numbers passed in via SheetsToKeep are their positions
before the code runs. Therefore, they do not change as sheets are
deleted. In this sense, they are absolute sheet positions. If during
the Delete loop you get down to a single visible worksheet, the code
exits. You can then call this and pass it any sort of array you want.

E.,g\

Dim Arr() As Variant
ReDim Arr(1 To 3)
Arr(1) = "Sheet1"
Arr(2) = 3
Arr(3) = "does not exist"
DeleteSheets SheetsToKeep:=Arr


' OR
Dim S As String
S = "sheet10;sheet8"
DeleteSheets SheetsToKeep:=Split(S, ";")

or any number of other ways to create the input array.


Sub DeleteSheets(SheetsToKeep As Variant, _
Optional WhichWorkbook As Workbook)
Dim WB As Workbook
Dim Arr() As String
Dim N As Long
Dim V As Variant

If WhichWorkbook Is Nothing Then
Set WB = ThisWorkbook
Else
Set WB = WhichWorkbook
End If

If IsArray(SheetsToKeep) Then
ReDim Arr(LBound(SheetsToKeep) To UBound(SheetsToKeep))
On Error Resume Next
For N = LBound(SheetsToKeep) To UBound(SheetsToKeep)
Arr(N) = WB.Worksheets(SheetsToKeep(N)).Name
Next N
Else
ReDim Arr(1 To 1)
Arr(1) = WB.Worksheets(SheetsToKeep).Name
End If
Application.DisplayAlerts = False
On Error Resume Next
For N = WB.Worksheets.Count To 1 Step -1
If IsError(Application.Match(WB.Worksheets(N).Name, Arr, 0)) Then
If VisibleSheetCount(WB) = 1 Then
Exit For
End If
WB.Worksheets(N).Delete
End If
Next N

Application.DisplayAlerts = True

End Sub

Private Function VisibleSheetCount(WB As Workbook) As Long
Dim WS As Worksheet
Dim N As Long
For Each WS In WB.Worksheets
If WS.Visible = xlSheetVisible Then
N = N + 1
End If
Next WS
End Function


Cordially,
Chip Pearson
Microsoft Most Valuable Professional,
Excel, 1998 - 2010
Pearson Software Consulting, LLC
www.cpearson.com

 

[deltaquattro]

Wow, Chip,

impressive as always! Thank you so much. Sorry for the late answer,
but I've been sick, and, after that, very busy to catch up with all
the work which piled up in the meantime! Just some questions, to
understand your code better:

[..]

If IsArray(SheetsToKeep) Then
    ReDim Arr(LBound(SheetsToKeep) To UBound(SheetsToKeep))
    On Error Resume Next
    For N = LBound(SheetsToKeep) To UBound(SheetsToKeep)
        Arr(N) = WB.Worksheets(SheetsToKeep(N)).Name
    Next N

Ok, this converts the array SheetsToKeep of scalars/strings to an
array Arr of sheet names. Right?

Else
    ReDim Arr(1 To 1)
    Arr(1) = WB.Worksheets(SheetsToKeep).Name
End If
Application.DisplayAlerts = False
On Error Resume Next
For N = WB.Worksheets.Count To 1 Step -1

Is it really necessary to go from the last to the first sheet? My code
worked correctly, even though it used a For Each... which (I guess)
doesn't scan the Sheets collection in a prescribed order. I think that
the code worked all the same because it uses sheets names (which do
not change when sheets are deleted) instead than sheet number (which
do change). I rewrite my code here, just as a reference:

Sub DeleteSheets(SheetsToBePreserved() As String)
' Delete all sheets in a workbook, except the SheetsToBePreserved
Dim sht As Object, DeleteSheet As Boolean, I As Long

For Each sht In Sheets

' assume the sheet is to be deleted
DeleteSheet = True

' check if the sheet must be preserved
For I = 1 To UBound(SheetsToBePreserved)
If sht.Name = SheetsToBePreserved(I) Then
DeleteSheet = False
End If
Next I

If DeleteSheet Then
' delete sheet
Application.DisplayAlerts = False
Sheets(sht.Name).Delete
End If

Next

End Sub

    If IsError(Application.Match(WB.Worksheets(N).Name, Arr, 0)) Then

Ah, you are using Match, because it's quicker than my using a For to
check the name of the sheet against all the names in Arr. Right?
I understand the rest of the code: if WB.Worksheets(N).Name is in Arr,
then count the number of visible sheets and if that's equal to 1, then
exit the For cycle (we cannot delete any more sheets), otherwise
delete the sheet and go to the next sheet in the Workbook.
VisibleSheetCount is declared private so that only subroutines in the
SheetsManagement module can use it.

Cordially,
Chip Pearson
Microsoft Most Valuable Professional,
        Excel, 1998 - 2010
Pearson Software Consulting, LLCwww.cpearson.com

Thanks again,

ciao

Sergio Rossi