Tek-Tips is the largest IT community on the Internet today!

Members share and learn making Tek-Tips Forums the best source of peer-reviewed technical information on the Internet!

  • Congratulations Chriss Miller on being selected by the Tek-Tips community for having the most helpful posts in the forums last week. Way to Go!

Count Number of Times a Sub is called from Excel Macro/VBA

Status
Not open for further replies.

sxschech

Technical User
Jul 11, 2002
1,034
US
I am trying to create a filename dynamically by counting each time a particular sub is called. The sub creates a pdf file and since at this point, don't know how to programmatically combine all the pdfs created, I am using a numerical system so that when the reports are finished printing, I can manually combine all open pdf files into one file. Since the code was created by someone else, I don't want to alter it too much. The Excel macro runs a series of reports. Each report is run individually (a separate macro) and some of the reports have multiple print ranges.

Current "Top Level" Macro:
Code:
Sub PrtAllRep()
    Application.Run Macro:="07F_FILE.XLS!PrtCover"
    Application.Run Macro:="07F_FILE.XLS!PrtOvrvw"
    Application.Run Macro:="07F_FILE.XLS!PrtSumReport"
  ..
..

This line of code appears in all the macros:
Code:
..
..
ActiveWindow.SelectedSheets.PrintOut Copies:=1
..
..

I had started to do something like this in the "top level" macro:
Code:
Application.Run Macro:="07F_FILE.XLS!PrtCover"
        RunReportAsPDF prmRptName:="COVER PAGE", prmPdfName:=Replace(stpath, ".XLS", "1.PDF")
Application.Run Macro:="07F_FILE.XLS!PrtOvrvw"
        RunReportAsPDF prmRptName:="OVERVIEW", prmPdfName:=Replace(stpath, ".XLS", "2.PDF")

and then comment out the printout code within the individual macro, however, since the individual macro may have more than one printout statement, this idea didn't seem like it would work as expected. Also, the other reason for dynamically creating the xxx1.pdf, xxx2.pdf, xxx3.pdf is then I wouldn't need to worry about changing the numbers if new reports are added.
 
Use a Public integer variable you increment each time you call the Sub ?

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ181-2886
 

Or - Use a Static integer variable declared in the Sub and you increment it each time you call the Sub


Have fun.

---- Andy
 
Thanks guys. I thought that might be the way to do it, but as I am not as familiar with Excel VBA as Access VBA, wasn't exactly sure where the counter was supposed to go.
 
Status
Not open for further replies.

Part and Inventory Search

Sponsor

Back
Top