PowerPoint VBA and the Purpose of Thursday

  • Article

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: