Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Please Help Me Annotate Subroutine
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 -- "It is the mark of an educated mind to be able to entertain a thought without accepting it." --Aristotle |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Please Help Me Annotate Subroutine
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)" wrote in message m... 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 -- "It is the mark of an educated mind to be able to entertain a thought without accepting it." --Aristotle |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
Please Help Me Annotate Subroutine
..
.. 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 -- "It is the mark of an educated mind to be able to entertain a thought without accepting it." --Aristotle "Bernie Deitrick" <deitbe @ consumer dot org wrote in message ... 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 |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
Please Help Me Annotate Subroutine
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 "RST Engineering (jw)" wrote in message m... . . 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 -- "It is the mark of an educated mind to be able to entertain a thought without accepting it." --Aristotle "Bernie Deitrick" <deitbe @ consumer dot org wrote in message ... 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 |
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
Please Help Me Annotate Subroutine
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 "RST Engineering (jw)" wrote: .. .. 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 -- "It is the mark of an educated mind to be able to entertain a thought without accepting it." --Aristotle "Bernie Deitrick" <deitbe @ consumer dot org wrote in message ... 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 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Call a subroutine using variable subroutine name | Excel Discussion (Misc queries) | |||
Annotate Chart with Text Boxes | Charts and Charting in Excel | |||
How To Quit Subroutine from a called subroutine | Excel Programming | |||
Annotate a point on a chart | Excel Discussion (Misc queries) | |||
How do I annotate the cell results from Excel to a Hyperlink? | Excel Discussion (Misc queries) |