VBA code works in debug mode but fails to run in whole

Question

Sub Export_as_PDF()
Dim fil As Variant
Dim strfile As String
Dim PPApp As PowerPoint.Application
Dim PPSlide As PowerPoint.Slide
Dim SlideCount As Long
Dim ws As Worksheet
Dim Wb As Workbook

Set PPApp = New PowerPoint.Application

PPApp.Presentations.Add

' Slide 1

 PPApp.ActivePresentation.Slides.Add PPApp.ActivePresentation.Slides.Count + 1,ppLayoutBlank
Set PPSlide = PPApp.ActivePresentation.Slides    (PPApp.ActivePresentation.Slides.Count)
PPApp.ActiveWindow.View.GotoSlide PPApp.ActivePresentation.Slides.Count
Sheet2.Range("F106").Copy
PPApp.Activate
PPApp.CommandBars.ExecuteMso "PasteExcelTableSourceFormatting"

shapecount = PPSlide.Shapes.Count 'Error is here when shapecount = 0
PPSlide.Shapes(shapecount).Select

PPApp.ActiveWindow.Selection.ShapeRange.Left = 15
PPApp.ActiveWindow.Selection.ShapeRange.Top = 15
PPApp.ActiveWindow.Selection.ShapeRange.Width = 100

End Sub

I use the above code (only part of a code is shown) to copy cell ranges from excel and paste as tables in ppt that can be edited. The error occurs in the line 'PPSlide.Shapes(shapecount).Select ' It fails since shapecount = 0 . But if i choose to debug and run the previous line to count shapes, then shapecount is set to 1 and the code runs smooth. I am puzzled. Need help

When a question contains _works in debug mode but fails to run in whole_, there are always two things to check: 1) is there any line of code which is loading something, that the code should wait for before continue running? 2) Are you sure what you do in debug is exactly the same you do in run-time, and you're not probably using some `Active` object that should rather contain an univoque reference? And, looking at your code which is built all of `Active` stuffs, my bet is in debug you keep the right window active while you don't at run-time. Get rid of `Active` stuffs, use proper reference. — Matteo NNZ, Apr 21 '15 at 16:26
@MatteoNNZ Thanks for the suggestion. But I checked and found that its not due to both the possible reasons you mentioned. Can there be any other reason? I am pretty sure I am not activating any window while running in debug mode. — Syed Kaleel Awn, Apr 21 '15 at 18:21

score 0 · Answer 1 · answered Apr 21 '15 at 20:14

0

This is a tricky one. The problem lies in the way you are pasting the data into PowerPoint. If you were using standard VBA commands, the pasting would run in a sequence, meaning the code would wait until the data is successfully pasted.

By using ExecuteMso you can never be sure what's going on.

Try experimenting with this command

PPApp.ActiveWindow.View.PasteSpecial DataType:=ppPasteDefault

and with different DataType values to achieve your goal.

answered Apr 21 '15 at 20:14

Marek Stejskal

2,698
1
20
30

Thanks @Marek .. but I cant use the command you had mentioned because i need to paste multiple ranges as table that can be edited. I tried using OLE object data type with links = false but that became messy in powerpoint while trying to edit... – Syed Kaleel Awn Apr 25 '15 at 18:04
I don't understand what do you mean by "that can be edited". You want users to edit powerpoint presentation and those edits be reflected in the excel file? Can you provide screenshots of what you are trying to achive? – Marek Stejskal Apr 25 '15 at 19:27
I meant the excel cell ranges copied and pasted into powerpoint should not be an image. Instead it has to be a regular table in powerpoint so that i can edit it. I don't want the changes to be reflected in Excel. – Syed Kaleel Awn Jun 20 '15 at 19:41

score 0 · Accepted Answer · edited May 23 '17 at 12:29

Based on Marek Stejskal's suggestion, maybe give this a try:

Sub Export_as_PDF()
Dim fil As Variant
Dim strfile As String
Dim PPApp As PowerPoint.Application
Dim PPSlide As PowerPoint.Slide
Dim SlideCount As Long
Dim ws As Worksheet
Dim Wb As Workbook
Dim I as integer

  Set PPApp = New PowerPoint.Application

  PPApp.Presentations.Add

' Slide 1

  PPApp.ActivePresentation.Slides.Add _
    PPApp.ActivePresentation.Slides.Count + 1,ppLayoutBlank
  Set PPSlide = PPApp.ActivePresentation.Slides PPApp.ActivePresentation.Slides.Count)
  PPApp.ActiveWindow.View.GotoSlide PPApp.ActivePresentation.Slides.Count
  Sheet2.Range("F106").Copy
  PPApp.Activate
  PPApp.CommandBars.ExecuteMso "PasteExcelTableSourceFormatting"

  i = 0    
  'this loop will wait for .ExecuteMso to do its thing
  'while the "i" counter will prevent it from hanging forever
  While PPSlide.shapes.count = 0 and i < 1000
    do events
    i = i + 1
  wend

  shapecount = PPSlide.Shapes.Count 'Error is here when shapecount = 0
  PPSlide.Shapes(shapecount).Select

  PPApp.ActiveWindow.Selection.ShapeRange.Left = 15
  PPApp.ActiveWindow.Selection.ShapeRange.Top = 15
  PPApp.ActiveWindow.Selection.ShapeRange.Width = 100

End Sub

If i < 1000 isn't enough, try increasing it until either

it completes successfully, or
you get tired of waiting for it

I was earlier tryin application.wait. It worked when i used 10 seconds but still failed sometimes. But your suggestion is a gud idea.. I will try this! — Syed Kaleel Awn, Apr 25 '15 at 18:06

VBA code works in debug mode but fails to run in whole

2 Answers2

Linked