script runs when manually stepping through it, but not otherwise

[rrands1]

I am having an issue that I *think* is related to timing. I have the following code, where I have inserted a couple of waits (to try and troubleshoot). If I step through it (i.e. set a breakpoint at the beginning of the sub & then step through it with F8), it works fine. Otherwise, if I just try and run through it at full speed, it just seems to end the sub w/o any messages or anything. In the example below, when running at full speed, it never presents the "wait 2 done" message box - it just quits processing. Any thoughts or tips on running this down?

Dim SourceWB As Workbook ' holds name of Source workbook (Coles) for easier reference
Dim TargetWB As Workbook ' holds name of Target workbook (County Export) for easier reference
Dim ssheet As Worksheet ' sheet1 in the source (Coles) workbook (to make coding shorter)
Dim tsheet As Worksheet  'sheet1 in the target (County) workbook (to make coding shorter)

Dim Sourcelastrow ' holds NUMBER of last row with Data from Source sheet
Dim Targetlastrow ' holds NUMBER of last row with Data from Target sheet

Dim targetfilepath As String

Dim L As Integer ' length of a cell's contents
Sub Add_Coles()
' integrates phone number (and Name) information from a COLES directory export into the County export

' prompt user to pick exported file from Coles directory
Set xfdCOLESexp = Application.FileDialog(msoFileDialogFilePicker)
    xfdCOLESexp.Title = "Choose Coles Export file to add the phone numbers from:"
    If xfdCOLESexp.Show = -1 Then
        xFdCOLESItem = xfdCOLESexp.SelectedItems(1)
        '     MsgBox ("Dir:  " & xFdCOLESItem)
    Else
        MsgBox ("You cancelled - terminating process - you will need to start over.")
        End
    End If

targetfilepath = ActiveWorkbook.Path & "\" & ActiveWorkbook.Name
    
Application.Wait Now + TimeValue("00:00:05")
    MsgBox ("wait 1 done")
Set SourceWB = Workbooks.Open(xFdCOLESItem)
Application.Wait Now + TimeValue("00:00:03")
    MsgBox ("wait 2 done")
Set ssheet = SourceWB.Sheets(1) ' sheet1 in the COLES workbook (to make coding shorter)
Set TargetWB = Workbooks.Open(targetfilepath)
Set tsheet = TargetWB.Sheets(1) ' sheet1 in the County export workbook (to make coding shorter)

Thank you for any help - I am totally stuck!!

 

[larryjfoster]

I am having an issue that I *think* is related to timing. I have the following code, where I have inserted a couple of waits (to try and troubleshoot). If I step through it (i.e. set a breakpoint at the beginning of the sub & then step through it with F8), it works fine. Otherwise, if I just try and run through it at full speed, it just seems to end the sub w/o any messages or anything. In the example below, when running at full speed, it never presents the "wait 2 done" message box - it just quits processing. Any thoughts or tips on running this down?

Dim SourceWB As Workbook ' holds name of Source workbook (Coles) for easier reference
Dim TargetWB As Workbook ' holds name of Target workbook (County Export) for easier reference
Dim ssheet As Worksheet ' sheet1 in the source (Coles) workbook (to make coding shorter)
Dim tsheet As Worksheet  'sheet1 in the target (County) workbook (to make coding shorter)

Dim Sourcelastrow ' holds NUMBER of last row with Data from Source sheet
Dim Targetlastrow ' holds NUMBER of last row with Data from Target sheet

Dim targetfilepath As String

Dim L As Integer ' length of a cell's contents
Sub Add_Coles()
' integrates phone number (and Name) information from a COLES directory export into the County export

' prompt user to pick exported file from Coles directory
Set xfdCOLESexp = Application.FileDialog(msoFileDialogFilePicker)
    xfdCOLESexp.Title = "Choose Coles Export file to add the phone numbers from:"
    If xfdCOLESexp.Show = -1 Then
        xFdCOLESItem = xfdCOLESexp.SelectedItems(1)
        '     MsgBox ("Dir:  " & xFdCOLESItem)
    Else
        MsgBox ("You cancelled - terminating process - you will need to start over.")
        End
    End If

