Hi Howardc
The secret here is not to try and monkey with something which is working (I hope - it was OK when I tried it out ;-), but to see what the minimum change is to use it in different ways. Your most recent request is to be able to do this for different sheets. (You say "all except the last two sheets") but you know as well as I that that requirement will change. (The Devil's Computer Dictionary defines "Requirement" as a necessary feature of the software which becomes apparent only after the software has been tested and delivered)
So you have two problems:
1) The original problem, and
2) Deciding which sheets you want to clean up in this way.
The only difference, really, is that you don't know (necessarily) at any moment which sheet you're dealing with. However, this doesn't matter - as long as you can demonstrate that it IS a sheet that you should be clearing, the original procedure will work fine. (Incidentally, if you compare the original that I sent with the version that you returned to me, you'll see that you have squashed to statements onto the same line. I'd leave it the way it was sent - one statement-one line)
So the only change that is needed is to make the sht object a parameter to the procedure, rather than a local variable. That takes care of requirement 1 very easily.
For requirement 2 we are going to add a function that says, in effect, "Do I want to work with this worksheet?" This is the function "TestSheetFor Operation in the second code snippet.
Lastly, we need something to control the whole thing. This is a sub that will iterate over all the sheets in the workbook. It sends each worksheet over to TestSheetForOperation, and if it gets an OK from that function, it calls the ClearSheetTotals function. This piece of code is Sub Driver, in the 3rd code snippet.
Some comments overall.
1) The first part of analyzing requirements is to make certain that you have them all (see Dictionary Definition above)., and to identify any repeated process. In this case the repeated process is the clearing of the totals. Code that first, and figure out what bit of that process will change each time. The changeable bit is going to become a parameter.
2) If there is any question about whether the parameterized code will be done every time, ie if there is a test for whether or not to do it, then put the test into a function. Making the decision (do I call the code) is very different from actually calling the code. If you put decisions like that into a function, then you don't need to change either the driver routine, or the working routine. The only place that would change would be the decision making routine.
3) Your main driving routine can now act as if those tests and operations were simply part of the language. If you look at Driver, it's as if the computer knows how to do everything you want. You know (and I know, and everyone on this list knows ;-) that you had to code those capabilities, but anyone reading the top level of code can understand at a glance what you're trying to do. Whether we like it or not, our code spends 99% of its life in maintenance mode - and the kindest thing you can do for a maintenance programmer is to make your code as easy to read and understand as possible.
Lastly, there is an excellent book that I think might help you. It's called Algorithms + Data Structures = Programs, by Niklaus Wirth, published by Prentice Hall. It's a classic and well worth adding to your library.
HTH,
Tony
Code:
Function ClearSheetTotals( sht as worksheet) as boolean
' By defining this procedure as a function we have the possibility of reporting any
' problems back to the calling procedure.
' This function does the "ClearSheet' operation previously defined, for a sheet
' which is passed in as a parameter.
Const FirstRowToClear = 4
Const FirstColumnToClear = "A"
Const LastColumnToClear = "D"
Const TotalColumn = "A"
Dim LastRowToClear As Integer
Dim RangeToClear As String
Dim LocationOfTotal As Range
dim NoErrorsFound as boolean
' Find the row where the word "Total" is in TotalColumn, and clear the contents of rows
' FirstRowToClear through LastRowToClear for columns FirstColumnToClear thru LastColumnToClear
NoErrorsFound=true
Set LocationOfTotal = Sht.Columns(TotalColumn).Find("Total")
' Last row to clear is the row BEFORE the Total
LastRowToClear = LocationOfTotal.Row - 1
' Now construct the range which we will be clearing
RangeToClear = FirstColumnToClear + Format(FirstRowToClear)
RangeToClear = RangeToClear + ":"
RangeToClear = RangeToClear + LastColumnToClear + Format(LastRowToClear)
' Do we want to clear the contents, or delete the cells? This example simply clears the contents
Sht.Range(RangeToClear).ClearContents
' Now return the value true or false, depending on whether or not we found any errors
ClearSheetTotals=NoErrorsFound
End Function
Code:
Function TestSheetForOperation (sht as worksheet) as boolean
' This function determines if the worksheet passed in needs to have its totals cleared. It's a very
' laborious way of doing it - but allows you to have any pretty arbitrary test. It's also a very inefficient
' algorithm, because the overhe3ad of counting the sheets is done every time. However, a
' little algorithmic inefficiency is worth the clarity of purpose!
dim Book as workbook
dim WrkSheet as worksheet
dim Name as string
dim NumSheets as integer
dim i as integer
' To be on the safe side, we return a value of false by default. We can change this later.
TestSheetForOperation=false
' This is the bit that we do every time, which you would probably do outside once you have your
' test fully defined. For now we don't say "Yes" if the sheet is one of the last 2 sheets, based on index
' within workbook.sheets.count
set Book=ActiveWorkBook
NumSheets=Book.Worksheets.Count
' Now find worksheet in the collection of worksheets
Name=sht.name ' Get the name of the sheet we passed in as a parameter
set WrkSheet=Book.Worksheets(Name) ' Find the sheet in the Workbook
i=WrkSheet.index
if i=0 then
Exit Function ' The parameter sheet does not exist in this workbook!
else
if i>=NumSheets-2 then
Exit Function ' The sheet is in the don't touch area!
else
TestSheetForOperation=true
endif
endif
End Function
Code:
Sub Driver
dim Book as workbook
dim w as integer
' Open the Workbook
set Book=ActiveWorkBook
' and look at every worksheet in it
' to see if we want to clear the totals
for w=1 to Book.Worksheets.Count
if TestWorksheetForOperation (Book.Worksheets(w)) then
ClearSheetTotals(Book.Worksheets(w))
endif
next w
End Sub
Posting Permissions
Reply With Quote
Bookmarks