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:
“fedcb”
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
Else
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]
else:
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]
else:
return pstring[strlen-(lastchar)::-1]
Both versions of the functions can be downloaded from Palin.zip.
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:
Newton Excel Bach, not (just) an Excel Blog 