Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Workaround to define an array of constants?
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 |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Workaround to define an array of constants?
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/sh...d.php?t=181370 Microsoft Office Help |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
Workaround to define an array of constants?
On 22 Feb, 12:21, joel wrote:
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/sh...d.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 |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
Workaround to define an array of constants?
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. -- joel ------------------------------------------------------------------------ joel's Profile: 229 View this thread: http://www.thecodecage.com/forumz/sh...d.php?t=181370 Microsoft Office Help |
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
Workaround to define an array of constants?
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 On Mon, 22 Feb 2010 01:48:26 -0800 (PST), deltaquattro wrote: 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 |
#6
Posted to microsoft.public.excel.programming
|
|||
|
|||
Workaround to define an array of constants?
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 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Array Constants | Excel Worksheet Functions | |||
Wierd range name defined as array of constants?!? | Excel Programming | |||
unions, intersections or array constants | Excel Worksheet Functions | |||
Can VBA use array constants | Excel Programming | |||
Help with named constants in Define Name dialog box!!!! | Excel Programming |