Excel Version Control with Git

Posted on May 2, 2018 by Dick Kusleika

This is a guest post from reader Björn Stiel.

Let’s Git Excel Under Version Control!

Git has become one of the most popular and trusted version control systems. In fact, so popular that even Microsoft moved their Windows codebase to Git. Why is Git so popular? Well, Git’s branching model is simple and works (branches provide isolated environments for changes without interfering with production-quality code on master and other branches). Also, Git is very flexible. It can be extended and customized in a myriad of imaginable (and unimagibale) ways, which is the basis for any successful ecosystem.

So what about version-controlling Excel workbooks then? If Git works great for code and is customizable, what does it actually take to make it work for Excel workbooks, too? Let’s get started with what happens when we put an Excel workbook file under Git version-control (I’m assuming some basic Git knowledge here, so if you are an absolute Git noob, have a look the excellent Atlassian Git tutorial, or, if you are are very short on time, check out the simple Git guide.

Let’s hit the ground running and get started with an example repository that contains a simple Excel workbook named Book1.xlsb. Clone the repo…

C:\Users\Bjoern>git clone https://github.com/ZoomerAnalytics/git-xltrail-examples.git

1	C:\Users\Bjoern>git clone https://github.com/ZoomerAnalytics/git-xltrail-examples.git

…and have a look at Book1’s version history (aka the file’s commit log):

C:\Users\Bjoern>git log Book1.xlsb

commit 429ee1ff383b8c706aa69c6a87f3a2c50fa1bcd1
Author: Bjoern Stiel <bjoern.stiel@zoomeranalytics.com>
Date:   Fri Feb 9 14:17:14 2018 +0000

	Added new VBA Module

commit 04b45b99c883e5d184a20cfd73e4556ef8d06bfd
Author: Bjoern Stiel <bjoern.stiel@zoomeranalytics.com>
Date:   Fri Feb 9 14:14:34 2018 +0000

	Added README.md

commit f9b5dadca880808ea1e73e252cc8ee236f98499e
Author: Bjoern Stiel <bjoern.stiel@zoomeranalytics.com>
Date:   Fri Feb 9 14:14:16 2018 +0000

	Added ExtractNumber VBA function

commit 37b7bfde9464209217dc57214d16a6cd707d6f21
Author: Bjoern Stiel <bjoern.stiel@zoomeranalytics.com>
Date:   Fri Feb 9 14:06:33 2018 +0000

	First commit

C:\Users\Bjoern>git log Book1.xlsb

commit 429ee1ff383b8c706aa69c6a87f3a2c50fa1bcd1

Author: Bjoern Stiel <bjoern.stiel@zoomeranalytics.com>

Date: Fri Feb 9 14:17:14 2018 +0000

Added new VBA Module

commit 04b45b99c883e5d184a20cfd73e4556ef8d06bfd

Author: Bjoern Stiel <bjoern.stiel@zoomeranalytics.com>

Date: Fri Feb 9 14:14:34 2018 +0000

Added README.md

commit f9b5dadca880808ea1e73e252cc8ee236f98499e

Author: Bjoern Stiel <bjoern.stiel@zoomeranalytics.com>

Date: Fri Feb 9 14:14:16 2018 +0000

Added ExtractNumber VBA function

commit 37b7bfde9464209217dc57214d16a6cd707d6f21

Author: Bjoern Stiel <bjoern.stiel@zoomeranalytics.com>

Date: Fri Feb 9 14:06:33 2018 +0000

First commit

So, you might wonder what changes were actually made in the latest commit “Added new VBA Module”? That is, you want to see the changes (the diff) between commits 04b45b99c883e5d184a20cfd73e4556ef8d06bfd and 429ee1ff383b8c706aa69c6a87f3a2c50fa1bcd1:

C:\Users\Bjoern>git diff 04b45b99c883e5d184a20cfd73e4556ef8d06bfd 429ee1ff383b8c706aa69c6a87f3a2c50fa1bcd1

diff --git a/Book1.xlsb b/Book1.xlsb
index 24f1c48..3a9d8e7 100644
Binary files a/Book1.xlsb and b/Book1.xlsb differ

C:\Users\Bjoern>git diff 04b45b99c883e5d184a20cfd73e4556ef8d06bfd 429ee1ff383b8c706aa69c6a87f3a2c50fa1bcd1

diff --git a/Book1.xlsb b/Book1.xlsb

index 24f1c48..3a9d8e7 100644

Binary files a/Book1.xlsb and b/Book1.xlsb differ

Well, that isn’t very helpful, is it? The problem is that Git does not understand Excel workbook files; to Git, an Excel workbook is just any binary file and therefore a black box.

Fortunately, thanks to Git’s extensibility and modularity, we can configure it to use another application to “diff” Excel file formats. Question is: Which application can diff Excel workbooks? Microsoft’s Spreadsheet Compare (which is part of Office Professional Plus and Office 365) is one option but there is quite a bit of technical DIY plumbing involved. If you are interested in the details, you can find them here.

In order to make Git Excel-ready almost out of the box, I created git-xltrail, a free, open-source Git extension. git-xltrail comes with a custom Excel workbook differ that understands the VBA code inside your workbook (handling spreadsheets is on the roadmap). And git-xltrail also takes care of the correct Git configuration so that all the Excel oddities (such as temporary files) are handled.

To get started, download and run the latest installer version. This installs the Git command-line extension, the workbook differ, and configures a few environment variables (more details can be found here). As soon as the installation is complete, open a new command line window:

C:\Users\Bjoern>git xltrail

git-xltrail/0.1.0 (windows; Python 3.6.4;)
git xltrail <command> [<args>]

Git xltrail is a system for managing Excel workbook files in
association with a Git repository. Git xltrail:
* installs a special git-diff for Excel files
* makes Git ignore temporary Excel files via .gitignore

Commands
--------

* git xltrail install:
    Install Git xltrail.
* git xltrail uninstall:
    Uninstall Git xltrail.
* git xltrail version:
    Report the version number.

C:\Users\Bjoern>git xltrail

git-xltrail/0.1.0 (windows; Python 3.6.4;)

git xltrail <command> [<args>]

Git xltrail is a system for managing Excel workbook files in

association with a Git repository. Git xltrail:

* installs a special git-diff for Excel files

* makes Git ignore temporary Excel files via .gitignore

Commands

--------

* git xltrail install:

Install Git xltrail.

* git xltrail uninstall:

Uninstall Git xltrail.

* git xltrail version:

Report the version number.

Run git xltrail install once to make git-xltrail work across all your (existing and future) repositories (the fire-and-forget solution). Alternatively, git-xltrail can be installed on a per-repository basis. In the repository’s root folder, run:

C:\Users\Bjoern>git xltrail install --local

1	C:\Users\Bjoern>git xltrail install --local

When installing git-xltrail in local mode, git-xltrail creates .gitattributes (or modifies in case it already exists); make sure .gitattributes is tracked as part of your repository.

With git-xltrail installed (either globally or locally in the example repository), revisit the example repository’s
commit history and compare the latest two versions. This time, you actually get a meaningful diff:

C:\Users\Bjoern>git diff 04b45b99c883e5d184a20cfd73e4556ef8d06bfd 429ee1ff383b8c706aa69c6a87f3a2c50fa1bcd1

diff --xltrail a/Book1.xlsb b/Book1.xlsb
--- /dev/null
+++ b/Book1.xlsb/VBA/Module2
+Option Explicit
+
+Function Count_once(Count_range As Range) As Long
+
+Dim strAddress As String
+Dim lMaxRow As Long, lEndCol As Long, lStartCol As Long
+Dim lColCount As Long
+Dim lLoop As Long, lArrElement As Long
+Dim lArray() As Long
+
+
+
+    lMaxRow = Rows.Count
+
+    lColCount = Count_range.Columns.Count
+
+    lEndCol = Count_range.Columns(lColCount).Column
+
+    ReDim lArray(lColCount)
+
+    lStartCol = Count_range.Columns(1).Column
+
+        For lLoop = lStartCol To lEndCol
+            lArray(lArrElement) = Cells(lMaxRow, lLoop).End(xlUp).Row
+            lArrElement = lArrElement + 1
+        Next lLoop
+
+        lMaxRow = WorksheetFunction.Max(lArray)
+
+        strAddress = Range(Count_range.Cells(1, 1), _
+            Cells(lMaxRow, lEndCol)).Address
+
+
+        Count_once = Evaluate("sumproduct((" & strAddress & "<>"""")/" _
+            & "countif(" & strAddress & "," & strAddress & "&""""))") - 1
+
+End Function
+
+

C:\Users\Bjoern>git diff 04b45b99c883e5d184a20cfd73e4556ef8d06bfd 429ee1ff383b8c706aa69c6a87f3a2c50fa1bcd1

diff --xltrail a/Book1.xlsb b/Book1.xlsb

--- /dev/null

+++ b/Book1.xlsb/VBA/Module2

+Option Explicit

+Function Count_once(Count_range As Range) As Long

+Dim strAddress As String

+Dim lMaxRow As Long, lEndCol As Long, lStartCol As Long

+Dim lColCount As Long

+Dim lLoop As Long, lArrElement As Long

+Dim lArray() As Long

+ lMaxRow = Rows.Count

+ lColCount = Count_range.Columns.Count

+ lEndCol = Count_range.Columns(lColCount).Column

+ ReDim lArray(lColCount)

+ lStartCol = Count_range.Columns(1).Column

+ For lLoop = lStartCol To lEndCol

+ lArray(lArrElement) = Cells(lMaxRow, lLoop).End(xlUp).Row

+ lArrElement = lArrElement + 1

+ Next lLoop

+ lMaxRow = WorksheetFunction.Max(lArray)

+ strAddress = Range(Count_range.Cells(1, 1), _

+ Cells(lMaxRow, lEndCol)).Address

+ Count_once = Evaluate("sumproduct((" & strAddress & "<>"""")/" _

+ & "countif(" & strAddress & "," & strAddress & "&""""))") - 1

+End Function

Much more helpful this time round. You can try it yourself by editing the VBA inside your working copy’s Book1.xlsb (and/or the text file README.md in the repository to see how it works when you edit Excel- and non-Excel files in the same commit) and compare your working copy versus your working version:

C:\Users\Bjoern>git diff

1	C:\Users\Bjoern>git diff

With this under your toolbelt, you can cross-check expected versus actual code changes and use branching so that you don’t mess up your production workbooks. In short, you can write better VBA code. What’s next for git-xltrail? We are planning to support merging (and to make our diffs support not only VBA but also worksheets). If you would like to see a feature, you are very welcome to open an issue or contribute to the project.

Documentation is available:

Userform Textbox Autocomplete

Posted on March 28, 2018 by Dick Kusleika

I’m working on a project where the user types some stuff into a textbox. A good portion of the time, what the user will type will match one of the last few things he typed. I wanted the textbox to autocomplete if there was a match to a list. Pretty simple, I think. For purposes of this demonstration, I’m going to match to a list of random sentences in a listbox.

Private mbEventsDisabled As Boolean

Private Sub TextBox1_Change()

    Dim vaRecent As Variant
    Dim i As Long
    Dim sEntered As String
    
    If Not mbEventsDisabled Then
        sEntered = Me.TextBox1.Text
        If Len(sEntered) <= 5 Then
            vaRecent = Me.ListBox1.List
            For i = LBound(vaRecent, 1) To UBound(vaRecent, 1)
                If Left$(vaRecent(i, 0), Len(sEntered)) = sEntered Then
                    mbEventsDisabled = True
                        With Me.TextBox1
                            .Text = vaRecent(i, 0)
                            .SelStart = Len(sEntered)
                            .SelLength = Len(vaRecent(i, 0))
                        End With
                    mbEventsDisabled = False
                    Exit For
                End If
            Next i
        End If
    End If
    
End Sub

Private mbEventsDisabled As Boolean

Private Sub TextBox1_Change()

Dim vaRecent As Variant

Dim i As Long

Dim sEntered As String

If Not mbEventsDisabled Then

sEntered = Me.TextBox1.Text

If Len(sEntered) <= 5 Then

vaRecent = Me.ListBox1.List

For i = LBound(vaRecent, 1) To UBound(vaRecent, 1)

If Left$(vaRecent(i, 0), Len(sEntered)) = sEntered Then

mbEventsDisabled = True

With Me.TextBox1

.Text = vaRecent(i, 0)

.SelStart = Len(sEntered)

.SelLength = Len(vaRecent(i, 0))

End With

mbEventsDisabled = False

Exit For

End If

Next i

End If

End Sub

I had to use that old disable events in a userform trick otherwise setting the .Text property would call the change event again.

I only look at the first five characters. After that, you just have to type what you want. If there’s a match, I set the .Text property to the matching sentence and set the selection so that the user can continue typing. It all worked very nicely except for backspacing. In the above screenshot, I’ve typed He but the textbox contains the whole sentence. If I hit backspace in this situation, I delete the highlighted portion and I’m left with He. Backspace does nothing.

I was hoping to find a simple and elegant solution. Instead, I did this.

Private bBackSpace As Boolean
Private mbEventsDisabled As Boolean

Private Sub TextBox1_Change()

    Dim vaRecent As Variant
    Dim i As Long
    Dim sEntered As String
    
    If Not mbEventsDisabled Then

        sEntered = Me.TextBox1.Text
        If bBackSpace And Len(sEntered) > 0 Then sEntered = Left$(sEntered, Len(sEntered) - 1)
        
        If Len(sEntered) < 5 Then
            vaRecent = Me.ListBox1.List
            
            For i = LBound(vaRecent, 1) To UBound(vaRecent, 1)
                If Left$(vaRecent(i, 0), Len(sEntered)) = sEntered Then
                    mbEventsDisabled = True
                        With Me.TextBox1
                            .Text = vaRecent(i, 0)
                            .SelStart = Len(sEntered)
                            .SelLength = Len(vaRecent(i, 0))
                        End With
                    mbEventsDisabled = False
                    Exit For
                End If
            Next i
        End If
    End If
    
End Sub

Private Sub TextBox1_KeyDown(ByVal KeyCode As MSForms.ReturnInteger, ByVal Shift As Integer)
    bBackSpace = KeyCode = 8
End Sub

Private bBackSpace As Boolean

Private mbEventsDisabled As Boolean

Private Sub TextBox1_Change()

Dim vaRecent As Variant

Dim i As Long

Dim sEntered As String

If Not mbEventsDisabled Then

sEntered = Me.TextBox1.Text

If bBackSpace And Len(sEntered) > 0 Then sEntered = Left$(sEntered, Len(sEntered) - 1)

If Len(sEntered) < 5 Then

vaRecent = Me.ListBox1.List

For i = LBound(vaRecent, 1) To UBound(vaRecent, 1)

If Left$(vaRecent(i, 0), Len(sEntered)) = sEntered Then

mbEventsDisabled = True

With Me.TextBox1

.Text = vaRecent(i, 0)

.SelStart = Len(sEntered)

.SelLength = Len(vaRecent(i, 0))

End With

mbEventsDisabled = False

Exit For

End If

Next i

End If

End Sub

Private Sub TextBox1_KeyDown(ByVal KeyCode As MSForms.ReturnInteger, ByVal Shift As Integer)

bBackSpace = KeyCode = 8

End Sub

I’m using a module-level variable to determine if the backspace was pressed while in the textbox. If it was and there’s still at least one character, I simply shorten the sEntered variable by one character. That leaves the whole SelStart and SelLength mechanism working as expected.

Todo.txt TDD Part 3

Posted on March 14, 2018March 23, 2018 by Dick Kusleika

As mentioned at the end of Part 2, after the creation date, the rest of the string is called the Description. It can contain projects that start with a plus sign(+) or contexts that start with an at symbol(@) or key/value pairs with a colon(:). We’ll test the projects piece now.

Sub TEST_Projects()

    Dim clsTodo As CTodo
    
    Set clsTodo = New CTodo
    clsTodo.Raw = "(A) 2016-04-30 Call Mom+Dad due:2016-05-30"
    Debug.Assert clsTodo.Projects.Count = 0
    
    Set clsTodo = New CTodo
    clsTodo.Raw = "(A) 2016-05-20 2016-04-30 Call Mom @Phone +Family due:2016-05-30"
    Debug.Assert clsTodo.Projects.Count = 1
    Debug.Assert clsTodo.Projects(1).Tag = "Family"
    
    Set clsTodo = New CTodo
    clsTodo.Raw = "(A) 2016-04-30 Call Mom +Phone +Family due:2016-05-30"
    Debug.Assert clsTodo.Projects.Count = 2
    
    Debug.Print "TEST_Projects"

End Sub

Sub TEST_Projects()

Dim clsTodo As CTodo

Set clsTodo = New CTodo

clsTodo.Raw = "(A) 2016-04-30 Call Mom+Dad due:2016-05-30"

Debug.Assert clsTodo.Projects.Count = 0

Set clsTodo = New CTodo

clsTodo.Raw = "(A) 2016-05-20 2016-04-30 Call Mom @Phone +Family due:2016-05-30"

Debug.Assert clsTodo.Projects.Count = 1

Debug.Assert clsTodo.Projects(1).Tag = "Family"

Set clsTodo = New CTodo

clsTodo.Raw = "(A) 2016-04-30 Call Mom +Phone +Family due:2016-05-30"

Debug.Assert clsTodo.Projects.Count = 2

Debug.Print "TEST_Projects"

End Sub

I’m testing zero, one, and two projects. Now let’s update Raw to make this pass

Public Property Let Raw(ByVal sRaw As String)
    
    Dim vaSplit As Variant
    Dim lNext As Long
    Dim i As Long
    Dim clsProject As CProject
    
    vaSplit = Split(sRaw, Space(1))
        
    Me.Complete = vaSplit(0) = "x"
            
    If vaSplit(0) = "x" Then
        lNext = lNext + 1
    End If

    If vaSplit(lNext) Like "([A-Z])" Then
        Me.Priority = Mid$(vaSplit(lNext), 2, 1)
        lNext = lNext + 1
    End If

    If IsDate(vaSplit(lNext)) Then
        If IsDate(vaSplit(lNext + 1)) Then
            Me.CompleteDate = DateValue(vaSplit(lNext))
            Me.CreationDate = DateValue(vaSplit(lNext + 1))
            lNext = lNext + 2
        Else
            Me.CreationDate = DateValue(vaSplit(lNext))
            lNext = lNext + 1
        End If
    End If
    
    For i = lNext To UBound(vaSplit)
        If Left$(vaSplit(i), 1) = "+" Then
            Set clsProject = New CProject
            clsProject.Tag = Mid$(vaSplit(i), 2, Len(vaSplit(i)))
            Me.Projects.Add clsProject
        End If
    Next i
    
End Property

Public Property Let Raw(ByVal sRaw As String)

Dim vaSplit As Variant

Dim lNext As Long

Dim i As Long

Dim clsProject As CProject

vaSplit = Split(sRaw, Space(1))

Me.Complete = vaSplit(0) = "x"

If vaSplit(0) = "x" Then

lNext = lNext + 1

End If

If vaSplit(lNext) Like "([A-Z])" Then

Me.Priority = Mid$(vaSplit(lNext), 2, 1)

lNext = lNext + 1

End If

If IsDate(vaSplit(lNext)) Then

If IsDate(vaSplit(lNext + 1)) Then

Me.CompleteDate = DateValue(vaSplit(lNext))

Me.CreationDate = DateValue(vaSplit(lNext + 1))

lNext = lNext + 2

Else

Me.CreationDate = DateValue(vaSplit(lNext))

lNext = lNext + 1

End If

For i = lNext To UBound(vaSplit)

If Left$(vaSplit(i), 1) = "+" Then

Set clsProject = New CProject

clsProject.Tag = Mid$(vaSplit(i), 2, Len(vaSplit(i)))

Me.Projects.Add clsProject

End If

Next i

End Property

This loops through the rest of the elements of the split array and looks for a plus sign at the start. If it finds one, it creates a Project instance and adds it to the Projects collection class. The contexts will be handled similarly.

Sub TEST_Contexts()

    Dim clsTodo As CTodo
    
    Set clsTodo = New CTodo
    clsTodo.Raw = "(A) 2016-04-30 Call Mom@home due:2016-05-30"
    Debug.Assert clsTodo.Contexts.Count = 0
    
    Set clsTodo = New CTodo
    clsTodo.Raw = "(A) 2016-05-20 2016-04-30 Call Mom @Phone +Family due:2016-05-30"
    Debug.Assert clsTodo.Contexts.Count = 1
    Debug.Assert clsTodo.Contexts(1).Tag = "Phone"
    
    Set clsTodo = New CTodo
    clsTodo.Raw = "(A) 2016-04-30 Call Mom @Phone @Family due:2016-05-30"
    Debug.Assert clsTodo.Contexts.Count = 2
    
    Debug.Print "TEST_Contexts"

End Sub

Sub TEST_Contexts()

Dim clsTodo As CTodo

Set clsTodo = New CTodo

clsTodo.Raw = "(A) 2016-04-30 Call Mom@home due:2016-05-30"

Debug.Assert clsTodo.Contexts.Count = 0

Set clsTodo = New CTodo

clsTodo.Raw = "(A) 2016-05-20 2016-04-30 Call Mom @Phone +Family due:2016-05-30"

Debug.Assert clsTodo.Contexts.Count = 1

Debug.Assert clsTodo.Contexts(1).Tag = "Phone"

Set clsTodo = New CTodo

clsTodo.Raw = "(A) 2016-04-30 Call Mom @Phone @Family due:2016-05-30"

Debug.Assert clsTodo.Contexts.Count = 2

Debug.Print "TEST_Contexts"

End Sub

Public Property Let Raw(ByVal sRaw As String)
    
    Dim vaSplit As Variant
    Dim lNext As Long
    Dim i As Long
    Dim clsProject As CProject
    Dim clsContext As CContext
    
    vaSplit = Split(sRaw, Space(1))
        
    Me.Complete = vaSplit(0) = "x"
            
    If vaSplit(0) = "x" Then
        lNext = lNext + 1
    End If

    If vaSplit(lNext) Like "([A-Z])" Then
        Me.Priority = Mid$(vaSplit(lNext), 2, 1)
        lNext = lNext + 1
    End If

    If IsDate(vaSplit(lNext)) Then
        If IsDate(vaSplit(lNext + 1)) Then
            Me.CompleteDate = DateValue(vaSplit(lNext))
            Me.CreationDate = DateValue(vaSplit(lNext + 1))
            lNext = lNext + 2
        Else
            Me.CreationDate = DateValue(vaSplit(lNext))
            lNext = lNext + 1
        End If
    End If
    
    For i = lNext To UBound(vaSplit)
        If Left$(vaSplit(i), 1) = "+" Then
            Set clsProject = New CProject
            clsProject.Tag = Mid$(vaSplit(i), 2, Len(vaSplit(i)))
            Me.Projects.Add clsProject
        ElseIf Left$(vaSplit(i), 1) = "@" Then
            Set clsContext = New CContext
            clsContext.Tag = Mid$(vaSplit(i), 2, Len(vaSplit(i)))
            Me.Contexts.Add clsContext
        End If
    Next i
    
End Property

Public Property Let Raw(ByVal sRaw As String)

Dim vaSplit As Variant

Dim lNext As Long

Dim i As Long

Dim clsProject As CProject

Dim clsContext As CContext

vaSplit = Split(sRaw, Space(1))

Me.Complete = vaSplit(0) = "x"

If vaSplit(0) = "x" Then

lNext = lNext + 1

End If

If vaSplit(lNext) Like "([A-Z])" Then

Me.Priority = Mid$(vaSplit(lNext), 2, 1)

lNext = lNext + 1

End If

If IsDate(vaSplit(lNext)) Then

If IsDate(vaSplit(lNext + 1)) Then

Me.CompleteDate = DateValue(vaSplit(lNext))

Me.CreationDate = DateValue(vaSplit(lNext + 1))

lNext = lNext + 2

Else

Me.CreationDate = DateValue(vaSplit(lNext))

lNext = lNext + 1

End If

For i = lNext To UBound(vaSplit)

If Left$(vaSplit(i), 1) = "+" Then

Set clsProject = New CProject

clsProject.Tag = Mid$(vaSplit(i), 2, Len(vaSplit(i)))

Me.Projects.Add clsProject

ElseIf Left$(vaSplit(i), 1) = "@" Then

Set clsContext = New CContext

clsContext.Tag = Mid$(vaSplit(i), 2, Len(vaSplit(i)))

Me.Contexts.Add clsContext

End If

Next i

End Property

The final special case inside the description is key/value pairs.

Sub TEST_KeyValue()

    Dim clsTodo As CTodo
    
    Set clsTodo = New CTodo
    clsTodo.Raw = "(A) 2016-04-30 Call Mom@home"
    Debug.Assert clsTodo.KeyValues.Count = 0
    
    Set clsTodo = New CTodo
    clsTodo.Raw = "(A) 2016-05-20 2016-04-30 Call Mom @Phone +Family due:2016-05-30"
    Debug.Assert clsTodo.KeyValues.Count = 1
    Debug.Assert clsTodo.KeyValues(1).Key = "due"
    Debug.Assert clsTodo.KeyValues(1).Value = "2016-05-30"
    
    Set clsTodo = New CTodo
    clsTodo.Raw = "(A) 2016-04-30 Call Mom @Phone @Family due:2016-05-30 key:val:ue"
    Debug.Assert clsTodo.KeyValues.Count = 2
    
    Debug.Print "TEST_KeyValue"

End Sub

Sub TEST_KeyValue()

Dim clsTodo As CTodo

Set clsTodo = New CTodo

clsTodo.Raw = "(A) 2016-04-30 Call Mom@home"

Debug.Assert clsTodo.KeyValues.Count = 0

Set clsTodo = New CTodo

clsTodo.Raw = "(A) 2016-05-20 2016-04-30 Call Mom @Phone +Family due:2016-05-30"

Debug.Assert clsTodo.KeyValues.Count = 1

Debug.Assert clsTodo.KeyValues(1).Key = "due"

Debug.Assert clsTodo.KeyValues(1).Value = "2016-05-30"

Set clsTodo = New CTodo

clsTodo.Raw = "(A) 2016-04-30 Call Mom @Phone @Family due:2016-05-30 key:val:ue"

Debug.Assert clsTodo.KeyValues.Count = 2

Debug.Print "TEST_KeyValue"

End Sub

Again I’m testing zero, one, and two instances.

Public Property Let Raw(ByVal sRaw As String)
    
    Dim vaSplit As Variant
    Dim lNext As Long
    Dim i As Long
    Dim clsProject As CProject
    Dim clsContext As CContext
    Dim clsKeyValue As CKeyValue
    Dim vaKeys As Variant
    
    vaSplit = Split(sRaw, Space(1))
        
    Me.Complete = vaSplit(0) = "x"
            
    If vaSplit(0) = "x" Then
        lNext = lNext + 1
    End If

    If vaSplit(lNext) Like "([A-Z])" Then
        Me.Priority = Mid$(vaSplit(lNext), 2, 1)
        lNext = lNext + 1
    End If

    If IsDate(vaSplit(lNext)) Then
        If IsDate(vaSplit(lNext + 1)) Then
            Me.CompleteDate = DateValue(vaSplit(lNext))
            Me.CreationDate = DateValue(vaSplit(lNext + 1))
            lNext = lNext + 2
        Else
            Me.CreationDate = DateValue(vaSplit(lNext))
            lNext = lNext + 1
        End If
    End If
    
    For i = lNext To UBound(vaSplit)
        If Left$(vaSplit(i), 1) = "+" Then
            Set clsProject = New CProject
            clsProject.Tag = Mid$(vaSplit(i), 2, Len(vaSplit(i)))
            Me.Projects.Add clsProject
        ElseIf Left$(vaSplit(i), 1) = "@" Then
            Set clsContext = New CContext
            clsContext.Tag = Mid$(vaSplit(i), 2, Len(vaSplit(i)))
            Me.Contexts.Add clsContext
        ElseIf InStr(1, vaSplit(i), ":") > 1 Then
            vaKeys = Split(vaSplit(i), ":", 2)
            Set clsKeyValue = New CKeyValue
            clsKeyValue.Key = vaKeys(0)
            clsKeyValue.Value = vaKeys(1)
            Me.KeyValues.Add clsKeyValue
        End If
    Next i
    
End Property

Public Property Let Raw(ByVal sRaw As String)

Dim vaSplit As Variant

Dim lNext As Long

Dim i As Long

Dim clsProject As CProject

Dim clsContext As CContext

Dim clsKeyValue As CKeyValue

Dim vaKeys As Variant

vaSplit = Split(sRaw, Space(1))

Me.Complete = vaSplit(0) = "x"

If vaSplit(0) = "x" Then

lNext = lNext + 1

End If

If vaSplit(lNext) Like "([A-Z])" Then

Me.Priority = Mid$(vaSplit(lNext), 2, 1)

lNext = lNext + 1

End If

If IsDate(vaSplit(lNext)) Then

If IsDate(vaSplit(lNext + 1)) Then

Me.CompleteDate = DateValue(vaSplit(lNext))

Me.CreationDate = DateValue(vaSplit(lNext + 1))

lNext = lNext + 2

Else

Me.CreationDate = DateValue(vaSplit(lNext))

lNext = lNext + 1

End If

For i = lNext To UBound(vaSplit)

If Left$(vaSplit(i), 1) = "+" Then

Set clsProject = New CProject

clsProject.Tag = Mid$(vaSplit(i), 2, Len(vaSplit(i)))

Me.Projects.Add clsProject

ElseIf Left$(vaSplit(i), 1) = "@" Then

Set clsContext = New CContext

clsContext.Tag = Mid$(vaSplit(i), 2, Len(vaSplit(i)))

Me.Contexts.Add clsContext

ElseIf InStr(1, vaSplit(i), ":") > 1 Then

vaKeys = Split(vaSplit(i), ":", 2)

Set clsKeyValue = New CKeyValue

clsKeyValue.Key = vaKeys(0)

clsKeyValue.Value = vaKeys(1)

Me.KeyValues.Add clsKeyValue

End If

Next i

End Property

Everything else is the description

Sub TEST_Description()

    Dim clsTodo As CTodo
    
    Set clsTodo = New CTodo
    clsTodo.Raw = "(A) 2016-04-30 Call Mom@home"
    Debug.Assert clsTodo.Desc = "Call Mom@home"
    
    Set clsTodo = New CTodo
    clsTodo.Raw = "(A) 2016-05-20 2016-04-30 Call Mom @Phone +Family due:2016-05-30"
    Debug.Assert clsTodo.Desc = "Call Mom"
    
    Set clsTodo = New CTodo
    clsTodo.Raw = "(A) 2016-04-30 Call Mom @Phone and Dad @Family due:2016-05-30 key:val:ue"
    Debug.Assert clsTodo.Desc = "Call Mom and Dad"
    
    Debug.Print "TEST_Description"

End Sub

Sub TEST_Description()

Dim clsTodo As CTodo

Set clsTodo = New CTodo

clsTodo.Raw = "(A) 2016-04-30 Call Mom@home"

Debug.Assert clsTodo.Desc = "Call Mom@home"

Set clsTodo = New CTodo

clsTodo.Raw = "(A) 2016-05-20 2016-04-30 Call Mom @Phone +Family due:2016-05-30"

Debug.Assert clsTodo.Desc = "Call Mom"

Set clsTodo = New CTodo

clsTodo.Raw = "(A) 2016-04-30 Call Mom @Phone and Dad @Family due:2016-05-30 key:val:ue"

Debug.Assert clsTodo.Desc = "Call Mom and Dad"

Debug.Print "TEST_Description"

End Sub

Here are the changes to the bottom of Raw

    For i = lNext To UBound(vaSplit)
        If Left$(vaSplit(i), 1) = "+" Then
            Set clsProject = New CProject
            clsProject.Tag = Mid$(vaSplit(i), 2, Len(vaSplit(i)))
            Me.Projects.Add clsProject
        ElseIf Left$(vaSplit(i), 1) = "@" Then
            Set clsContext = New CContext
            clsContext.Tag = Mid$(vaSplit(i), 2, Len(vaSplit(i)))
            Me.Contexts.Add clsContext
        ElseIf InStr(1, vaSplit(i), ":") > 1 Then
            vaKeys = Split(vaSplit(i), ":", 2)
            Set clsKeyValue = New CKeyValue
            clsKeyValue.Key = vaKeys(0)
            clsKeyValue.Value = vaKeys(1)
            Me.KeyValues.Add clsKeyValue
        Else
            Me.Desc = Me.Desc & vaSplit(i) & Space(1)
        End If
    Next i
    
    'remove the trailing space
    If Len(Me.Desc) > 1 Then
        Me.Desc = Left$(Me.Desc, Len(Me.Desc) - 1)
    End If

For i = lNext To UBound(vaSplit)

If Left$(vaSplit(i), 1) = "+" Then

Set clsProject = New CProject

clsProject.Tag = Mid$(vaSplit(i), 2, Len(vaSplit(i)))

Me.Projects.Add clsProject

ElseIf Left$(vaSplit(i), 1) = "@" Then

Set clsContext = New CContext

clsContext.Tag = Mid$(vaSplit(i), 2, Len(vaSplit(i)))

Me.Contexts.Add clsContext

ElseIf InStr(1, vaSplit(i), ":") > 1 Then

vaKeys = Split(vaSplit(i), ":", 2)

Set clsKeyValue = New CKeyValue

clsKeyValue.Key = vaKeys(0)

clsKeyValue.Value = vaKeys(1)

Me.KeyValues.Add clsKeyValue

Else

Me.Desc = Me.Desc & vaSplit(i) & Space(1)

End If

Next i

'remove the trailing space

If Len(Me.Desc) > 1 Then

Me.Desc = Left$(Me.Desc, Len(Me.Desc) - 1)

End If

And that’s it. A properly parsed Todo.txt string ready to be used in your application. And if I make an changes to my app, I can run these tests to make sure I didn’t break anything.

You can download TodoTxt.zip

Series:
Series:

Todo.txt TDD Part 2

Posted on March 14, 2018March 23, 2018 by Dick Kusleika

In Part I, I started writing tests and then writing code to make them pass. Let’s continue with more tests.

The next test will be for an incomplete todo with no priority and a completion date.

Sub TEST_NotCompleteNoPriorityCompletionDate()

    Dim clsTodo As CTodo
    
    Set clsTodo = New CTodo
    clsTodo.Raw = "2016-05-20 Call Mom @Phone +Family due:2016-05-30"
    Debug.Assert Not clsTodo.Complete
    Debug.Assert clsTodo.Priority = vbNullString
    Debug.Assert clsTodo.CompleteDate = DateSerial(2016, 5, 20)
    
    Debug.Print "TEST_NotCompleteNoPriorityCompletionDate"

End Sub

Sub TEST_NotCompleteNoPriorityCompletionDate()

Dim clsTodo As CTodo

Set clsTodo = New CTodo

clsTodo.Raw = "2016-05-20 Call Mom @Phone +Family due:2016-05-30"

Debug.Assert Not clsTodo.Complete

Debug.Assert clsTodo.Priority = vbNullString

Debug.Assert clsTodo.CompleteDate = DateSerial(2016, 5, 20)

Debug.Print "TEST_NotCompleteNoPriorityCompletionDate"

End Sub

Hey, it already passes. Let’s add some tests for when there’s a priority and a completion date

Sub TEST_CompletePriorityCompletionDate()

    Dim clsTodo As CTodo
    
    Set clsTodo = New CTodo
    clsTodo.Raw = "x (A) 2016-05-20 Call Mom @Phone +Family due:2016-05-30"
    Debug.Assert clsTodo.Complete
    Debug.Assert clsTodo.Priority = "A"
    Debug.Assert clsTodo.CompleteDate = DateSerial(2016, 5, 20)
    
    Debug.Print "TEST_CompletePriorityCompletionDate"

End Sub

Sub TEST_NotCompletePriorityCompletionDate()

    Dim clsTodo As CTodo
    
    Set clsTodo = New CTodo
    clsTodo.Raw = "(A) 2016-05-20 Call Mom @Phone +Family due:2016-05-30"
    Debug.Assert Not clsTodo.Complete
    Debug.Assert clsTodo.Priority = "A"
    Debug.Assert clsTodo.CompleteDate = DateSerial(2016, 5, 20)
    
    Debug.Print "TEST_NotCompletePriorityCompletionDate"

End Sub

Sub TEST_CompletePriorityCompletionDate()

Dim clsTodo As CTodo

Set clsTodo = New CTodo

clsTodo.Raw = "x (A) 2016-05-20 Call Mom @Phone +Family due:2016-05-30"

Debug.Assert clsTodo.Complete

Debug.Assert clsTodo.Priority = "A"

Debug.Assert clsTodo.CompleteDate = DateSerial(2016, 5, 20)

Debug.Print "TEST_CompletePriorityCompletionDate"

End Sub

Sub TEST_NotCompletePriorityCompletionDate()

Dim clsTodo As CTodo

Set clsTodo = New CTodo

clsTodo.Raw = "(A) 2016-05-20 Call Mom @Phone +Family due:2016-05-30"

Debug.Assert Not clsTodo.Complete

Debug.Assert clsTodo.Priority = "A"

Debug.Assert clsTodo.CompleteDate = DateSerial(2016, 5, 20)

Debug.Print "TEST_NotCompletePriorityCompletionDate"

End Sub

I expected these would already pass as a result of my refactoring, and they did. The next part of the spec says “Optional Creation Date, must be specified if completion date is”. First, I just want to test that it exists. That is, if there’s a completion date, there must be a creation date.

Sub TEST_CreationDateExists()

    Dim clsTodo As CTodo
    
    Set clsTodo = New CTodo
    clsTodo.Raw = "(A) 2016-05-20 2016-04-30 Call Mom @Phone +Family due:2016-05-30"
    Debug.Assert Not clsTodo.Complete
    Debug.Assert clsTodo.Priority = "A"
    Debug.Assert clsTodo.CompleteDate = DateSerial(2016, 5, 20)
    Debug.Assert clsTodo.CreationDate <> TimeSerial(0, 0, 0)
    
    Set clsTodo = New CTodo
    clsTodo.Raw = "(A) Call Mom @Phone +Family due:2016-05-30"
    Debug.Assert Not clsTodo.Complete
    Debug.Assert clsTodo.Priority = "A"
    Debug.Assert clsTodo.CompleteDate = TimeSerial(0, 0, 0)
    Debug.Assert clsTodo.CreationDate = TimeSerial(0, 0, 0)
    
    Debug.Print "TEST_CreationDateExists"

End Sub

Sub TEST_CreationDateExists()

Dim clsTodo As CTodo

Set clsTodo = New CTodo

clsTodo.Raw = "(A) 2016-05-20 2016-04-30 Call Mom @Phone +Family due:2016-05-30"

Debug.Assert Not clsTodo.Complete

Debug.Assert clsTodo.Priority = "A"

Debug.Assert clsTodo.CompleteDate = DateSerial(2016, 5, 20)

Debug.Assert clsTodo.CreationDate <> TimeSerial(0, 0, 0)

Set clsTodo = New CTodo

clsTodo.Raw = "(A) Call Mom @Phone +Family due:2016-05-30"

Debug.Assert Not clsTodo.Complete

Debug.Assert clsTodo.Priority = "A"

Debug.Assert clsTodo.CompleteDate = TimeSerial(0, 0, 0)

Debug.Assert clsTodo.CreationDate = TimeSerial(0, 0, 0)

Debug.Print "TEST_CreationDateExists"

End Sub

This fails because I haven’t parsed the creation date yet. So let’s do that.

Public Property Let Raw(ByVal sRaw As String)
    
    Dim vaSplit As Variant
    Dim lNext As Long
    
    vaSplit = Split(sRaw, Space(1))
        
    Me.Complete = vaSplit(0) = "x"
            
    If vaSplit(0) = "x" Then
        lNext = lNext + 1
    End If

    If vaSplit(lNext) Like "([A-Z])" Then
        Me.Priority = Mid$(vaSplit(lNext), 2, 1)
        lNext = lNext + 1
    End If

    If IsDate(vaSplit(lNext)) Then
        If IsDate(vaSplit(lNext + 1)) Then
            Me.CompleteDate = DateValue(vaSplit(lNext))
            Me.CreationDate = DateValue(vaSplit(lNext + 1))
            lNext = lNext + 2
        Else
            Me.CreationDate = DateValue(vaSplit(lNext))
            lNext = lNext + 1
        End If
    End If
    
End Property

Public Property Let Raw(ByVal sRaw As String)

Dim vaSplit As Variant

Dim lNext As Long

vaSplit = Split(sRaw, Space(1))

Me.Complete = vaSplit(0) = "x"

If vaSplit(0) = "x" Then

lNext = lNext + 1

End If

If vaSplit(lNext) Like "([A-Z])" Then

Me.Priority = Mid$(vaSplit(lNext), 2, 1)

lNext = lNext + 1

End If

If IsDate(vaSplit(lNext)) Then

If IsDate(vaSplit(lNext + 1)) Then

Me.CompleteDate = DateValue(vaSplit(lNext))

Me.CreationDate = DateValue(vaSplit(lNext + 1))

lNext = lNext + 2

Else

Me.CreationDate = DateValue(vaSplit(lNext))

lNext = lNext + 1

End If

End Property

My test passes, but I broke a previous one. In my prior completion date testing, I didn’t include a creation date because I wasn’t that far in the spec yet. I need to rewrite those tests

ub TEST_CompleteNoPriorityCompletionDate()

    Dim clsTodo As CTodo
    
    Set clsTodo = New CTodo
    clsTodo.Raw = "x 2016-05-20 2016-04-30 Call Mom @Phone +Family due:2016-05-30"
    Debug.Assert clsTodo.Complete
    Debug.Assert clsTodo.Priority = vbNullString
    Debug.Assert clsTodo.CompleteDate = DateSerial(2016, 5, 20)
    
    Debug.Print "TEST_CompleteNoPriorityCompletionDate"

End Sub

Sub TEST_NotCompleteNoPriorityCompletionDate()

    Dim clsTodo As CTodo
    
    Set clsTodo = New CTodo
    clsTodo.Raw = "2016-05-20 2016-04-30 Call Mom @Phone +Family due:2016-05-30"
    Debug.Assert Not clsTodo.Complete
    Debug.Assert clsTodo.Priority = vbNullString
    Debug.Assert clsTodo.CompleteDate = DateSerial(2016, 5, 20)
    
    Debug.Print "TEST_NotCompleteNoPriorityCompletionDate"

End Sub

Sub TEST_CompletePriorityCompletionDate()

    Dim clsTodo As CTodo
    
    Set clsTodo = New CTodo
    clsTodo.Raw = "x (A) 2016-05-20 2016-04-30 Call Mom @Phone +Family due:2016-05-30"
    Debug.Assert clsTodo.Complete
    Debug.Assert clsTodo.Priority = "A"
    Debug.Assert clsTodo.CompleteDate = DateSerial(2016, 5, 20)
    
    Debug.Print "TEST_CompletePriorityCompletionDate"

End Sub

Sub TEST_NotCompletePriorityCompletionDate()

    Dim clsTodo As CTodo
    
    Set clsTodo = New CTodo
    clsTodo.Raw = "(A) 2016-05-20 2016-04-30 Call Mom @Phone +Family due:2016-05-30"
    Debug.Assert Not clsTodo.Complete
    Debug.Assert clsTodo.Priority = "A"
    Debug.Assert clsTodo.CompleteDate = DateSerial(2016, 5, 20)
    
    Debug.Print "TEST_NotCompletePriorityCompletionDate"

End Sub

ub TEST_CompleteNoPriorityCompletionDate()

Dim clsTodo As CTodo

Set clsTodo = New CTodo

clsTodo.Raw = "x 2016-05-20 2016-04-30 Call Mom @Phone +Family due:2016-05-30"

Debug.Assert clsTodo.Complete

Debug.Assert clsTodo.Priority = vbNullString

Debug.Assert clsTodo.CompleteDate = DateSerial(2016, 5, 20)

Debug.Print "TEST_CompleteNoPriorityCompletionDate"

End Sub

Sub TEST_NotCompleteNoPriorityCompletionDate()

Dim clsTodo As CTodo

Set clsTodo = New CTodo

clsTodo.Raw = "2016-05-20 2016-04-30 Call Mom @Phone +Family due:2016-05-30"

Debug.Assert Not clsTodo.Complete

Debug.Assert clsTodo.Priority = vbNullString

Debug.Assert clsTodo.CompleteDate = DateSerial(2016, 5, 20)

Debug.Print "TEST_NotCompleteNoPriorityCompletionDate"

End Sub

Sub TEST_CompletePriorityCompletionDate()

Dim clsTodo As CTodo

Set clsTodo = New CTodo

clsTodo.Raw = "x (A) 2016-05-20 2016-04-30 Call Mom @Phone +Family due:2016-05-30"

Debug.Assert clsTodo.Complete

Debug.Assert clsTodo.Priority = "A"

Debug.Assert clsTodo.CompleteDate = DateSerial(2016, 5, 20)

Debug.Print "TEST_CompletePriorityCompletionDate"

End Sub

Sub TEST_NotCompletePriorityCompletionDate()

Dim clsTodo As CTodo

Set clsTodo = New CTodo

clsTodo.Raw = "(A) 2016-05-20 2016-04-30 Call Mom @Phone +Family due:2016-05-30"

Debug.Assert Not clsTodo.Complete

Debug.Assert clsTodo.Priority = "A"

Debug.Assert clsTodo.CompleteDate = DateSerial(2016, 5, 20)

Debug.Print "TEST_NotCompletePriorityCompletionDate"

End Sub

I added a creation date to the Raw for each of those tests, and now all tests pass. Now I can move on to testing what the creation date actually is.

Sub TEST_CreationDate()

    Dim clsTodo As CTodo
    
    Set clsTodo = New CTodo
    clsTodo.Raw = "(A) 2016-05-20 2016-04-30 Call Mom @Phone +Family due:2016-05-30"
    Debug.Assert Not clsTodo.Complete
    Debug.Assert clsTodo.Priority = "A"
    Debug.Assert clsTodo.CompleteDate = DateSerial(2016, 5, 20)
    Debug.Assert clsTodo.CreationDate = DateSerial(2016, 4, 30)
    
    Set clsTodo = New CTodo
    clsTodo.Raw = "(A) 2016-04-30 Call Mom @Phone +Family due:2016-05-30"
    Debug.Assert Not clsTodo.Complete
    Debug.Assert clsTodo.Priority = "A"
    Debug.Assert clsTodo.CompleteDate = TimeSerial(0, 0, 0)
    Debug.Assert clsTodo.CreationDate = DateSerial(2016, 4, 30)
    
    Debug.Print "TEST_CreationDate"

End Sub

Sub TEST_CreationDate()

Dim clsTodo As CTodo

Set clsTodo = New CTodo

clsTodo.Raw = "(A) 2016-05-20 2016-04-30 Call Mom @Phone +Family due:2016-05-30"

Debug.Assert Not clsTodo.Complete

Debug.Assert clsTodo.Priority = "A"

Debug.Assert clsTodo.CompleteDate = DateSerial(2016, 5, 20)

Debug.Assert clsTodo.CreationDate = DateSerial(2016, 4, 30)

Set clsTodo = New CTodo

clsTodo.Raw = "(A) 2016-04-30 Call Mom @Phone +Family due:2016-05-30"

Debug.Assert Not clsTodo.Complete

Debug.Assert clsTodo.Priority = "A"

Debug.Assert clsTodo.CompleteDate = TimeSerial(0, 0, 0)

Debug.Assert clsTodo.CreationDate = DateSerial(2016, 4, 30)

Debug.Print "TEST_CreationDate"

End Sub

This test already passes. Once I get past the creation date, the rest of the string is called the Description. It can contain projects that start with a plus sign(+) or contexts that start with an at symbol(@) or key/value pairs with a colon(:). We’ll test those in the next part.

You can download TodoTxt.zip

Series:
Series:

Todo.txt TDD Part 1

Posted on March 14, 2018March 23, 2018 by Dick Kusleika

Earlier, I wrote a post inviting you to try your hand at test-first development. This post is the first in a series of how I did it. In the previous post, I had all the tests written, but here I’m starting from scratch and writing the tests as I go. Well, I’m not starting from scratch in that the classes are already set up. If you want to see what the classes look like, download the workbook from the previous post or the one at the bottom of this post.

First, create the property in CTodo that will parse the string. There’s nothing in it, but we’ll get to that shortly.

Public Property Let Raw(ByVal sRaw As String)
    
End Property

Public Property Let Raw(ByVal sRaw As String)

End Property

Write a test. This test will determine if the todo item is complete. Per the spec, the first thing in the string is an “x” if it’s complete

Sub TEST_Complete()
    
    Dim clsTodo As CTodo
    
    Set clsTodo = New CTodo
    clsTodo.Raw = "x (A) Call Mom @Phone +Family due:2016-05-30"
    Debug.Assert clsTodo.Complete
    
    Debug.Print "TEST_Complete"
    
End Sub

Sub TEST_Complete()

Dim clsTodo As CTodo

Set clsTodo = New CTodo

clsTodo.Raw = "x (A) Call Mom @Phone +Family due:2016-05-30"

Debug.Assert clsTodo.Complete

Debug.Print "TEST_Complete"

End Sub

Now write the simplest code to make the test pass. I probably could have written simpler code than this, but don’t get too hung up on that. Just write simple code and don’t try to solve the next test – only this test.

Public Property Let Raw(ByVal sRaw As String)
    
    Dim vaSplit As Variant
    
    vaSplit = Split(sRaw, Space(1))
        
    Me.Complete = vaSplit(0) = "x"

End Property

Public Property Let Raw(ByVal sRaw As String)

Dim vaSplit As Variant

vaSplit = Split(sRaw, Space(1))

Me.Complete = vaSplit(0) = "x"

End Property

When I split the string on a space, the Complete property is set to whether the first element is “x”. The test runs successfully. Next, write a test for incomplete todos.

Sub TEST_NotComplete()
    
    Dim clsTodo As CTodo
    
    Set clsTodo = New CTodo
    clsTodo.Raw = "(A) Call Mom @Phone +Family due:2016-05-30"
    Debug.Assert Not clsTodo.Complete
    
    Debug.Print "TEST_NotComplete"
    
End Sub

Sub TEST_NotComplete()

Dim clsTodo As CTodo

Set clsTodo = New CTodo

clsTodo.Raw = "(A) Call Mom @Phone +Family due:2016-05-30"

Debug.Assert Not clsTodo.Complete

Debug.Print "TEST_NotComplete"

End Sub

Oh goodness, that test already runs successfully. There’s no “x”, so Complete is set to False. Next, write a test for a completed todo with a priority. Per the spec, the first element after the optional “x” is a capital letter in parentheses.

Sub TEST_CompletePriority()
    
    Dim clsTodo As CTodo
    
    Set clsTodo = New CTodo
    clsTodo.Raw = "x (A) Call Mom @Phone +Family due:2016-05-30"
    Debug.Assert clsTodo.Complete
    Debug.Assert clsTodo.Priority = "A"
    
    Debug.Print "TEST_CompletePriority"
    
End Sub

Sub TEST_CompletePriority()

Dim clsTodo As CTodo

Set clsTodo = New CTodo

clsTodo.Raw = "x (A) Call Mom @Phone +Family due:2016-05-30"

Debug.Assert clsTodo.Complete

Debug.Assert clsTodo.Priority = "A"

Debug.Print "TEST_CompletePriority"

End Sub

This test fails on Debug.Assert clsTodo.Priority = "A", so it’s time to write the simplest code to make it pass.

Public Property Let Raw(ByVal sRaw As String)
    
    Dim vaSplit As Variant
    
    vaSplit = Split(sRaw, Space(1))
        
    Me.Complete = vaSplit(0) = "x"
        
    Me.Priority = Mid$(vaSplit(1), 2, 1)
    
End Property

Public Property Let Raw(ByVal sRaw As String)

Dim vaSplit As Variant

vaSplit = Split(sRaw, Space(1))

Me.Complete = vaSplit(0) = "x"

Me.Priority = Mid$(vaSplit(1), 2, 1)

End Property

The Priority property is set to the second character of the second element. The test passes. Did we break anything? Let’s see.

Sub TEST_All()
    
    TEST_Complete
    TEST_NotComplete
    TEST_CompletePriority

End Sub

Sub TEST_All()

TEST_Complete

TEST_NotComplete

TEST_CompletePriority

End Sub

Nope, everything passes so far. Time for the next test. Check the priority for an incomplete todo.

Sub TEST_NotCompletePriority()

    Dim clsTodo As CTodo
    
    Set clsTodo = New CTodo
    clsTodo.Raw = "(A) Call Mom @Phone +Family due:2016-05-30"
    Debug.Assert Not clsTodo.Complete
    Debug.Assert clsTodo.Priority = "A"
    
    Debug.Print "TEST_NotCompletePriority"

End Sub

Sub TEST_NotCompletePriority()

Dim clsTodo As CTodo

Set clsTodo = New CTodo

clsTodo.Raw = "(A) Call Mom @Phone +Family due:2016-05-30"

Debug.Assert Not clsTodo.Complete

Debug.Assert clsTodo.Priority = "A"

Debug.Print "TEST_NotCompletePriority"

End Sub

It fails, so let’s write some code

Public Property Let Raw(ByVal sRaw As String)
    
    Dim vaSplit As Variant
    
    vaSplit = Split(sRaw, Space(1))
        
    Me.Complete = vaSplit(0) = "x"
            
    If vaSplit(0) = "x" Then
        Me.Priority = Mid$(vaSplit(1), 2, 1)
    Else
        Me.Priority = Mid$(vaSplit(0), 2, 1)
    End If
    
End Property

Public Property Let Raw(ByVal sRaw As String)

Dim vaSplit As Variant

vaSplit = Split(sRaw, Space(1))

Me.Complete = vaSplit(0) = "x"

If vaSplit(0) = "x" Then

Me.Priority = Mid$(vaSplit(1), 2, 1)

Else

Me.Priority = Mid$(vaSplit(0), 2, 1)

End If

End Property

If my fist element is an “x”, get the second element, otherwise get the first element. Pretty simple and the test passes. Every test I write, I add to the TEST_All() procedure to make sure I don’t break any prior tests. The next part of the spec is an optional completion date. Let’s start with a completed todo with no priority and a completion date.

Sub TEST_CompleteNoPriorityCompletionDate()

    Dim clsTodo As CTodo
    
    Set clsTodo = New CTodo
    clsTodo.Raw = "x 2016-05-20 Call Mom @Phone +Family due:2016-05-30"
    Debug.Assert clsTodo.Complete
    Debug.Assert clsTodo.Priority = vbNullString
    Debug.Assert clsTodo.CompleteDate = DateSerial(2016, 5, 20)
    
    Debug.Print "TEST_CompleteNoPriorityCompletionDate"

End Sub

Sub TEST_CompleteNoPriorityCompletionDate()

Dim clsTodo As CTodo

Set clsTodo = New CTodo

clsTodo.Raw = "x 2016-05-20 Call Mom @Phone +Family due:2016-05-30"

Debug.Assert clsTodo.Complete

Debug.Assert clsTodo.Priority = vbNullString

Debug.Assert clsTodo.CompleteDate = DateSerial(2016, 5, 20)

Debug.Print "TEST_CompleteNoPriorityCompletionDate"

End Sub

Public Property Let Raw(ByVal sRaw As String)
    
    Dim vaSplit As Variant
    
    vaSplit = Split(sRaw, Space(1))
        
    Me.Complete = vaSplit(0) = "x"
            
    If vaSplit(0) = "x" Then
        If vaSplit(1) Like "([A-Z])" Then
            Me.Priority = Mid$(vaSplit(1), 2, 1)
        Else
            Me.CompleteDate = DateValue(vaSplit(1))
        End If
    Else
        If vaSplit(0) Like "([A-Z])" Then
            Me.Priority = Mid$(vaSplit(0), 2, 1)
            Me.CompleteDate = DateValue(vaSplit(1))
        Else
            Me.CompleteDate = DateValue(vaSplit(0))
        End If
    End If
    
End Property

Public Property Let Raw(ByVal sRaw As String)

Dim vaSplit As Variant

vaSplit = Split(sRaw, Space(1))

Me.Complete = vaSplit(0) = "x"

If vaSplit(0) = "x" Then

If vaSplit(1) Like "([A-Z])" Then

Me.Priority = Mid$(vaSplit(1), 2, 1)

Else

Me.CompleteDate = DateValue(vaSplit(1))

End If

Else

If vaSplit(0) Like "([A-Z])" Then

Me.Priority = Mid$(vaSplit(0), 2, 1)

Me.CompleteDate = DateValue(vaSplit(1))

Else

Me.CompleteDate = DateValue(vaSplit(0))

End If

End Property

My new test passes, but I get an error in one of my old ones. Plus this code is getting pretty ugly. When your code is ugly or repetitive, it’s time to refactor. Instead of a bunch of nested If’s, I’ll just move a pointer down the line.

Public Property Let Raw(ByVal sRaw As String)
    
    Dim vaSplit As Variant
    Dim lNext As Long
    
    vaSplit = Split(sRaw, Space(1))
        
    Me.Complete = vaSplit(0) = "x"
            
    If vaSplit(0) = "x" Then
        lNext = lNext + 1
    End If

    If vaSplit(lNext) Like "([A-Z])" Then
        Me.Priority = Mid$(vaSplit(lNext), 2, 1)
        lNext = lNext + 1
    End If

    If IsDate(vaSplit(lNext)) Then
        Me.CompleteDate = DateValue(vaSplit(lNext))
        lNext = lNext + 1
    End If
    
End Property

Public Property Let Raw(ByVal sRaw As String)

Dim vaSplit As Variant

Dim lNext As Long

vaSplit = Split(sRaw, Space(1))

Me.Complete = vaSplit(0) = "x"

If vaSplit(0) = "x" Then

lNext = lNext + 1

End If

If vaSplit(lNext) Like "([A-Z])" Then

Me.Priority = Mid$(vaSplit(lNext), 2, 1)

lNext = lNext + 1

End If

If IsDate(vaSplit(lNext)) Then

Me.CompleteDate = DateValue(vaSplit(lNext))

lNext = lNext + 1

End If

End Property

I use lNext to keep track of where I am in the array. If the first element is an “x”, I advance the pointer. Then I check vaSplit(lNext) rather than a specific element number. All my tests pass.

In the next installment, I keep writing tests, writing code, and refactoring.

The below workbook has all the tests and the completed Raw property. It also has a userform, but it’s not complete.

You can download TodoTxt.zip

Series:

Test First Todo.txt

Posted on March 11, 2018 by Dick Kusleika

In my ongoing struggle to find a todo list app I like, I took a look at Todo.txt. I ended up going back to GoodTodo, but I was intrigued by the text based system. I wanted to build something in Excel to be an interface to Todo.txt and I used a test-first methodology to parse the file. You may remember my first foray into test-first development when I converted numbers into words.

Later this week, I’ll post how I wrote the parser using tests. If you’ve ever wanted to try to write code using test-first, here’s your chance. Download the workbook below. I’ve setup all the classes and the tests. I even wrote enough code to make the first test pass. If you’re interested in this exercise, follow these steps:

Download, unzip, and open the workbook
Go to the MTest module and run the TEST_All sub and see that it passes
Uncomment each link in TEST_All one at a time
Run TEST_All to see that it doesn’t pass. If it passes, uncomment the next test
If it doesn’t pass, go to the Raw property in the CTodo module and write just enough code to get the test to pass
When your code looks cumbersome or you see a pattern emerge, refactor Raw
Repeat until all the lines in TEST_All are uncommented

Check back later this week to see what I came up with.

You can download TodoTxtTestFirst.zip

Ribbon customUI Text Editor

Posted on March 7, 2018 by Doug Glancy

Over at my site, yoursumbuddy, I’ve published an Excel addin for creating, editing and validating the XML that makes up Excel Ribbons. I developed it because I can’t install executables at work and want an addin that I can just run from Excel. It works on ribbons in workbooks and addins and runs in Excel 2010 and later.

Along the way I learned about XPATH, SAX, and DOM as they relate to XML and VBA. I’ve already forgotten what those things are and how they work, but they were important for a tool that downloads customUI/customUI14 XML, validates multiple errors, creates and/or modifies the .rel entries, and then uploads it all back into the zip file that is an Excel workbook. Whew. Should you care to learn more, my unpassword-protected code is available for your reading pleasure. If you poke around you’ll see that my code is built on the work of Jan Karel Pieterse, Ken Puls, Ron deBruin and even keepitcool. Thanks to all of them!

Here’s a couple of pictures of the addin form:

To learn more and download the addin, just visit the yoursumbuddy Ribbon customUI XML Editor page.

Designing a data entry sheet

Posted on March 1, 2018March 2, 2018 by jkpieterse

Hi!

It’s been quite a while, but finally I got round to publishing a new article on my site.
It is a nice trick to make full use of the Data Validation’s Input message option, without the popup message covering your sheet.
So not like this:

But like this:

Have a look at the article on my website

Daily Dose of Excel

Haphazardly Posted Excel Information and Other Stuff

Excel Version Control with Git

Userform Textbox Autocomplete

Todo.txt TDD Part 3

Todo.txt TDD Part 2

Todo.txt TDD Part 1

Test First Todo.txt

Ribbon customUI Text Editor

Designing a data entry sheet