Please Help Me Annotate Subroutine

[RST Engineering \(jw\)]

I asked in this ng a few days ago how I could selectively print a workbook
with a lot of worksheets in it with only those sheets with a particular
value in one particular cell on each worksheet. That is, if (for example)
cell F52 is zero, then don't print the sheet, for any value OTHER than zero
print the sheet.

Thanks to Archimede's Lever for the code shown below.

One of the things we found is that for this routine to work, F52 has to be
formatted as a number, not general, not currency, a number. I don't
understand why, but I do understand that is a requirement.

What I would like to do is go through the subroutine that was given to me
(that works) and ask questions as I go. If some kind soul could answer
those questions it would go a long way towards my understanding of the
process. My comments and questions are all prefaced by a '* so you know
that they are mine and not the general comment line in the author's code.

The routine simply examines the contents of F52 and if it is zero, the sheet
is forced to be hidden. Then when you print, you only get the unhidden
sheets.


Here we go:

Sub selprint()
'* A subroutine named "selprint" (selectively print certain sheets as a
function of what is in cell F52). I'm not at all sure what the () are for
other than that is the way you have to tell VB that you are writing a
subroutine; is that right? Does it have to be Sub or can it be sub?

Dim i As Integer
'* Forcing ("dimensioning") the variable i as an integer variable. Does
"Integer" have to be capitalized or would "integer" work as well?

Dim currentsheet As Worksheet
'* Forcing the current sheet to be called the variable "Worksheet". Why
don't we have to dimension "Worksheet" as we are about to make it an array
in the next couple of steps? Or are we? It appears as though the plural
WorksheetS is what we are going to use.

For i = 1 To ActiveWorkbook.Worksheets.Count
'* Setting the range of the For loop from 1 to the number of sheets in the
workbook. Is "ActiveWorkbook" a defined function that tells the subroutine
to use the current workbook? I don't understand why that is necessary,
since the subroutine only resides in the workbook where it is loaded, not
universally to all spreadsheets in that particular installation of Excel.
Also, I'm not sure where the command "Count" comes from. I know what it
DOES; it counts the number of sheets, but is it a defined function in VB?

Set currentsheet = ActiveWorkbook.Worksheets(i)
'* We are now setting the variable "currentsheet" to be the worksheet
loaded in the FOR loop at point i. That I understand. However, we now have
a NEW variable called WorksheetS. Not Worksheet as in the second step
above, but the PLURAL new variable Worksheets. Why?

Worksheets(i).Activate
'* I'm again not understanding the meaning of the command "Activate". If
the currentsheet is already set in the step above this one, why do we have
to activate it?

'Skip empty sheets and hidden sheets
If Application.CountA(currentsheet.Cells) <> 0 And currentsheet.Visible
Then
'* Now I'm really lost. What does the ?command? "Application" do? Where
did the variable CountA come from? what does (currentsheet.Cells) not equal
to zero do? Why do we care if the currentsheet is visible? And is Visible
a VB command or function? Where are the THEN ELSE statements that go with
this IF command?


'change the hard-coded cell here if not F52
If (Not IsNull(Range("F52"))) And (Range("F52").Value <> 0) Then
'* I understand WHAT is being done here but not WHY it is done. What is
being done is to look and see if cell F52 is zero or some other value. I'm
not exactly sure why we go through the AND function to do this.

ActiveSheet.Visible = True
Else: ActiveSheet.Visible = False
'* This I do understand. If F52 is zero, the active sheet is hidden.
Otherwise the active sheet is not hidden.


'un-comment the next line when debugging completed
' ActiveSheet.PrintOut
'add comment at start of next line when debugging completed
' ActiveSheet.PrintPreview
'* These snippets of code were left over from a prior attempt to print out
as we examined the sheets and not all at once. To a regular ink printer
this was no big deal, just a lot of individual sheet print commands sent to
the printer. However, if you go to "print" your workbook to a pdf file, you
get one file for each sheet, which for a 34 page workbook was a royal pain
in the labonza.


End If
'* Ending the IF command that sets the sheet to be hidden or not. Is the
indenting necessary or programming style?

End If
'* Ending the IF command that looks to see if the current sheet is hidden
already.

Next i
'* Examines the next sheet of the workbook

End Sub
'* We're done.


Thanks,

Jim

 

[Bernie Deitrick]

Jim,

Did you try this code that I posted?

Sub PrintJob()
Dim Sel As Boolean
Dim myS As Worksheet

Sel = True

For Each myS In Worksheets
If myS.Visible Then
If myS.Range("F52").Value > 0 Then
myS.Select Sel
Sel = False
End If
End If
Next myS

ActiveWindow.SelectedSheets.PrintOut

End Sub

HTH,
Bernie
MS Excel MVP

 

[RST Engineering \(jw\)]

..
..
Bernie ...

I saw it after I posted my question. It seems somebody hijacked my original
post from a week ago and passed it off as their own under a different
Subject. I will certainly try your code. However, it seems like your code
prints sheets out one by one, doesn't it? Or does it batch print them?

The problem with printing one by one is that when you go to "print" to a pdf
file each sheet has to be given a different file name, then you have to
assemble all the pdf files into one file, making sure that the sheets are
all in order (which they rarely are).

Can you step me through your code, treating me as you would any other
freshman history major?

{;-)

Jim

 

[Bernie Deitrick]

Jim,

See my comments inline: the printout to a pdf should work if you have the PDF printer as your
default printer

Sub PrintJob()
Dim Sel As Boolean
Dim myS As Worksheet

'Sel is a flag for selecting the already selected sheet with the first sheet that meets the criteria
'Otherwise, the currently active sheet would be printed as well

Sel = True

'Step through the worksheets
For Each myS In Worksheets
'Check only visible (unhidden) sheets and ignore hidden sheets
If myS.Visible Then
'Check the value of cell F52 on that sheet
If myS.Range("F52").Value > 0 Then
'Create the sheet grouping using the Replace parameter
'Sel is true for the first sheet, False for the rest
myS.Select Sel

'Set the replace parameter to false so that other sheets can be added
'to the collection of sheets to print
Sel = False
End If
End If
Next myS

'Print all the sheets at once
ActiveWindow.SelectedSheets.PrintOut

End Sub


HTH,
Bernie
MS Excel MVP

 

[Barb Reinhardt]

I'll assist. I'm sure Bernie will correct me if I'm wrong.

Sub PrintJob()
Dim Sel As Boolean 'Options for Boolean are TRUE and FALSE
Dim myS As Worksheet 'Should be self explanatory

Sel = True

'Loops through all Worksheets in the active workbook
' If there are "Chart Sheets", they won't be included.
For Each myS In Worksheets

'Tests to see if WOrksheet is Hidden.
'Visible property can be Visible, Hidden and Very Hidden, IIRC.
If myS.Visible Then
'Checks value in Cell F52 of the current worksheet
If myS.Range("F52").Value > 0 Then
'Selects the "current" worksheet and I presume if SEL is false, it adds it to
' a group of worksheets that are selected.

myS.Select Sel
Sel = False
End If
End If
Next myS

'Prints out the selected sheets in the active workbook.
ActiveWindow.SelectedSheets.PrintOut

End Sub

HTH,
Barb Reinhardt