Linked Lists in VBA (Excel)

I use Microsoft Excel quite a bit. And recently we had a challenge at work to validate lane use signal displays using conflict matrices. Well, I had some Lisp written to generate sequences given a set of allowed 'transitions'. So I wanted to put that logic into Excel, which meant VBA. I thought it would be quick, but it took about two and a half full days of work... Part of porting over my logic was creating a linked list class named "List" in VBA. It is my first VBA class module (yay!). Here's the code in the "Node" class module:

'' Linked Lists in VBA is released under a BSD licence. Author: Jonathan F Johansen
Option Explicit
Public Car As Variant
Public Cdr As List

Public Function ToString() As String
    ToString = "[" & PlainListString() & "]"
End Function

Public Function PlainListString() As String
    If Cdr Is Nothing Then
        PlainListString = CStr(Car)
    Else
        PlainListString = CStr(Car) & ", " & Cdr.PlainListString()
    End If
End Function

I had more in the class module, but took most of the methods out of there because I wanted to treat Nothing as the empty list. Here are the functions from a separate module:

'' Linked Lists in VBA is released under a BSD licence. Author: Jonathan F Johansen
Option Explicit

'' Making Lists:

Function Cons(Item As Variant, Optional Rest As List) As List
    Set Cons = New List
    Cons.Car = Item
    Set Cons.Cdr = Rest
End Function

Function MakeList(ParamArray Items() As Variant) As List
    On Error GoTo ZeroLength ' Leaving MakeList as Nothing.
    Dim I As Long
    For I = UBound(Items) To LBound(Items) Step -1
        Set MakeList = Cons(Items(I), MakeList)
    Next I
ZeroLength:
End Function

'' Working with Lists

Function Append(aList As List, OtherList As List) As List
    If aList Is Nothing Then
        Set Append = OtherList
    ElseIf OtherList Is Nothing Then
        Set Append = aList
    ElseIf aList.Cdr Is Nothing Then
        Set Append = Cons(aList.Car, OtherList)
    Else
        Set Append = Cons(aList.Car, Append(aList.Cdr, OtherList))
    End If
End Function

Function Reverse(aList As List, Optional OntoFrontOf As List) As List
    If aList Is Nothing Then
        Set Reverse = OntoFrontOf
    Else
        Set Reverse = Reverse(aList.Cdr, Cons(aList.Car, OntoFrontOf))
    End If
End Function

Function Length(aList As List) As Long
    If aList Is Nothing Then Exit Function 'Returning 0
    Length = 1 + Length(aList.Cdr)
End Function

Function Member(Item As Variant, aList As List) As Boolean
    If aList Is Nothing Then Exit Function
    If aList.Car = Item Then
        Member = True
    ElseIf aList.Cdr Is Nothing Then
        Member = False
    Else
        Member = Member(Item, aList.Cdr)
    End If
End Function

Function Replace(OldItem As Variant, NewItem As Variant, aList As List) As List
    If aList Is Nothing Then
    ElseIf aList.Cdr Is Nothing Then
        If aList.Car = OldItem Then
            Set Replace = Cons(NewItem)
        Else
            Set Replace = aList
        End If
    Else
        If aList.Car = OldItem Then
            Set Replace = Cons(NewItem, Replace(OldItem, NewItem, aList.Cdr))
        Else
            Set Replace = Cons(aList.Car, Replace(OldItem, NewItem, aList.Cdr))
        End If
    End If
End Function

Function Remove(Item As Variant, aList As List) As List
    If aList Is Nothing Then Exit Function
    If Item = aList.Car Then
        Set Remove = Remove(Item, aList.Cdr)
    Else
        Set Remove = Cons(aList.Car, Remove(Item, aList.Cdr))
    End If
End Function

Function Count(Item As Variant, List As List) As Long
    If List Is Nothing Then Exit Function
    If List.Car = Item Then Count = 1
    Count = Count + Count(Item, List.Cdr)
End Function

Function CountMaxConsecutive(Item As Variant, List As List) As Long
    If List Is Nothing Then Exit Function
    Dim Rest As List, Count As Long
    Set Rest = List
    Do Until Rest Is Nothing
        If Rest.Car = Item Then
            Count = Count + 1
        Else
            Count = 0
        End If
        If Count > CountMaxConsecutive Then CountMaxConsecutive = Count
        Set Rest = Rest.Cdr
    Loop
