asked on
VBA MsgBox command is dumping object variables?
Programming in PowerPoint 2010 VBA, an automatic bio slide maker. The user selects up to 6 bios from a picklist, then the macro inserts photos and text elements. Name, title, and bio text for each person are kept in separate Word documents (for ease of maintenance), and I use VBA, for each list chosen, to open the document, parse out the pieces, and stick them on the slide in the proper Text Placeholders.
Because PowerPoint assigns new shape names when a slide is created from a layout, I use a complicated part of the macro (not shown here) to iterate all the Text Placeholders and use their position on the slide to assign them to a shape array that I can fill later.
Everything is working great as long as the macro finds all the supporting files. For testing purposes, I renamed one of the word docs deliberately so it does not match the name on the pick list. In that case, I want to warn the user, then continue on to fill the remaining placeholders. As expected, when it runs into the missing doc, it warns, but then it loses track all of the remaining shape array objects and gives me an "Object Required" error. I verified in the Immediate Pane that all of the biotextshapes have been dumped. It doesn't matter whether it's the first name or somewhere in the middle, it works perfectly up until the msgbox, then the remaining code fails.
When I try commenting out the msgbox, it skips the name it can't find, but then finishes the remaining ones correctly. So I'm left to conclude there is something about the MsgBox line itself that is causing VBA to lose track of the shape array I already assigned. Has anybody ever run into this behavior and have a workaround? I could always hold the msgbox until after the For/Next loop completes, but I'm really stumped why it is doing this.
Because PowerPoint assigns new shape names when a slide is created from a layout, I use a complicated part of the macro (not shown here) to iterate all the Text Placeholders and use their position on the slide to assign them to a shape array that I can fill later.
Everything is working great as long as the macro finds all the supporting files. For testing purposes, I renamed one of the word docs deliberately so it does not match the name on the pick list. In that case, I want to warn the user, then continue on to fill the remaining placeholders. As expected, when it runs into the missing doc, it warns, but then it loses track all of the remaining shape array objects and gives me an "Object Required" error. I verified in the Immediate Pane that all of the biotextshapes have been dumped. It doesn't matter whether it's the first name or somewhere in the middle, it works perfectly up until the msgbox, then the remaining code fails.
When I try commenting out the msgbox, it skips the name it can't find, but then finishes the remaining ones correctly. So I'm left to conclude there is something about the MsgBox line itself that is causing VBA to lose track of the shape array I already assigned. Has anybody ever run into this behavior and have a workaround? I could always hold the msgbox until after the For/Next loop completes, but I'm really stumped why it is doing this.
For x = 0 To Me.ListBox2.ListCount - 1
doc2open = contentlibraryfolder & "\Staff\BIO Text\" & Me.ListBox2.List(x, 0) & " " & Me.ListBox2.List(x, 1) & ".docx"
If Dir(doc2open) = "" Then
MsgBox "bio text not found for " & Me.ListBox2.List(x, 0) & " " & Me.ListBox2.List(x, 1)
Else
Set wdDoc = wdApp.Documents.Open(FileName:=doc2open)
' NEXT LINE IS WHERE IT THROWS AN 'OBJECT REQUIRED' ERROR IN THE NEXT LOOP AFTER SHOWING THE MSGBOX
biotextshape(x + 1, 1).TextFrame.TextRange.text = left(wdDoc.Paragraphs(1).Range, Len(wdDoc.Paragraphs(1).Range) - 1) ' name
biotextshape(x + 1, 2).TextFrame.TextRange.text = left(wdDoc.Paragraphs(2).Range, Len(wdDoc.Paragraphs(2).Range) - 1) ' title
biotextshape(x + 1, 3).TextFrame.TextRange.text = wdDoc.Paragraphs(3).Range
wdDoc.Close
End If
Next x
Microsoft OfficeVBAMicrosoft PowerPoint
Last Comment
Bryce Bassett