We help IT Professionals succeed at work.
Get Started

VBA MsgBox command is dumping object variables?

295 Views
Last Modified: 2017-03-23
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)
    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

Open in new window

Thanks for any suggestions.
Comment
Watch Question
crystal (strive4peace) - Microsoft MVP, AccessRemote Training and Programming
CERTIFIED EXPERT
Top Expert 2015
Commented:
This problem has been solved!
Unlock 3 Answers and 8 Comments.
See Answers
Why Experts Exchange?

Experts Exchange always has the answer, or at the least points me in the correct direction! It is like having another employee that is extremely experienced.

Jim Murphy
Programmer at Smart IT Solutions

When asked, what has been your best career decision?

Deciding to stick with EE.

Mohamed Asif
Technical Department Head

Being involved with EE helped me to grow personally and professionally.

Carl Webster
CTP, Sr Infrastructure Consultant
Ask ANY Question

Connect with Certified Experts to gain insight and support on specific technology challenges including:

  • Troubleshooting
  • Research
  • Professional Opinions
Did You Know?

We've partnered with two important charities to provide clean water and computer science education to those who need it most. READ MORE