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:
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: