Tag Archives: vba

Word VBA: for speed, avoid Document.Paragraphs(idx)

by

0

In Word VBA, calling doc.Paragraphs(idx) to get a paragraph by its index is very slow.  Instead, once you have a Paragraph object, use para.Next and para.Previous to navigate, and your code will be much faster!  I just refactored an old routine this way and received a dramatic speedup.

I think doc.Paragraphs() must be iterating through the document starting from the beginning, but I don’t know that for sure.  I do know that adding paragraphs before the region my code was working with made the code run perceptibly slower, even though the code was never touching the added paragraphs.

Here are some simple helpers, in case you want to manually check for errors rather than throwing when you run off the end of the document:

Private Function NextParaOrNothing_(p As Paragraph) As Paragraph
    Set NextParaOrNothing_ = Nothing
    On Error Resume Next
    Set NextParaOrNothing_ = p.Next
End Function 'NextParaOrNothing_

Private Function PrevParaOrNothing_(p As Paragraph) As Paragraph
    Set PrevParaOrNothing_ = Nothing
    On Error Resume Next
    Set PrevParaOrNothing_ = p.Previous
End Function 'PrevParaOrNothing_

 

How to delete all the animations in a PowerPoint presentation using VBA

by

0

Very useful! This answer is copied from here. I tried to archive it, but archive.org wasn’t able to save it for some reason. I am posting it here so it can be archived for posterity.

answerspleasee asked on October 19, 2010
 Q:
 How to delete all the animations in a presentation
 heyehy

how do I delete all animations i have put on for the past 200 slides.

k.thanks.bye

Shyam Pillai replied on October 19, 2010
 MVP
 A:
 Hi,

You can turn off the animations by going to Setup Slide Show and under Show Options tick the Show without animation option and click OK. Now run the show and it will display without the animations.

If you really want to delete all the animations in a single sweep then you will need to run this macro.

Sub StripAllBuilds()
 Dim I As Integer: Dim J As Integer
 Dim oActivePres As Object
 Set oActivePres = ActivePresentation
 With oActivePres
 For I = 1 To .Slides.Count
 If val(Application.Version) < 10 Then
 ' Older versions of PowerPoint 97/2000
 ' In each slide set the animation property
 ' of the Shape object to FALSE
 For J = 1 To .Slides(I).Shapes.Count
 .Slides(I).Shapes(J).AnimationSettings.Animate = msoFalse
 Next J
 Else
 ' New versions support the Timeline object
 For J = .Slides(I).TimeLine.MainSequence.Count To 1 Step -1
 .Slides(I).TimeLine.MainSequence(J).Delete
 Next J
 End If
 Next I
 End With
 Set oActivePres = Nothing
End Sub

Regards, Shyam Pillai. http://skp.mvps.org
 Regards
 Shyam Pillai

http://skphub.com

 

Breaking infinite loops in VBA

by

1

I do a lot of Visual Basic for Applications (VBA) development in Microsoft Office. Any program can get stuck in an infinite loop, and when my VBA programs have it has always meant data loss. The Internet to the rescue, as of not quite a month ago!

To break VBA code execution, at least in Office 2013, hit Ctrl+Scroll Lock.  Obvious, right? 😉

Huge thanks to MSDN user povidla, who posted this solution in the MSDN forums.  Povidla, if you’re reading this, please comment so I can thank you directly!

Edit I have found that, in some situations, this doesn’t work unless there is a DoEvents call to catch the Ctrl+ScrLk.  If you have a loop that might be infinite, sticking a DoEvents in it is probably a good practice.

Greetings to Stack Overflow users!  I keep hoping that Povidla will find this page.  If you happen to know who Povidla is, please pass along a thank you from me.  Regardless, thanks for reading!