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
                Next
              Next
          End If
      Next
  Next
  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 tag mp3 files

I have a collection of mp3 files which I have named in the form "ARTIST – TITLE.mp3" and wanted to get them tagged properly.
My first plan was to write a Python script to do so, I tried two Python libraries: pytaglib and eyeD3. pytaglib didn’t install, on Windows you need a Visual Studio C++ compiler installed to make it work, which I don’t have currently. pytaglib was the reason why I tried to deal with ubuntu which confronted me with lots of other problems and finally didn’t buy me anything since pytaglib also didn’t install properly on ubuntu and ran into some other compile issues.
eyeD3 installed but apparenty can not handle modern mp3 tag formats.
I also tried MusicBrainz recommend in this article "How to tag all your audio files in the fastest possible way", but its user interface is weird and didn’t get me my files tagged. And I tried the linux id3tag command mentioned in the same article, again no success, looks like it does not support latest tag formats neither.
Then I bumped into Mp3tag for Windows. Brilliant. It made it a piece of cake to tag my mp3 files through a function ‘filename to tag’ where you can specify some sort of pattern for the filenames you have been using, %Artist% – %Title%.mp3 in my case, and a few clicks later all my files have been tagged properly.
I right away donated 5 bucks to the author of this freeware tool.

Enabling 1366×768 resolution in Windows 10

I have been struggling for a while to set this up: enabling  Display Settings so that they would support the 1366×768 resolution of my TV screen, after I connected my laptop with Windows 10 installed to my TV.1) In Display Settings this resolution simply was not offered.

Until I figured out this setting in Display Settings –> Advanced Display Settings –> Display Adapter Properties –> Monitor:

Removing the check mark from this check box revealed the resolution I was looking for. Apparently Windows didn’t have much of a clue about the connected monitor. OK, I admit, I connected it via a simple VGA cable.

Smiley

1)I gave up on ubuntu, too many things didn’t work out of the box ( like sending laptop to sleep when closing the lid ), too much googling and obscure hacking needed, and then display driver started crashing when laptop woke up later on. I do complain about Windows a lot, but Linux systems can be worse, and with Windows 10 Microsoft made a great move towards good quality.

How to enable 1366×768 screen resolution in ubuntu 16-04

It took me a while and some hacking until I figured out how to enable 1366×768 screen resolution in ubuntu 16-04 for my secondary display, a Fujitsu-Siemens TV from pre-HD era, means: HD ready but not providing the full resolution, just 1366×768.

Anyway, here is the magic:

cvt 1366 768 60
xrandr –newmode "1368x768_60.00"   85.25  1368 1440 1576 1784  768 771 781 798 -hsync +vsync
xrandr –addmode VGA-0 "1368x768_60.00"
xrandr –output VGA-0 –mode "1368x768_60.00"

This is sort of what distinguishes a linux system from a Windows system. Often you have to do a lot of weird hacking to achieve certain things.

Nevertheless, I must say, when I had Fedora 25 installed on my computer beforehand it offered 1368×768 screen resolution out of the box, ubuntu didn’t. Well, that’s what makes linux so exciting for many: the need to tinker and learn interesting technical, sometimes magical secrets.

Thanks to this internet articles I finally solved my problem:

“Resolution” in wiki.ubuntu.com

And again Windows 10 messed with my display driver …

And again today Windows 10 messed with my display driver …

Suddenly my screen turned black and after a couple of seconds my desktop came back, but a little later I realized that the ‘Sleep’ menu entry in the ‘Start’ –> ‘Power’ menu had been gone.

Running powercfg –a as recommended here revealed that the ‘Sleep’ capability had been disabled by my graphics driver. And checking my graphics driver revealed: it once more had been changed to ‘Microsoft Basic Display Adapter’.

I found 341.81-desktop-win10-64bit-international.exe in my download folder downloaded in October last year when this happened, ran it and let it re-install my NVIDIA graphics driver. I had to reboot – problem solved.

How often do you ask for help ?

This is about Gibbs’s Rule # 28: "When you need help, ask."

If you don’t know Gibbs you probably never watched the TV show “Navy CIS” ? This rule caught my attention when watching episode “Blood Brothers” recently.

How often do I ask for help when I need it ? Probably too seldom. Isn’t asking for help a sign of weakness ? Of course, not. Helping each other is what makes teams strong. But sometimes you hesitate to ask for help about something others would except you not to need any help for, right ? 

Saying “sorry” is a sign of weakness, according to Gibbs’s rules ( rule # 6 ). Is it ?

How to get shared folder to work with a virtual ubuntu in Virtualbox

Getting shared folder to work with a virtual ubuntu in Virtualbox is a bit of a nightmare.
Initially they simply can’t be accessed, even after re-installing Virtualbox guest add-ons.
The reason and solution is described here, but adding root account to group vboxsf didn’t work as well because of this problem: "usermod: cannot lock /etc/passwd; try again later.".

Thus, to summarize this mess, to make it work
# you have to reboot into recovery mode
# run this: mount -o remount,rw /
# then that: usermod -a -G vboxsf adminuser
# resume booting into normal mode

Problem solved.
Shared folders BTW can be found under /media.