End Function

Function CellTextToList(aRange As Range) As List
    Dim I As Long
    If IsEmpty(aRange) Then Exit Function
    For I = aRange.Cells.Count To 1 Step -1
        Set CellTextToList = Cons(aRange.Cells(I).Text, CellTextToList)
    Next I
End Function

Function ListToRow(aList As List, Target As Range) As Range
    Dim List As List
    Set List = aList
    Set ListToRow = Target
    Do Until List Is Nothing
        ListToRow = List.Car
        Set ListToRow = ListToRow.Offset(0, 1)
        Set List = List.Cdr
    Loop
End Function

I found it useful, but it could do with a few more utilities. One interesting thing is that looping over a list isn't to complex, as you can see in the last function above. You just declare a local List variable and then use a Do Until localList Is Nothing, and at the end of the loop body, Set localList = localList.Cdr. If you don't declare a local list, but modify the argument, you'll find that the list is chewed up in the caller's scope too...

Here are some examples of the List in action:
Option Explicit

Sub TestLinkedLists()
    MsgBox Cons("a", Cons("b")).ToString
    MsgBox Length(MakeList(1, 2, 3, 4, 5))
    MsgBox Reverse(MakeList(1, 2, 3, 4, 5)).ToString
    MsgBox MakeList(1, 2, 3, 4, 5).ToString
    MsgBox Member(4, MakeList(1, 2, 3, 4, 5))
    MsgBox Member(4, Nothing)
    MsgBox Append(MakeList(1, 2, 3, 4), MakeList(5, 6, 7)).ToString
    MsgBox Replace(4, "four", MakeList(1, 2, 3, 4, 5)).ToString
    MsgBox Remove(3, MakeList(1, 2, 3, 4, 5)).ToString
    MsgBox Count(True, MakeList(False,True,True,True,False,True))
    MsgBox CountMaxConsecutive(True, _
                     MakeList(False, True, True, True, False, True))
    MsgBox CellTextToList(Range("A2:A6")).ToString
    MsgBox "List output to B2, range returned is " _
         & ListToRow(CellTextToList(Range("A2:A6")), Range("C2")).Address _
         & " ready to go for something else..."
End Sub

If you've read the code, you might notice the liberal use of recursion, and the use of functional style. I used recursion a lot in the spreadsheet, and I think this List class helped a lot.

The code on this page is collected into an Excel spreadsheet for convenience too. You can download it from here. As noted in the code comments, the code is released under the BSD license - and I'd love to hear from you if you use it or extend it. Enjoy!

Excel Table to Jira Table

Excel table, meet Jira :-). After writing the previous AutoHotkey script to convert the clipboard contents to a comma separated list, I wrote this one. It makes constructing Jira tables a breeze.

;;; Converts a copied table from Excel into Jira format
#+j::
  StringReplace, ForFirstLine, Clipboard, `r`n, §
  StringSplit, ForFirstLine, ForFirstLine, §
  StringReplace, ForFirstLine1, ForFirstLine1, %A_Tab%, ||, All
  StringReplace, ForFirstLine2, ForFirstLine2, %A_Tab%, |, All
  ForFirstLine2 := RTrim(ForFirstLine2, "`r`n")
  StringReplace, ForFirstLine2, ForFirstLine2, `r`n, |`r`n|, All
  Clipboard := "||" . ForFirstLine1 . "||`r`n|" . ForFirstLine2 . "|"
  Send ^v
Return

