Excel VBA - Flow of control between routine and subroutine 1

[Gruuuu]

Ok, I'm relatively newbish to VBA. Most of what I've learned has been from the Microsoft Help Files or from OzGrid, with a smattering from the Tek-Tips forums.

The process I am trying to run should be automating macros in a dynamic list of files.

I have a Range on the workbook from which I run the main process. The range is named "reports". It will hold a list of file names, which are to be opened and then have a subroutine run, which is saved in that file.

Currently, the process correctly opens and runs the subroutine. The problem arises when the routine is finished and the file closes. Control is not given back to the main workbook to process the next file.

Also, I'm sure there are a slew of terrible things in my coding style. I will not be offended if you feel it appropriate to correct or scold me! Please, feel free!

Main File

Sub main()
[green]'Define some constants[/green]
[green]'Name of the main book[/green]
Static masterBook As String
[green]'Paths[/green]
Static wkgFolder As String, histFolder As String, dbFolder As String
[green]'timeframe variables[/green]
Static weDate As Date, fy As String, period As String, week As String
[green]'checkbox variables[/green]
Static rptRun As Boolean, rptHist As Boolean, rptDB As Boolean, rptVerify As Boolean

masterBook = ActiveWorkbook.Name
wkgFolder = Workbooks(masterBook).Sheets("Main").Range("wkg").Value
histFolder = Workbooks(masterBook).Sheets("Main").Range("hst").Value
dbFolder = Workbooks(masterBook).Sheets("Main").Range("db").Value
weDate = Workbooks(masterBook).Sheets("Main").Range("we").Value
fy = Workbooks(masterBook).Sheets("Main").Range("fy").Value
period = Workbooks(masterBook).Sheets("Main").Range("pd").Value
week = Workbooks(masterBook).Sheets("Main").Range("wk").Value
rptRun = Sheets("Main").runbox.Value
rptHist = Sheets("Main").histbox.Value
rptDB = Sheets("Main").dbbox.Value
rptVerify = Sheets("Main").verifybox.Value

[green]'Now, to loop through the report list and run macros for each one.[/green]
    For Each rpt In Workbooks(masterBook).Sheets("Main").Range("reports")
        If rpt = Empty Then
            MsgBox "List completed."
            Exit Sub
        Else
            macroName = "'" & rpt & "'!subordinate_macro.subordinate_macro"
            Workbooks.Open Filename:=Workbooks(masterBook).Sheets("Main").Range("wkg").Value & rpt, UpdateLinks:=False
            Application.Run macroName, rptRun, rptHist, weDate, rptDB, rptVerify, wkgFolder, histFolder, dbFolder, masterBook, rpt
        End If
    Next rpt
End Sub

Standard Listed File
Most reports follow this basic structure, with variations in the 'Run' section

Sub subordinate_macro(ByVal rptRun As Boolean, _
                      ByVal rptHist As Boolean, _
                      ByVal weDate As Date, _
                      ByVal rptDB As Boolean, _
                      ByVal rptVerify As Boolean, _
                      ByVal wkgFolder As String, _
                      ByVal histFolder As String, _
                      ByVal dbFolder As String, _
                      ByVal masterbook As String, _
                      ByVal rpt As String)


[green]'Run[/green]
If rptRun Then
    ActiveWorkbook.RefreshAll
    ActiveWorkbook.UpdateLink Name:=ActiveWorkbook.LinkSources

[green]'Verify[green]
If rptVerify Then
    macroName = "'" & masterbook & "'!stdfn.stdVerify"
    Application.Run macroName, masterbook
End If

[green]'History[/green]
If rptHist Then
    ActiveWorkbook.Save
    macroName = "'" & masterbook & "'!stdfn.stdHist"
    Application.Run macroName, histFolder, rpt, weDate
End If


[green]'Databank[/green]
If rptDB Then
    [green]'Paste Values[/green]
    macroName = "'" & masterbook & "'!stdfn.stdPV"
    Application.Run macroName
    [green]'Save[/green]
    macroName = "'" & masterbook & "'!stdfn.stdDB"
    Application.Run macroName, dbFolder, rpt
End If

ActiveWorkbook.Close

End Sub

 

[Gruuuu]

Neeeevermind! I figured it out. Or I should say I found it, since I don't understand why it works the way it does.

The issue was: I was issuing the Window.Close command inside the subroutine. If I remove the close command from the file, and instead leave it in the main function, it processes smoothly.

What I don't understand is: why does it do this? I don't need to know, but I would like to understand, in case I run into this again.

 

[SkipVought]

Your posted code has NO Window.Close

It does have ActiveWorkbook.Close

?????

Skip,

_{Just traded in my old subtlety...
for a NUANCE!}

 

[Gruuuu]

Sorry, that's what I meant.

 

[SkipVought]

You really have to be VERY careful using ActiveWHATEVER. Your macro could be making some other workbook active.

If you OPEN a workbook in your code, I would recommend assigning to a workbook object like...

dim wb as workbook

set wb = workbooks.open(somefile)

'do stuff to the workbook

wb.save

wb.close

set wb = nothing

or even like this...

with workbooks.open(somefile)

'do stuff to the workbook

  .save

  .close
end with

Skip,

_{Just traded in my old subtlety...
for a NUANCE!}

 

[Gruuuu]

Yeah, this is one of those learning projects. I learn a lot as I got along, so there's probably some remnants of terrible or poorly implemented code in here.

I think I'd really like to go back and rewrite the references so that I can refer to things by reference instead of by object name, as you've suggested.

Once I get all that cleaned up and rewritten, I'll repost the code!
Thanks Skip!

 

[SkipVought]

Hey! Don't think that you're the only one who as done some 'bad code'. I've got plenty that I look at and say, "Why did I code that?"

Skip,

_{Just traded in my old subtlety...
for a NUANCE!}

 

[Gruuuu]

Decided not to instantiate object references. I briefly forgot that I already had the file name handy from my earlier cell references...

    For Each [b][red]rpt In Workbooks(masterBook).Sheets("Main").Range("reports")[/red][/b]
        If rpt = Empty Then
            MsgBox "List completed."
            Exit Sub
        Else
            macroName = "'" & rpt & "'!subordinate_macro.subordinate_macro"
            Workbooks.Open Filename:=Workbooks(masterBook).Sheets("Main").Range("wkg").Value & rpt, UpdateLinks:=False
            Application.Run macroName, rptRun, rptHist, weDate, rptDB, rptVerify, wkgFolder, histFolder, dbFolder, masterBook, rpt
            [b][red]Workbooks(rpt.Value).Close[/red][/b]
        End If
    Next rpt

 

[SkipVought]

Now, my question to you is, WHY does the reports range have empty cells?

Much better to define your ranges to coincide with your data.

Skip,

_{Just traded in my old subtlety...
for a NUANCE!}

 

[Gruuuu]

I have considered doing exactly that several times. I may still, even.

Regardless of whether or not I define my range to coincide with my data, there will potentially be times when there will be blank cells.

Basically: I have a script that populates the reports range with filenames based on user-defined filters. Naturally there will be extraneous files listed. I will need to allow for some of the cells to be cleared before processing the list.

Furthermore I COULD sort the list before processing, but the order of processing is often important.

I could also try and see if I could remove blank spaces between file names... but now we're just getting to the point where there isn't enough return for the time investment.

Ultimately I will likely dynamically assign the range, and have my main function trap empty cells.

I appreciate the comment, though. It helps me consider my options.