Can you force code to run in order?

[ModusPonen]

I have a sub that runs a few lines and then if everything checks out it calls two different macros and then ends. My issue is that the second macro never runs. It's like the sub ends before that macro can be run. So is there something I can add to the script to make it complete the first macro, then complete the second macro, and then move on?
If specifics help, the first macro is a function that opens and pulls an array of data from another workbook, pastes it to a sheet and closes that workbook. The second macro manipulates that data. Both work flawlessly on their own. I have resorted to using 2 buttons, but would prefer to understand why this happens and run them from the same sub.
Any ideas are appreciated.

[Wireless Guy]

That's kind of a difficult question to answer. What is stopping the second script from running? Did you step through it and figure out what is going on?

Maybe if you post, it'd help.

[macropod]

I have a sub that runs a few lines and then if everything checks out it calls two different macros and then ends. My issue is that the second macro never runs. It's like the sub ends before that macro can be run. So is there something I can add to the script to make it complete the first macro, then complete the second macro, and then move on?

Have you tested whether and when the second macro executes? For example:
[vba]Sub Main()
'Do Stuff
MsgBox "Calling MacroOne"
Call MacroOne
MsgBox "Calling MacroTwo"
Call MacroTwo
MsgBox "Main has finished"
End sub

Sub MacroOne()
'Do Stuff
MsgBox "MacroOne has finished"
End sub

Sub MacroTwo()
'Do Stuff
MsgBox "MacroTwo has finished"
End sub[/vba]
Obviously, if you don't get all the messages in the right order, you've got issues...

Seeing the code might help.

[ModusPonen]

I tried msgbox several places in the sub and sometimes it would run the second macro if the box was AFTER the second macro, but never before. That's why I believe it is executing code to end the sub while the first macro is still running. There's error handling all over the place in the second macro, and if the data wasn't pulled yet from the first macro I would get message boxes from it, which is how I figure the second isn't even being called. Years ago in Microsoft's forum, an MVP said something to the effect that code doesn't always run in order when passing control to other procedures, but I don't really know why or if he said how to fix that. When a sub passes control to another sub, it executes the sub and returns to the next line down of the first sub, right?

[mdmackillop]

Very clumsy but call each from the previous
[VBA]Sub Main()
'Do Stuff
MsgBox "Calling MacroOne"
Call MacroOne
MsgBox "MacroThree has finished"
End Sub

Sub MacroOne()
'Do Stuff
MsgBox "Calling MacroTwo"
Call MacroTwo

End Sub


Sub MacroTwo()
MsgBox "MacroOne has finished"
'Do Stuff
MsgBox "Calling MacroThree"
Call MacroThree

End Sub

Sub MacroThree()
MsgBox "MacroTwo has finished"
'Do Stuff
End Sub
[/VBA]

[macropod]

Hi mdmackillop,

Yes, that's potentially a viable solution, but I think the issue here might be a timing one, in which case it won't. If a function executes and passes control on to the next function before Word's finished processing the first one, the latter function might not process a complete data set. Perhaps ModusPonen needs to implement a loop or a delay to ensure all the data from the previous function have been processed by Word before the next function executes.

[Paul_Hossler]

ModusPonen --

