## Saturday, 15 June 2013

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

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 " _
& " 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!

## Saturday, 8 June 2013

### 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, rn, §
StringSplit, ForFirstLine, ForFirstLine, §
StringReplace, ForFirstLine1, ForFirstLine1, %A_Tab%, ||, All
StringReplace, ForFirstLine2, ForFirstLine2, %A_Tab%, |, All
ForFirstLine2 := RTrim(ForFirstLine2, "rn")
StringReplace, ForFirstLine2, ForFirstLine2, rn, |rn|, All
Clipboard := "||" . ForFirstLine1 . "||rn|" . 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.

## Saturday, 1 June 2013

### 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, rn, ,%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.

## Friday, 24 May 2013

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%'"

## Saturday, 16 February 2013

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