PowerPoint VBA and the Purpose of Thursday

Most weekly newspapers publish on Thursday because this is the day when the majority of young people plan their weekends. Thursday is a good day for a lot of things; it's when I have my team meeting. I choose Thursday because there's a good bit of news to recap with the team, and there's a bit of time to put things on track that need to be put on track. As it turns out Thursday (especially this one) is a good day for a blog post. I have some things to share, and some news that is worth reading about Office 2010 for Developers.

Did you see the newly designed Office Developer Center on MSDN?

For designers: A quick taste of PowerPoint VBA. People who know me well have witnessed my neurosis for PowerPoint slide design, particularly how type is treated. I lay awake at night wondering how much mental sludge one accumulates after looking at poorly set type over a period of years. For an untrained eye poorly designed slides are hard to read. For a designer they're like nails on a chalkboard. It doesn't have to be this way.

I review a lot of decks (on the order of 50 per week as a guess). I'm famous for fixing them before I can read them. I can't sit and read a bunch of text that is mashed together or poorly organized. I am compelled to act. As a compromise to the people sitting in my office watching me re-typeset their decks, I spent some time coding up a simple VBA add-in to programmatically fix the things that drive me up the wall. I'd like to share a brief sample of that for folks who need an introduction to VBA in PowerPoint. One of the things that I must fix is the line spacing of text. In auto-fit mode, PowerPoint will compress that line spacing down to values as low as 0.9. This gives me the same claustrophobia as bunk beds that are stacked too closely together. The VBA routine below fixes the line spacing issue.

The magic is highlighted below. It reviews each shape on a given slide, checks whether that shape is a text frame, and sets the "SpaceWithin" attribute to 1.0, giving lines 100% height, and a little air to breathe. The remaining code is a few simple loops and variables – probably 100 ways to do it better, the example is crude. But this saves me a tremendous amount of time.

Warning: this may shrink your type in auto-fit mode, but I'll take smaller type sizes over the claustrophobic bunk bed problem 100% of the time. I have similar modules for getting rid of underline type, fixing user-inflicted kerning (tracking / character spacing) problems, and a handful of other things. Each of them took no more than 10 minutes to code, and I save at least 10 minutes of review time on almost every slide deck I see. – per my simple math above, that's 500 minutes a week. Message to fellow designers: It's worth the time to learn how to use VBA in PowerPoint.

Option Explicit
Sub FixLineSpacing()

Dim i,y,j,z As Integer
y = 1
z = 1
i = ActivePresentation.Slides.Count + 1

While y < i

With ActivePresentation.Slides(y)
j = ActivePresentation.Slides(y).Shapes.Count + 1

While z < j
With ActivePresentation.Slides(y).Shapes(z)
If ActivePresentation.Slides(y).Shapes(z).HasTextFrame Then
With .TextFrame.TextRange
.Lines.ParagraphFormat.SpaceWithin = 1
End With
End If
End With
z = z + 1

Wend
z = 1

End With
y = y + 1

Wend

End Sub

By the way I am very aware that the setup to explain what the thing does and why I have it is longer than the code itself -- I encourage you to draw conclusions about the complexity of coding in VBA. It is not a hard mountain to climb.

The great thing about the product launch window is that there is so much great information to share. Following is a list of very good blog posts relating to Office 2010 development. There are enough now that they're hard to keep track of:

• John Durant discusses some VBA routines in Excel to help with data analysis.
• Mirko Mandic discusses some of the changes to the Fluent™ User Interface extensibility in Office 2010.
• TechRepublic gives us a little more broad view of Excel 2010
• Redmond Developer News gave the Office 2010 app compat program a little ink. Great to see and much appreciated.
• Eric White has yet another outstanding post on working with Open XML – this time presenting SharePoint lists in documents
• Asif Rehmani breaks down some of the new capabilities in InfoPath 2010
• John Morgan illustrates some of the problems with the Seahawks and what it will take to get them on track. Good thing we have the Lions at home this Sunday, although I fear that this might mess up our march toward the #1 draft pick.

Comments

• Anonymous
  January 01, 2003
  Hi Lori (if this is the same Lori from back in my publishing systems heyday :)), Thanks for your comment.. yes, the VBA stuff is fun to do, I plan to offer more of the code in future posts in the form of VS2010 & Visual Basic add-ins. Mostly similar language and OM calls with VBA & VB, but some minor differences. Have lots to share about PPT fix-up!

• Anonymous
  November 06, 2009
  Hi all.. really i am happy to see this site and all.. it has lot of information's to the public.. thanks for sharing this in internet... http://www.dmaxonline.com

• Anonymous
  November 17, 2009
  Thank you for posting your scripts for PowerPoint. I have been fighting some of the same issues with decks provided to me to redesign. This should make some of my work MUCH easier. At least it is not like AppleScripting on Belgium keyboard.