Why does my VBA code jump to ActiveWorkbook.Save without running the rest of the code?

[statiCat]

I have a long macro that works great every time I run it except if I try to save at the end. If I put ActiveWorkbook.Save at the end it skips the code in front of it and just saves and does nothing else. What would cause this? I'm using the latest Excel and windows 10.

 

[statiCat]

If you are using the 'bad' double quote characters then it is unlikely your code is running fine. For example used in this line

[COLOR=#574123]      Case "Sheet2Graph", “Sheet4Graph”
[/COLOR]

it means that “Sheet4Graph” is not being evaluate as a literal string, but instead as an empty variable. Let the compiler help you weed them out by putting this statement at the top of your code module

Option Explicit

Which will force all variables to be defined (which will help your coding in other ways as well)

The mismatched quotes were a result of copying/changing names/pasting into this forum. I have double checked and they are all the same in my code ("Sheet2Graph"). I have added Option Explicit to the top of my code module. The problem persists. When ActiveWorkbook.Save is added to the code, it skips right to it.

 

[rlv01]

When ActiveWorkbook.Save is added to the code, it skips right to it.

What is being skipped? That is, where does the skipping begin and which lines of code are being skipped?

 

[statiCat]

What is being skipped? That is, where does the skipping begin and which lines of code are being skipped?

My code first check to see if the date is 7 days past the last row's date:

 [COLOR=black][FONT=Calibri]Sub Workbook_Refresher()[/FONT][/COLOR][COLOR=black][FONT='inherit']   
    ActiveWorkbook.Sheets("FirstSheet").Activate
    Dim lastRowDate As Date
    lastRowDate = ActiveSheet.Range("B458").Value
    
    If Date - lastRowDate >= 7 Then<o:p></o:p>[/FONT][/COLOR]
[COLOR=black][FONT='inherit']       Dim ws As Worksheet
        For Each ws In Worksheets
            Select Casews.Name
               Case "FirstSheet", "ThirdSheet"               
                   Application.GoTo ws.Rows("459:459") 'select last row'
                   Selection.Delete
                   Application.GoTo ws.Range("M458")
                   Selection.Formula = "=L458/C458"
                                       
               Case "Sheet2Graph", "Sheet4Graph"
                    
                   Application.GoTo ws.Rows("4:4") 'select top row
                   Selection.Delete
                   Application.GoTo ws.Rows("59:59") 'select bottom row
                   Selection.Delete
    
            End Select
            
            Next ws<o:p></o:p>[/FONT][/COLOR]
[COLOR=black][FONT='inherit']<o:p> </o:p>[/FONT][/COLOR]
[COLOR=black][FONT='inherit']    ForEach oWksht In ActiveWorkbook.Worksheets
        For Each oChart InoWksht.ChartObjects
            For EachmySrs In oChart.Chart.SeriesCollection
               mySrs.Formula = WorksheetFunction.Substitute(mySrs.Formula, 57, 58)
            Next
        Next
    Next
            
    End If

[/FONT][/COLOR]

It isn't 7 days past until Friday so it's fine if it skips that part.

However, after the If statement, it should run this code regardless of what day it is:

[COLOR=black][FONT='inherit']<o:p> </o:p>[/FONT][/COLOR]
[COLOR=black][FONT='inherit']    ForEach oWksht In ActiveWorkbook.Worksheets
        For Each oChart InoWksht.ChartObjects
            For EachmySrs In oChart.Chart.SeriesCollection
               mySrs.Formula = WorksheetFunction.Substitute(mySrs.Formula, 58, 58)
            Next
        Next
    Next<o:p></o:p>[/FONT][/COLOR]
[COLOR=black][FONT='inherit']ActiveWorkbook.RefreshAll<o:p></o:p>[/FONT][/COLOR]
[COLOR=black][FONT='inherit']ActiveWorkbook.Sheets("Sheet2Graph")Activate
Application.DisplayAlerts = False<o:p></o:p>[/FONT][/COLOR]
[COLOR=black][FONT='inherit']
ActiveWorkbook.Save<o:p></o:p>[/FONT][/COLOR]
[COLOR=black][FONT='inherit']    
End Sub<o:p></o:p>[/FONT][/COLOR]

That is what is being skipped. It does none of that when ActiveWorkbook.Save is in the code. I would know if it had done ActiveWorkbook.RefreshAll because there would be more data in the workbook.

 

[statiCat]

What is being skipped? That is, where does the skipping begin and which lines of code are being skipped?

After End If the code should run regardless of what day it is. So this is the part being skipped:

 [COLOR=black][FONT=Calibri]    ForEach oWksht In ActiveWorkbook.Worksheets[/FONT][/COLOR][COLOR=black][FONT='inherit']        For Each oChart InoWksht.ChartObjects
            For EachmySrs In oChart.Chart.SeriesCollection
               mySrs.Formula = WorksheetFunction.Substitute(mySrs.Formula, 58, 58)
            Next
        Next
    Next<o:p></o:p>[/FONT][/COLOR]
[COLOR=black][FONT='inherit']ActiveWorkbook.RefreshAll<o:p></o:p>[/FONT][/COLOR]
[COLOR=black][FONT='inherit']ActiveWorkbook.Sheets("Sheet2Graph")Activate
Application.DisplayAlerts = False<o:p></o:p>[/FONT][/COLOR]
[COLOR=black][FONT='inherit']
[/FONT][/COLOR]

The next line is...

ActiveWorkbook.Save[/FONT][/COLOR][COLOR=black][FONT='inherit']
End Sub

<o:p></o:p>[/FONT][/COLOR]

...which it does.

 

[RoryA]

Put a break point on the RefreshAll line, then run the macro from the dialog. Does it stop on the Refreshall line?

My guess would be that you're refreshing some queries which run in the background and the save is interrupting them.

 

[rlv01]

If RoryA's theory turns out to be correct, try adding a time delay after the .RefreshAll statement:

     ForEach oWksht In ActiveWorkbook.Worksheets        For Each oChart InoWksht.ChartObjects
            For EachmySrs In oChart.Chart.SeriesCollection
               mySrs.Formula = WorksheetFunction.Substitute(mySrs.Formula, 58, 58)
            Next
        Next
    Next
ActiveWorkbook.RefreshAll

WasteTime 5000  '<- adjust time delay downward via experimentation

ActiveWorkbook.Sheets("Sheet2Graph")Activate
Application.DisplayAlerts = False

The code for WasteTime is here. This is a routine I use a lot (not my code, but I don't remember exactly where I originally found it).

''' Simple delay (in milliseconds) procedure. Uses the Windows GetTickCount API
Sub WasteTime(DelayTimeInMS As Long)
    Dim T As Double
    Dim NowTick As Long
    Dim EndTick As Long

    EndTick = GetTickCount + (DelayTimeInMS)

    T = Timer
    Do
        NowTick = GetTickCount
        DoEvents
    Loop Until NowTick >= EndTick
End Sub

Needed declarations for WasteTime

Option Explicit

'Declarations  (these declarations go at the top of the module)
[URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=If]#If[/URL]   Win64 Then
    '  Code is running in 64-bit version of Microsoft Office
    Public Declare PtrSafe Function GetTickCount Lib "kernel32" () As Long
[URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=Else]#Else[/URL]  
    '  Code is running in 32-bit version of Microsoft Office
    Public Declare Function GetTickCount Lib "kernel32" () As Long
[URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=End]#End[/URL]   If

 

[RoryA]

Or amend the connection properties to not refresh in the background.

 

[rlv01]

Or amend the connection properties to not refresh in the background.

or that