1. Are these all in Excel? (Paul mentioned Word in #6)

2. If this is all in Excel, then I would believe that there is a logic error, since as far as I know, the VBA executes sequentially.

3. If the macro is talking to another application, then I could believe a timing issue

4. "There's error handling all over the place in the second macro, " -- try disabling the error handling and see if there's a error that is being ignored

Can you post a small sample that shows the problem? I'd always like to learn something new

Paul

[mdmackillop]

... that's potentially a viable solution

Damned by faint praise!
I would only use the sequential macros while trying to find the error. Much too confusing to follow in a real situation.

[macropod]

1. Are these all in Excel? (Paul mentioned Word in #6)l

Just a slip of the fingers ... don't place too much stock on that!

[Paul_Hossler]

Macropod

I figured, just wanted to make sure I wasn't missing something since I didn't see how there could be a timing issue solely within a Excel instance

Not aware of any way that Excel could be processing two or more tasks at the same time in order to cause a timing issue.

Hoping to learn more ...

Paul

[mdmackillop]

Trying to replicate. Macro1 takes time, but as far as I can see, is complete before Macro2 executes.
[VBA]Sub test()

Application.ScreenUpdating = False
Call Macro1
Call macro2
Application.ScreenUpdating = True
Application.Goto Cells(999, 1)
End Sub

Sub Macro1()
For I = 1 To 1000
For j = 1 To 200
Cells(I, j) = I * j
Next j
Next I
End Sub

Sub macro2()
Cells(999, 1) = "Test"
End Sub
[/VBA]

[ModusPonen]

Well, I hesitated to post code, because it's a lot. But I've tried to clean it up a little. Some additional information: The files are on a network. The one with this code has the following sheets... "PO_List" which is a simple list down column A that is added too during the first procedure. "DataIO" which is a dumping ground for the second procedure. "OpMatrix" which is where the data from DataIO is added to preexisting data during the third procedure.
This data is pulled from other workbooks. Those workbooks are all identical except for the data itself and the name of the file. They also have a sheet named DataIO with the named range "record".
I'm still working to get everything smooth, I admit some things are handled wrong, but I don't believe they have bearing on this issue (of course I don't know for certain). The line "PassControl:" in the first procedure is where the other two procedures are called.
Oh I almost forgot, I didn't write the procedure "ImportRangeFromWB" and somewhere in time I deleted the comments saying who did and not to delete the comments... So if you read it and say, 'hey! I wrote that!' then I'm sorry and thank you, it works awesome.
[VBA]Option Explicit
Sub AddPO()
'Index match the POnumber, reject if found, add if not found.
'If added, pass control to macros "ImportRangeFromWB" and "OpMatrixCounts".
Dim iLastRow As Integer
Dim vNmbr As Variant
Dim dAddMatch As Double
Dim rAddRng As Range

Set rAddRng = ActiveCell
Application.ScreenUpdating = False

vNmbr = ""
vNmbr = Worksheets("OpMatrix").Range("POnumber").Value
If vNmbr = "" Then GoTo EmptyInput:
Sheets("PO_Log").Select
iLastRow = Worksheets("PO_Log").Cells(10000, 1).End(xlUp).Row + 1
On Error GoTo AddIt:
dAddMatch = Application.WorksheetFunction.Match(vNmbr, _
Worksheets("PO_Log").Range("A2:A10000"), 0)
On Error GoTo 0
GoTo AlreadyAdded:

''Error handler for no match found.
AddIt:
On Error GoTo 0
Resume PassControl:
''Handler for match found.
AlreadyAdded:
msgbox ("Data for this PO Number has already been added." & Chr(10) & _
"See sheet PO_Log, row " & dAddMatch + 1 & ".")
Exit Sub
''Handler for empty input from user.
EmptyInput:
msgbox ("No PO Number found.")
Exit Sub

''Gate to ImportRangeFromWB and OpMatrixCounts.
PassControl:
Cells(iLastRow, 1).Select
ActiveCell = vNmbr
Sheets("DataIO").Select
Columns("B:G").Select
Selection.ClearContents
'Passing to second procedure.
ImportRangeFromWB "G:\folderpath\" & vNmbr & ".xlsx", "DataIO", "record", True, _
ThisWorkbook.Name, "DataIO", "B1"
'Passing to third procedure.
OpMatrixCounts

Application.GoTo rAddRng 'Drop user at start position.
Application.ScreenUpdating = True
Set rAddRng = Nothing 'Clean up.
End Sub
'=================================================

Option Explicit
Sub ImportRangeFromWB(SourceFile As String, SourceSheet As String, _
SourceAddress As String, PasteValuesOnly As Boolean, _
TargetWB As String, TargetWS As String, TargetAddress As String)
' Imports the data in Workbooks(SourceFile).Worksheets(SourceSheet).Range(SourceAddress)
' to Workbooks(TargetWB).Worksheets(TargetWS).Range(TargetAddress)
' Replaces existing data in Workbooks(TargetWB).Worksheets(TargetWS) without _
' prompting for confirmation.
' Example:
' ImportRangeFromWB "C:\FolderName\TargetWB.xls", "Sheet1", "C120", True, _
' ThisWorkbook.Name , "ImportSheet", "A1"

Dim SourceWB As Workbook, SourceWS As String, SourceRange As Range
Dim TargetRange As Range, A As Integer, tString As String
Dim r As Long, C As Integer
' validate the input data if necessary
If Dir(SourceFile) = "" Then msgbox ("PO Number provided appears to be a " & _
"mismatch. Please check PO Number again.")
If Dir(SourceFile) = "" Then Exit Sub ' SourceFile doesn't exist
Application.EnableEvents = False
Set SourceWB = Workbooks.Open(SourceFile, True, True)
Application.EnableEvents = True
Workbooks(TargetWB).Activate
Worksheets(TargetWS).Activate
' perform import
Set TargetRange = Range(TargetAddress).Cells(1, 1)
Set SourceRange = SourceWB.Worksheets(SourceSheet).Range(SourceAddress)
For A = 1 To SourceRange.Areas.Count
SourceRange.Areas(A).Copy
If PasteValuesOnly Then
TargetRange.PasteSpecial xlPasteValues
TargetRange.PasteSpecial xlPasteFormats
Else
TargetRange.PasteSpecial xlPasteAll
End If
Application.CutCopyMode = False
If SourceRange.Areas.Count > 1 Then
Set TargetRange = _
TargetRange.Offset(SourceRange.Areas(A).Rows.Count, 0)
End If
Next A
' clean up
Set SourceRange = Nothing
Set TargetRange = Nothing
Range(TargetAddress).Cells(1, 1).Select
SourceWB.Close False
Set SourceWB = Nothing
End Sub
'=================================================

Option Explicit
Sub OpMatrixCounts()
'Index match, and then increase counts of values found.
Dim vSAP As Variant
Dim vOpStart As Variant
Dim vOpFinish As Variant
Dim vIssue As Variant
Dim vNote As Variant
Dim vOldComment As Variant
Dim dRow As Double
Dim dStartCol As Double
Dim dFinishCol As Double
Dim dIssueCol As Double
Dim lStartCnt As Long
Dim lFinishCnt As Long
Dim lIssueCnt As Long
Dim i As Integer
Dim iFinalRow As Integer
Dim bHasComment As Boolean
''Set initial constants.
iFinalRow = Worksheets("DataIO").Cells(10000, 3).End(xlUp).Row 'No need for _
'over 10000 lines at this time.

''''''''''''''''''''''
For i = 1 To iFinalRow
''Set loop constants.
vSAP = Worksheets("DataIO").Cells(i, 3).Text
vOpStart = Worksheets("DataIO").Cells(i, 4).Text
vOpFinish = Worksheets("DataIO").Cells(i, 5).Text
vIssue = Worksheets("DataIO").Cells(i, 6).Text
vNote = Worksheets("DataIO").Cells(i, 7).Text
On Error GoTo SkipAll1: 'Skip this loop if SAP is a mismatch.
dRow = Application.WorksheetFunction.Match(vSAP, _
Worksheets("OpMatrix").Range("SAPlist"), 0)
On Error GoTo 0
''Starting Operator Count code.
If vOpStart = "" Then GoTo SkipStart2: 'Skip Starting Operator count if empty value.
On Error GoTo SkipStart1: 'Skip Starting Operator count if number is a mismatch.
dStartCol = Application.WorksheetFunction.Match(vOpStart, _
Worksheets("OpMatrix").Range("operators"), 0)
lStartCnt = Worksheets("OpMatrix").Range("E22").Cells(dRow, dStartCol).Value
lStartCnt = lStartCnt + 1
Worksheets("OpMatrix").Range("E22").Cells(dRow, dStartCol).Select
ActiveCell = lStartCnt
GoTo SkipStart2:
''Error handler for Starting Operator.
SkipStart1:
Resume SkipStart2:
SkipStart2:
On Error GoTo 0
''Finishing Operator Count code.
If vOpFinish = "" Then GoTo SkipIsue2: 'Skip Finishing Operator count if empty value.
On Error GoTo SkipIsue1: 'Skip Finishing Operator count if number is a mismatch.
dFinishCol = Application.WorksheetFunction.Match(vOpFinish, _
Worksheets("OpMatrix").Range("operators"), 0) + 1
lFinishCnt = Worksheets("OpMatrix").Range("E22").Cells(dRow, dFinishCol).Value
lFinishCnt = lFinishCnt + 1
Worksheets("OpMatrix").Range("E22").Cells(dRow, dFinishCol).Select
ActiveCell = lFinishCnt
''Issue Count code; applied to the Finishing Operator.
If vIssue = "" Then GoTo SkipIsue2: 'Skip Finishing Operator Issue count if _
'empty value.
On Error GoTo SkipIsue1: 'Skip Finishing Operator Issue count if number is a mismatch.
dIssueCol = Application.WorksheetFunction.Match(vOpFinish, _
Worksheets("OpMatrix").Range("operators"), 0) + 2
lIssueCnt = Worksheets("OpMatrix").Range("E22").Cells(dRow, dIssueCol).Value
lIssueCnt = lIssueCnt + 1
Worksheets("OpMatrix").Range("E22").Cells(dRow, dIssueCol).Select
ActiveCell = lIssueCnt
''Notes code; added to cell comment if notes are present.
If vNote = "" Then GoTo SkipIsue2: 'Skip add comment if no note.
On Error Resume Next 'If cell has comment, this will pass boolean as true. _
'If no cell comment, it will error, leaving boolean at default(false).
bHasComment = ActiveCell.Comment.Parent.Address = ActiveCell.Address
On Error GoTo 0
If bHasComment = True Then 'Adding new comment to old comment(s).
vOldComment = ActiveCell.Comment.Text
ActiveCell.Comment.Text Text:=vOldComment & Chr(10) & vNote
Else
ActiveCell.AddComment Text:=vNote 'Creating new comment.
End If
GoTo SkipIsue2:
''Error handler for Finishing Operator, Issues, and Notes.
SkipIsue1:
Resume SkipIsue2:
SkipIsue2:
GoTo SkipAll2:
''Error handler for SAP mismatch.
SkipAll1:
msgbox ( _
"One or more SAP numbers need verification; match not found." _
& Chr(10) & _
"Compair SAP from worksheet OpMatrix, row " & dRow + 21 & _
" and worksheet DataIO, row " & i & ".")
Resume SkipAll2:
SkipAll2:
On Error GoTo 0
''Reset loop constants for next loop.
vSAP = ""
vOpStart = ""
vOpFinish = ""
vIssue = ""
vNote = ""
vOldComment = ""
dStartCol = Empty
dFinishCol = Empty
dIssueCol = Empty
bHasComment = False
Next i
''''''''''''''''''''''

End Sub
'=================================================
[/VBA]

[mdmackillop]

When you are working between multiple books/sheets, it is best to qualify all locations. For example, you use ActiveCell, but looking at the code, I don't know which sheet or book that value is in.
Try to avoid Selecting/Activating. If you have variables, you can read/change values without going to that location. This will also spped up execution considerably.
I've done a little tidying to this as an example, but to go further, you'll need to create the rest of the variables.

[VBA]Sub AddPO()
'Index match the POnumber, reject if found, add if not found.
'If added, pass control to macros "Import" and "OpMatrixCounts".
Dim iLastRow As Integer
Dim vNmbr As Variant
Dim dAddMatch As Double
Dim rAddRng As Range
Dim PO_Log As Worksheet

Set PO_Log = Worksheets("PO_Log")

Set rAddRng = ActiveCell
Application.ScreenUpdating = False

vNmbr = Worksheets("OpMatrix").Range("POnumber").Value

With PO_Log
If vNmbr = "" Then GoTo EmptyInput:
iLastRow = .Cells(10000, 1).End(xlUp).Row + 1
On Error GoTo AddIt:
dAddMatch = Application.WorksheetFunction.Match(vNmbr, .Range("A2:A10000"), 0)
On Error GoTo 0
GoTo AlreadyAdded:

''Error handler for no match found.
AddIt:
On Error GoTo 0
Resume PassControl:
''Handler for match found.
AlreadyAdded:
MsgBox ("Data for this PO Number has already been added." & Chr(10) & _
"See sheet PO_Log, row " & dAddMatch + 1 & ".")
Exit Sub
''Handler for empty input from user.
EmptyInput:
MsgBox ("No PO Number found.")
Exit Sub

''Gate to ImportRangeFromWB and OpMatrixCounts.
PassControl:
.Cells(iLastRow, 1) = vNmbr
End With

Sheets("DataIO").Columns("B:G").ClearContents

'Passing to second procedure.
ImportRangeFromWB "G:\folderpath\" & vNmbr & ".xlsx", "DataIO", "record", True, _
ThisWorkbook.Name, "DataIO", "B1"
'Passing to third procedure.
OpMatrixCounts

Application.Goto rAddRng 'Drop user at start position.
Application.ScreenUpdating = True
Set rAddRng = Nothing 'Clean up.
End Sub
[/VBA]

[Paul_Hossler]

ModusPonen --

Going all the way back to your #1 now, which of the subs you've posted is the one that you find doesn't run, and what order are you doing them in?

Paul

[ModusPonen]

When you are working between multiple books/sheets, it is best to qualify all locations...This will also speed up execution considerably.

Thanks, I didn't know about that. Anything to cut execution speed is good.

So Paul, going back to the problem. All three procedures work on their own. The first procedure calls the second and third. The third is being skipped for no known reason. Some oddities are that sometimes the third will run if a msgbox is placed AFTER the third, and most times the remaining code of the first is also skipped.

[ModusPonen]

Alright, I've done some additional work and here's what I found...

I placed a msgbox at the first and last lines of all 3 macros, and a msgbox to announce the leaving and returning from macro1 when it passes to the other subs. And I commented out the screenupdating=false so I could watch everything.

The result is that it specifically skips the loop in macro 3. So I added a msgbox in the loop that returns the loop number. It runs through the loops, displays all messages, but never runs the rest of the loop code; just the messages. Afterward, I ran the third macro by itself because the data is still available for the loops, and it ran perfect (msgboxes and data manipulation).

ANY ideas are welcome, and I can supply my files if someone wants to take a closer look.

(Sorry for the double post... I should have edited the last one instead.)


Later that day...
OMG! I did it! Probably through some poor coding choices, the loop has to have the "OpMatrix" sheet activated before the "For i = 1 To iFinalRow" loop intro in order to run properly; although I don't understand why, since within the loop the code tells where to go at each step. Anyone? Well at any rate, big thanks to macropod for suggesting the message boxes at each step. And big thanks to mdmackillop for suggesting I qualify all locations, because if I had done that in the first place, I would probably have understood the problem or not had it at all (since this seems to be a location issue). And Paul you were there, and the tin man and the scarecrow... Thanks everyone!!!