VBA Macro Works Only in Debug (Step Through)

FatLane

New Member
Joined
May 17, 2009
Messages
32
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.

VBA Code:
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
 

Excel Facts

Shade all formula cells
To shade all formula cells: Home, Find & Select, Formulas to select all formulas. Then apply a light fill color.
L

Legacy 456129

Guest
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.

VBA Code:
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

MrExcel MVP
Joined
Oct 15, 2007
Messages
6,751
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.
 

Watch MrExcel Video

Forum statistics

Threads
1,129,497
Messages
5,636,662
Members
416,935
Latest member
Atulcp

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top