VBA Macro Works Only in Debug (Step Through)

[FatLane]

I've seen others with this problem, but there doesn't seem to be a common solution.

My export image sub worked fine on my old computer but with new PC it exports blank image. Same operating system (Win7) and same Excel version (2016). Same program file.

When stepping though in debug mode, it works properly if the "Pic" worksheet is selected/activated.

Perhaps someone here can examine my code with fresh eyes and see if I am missing anything obvious. Thanks.

Sub C06_ExportImage()

Dim sFilePath As String
Dim sView As String
Dim Folder As String
Dim Chartobj As ChartObject
Dim Area As Range
    
'Captures current window view
sView = ActiveWindow.View

'Sets the current view to normal so there are no "Page X" overlays on the image
ActiveWindow.View = xlNormalView

'Temporarily disable screen updating
Application.ScreenUpdating = False

'Set Sheet = ActiveSheet
Set Sheet = ThisWorkbook.Worksheets("Pic")

sFilePath = ThisWorkbook.Worksheets("vars").Range("b18").Value & "archives\img\"

'Check to see if IMG folder exists, if not, make it
If Len(Dir(sFilePath, vbDirectory)) = 0 Then
   MkDir sFilePath
End If

sFilePath = sFilePath & Folder & ThisWorkbook.Worksheets("vars").Range("b5").Value

If ThisWorkbook.Worksheets("Vars").Range("B20").Value = "No" Then
    sFilePath = sFilePath & "_" & ThisWorkbook.Worksheets("vars").Range("b6").Value & ".png"
  Else
    sFilePath = sFilePath & ".png"
End If

'Export print area as correctly scaled PNG image, courtasy of Winand
'With Sheet
    zoom_coef = 200 / Sheet.Parent.Windows(1).Zoom
    Set Area = Sheet.Range(Sheet.PageSetup.PrintArea)
    Area.CopyPicture xlPrinter
    Set Chartobj = Sheet.ChartObjects.Add(0, 0, Area.Width * zoom_coef, Area.Height * zoom_coef)
'End With

Chartobj.Chart.Paste
Chartobj.Chart.Export sFilePath, "png"
Chartobj.Delete

'Returns to the previous view
ActiveWindow.View = sView

'Re-enables screen updating
Application.ScreenUpdating = True


'Tells the user where the image was saved
'MsgBox ("Export completed! The file can be found here:" & Chr(10) & Chr(10) & sFilePath)

End Sub

 

[Legacy 456129]

Sometimes you need to specify Do Until loop or 'DoEvents' to allow processes to catch up before VBA moves on to the next step. E.g.

Do Until ieBrwsr.readyState = READYSTATE_COMPLETE
Loop

This makes VBA wait until the webbrowser process has caught up loading a web page.

Sometimes it's even longer so build an On error Goto WaitAWhile as standard catch at bottom of procedures, which calls a sub that waits 30 seconds, then does a Resume Next and tries again beofre a final fail.

DoEvents is by far the easiest out of the box. Got that splattered all over the place wherever race condition develops.

 

[John_w]

I've seen others with this problem, but there doesn't seem to be a common solution.

My export image sub worked fine on my old computer but with new PC it exports blank image.

I've had this problem with using a temporary chart to create an image file resulting in a blank image with Excel 2016.

The solution is to activate the chart, so add Chartobj.Activate immediately above the Chartobj.Chart.Paste line.

 

[FatLane]

The solution is to activate the chart, so add Chartobj.Activate immediately above the Chartobj.Chart.Paste line.

This seems to have solved the problem. Much thanks...