Powerpoint VBA Code

Finding documentation about how to write VBA code for MS Powerpoint is a challenge. When it comes to writing VBA macros most people think about number crunching with MS Excel first.

I got a presentation from our offering management containing RTC work item numbers and I wanted to write a VBA macro to extract those numbers ( to then run a RTC query to cross-check those work items in RTC itself ). Should be a piece of cake, shouldn’t it ? Well …

I bumped into some material here and here about the Powerpoint Object Model, but at the end this was not that helpful. At least it got me started, together with this article on Lifehacker how to loop through slides and shapes in a Powerpoint presentation.

I started to use the VBA Development Environment in Powerpoint and especially the Object Browser to discover what type of objects to use. I used a lot of intuition to go fishing in the sea of classes and members . At the end I figured it out, thus here is the code to get the job done and loop through all text in all table cells in all tables and look for digits of length 5 or 6:

Sub ExtractTextFromTableCells()

  Dim slide As Object
  Dim shape As Object
  Dim regEx As Object
  Dim strPattern As String: strPattern = "^\d{5,6}"
  Dim word As String
  Dim listOfIds As String
  listOfIds = ""
  Set regEx = CreateObject("vbscript.regexp")
  With regEx
        .Global = True
        .MultiLine = False
        .IgnoreCase = False
        .Pattern = strPattern
    End With
  Debug.Print "——————————————-"

  For Each slide In ActivePresentation.Slides
      For Each shape In slide.Shapes
          If shape.HasTable Then
              For Each Row In shape.Table.Rows
                For Each Cell In Row.Cells
                    txt = Cell.shape.TextFrame.TextRange.Text
                    If regEx.test(txt) Then
                        Dim WrdArray() As String
                        WrdArray() = Split(txt)
                        For i = LBound(WrdArray) To UBound(WrdArray)
                            Dim WrdArray2() As String
                            WrdArray2() = Split(WrdArray(i), ",")
                            For j = LBound(WrdArray2) To UBound(WrdArray2)
                                word = Replace(WrdArray2(j), " ", "")
                                word = Replace(word, "\n", "")
                                word = Replace(word, "|", "")
                                If regEx.test(word) And word <> "" Then
                                    listOfIds = listOfIds & word & ","
                                End If
                            Next j
                        Next i
                    End If
          End If
  Debug.Print listOfIds
End Sub

That script will return a comma separated list of RTC work item ids which can be easily used in a RTC query like so:

How to write your own OpenOffice Calc Functions

Today I have been looking for a function in OpenOffice which I would call ( and know from many programming languages ) ‘substring’ and which would allow me to split a string into chunks using a pre-defined delimiter and then return one or more words from this collection of chunks. Did I explain this well ? If not, here is an example: from a string like ‘+   6.84%’ I just need the numeric part, the 6.84 so to speak – to then be able to convert that to a number.

Unfortunately such a function is not offered with OpenOffice. Or should I say … fortunately ? Because I actually started to figure out how to write my own functions in OpenOffice using Visual Basic For Applications, aka VBA. And at the end of the day I know how to do this and probably write many more functions in the future.

Here is my substring function in VBA:

Function substring(str As String, start As Integer, optional n As Integer, optional delim As String) As String
  If isMissing(n) Then
      n = 1
  If isMissing(delim) Then
      delim = " "
  out_str = ""
  cnt = 0
  Dim arr() as String 
  arr = Split(str,delim)
  For i = 0 to UBound(arr)
      If (i+1) >= start Then
          If arr(i) <> "" Then
                    cnt = cnt + 1            ' Ignore empty values
          if cnt <= n Then
              if cnt = n Then
                  delim = ""                ' no more delimiter needed at the end
              out_str = out_str & arr(i) & delim
  substring = out_str
End Function

As you probably can see it takes a string and a starting position as parameter 1 and 2, the number of words to return and the delimiter to use as optional parameter 3 and 4. Parameter 3 will be 1 as default, parameter 4 a white space, if not specified.

Here is an example how I now can use this function to do my data tweaking together with two of the built-in functions SUBSTITUTE and VALUE to finally achieve what I described above:

=VALUE(SUBSTITUTE(SUBSTRING(SUBSTRING(A1;2;1;” “);1;1;”%”);”.”;”,”))

would get me 6,84 in numeric format if cell A1 contains ‘+   6.84%’.

So far, so good. How to get such a function into OpenOffice Calc ?

It starts with navigating to the Macro Organizer: Tools –> Macros –> Organize Macros –> OpenOffice.org Basic

From here I select “My Macros” –> “Standard” –> “Module1” and then click the Edit button. An IDE for VBA opens allowing me to paste in the code I have shown above. Done.

That IDE is quiet useful since it also allows me to do debugging, like setting breakpoints and inspecting the content of variables.