Avatar of Bryce Bassett
Bryce Bassett
Flag for United States of America

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.
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)
        Set wdDoc = wdApp.Documents.Open(FileName:=doc2open)
        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
    End If
Next x

Open in new window

Thanks for any suggestions.
Microsoft OfficeVBAMicrosoft PowerPoint

Avatar of undefined
Last Comment
Bryce Bassett

8/22/2022 - Mon