How Return to Calling VBA Code after Application.Run

Raider52 · Apr 20th 2020

Hello All

I am stuck. I need to call vba code in a separate Workbook and return to the calling vba procedure an continue execution from the point of the call. I am using Application.Run, but everything stops after the Application.Run ends with End Sub. I am new to VBA and would appreciate some help and a sample snipet of code would be greatly appreciated.

Thanks

Raider52

**royUK** · Apr 20th 2020

It shouldn't stop. Can you post your code - use Code Tags as explained in the Forum Rules.

Raider52 · Apr 20th 2020

Code

>>>>This Is the Calling Procedure>>>>>>>>>
Sub SingleRowPDF()
Application.DisplayAlerts = False
    Dim PathToFile As String, _
    NameOfFile As String, _
    wbTarget As Workbook, _
    CloseIt As Boolean
'
     'Set file name and location. You will need to update this info!
    NameOfFile = "PDF_Summary.xlsm"
    PathToFile = "V:\BankStatementFiles"
'
     'Attempt to set the target workbook to a variable.  If an error is
     'generated, then the workbook is not open, so open it
    
    On Error Resume Next
    Set wbTarget = Workbooks(PDF_Summary.xlsm)
     
    If Err.Number <> 0 Then
         'Open the workbook
        Err.Clear
        Set wbTarget = Workbooks.Open(PathToFile & "\" & NameOfFile)
       CloseIt = True
    End If
     
     'Check and make sure workbook was opened
    If Err.Number = 1004 Then
        MsgBox "Sorry, but the file you specified does not exist!" _
        & vbNewLine & PathToFile & "\" & NameOfFile
        Exit Sub
    End If
    On Error GoTo 0
     
     'Run the macro!  (You will need to update "MacroName" to the
     'name of the macro you wish to run)
    Application.Run (wbTarget.Name & "!Make1RowPDF")
GoTo UFinn
    If CloseIt = True Then
         'If the target workbook was opened by the macro, close it
        wbTarget.Close savechanges:=False
    Else
         'If the target workbook was already open, reactivate this workbook
        ThisWorkbook.Activate
    End If
'
Application.DisplayAlerts = True
UFinn:
'
Call FinalOne 'Module 4
End Sub

>>>>>>>This Is the Called Procedure (Partial)>>>>>>>>>>>
Sub Make1RowPDF()
Application.DisplayAlerts = False
'GoTo CheckThis
Dim CheckNo As String
Dim iCntr As String
Dim i As Integer
Dim wrksht1 As Worksheet
Dim wrksht2 As Worksheet
'
Set FSO = CreateObject("Scripting.FileSystemObject")
'Dim src As Workbook         ' THE Invoice WORKBOOK.
'Dim src4 As Workbook         ' THE Invoice WORKBOOK.
'Set src = Workbooks.Open("V:\BankStatementFiles\PDF_Summary.xlsx", True, True)
'
Set wrksht2 = ThisWorkbook.Worksheets("BStmt1Row")
        wrksht2.Visible = xlSheetVisible
         wrksht2.Activate
 '      ThisWorkbook.Worksheets("BStmt1Row")
      ERow = wrksht2.Cells(Rows.Count, 1).End(xlUp).Row
      iCntr = "A2:" & "AF" & ERow
      wrksht2.Range(iCntr).EntireRow.Delete
      '
'Stop
      '
 Set wrksht1 = ThisWorkbook.Worksheets("BStmtDetail")
        wrksht1.Visible = xlSheetVisible
        wrksht1.Activate
      LstRow = wrksht1.Cells(Rows.Count, 1).End(xlUp).Row + 1
'
DRank = 2
Filex = "Junk"
PageNx = 9999
'
  For i = 2 To LstRow
     If i = 2 Then
     GoTo UStart
         ElseIf Filex = FileName And PageNx = PageNo Then
     Rank = Worksheets("BStmtDetail").Cells(i, 7)
         GoTo GitMore
       End If
                    Worksheets("BStmt1Row").Cells(DRank, 1) = SoureFile
                    Worksheets("BStmt1Row").Cells(DRank, 2) = FileName
                    Worksheets("BStmt1Row").Cells(DRank, 3) = PageNo
                    Worksheets("BStmt1Row").Cells(DRank, 4) = PrintDate
                    Worksheets("BStmt1Row").Cells(DRank, 5) = CheckNo
                    Worksheets("BStmt1Row").Cells(DRank, 6) = Amt
                    Worksheets("BStmt1Row").Cells(DRank, 7) = ChkDetail
                    Worksheets("BStmt1Row").Cells(DRank, 8) = 
                    Worksheets("BStmt1Row").Cells(DRank, 28) = BaiCode
                    Worksheets("BStmt1Row").Cells(DRank, 29) = OneDayF
                    Worksheets("BStmt1Row").Cells(DRank, 30) = CeoID
                    Worksheets("BStmt1Row").Cells(DRank, 31) = CreatedBy
                    Worksheets("BStmt1Row").Cells(DRank, 32) = AddlItmDtl
        DRank = DRank + 1
'
UStart:
          SoureFile = Worksheets("BStmtDetail").Cells(i, 10)
          FileName = Worksheets("BStmtDetail").Cells(i, 11)
          PageNo = Worksheets("BStmtDetail").Cells(i, 1)
          PrintDate = Worksheets("BStmtDetail").Cells(i, 2)
          ChkDetail = Worksheets("BStmtDetail").Cells(i, 3)
          Rank = Worksheets("BStmtDetail").Cells(i, 7)
          CheckNo = Worksheets("BStmtDetail").Cells(i, 8)
          Amt = Worksheets("BStmtDetail").Cells(i, 9)
'          CheckAmt = Worksheets("BStmtDetail").Cells(i, 5)
          GoTo Nudder
GitMore:
'          Rank = Worksheets("BStmtDetail").Cells(i, 7)
           If Rank = 2 Then
           GoTo Git02
           ElseIf Rank = 3 Then
           GoTo Git03
           ElseIf Rank = 4 Then
           GoTo Git04
           ElseIf Rank = 5 Then
           GoTo Git05
           ElseIf Rank = 6 Then
           GoTo Git06
           ElseIf Rank = 7 Then
           
           ElseIf Rank = 24 Then
           GoTo Git24
           ElseIf Rank = 25 Then
           GoTo Git25
      End If
Git02:
'           Amt = Worksheets("BStmtDetail").Cells(i, 9)
           CheckAmt = Worksheets("BStmtDetail").Cells(i, 5)
           GoTo Nudder
Git03:
           AcctNo = Worksheets("BStmtDetail").Cells(i, 5)
           GoTo Nudder
Git04:
           Eisn = Worksheets("BStmtDetail").Cells(i, 5)
           BaiCode = Worksheets("BStmtDetail").Cells(i, 5)
           GoTo Nudder
Git22:
           OneDayF = Worksheets("BStmtDetail").Cells(i, 5)
           GoTo Nudder
Git23:
           CeoID = Worksheets("BStmtDetail").Cells(i, 5)
           GoTo Nudder
Git24:
           CreatedBy = Worksheets("BStmtDetail").Cells(i, 5)
           GoTo Nudder
Git25:
           AddlItmDtl = Worksheets("BStmtDetail").Cells(i, 5)

Nudder:
           Filex = Worksheets("BStmtDetail").Cells(i, 11)
          PageNx = Worksheets("BStmtDetail").Cells(i, 1)
Next
'
                    Worksheets("BStmt1Row").Cells(DRank, 1) = SoureFile
                    Worksheets("BStmt1Row").Cells(DRank, 2) = FileName
                    Worksheets("BStmt1Row").Cells(DRank, 3) = PageNo
                    Worksheets("BStmt1Row").Cells(DRank, 4) = PrintDate
                    Worksheets("BStmt1Row").Cells(DRank, 5) = CheckNo
                    Worksheets("BStmt1Row").Cells(DRank, 6) = Amt
DepositID
                    Worksheets("BStmt1Row").Cells(DRank, 22) = Description
                    Worksheets("BStmt1Row").Cells(DRank, 23) = ItemSeqNo
                    Worksheets("BStmt1Row").Cells(DRank, 24) = BankID
                    Worksheets("BStmt1Row").Cells(DRank, 25) = Location
                    Worksheets("BStmt1Row").Cells(DRank, 26) = ItemSeq
                    Worksheets("BStmt1Row").Cells(DRank, 27) = BankID
                    Worksheets("BStmt1Row").Cells(DRank, 28) = BaiCode
                    Worksheets("BStmt1Row").Cells(DRank, 29) = OneDayF
                    Worksheets("BStmt1Row").Cells(DRank, 30) = CeoID
                    Worksheets("BStmt1Row").Cells(DRank, 31) = CreatedBy
                    Worksheets("BStmt1Row").Cells(DRank, 32) = AddlItmDtl
UFinn:
'Stop
ActiveWorkbook.Save
ActiveWorkbook.SaveAs FileName:="V:\BankStatementFiles\PDFX_Summary.xlsx", FileFormat:=xlOpenXMLWorkbook
ActiveWorkbook.Close
Application.DisplayAlerts = True
'Stop
End Sub
>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>
The Called Procedure "Make1Row" has been edited to shorten because it was very long.  The Calling Procedure has not been edited.  Thanks very much for your help

Display More

**royUK** · Apr 21st 2020

Code

Application.Run (wbTarget.Name & "!Make1RowPDF")
GoTo UFinn

After you have called the macro you have

Code

GoTo UFinn

This jumps to the end of the macro and

Call FinalOne 'Module 4

I don't know what this other code does

Raider52 · Apr 21st 2020

Code

Application.Run (wbTarget.Name & "!Make1RowPDF")
MsgBox "YOU ARE HERE!!!!!!"
'GoTo UFinn

When I put a MsgBox just after the Application.Run call, the message never appears, which tells me the return from !Make1RowPDF terminates the calling procedure (SingleRowPDF). So SingleRowPDF never executed the Call FinalOne 'Module 4 statement. There has to be something wrong within Make1RowPDF, although it does all I want it to do. Make1RowPDF never returns control to SingleRowPDF.

**royUK** · Apr 21st 2020

Have you got the references set?

You need to step through the code to see where the error is

dangelor · Apr 21st 2020

Possibly surrounding the file name with single quotes...

Code

Application.Run ("'" & wbTarget.Name & "'!Make1RowPDF")

Raider52 · Apr 22nd 2020

Code

ActiveWorkbook.Save
ActiveWorkbook.SaveAs FileName:="V:\BankStatementFiles\PDFX_Summary.xlsx", FileFormat:=xlOpenXMLWorkbook
ActiveWorkbook.Closeapplication.DisplayAlerts = True
'Stop
End Sub

I found the problem... The problem was I was closing the called Workbook with the statement, below. That also terminated the calling procedure. When I commented out the statement below, the return to calling procedure completed the remainder of the processing.

Thanks to both of you for your help... Stay Safe.

Code

ActiveWorkbook.Close

How Return to Calling VBA Code after Application.Run

Participate now!

Share

Tags