Reversing Bob

Following our look at Weird Al Jankovic’s palindromic song, Bob, I have written two short VBA User Defined Functions (UDFs) to help writing palindromes (and possibly with other tasks as well).

Reverse() will, as the name suggests, reverse any text entered as the argument, for instance:
” I, man, am regal – a German am I” becomes:
” I ma namreG a – lager ma ,nam ,I”

Reverse also has two optional arguments to start and stop at specified characters, counting from the start and end of the original string, so:
=reverse(“abcdefgh”,2,3) returns:

Palin() returns a palindrome consisting of the argument string followed by the string reversed, so:
=Palin(“Never od”) returns:
“Never oddo reveN”

Palin has two optional arguments:
“Rev” = False (default) will start with the input string, followed by the reversed string, whereas Rev = True will put the reversed string first.
“RepeatCen” = False (default) will not repeat the last character of the input string, whereas True will repeat the character.

The code for these two functions is quite short:

Function Reverse(Base As String, Optional FirstChar As Long = 1, Optional LastChar As Long = 1) As String
Dim Rtn As String, i As Long

For i = Len(Base) - LastChar + 1 To FirstChar Step -1
    Rtn = Rtn & Mid(Base, i, 1)
Next i
Reverse = Rtn

End Function

Function Palin(Base As String, Optional Rev As Boolean = False, Optional RepeatCen As Boolean = False) As String
Dim Rtn As String, i As Long, LastChar As Long

If RepeatCen = True Then LastChar = 1 Else LastChar = 2

If Rev = True Then
    Rtn = Reverse(Base, LastChar, 1)
    Palin = Rtn & Base
    Rtn = Reverse(Base, 1, LastChar)
    Palin = Base & Rtn
End If

End Function

But since one of the functions is named Palin, we really should do it in Python, which is even shorter:

def palin(pstring, rev, repeatcen):

    lastchar = 1
    if repeatcen == True: lastchar = 0
    strlen = len(pstring)
    if rev == True:
        return pstring[::-1] + pstring[lastchar:strlen]
        return pstring + pstring[strlen-1-lastchar::-1] 

def reverse(pstring, firstchar, lastchar):
    strlen = len(pstring)
    if firstchar > 1:
        return pstring[strlen-(lastchar):firstchar-2:-1]
        return pstring[strlen-(lastchar)::-1]

Both versions of the functions can be downloaded from

The Python version includes all the necessary ExcelPython files, but will need an installed copy of Python.

The files also include the full lyrics of Bob, both forwards and backwards:


This entry was posted in Excel, Link to Python, UDFs, VBA and tagged , , , , , . Bookmark the permalink.

Leave a Reply

Fill in your details below or click an icon to log in: Logo

You are commenting using your account. Log Out /  Change )

Google+ photo

You are commenting using your Google+ account. Log Out /  Change )

Twitter picture

You are commenting using your Twitter account. Log Out /  Change )

Facebook photo

You are commenting using your Facebook account. Log Out /  Change )


Connecting to %s

This site uses Akismet to reduce spam. Learn how your comment data is processed.