To use it, just press Windows+Shift+J (but that's easily changed). It puts double pipes on the first line, and single pipes on other lines.

Clipboard Processing with AutoHotkey

I've been doing a lot of text processing recently, and a lot of it has been to convert a column of cells from Excel into a comma separated list. Well, I used to do that with Notepad++, but last week I bit the bullet and wrote an AutoHotkey script to do it for me. Without further ado, here are the goods:

#v::
    StringReplace, Clipboard, Clipboard, `r`n, `,%A_Space%, All
    Clipboard := RTrim(Clipboard, ", `n") ; Gets rid of the annoying trailing newline that Office programs put in.
    Send ^v
Return

It will paste the converted clipboard contents when you press Windows+V. Also, I found a way to get text/content copied from MS Office to plain, plain text by removing the trailing newline character.

Notepad++ Find and Replace

I think Notepad++ is a great program. I use it a lot at work and at home. I have used it to process text in various ways. I really like Notepad++'s find and replace options, especially since the Regular Expression engine was updated to PCRE. A great post on Notepad++'s find and replace can be found on Mark's Speechblog. In the examples below, don't type in the double quotes. Here are a few Find and Replace searches I have found useful:

This one turns new lines into commas with spaces. I use it to convert a column of Excel data into text as a comma separated list. I've since written an AutoHotkey script to do this too (because I do this so often), which will be the topic of a future post.
Search Mode: Extended
Find: "\r\n"
Replace with: ", "

This finds a specific second column of freeform text from several other columns with digits. The second find string converts a line like "11937577    M0609/VD06   108721      11933802    198024      11933835" into just "M0609/VD06".
Search Mode: Regular expression
Find: "\d+ +(.+)" and "\d+ +(.+) +\d+ +\d+ +\d+ +\d+"
Replace with: "\1"

This one uses start and end of line anchors to ensure the whole line is matched, and grabs the last column. It converts something like "108608     Left turn MVT from Road SEB to Ave NEB    NULL       9      7774/SEB/LQ" into "7774/SEB/LQ".
Search Mode: Regular expression
Find: "^.* +(.+)$"
Replace with: "\1"

This one puts a capital M in front of lines that begin with a digit.
Search Mode: Regular expression
Find: "^(\d)"
Replace with: "M\1"

This one splits some text in a specific tabular form into separate lines.
Search Mode: Regular expression
Find: "(\d+/[^\t\r\n]*\t[^\t\r\n]*\t[^\t\r\n]*\t(SB|NB)\t[^\t\r\n]*\t[^\t\r\n]*\t[^\t\r\n]*\t[^\t\r\n]*\t[^\t\r\n]*\t)"
Replace with: "\1\r\n"

This one removes a tab from the end of a line if it has any. I should have put the last \t in the above regular expression outside of the group, as in "(...[^\t\r\n]*)\t".
Search Mode: Regular expression
Find: "\t$"
Replace with: "" (nothing)

For this find and replace, I wanted to wrap everything on non-empty lines with some other text (in this case it's for a custom SQL query).
Search Mode: Regular expression
Find: "^(.+)$"
Replace with: "   or v.Identifier like '%\1%'"

Have fun with Notepad++!

Simple Glossaries in LaTeX

I use LaTeX (via MikTeX) and TeXnicCenter a bit, starting from when I did my PhD. It's a great document preparation system which has some annoying sides, but I won't go down that path. I tried to set up a glossary in a document recently, and it wasn't obvious to me how to do that. I did get it working though. Here's what I did, so that others (including myself) don't have to figure it out again:

1. Follow the steps provided at LaTeX Matters, namely:
1.  Put two lines into your preamble (before \begin{document}):
   \usepackage{glossary}
   \makeglossary
2. When you define or introduce certain terms, wrap them in a \glossary command like so:
   \glossary{name={entry name}, description={entry description}}
   The inside curly braces are only necessary if you have commas in the entries.
3. Place this command where you want the glossary to appear:
   \printglossary
4. If you want your glossary to have an entry in the Table of Contents, then put this with the previous line:
   \addcontentsline{toc}{chapter}{Glossary}
5. Then, instead of running a command as described in the post, follow the next step, which is equivalent, but for TeXnicCenter.
2. Prompted by Archiddeon's comment in a LaTex forum, in TeXnicCenter, add a post-processing step to a build profile:
1. Select the Build menu then choose Define Output Profiles...
2. On the left, select a profile you want to start from
3. Click the Copy button down below, enter a new name for the profile and click OK
4. Then click on the Postprocessor tab
5. Create a new postprocessor step by clicking the little "new folder" icon at the top right
6. Then enter a name like "Prepare the glossary" and the following:
   Executable: makeindex
   Arguments: "%tm.glo" -s "%tm.ist" -t "%tm.glg" -o "%tm.gls"
3. Click OK until you're back at the main window!

Then you should be able to select the new profile and generate the document with a glossary! I'd run it 2-3 times to let the page numbers settle. Joy!