targetfilepath = ActiveWorkbook.Path & "\" & ActiveWorkbook.Name
   
Application.Wait Now + TimeValue("00:00:05")
    MsgBox ("wait 1 done")
Set SourceWB = Workbooks.Open(xFdCOLESItem)
Application.Wait Now + TimeValue("00:00:03")
    MsgBox ("wait 2 done")
Set ssheet = SourceWB.Sheets(1) ' sheet1 in the COLES workbook (to make coding shorter)
Set TargetWB = Workbooks.Open(targetfilepath)
Set tsheet = TargetWB.Sheets(1) ' sheet1 in the County export workbook (to make coding shorter)

Thank you for any help - I am totally stuck!!

Have you tried making the wait after opening the user selected WB absurdly long to rule out it just not being enough time for the workbook to open? Like a minute or so? Three seconds may just not be long enough. If that does resolve the problem you can then reduce the time until it works...

 

[rrands1]

Thanks, @larryjfoster - I had done up to 15s, but just did it again at 1 min (the file only has has <700 rows and I also shortened it to just 6 lines, and get the same result)

I put a wait in front of the "Set SourceWB = Workbooks.Open(xFdCOLESItem)" , and another one after it. It does not even attempt to run the one after it - it opens the file & then just seems to quit the macro! :S It's driving me crazy! Again - if I F8 through it, it seems to work ok.

One other thing I noticed, in case that helps. This is in a module that is called from a subroutine in a different module. If I just go into this one and run the subroutine directly (by pressing F5), it seems to work ok. So, I copied the subroutine into the module calling it and tried running it there, but it still stops working after opening the file.

Any other ways to debug this?

 

[rrands1]

Just a bump to see if anyone had anymore ideas on troubleshooting? Thank you for any help!

 

[JEC]

Try changing:
Activeworkbook.path to ThisWorkbook.Path and ActiveWorkbook.Name to ThisWorkbook.Name
Change End to Exit sub as well

Also, you shouldn't need waiting times

 

[rrands1]

Try changing:
Activeworkbook.path to ThisWorkbook.Path and ActiveWorkbook.Name to ThisWorkbook.Name
Change End to Exit sub as well

Also, you shouldn't need waiting times

Thanks, @JEC - I will give those a go. I do want the “end” there - vs “exit sub”, as this sub is called from another one, and if they don’t pick a file, I want the entire process to stop. Exit sub just stops this sub & then continues the other one, which is not what I want in this case.

just a note, too, the line that is “crashing” when running at full speed is this one:
Set SourceWB = Workbooks.Open(xFdCOLESItem)

i put the timers in there just for testing, but at full speed, even with those, it never executes either the 2nd wait command (when those run, my cursor changes to an hourglass, and it only does it for the first one), nor any other lines after that. I even tried setting them both to a minute, same result.

is there some place that would log what is happening, or where I can try to trap some errors? I am pretty sure it’s a timing thing, just not sure how to change/test/trap it.

 

[JEC]

Solved??

 

[rrands1]

No - I tried the things you suggested, but no difference in behavior.

BUT... I did find something out - if I run the macro by opening the Excel IDE and pressing F5 at the start, it runs fine. It is only when I run it via the hotkey I have assigned to it (Ctrl-Shift-G in my case, but have tried others now, with same result) that it crashes. I am going to go look to see if I can find out if there is a difference to Excel when it runs via hotkey... it's bizarre, for sure!! I think I saw some posts about that kind of thing when researching this issue, so will go dig a bit...

 

[rrands1]

YAY! I figured it out! Apparently Excel has a bug where if you put a Shift in the macro shortcut key sequence (I had been using Ctrl-Shift-G), AND do a file open, it will fail. Changing the shortcut key to Ctrl-Alt-G works fine now!

link to where I found that out: Macro doesn't run via shortcut but does run from VB
(Last post there)

 

[JEC]

Nice! And weird?